228
Part II: Building Databases and Working with Data
MgrID int FOREIGN KEY REFERENCES Employee(EmployeeID));INSERT INTO dbo.Employee
VALUES(1, 'Janet Jones', NULL),
(2, 'Tom Smith', 1),
(3, 'Ted Adams', 2),
(4, 'Mary Thomas', 2),
(5, 'Jack Jones', 2),
(6, 'Anita Kidder', 3),
(7, 'William Owens', 3),
(8, 'Sean Watson', 4),
(9, 'Brenda Jackson', 5),
(10, 'Frank Johnson', 5)The Employee sample table uses a self-join between an Employee and his manager, as
shown in the database diagram in Figure 9-6. The managers are also employees, of course,
and are listed in the same table. They link back to their managers, and so on, until the top
of the hierarchy is reached when there is a NULL value in the MgrID column. The sample
database is populated with three levels that can be used for sample queries.FIGURE 9-6
The database diagram of the Employee table shows the self-referencing foreign key
relationship.c09.indd 228c09.indd 228 7/30/2012 4:25:07 PM7/30/2012 4:25:07 PM
http://www.it-ebooks.info