HWA2_P4_1.sql

/* -------------------------------------------------------------------------
   Yu Cheng
   ICS 321 Assignment 2
   October 23, 2008

   PART 4.1
   Alter the Billet Table to Comply with the Metadata Dictionary
   documentation. Import the PersonnelAssignment MS access table. Using this
   table, update the BILLET with the appropriate organization ID. Establish
   the required PK/FK referential integrity constraint.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- Alter the Billet Table to Comply with the Metadata Dictionary
-- documentation. Temporarily allow NULL values.

ALTER TABLE BILLET
	ADD OrganizationId INT NULL
	CONSTRAINT FK_BILLET_ORGANIZATION
		FOREIGN KEY (OrganizationId)
		REFERENCES ORGANIZATION(Id)
GO

-- Import the PersonnelAssignment MS access table. Using this table, update
-- the BILLET with the appropriate organization ID.

UPDATE BILLET
	SET BILLET.OrganizationId = ORGANIZATION.Id

	FROM OPENROWSET
	(
		'Microsoft.Jet.OLEDB.4.0',
		'T:\PersonnelAssignments.mdb';'admin';'',
		'SELECT * FROM PerAssign'
	)
	AS MDB

	JOIN ORGANIZATION ON ORGANIZATION.Title = MDB.Organization

	WHERE BILLET.Title = MDB.Billet

GO

-- Ensure compliance with the Metadata Dictionary.  Do not allow NULL
-- values.  Note that two records in the BILLET table have NULL values for
-- their OrganizationId.  I'll first delete these records (as suggested
-- via email).

DELETE FROM BILLET
WHERE OrganizationId IS NULL
GO

-- Now it is possible to add the NOT NULL constraint.

ALTER TABLE BILLET
	ALTER COLUMN OrganizationId INT NOT NULL
GO