/* -------------------------------------------------------------------------
   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
