HWA2_P4_2.sql

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

   PART 4.2
   Create a script that will create the PERSON_DESIGNATE table.  Populate
   the PERSON_DESIGNATE table with the data provided in the imported table
   established in step 1 of part 4.  Assign constraints as defined in the
   Metadata Dictionary documentation.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- Create the PERSON_DESIGNATE table. Assign constraints as defined in the
-- Metadata Dictionary documentation.

CREATE TABLE PERSON_DESIGNATE
(
	BilletId  INT      NOT NULL,
	PersonId  INT      NOT NULL,
	StartDate DATETIME NOT NULL,
	EndDate   DATETIME     NULL

	CONSTRAINT PK_PERSON_DESIGNATE
		PRIMARY KEY (BilletId, PersonId, StartDate)

	CONSTRAINT FK_PERSON_DESIGNATE_PERSON
		FOREIGN KEY (PersonId)
		REFERENCES dbo.PERSON(Id),

	CONSTRAINT FK_PERSON_DESIGNATE_BILLET
		FOREIGN KEY (BilletId)
		REFERENCES dbo.BILLET(Id)
)
GO

-- Populate the PERSON_DESIGNATE table with the data provided in the
-- imported table established in step 1 of part 4.

INSERT INTO PERSON_DESIGNATE
	SELECT
		BILLET.Id AS BilletId,
		PERSON.Id AS PersonId,
		StartDate,
		EndDate

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

	JOIN BILLET ON MDB.[Billet]    = BILLET.Title
	JOIN PERSON ON MDB.[Person ID] = PERSON.Id
GO