HWA2_P3_1.sql

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

   PART 3.1
   Import the MS Access database (PERSON SKILL DB). If required, provide the
   SQL commands to alter the imported table to conform with the PERSON_SKILL
   table defined in the Metadata Dictionary documentation. Provide the SQL
   script to delete a PERSON_SKILL row if the row violates referential
   integrity. Also provide the SQL SCRIPT to establish required referential
   integrity.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- Alter the SKILL table according to the Metadata Dictionary documentation.

-- Note: I believe there is a typo in the file 'ATTRIBUTE DEFINITION
-- HWA2F08.doc'.  The Skill Code is shown as Alphanumeric(20), but it was
-- previously an Integer.  Integer makes more sense, and the SkillCode field
-- from the Access file seems to agree since all skill codes are represented
-- as integers.  Therefore, I will not change its type in this script.

ALTER TABLE SKILL
ALTER COLUMN Description NVARCHAR(150) NOT NULL
GO

ALTER TABLE SKILL
ALTER COLUMN Title NVARCHAR(50) NOT NULL
GO

-- Create the PERSON_SKILL table according to the Metadata Dictionary
-- documentation, establishing required referential integrity.

CREATE TABLE PERSON_SKILL
(
	SkillId        INT      NOT NULL,
	PersonId       INT      NOT NULL,
	AssessmentDate DATETIME     NULL,
	ReviewDate     DATETIME     NULL

	CONSTRAINT PK_PERSON_SKILL
		PRIMARY KEY (SkillId, PersonId)

	CONSTRAINT FK_PERSON_SKILL_SKILL
		FOREIGN KEY (SkillId)
		REFERENCES SKILL(Id),

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

-- Import the MS Access database (PERSON SKILL DB), excluding PERSON_SKILL
-- rows if the row violates referential integrity.

INSERT INTO PERSON_SKILL
	SELECT
		SkillCode,
		PerId,
		AssessmentDate,
		ReviewDate

	FROM OPENROWSET
	(
		'Microsoft.Jet.OLEDB.4.0',
		'T:\Person SKill DB.mdb';'admin';'',
		'SELECT * FROM PERSON_SKILL'
	)
	AS MDB

	JOIN PERSON ON MDB.PerID     = PERSON.Id
	JOIN SKILL  ON MDB.SkillCode = SKILL.Id
GO