HWA3_P1_1.sql

/* -------------------------------------------------------------------------
   Jade Cheng
   ICS 321 Assignment 3
   December 12, 2008

   PART 1.1
   Create a script that will create the PROFILE and PERSON_PROFILE tables as
   defined in the Metadata Dictionary documentation,  Entity Definitions,
   Attribute Definitions and Domain definitions. Ensure the relationships'
   are established with constraint commands. Populate the PROFILE table with
   the two profiles given in the business rule and defined in the Domain
   definitions document. Use the INSERT command. Populate the PERSON_PROFILE
   table with values that reflect personnel that have already issued badges,
   as noted in the BadgeData file. Provide the INSERT command to populate
   the PERSON_PROFILE table.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- First, create the PROFILE table.

CREATE TABLE PROFILE
(
	Id          NCHAR(4)      NOT NULL,
	Description NVARCHAR(100) NOT NULL

	CONSTRAINT PK_PROFILE
		PRIMARY KEY (Id)
)
GO

-- Next, populate the PROFILE table with the two profiles given in the
-- business rule and defined in the Domain definitions document.

INSERT INTO PROFILE (Id, Description) VALUES ('Eval', 'Evaluator')
INSERT INTO PROFILE (Id, Description) VALUES ('Scty', 'Badge Security')
GO

-- Create the PERSON_PROFILE table.

CREATE TABLE PERSON_PROFILE
(
	ProfileId      NCHAR(4) NOT NULL,
	PersonId       INT      NOT NULL,
	AssignmentDate DATETIME NOT NULL

	CONSTRAINT PK_PERSON_PROFILE
		PRIMARY KEY (ProfileId, PersonId)

	CONSTRAINT FK_PERSON_PROFILE_PROFILE
		FOREIGN KEY (ProfileId)
		REFERENCES PROFILE(Id),

	CONSTRAINT FK_PERSON_PROFILE_PERSON
		FOREIGN KEY (PersonId)
		REFERENCES PERSON(Id)
)
GO

-- Populate the PERSON_PROFILE table with values that reflect personnel
-- that have already issued badges, as noted in the BadgeData file.

-- TBD The BadgeData.xls file indicates the date when a badge was issued,
-- not a date when a person became a badge issuer.  This script will
-- assume this date to be the current date.

-- NOTE: I've used the SUBST command to assign the T: drive as the location
-- for my homework 3 project files.
--
-- e.g. C:\>SUBST T: \path\to\my\project\files

INSERT INTO PERSON_PROFILE
	SELECT * FROM OPENROWSET
	(
		'Microsoft.Jet.OLEDB.4.0',
		'Excel 8.0;Database=T:\BadgeData.xls;HDR=YES',
		'SELECT DISTINCT
			''Scty''    AS ProfileId,
			[Issued by] AS PersonId,
			NOW()       AS AssignmentDate
			FROM [Sheet1$]
			ORDER BY [Issued By]'
	)
GO