HWA2_P5_1.sql

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

   PART 5.1
   Create the PROJECT_ASSIGNMENT as defined in the Metadata Dictionary
   documentation.  Establish the required referential integrity constraints.
   Populate the PROJECT_ASSIGNMENT table with the data provided
   (PROJECT_ASSIGNMENT_data.txt) using the BULK INSERT command.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- Create the PROJECT_ASSIGNMENT as defined in the Metadata Dictionary
-- documentation, establishing the required referential integrity
-- constraints.

CREATE TABLE PROJECT_ASSIGNMENT
(
	PersonId      INT      NOT NULL,
	ProjectId     NCHAR(6) NOT NULL,
	RoleId        NCHAR(5) NOT NULL,
	AssignedHours NUMERIC      NULL,
	ActualHours   NUMERIC      NULL

	CONSTRAINT PK_PROJECT_ASSIGNMENT
		PRIMARY KEY (PersonId, ProjectId, RoleId)

	CONSTRAINT FK_PROJECT_ASSIGNMENT_PERSON
		FOREIGN KEY (PersonId)
		REFERENCES PERSON(Id),

	CONSTRAINT FK_PROJECT_ASSIGNMENT_PROJECT
		FOREIGN KEY (ProjectId)
		REFERENCES PROJECT(Id),

	CONSTRAINT FK_PROJECT_ASSIGNMENT_ROLE
		FOREIGN KEY (RoleId)
		REFERENCES PROJECT_ROLE(Id)
)
GO

-- Populate the PROJECT_ASSIGNMENT table with the data provided
-- (PROJECT_ASSIGNMENT_data.txt) using the BULK INSERT command.

BULK INSERT PROJECT_ASSIGNMENT
	FROM 'T:\PROJECT_ASSIGNMENT_data.txt'
	WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' )
GO