/* ------------------------------------------------------------------------- Yu Cheng ICS 321 Assignment 2 October 23, 2008 PART 6.1 Update the ORGANIZATION table as defined in the Metadata Dictionary documentation. Establish a recursive constraint as required. Create SQL scripts that will update the ORGANIZATION table to reflect the organization chart shown in Figure 2.8. ------------------------------------------------------------------------- */ USE DB9975 GO -- Create the ORGANIZATION table as defined in the Metadata Dictionary -- documentation, establishing a recursive constraint as required. ALTER TABLE ORGANIZATION ADD ParentId INT NULL CONSTRAINT FK_ORGANIZATION_ORGANIZATION FOREIGN KEY (ParentId) REFERENCES ORGANIZATION(Id) GO -- First, create a temporary procedure to improve readability when linking -- the childred to their parents in the organization chart structure. CREATE PROCEDURE Link(@Parent INT, @Child INT) AS UPDATE ORGANIZATION SET ParentId = @Parent WHERE Id = @Child GO -- Now, link all elements of the table. EXECUTE Link 1, 2 EXECUTE Link 1, 6 EXECUTE Link 1, 9 EXECUTE Link 1, 22 EXECUTE Link 1, 25 EXECUTE Link 1, 28 EXECUTE Link 2, 3 EXECUTE Link 2, 4 EXECUTE Link 2, 5 EXECUTE Link 6, 7 EXECUTE Link 6, 8 EXECUTE Link 9, 10 EXECUTE Link 9, 13 EXECUTE Link 9, 14 EXECUTE Link 9, 17 EXECUTE Link 10, 11 EXECUTE Link 10, 12 EXECUTE Link 14, 15 EXECUTE Link 14, 16 EXECUTE Link 17, 18 EXECUTE Link 17, 19 EXECUTE Link 17, 20 EXECUTE Link 17, 21 EXECUTE Link 22, 23 EXECUTE Link 22, 24 EXECUTE Link 25, 26 EXECUTE Link 25, 27 GO -- Finally, drop the temporary procedure. DROP PROCEDURE Link GO
