HWA2_P2_1.sql

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

   PART 2.1
   Import the data from the excel Spread sheet BadgeData. Restructure the
   SHEET$ table to comply with Metadata Dictionary provided above.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- NOTE: The 'ENTITY DEFINITION HWA2F08.doc' does not indicate the
-- IssuingPerson field should have a foreign key relationship with the PERSON
-- table, but it seems likely this should be the case.  I'll add this
-- constraint in this script.
--
-- Furthermore, the 'DOMAIN DEFINTION HWA2F08.doc' states the following about
-- the Badge Issuer: "Must be authorized to issue a badge as noted in the
-- PERSON_PROFILE table".  But the PERSON_PROFILE table does not exist and is
-- not created in this assignment.  Therefore, this constraint is not
-- enforced here.

-- Create the badge table, complying with Metadata Dictionary.

CREATE TABLE BADGE
(
	Id            INT      NOT NULL,
	IssuingPerson INT      NOT NULL,
	IssueDate     DATETIME NOT NULL

	CONSTRAINT PK_BADGE
		PRIMARY KEY (Id)

	CONSTRAINT FK_BADGE_PERSON
		FOREIGN KEY (IssuingPerson)
		REFERENCES dbo.PERSON(Id)
)
GO

-- Import the data from the excel Spread sheet BadgeData. Restructure the
-- SHEET$ table to comply with Metadata Dictionary provided.

INSERT INTO BADGE
	SELECT
		[Badge Number]  AS Id,
		[Issued by]     AS IssuingPerson,
		[Date of Issue] AS IssueDate

	FROM OPENROWSET
	(
		'Microsoft.Jet.OLEDB.4.0',
		'Excel 8.0;Database=T:\BadgeData.xls;HDR=YES',
		'SELECT * FROM [Sheet1$]'
	)
GO