HWA2_P2_2.sql

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

   PART 2.2
   Using the UPDATE SQL command, update the PERSON table with the assigned
   badge number as indicated in the BADGE table. Ensure compliance
   compliance with the Metadata Dictionary provided above. Create a foreign
   key constraint between the badge number ID in the BADGE table and the
   badge number in the PERSON table. The Badge Number in the PERSON table
   can only exist once in the table.
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- Create a foreign key constraint between the badge number ID in the BADGE
-- table and the badge number in the PERSON table.  Temporarily allow NULL
-- values.

ALTER TABLE PERSON
	ADD Badge INT NULL
	CONSTRAINT FK_PERSON_BADGE
		FOREIGN KEY (Badge)
		REFERENCES BADGE(Id)
GO

-- Using the UPDATE SQL command, update the PERSON table with the assigned
-- badge number as indicated in the BADGE table. Ensure compliance
-- compliance with the Metadata Dictionary provided above.

UPDATE PERSON
	SET Badge =
	(
		SELECT [Badge Number] AS Badge
		FROM OPENROWSET
		(
			'Microsoft.Jet.OLEDB.4.0',
			'Excel 8.0;Database=T:\BadgeData.xls;HDR=YES',
			'SELECT * FROM [Sheet1$]'
		)
		AS XLS

		WHERE XLS.[Issued to] = PERSON.Id
	)
GO

-- Ensure compliance compliance with the Metadata Dictionary.  Do not allow
-- NULL values.

ALTER TABLE PERSON
	ALTER COLUMN Badge INT NOT NULL
GO

-- The Badge Number in the PERSON table can exist only once in the table.

ALTER TABLE PERSON
	ADD CONSTRAINT UN_PERSON_BADGE UNIQUE (Badge)
GO