HWA2_P1_2.sql

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

   PART 1.2
   Importing the Personnel Payroll MS Xcel file and name as PAYROLL. Create
   the SQL script(s) statement that will populate the subtype tables from
   the PAYROLL table. Ensure the subtype tables comply with the Metadata
   Dictionary documentation. Delete the PAYROLL following a successful
   population of the subtype tables. Create the Foreign Key constraints
   between the subtypes and the PERSON table
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- Populate the FULL_TIME table, ensuring the subtype table complies with
-- the Metadata Dictionary documentation.  First create it.

CREATE TABLE FULL_TIME
(
	Id              INT         NOT NULL,
	AnnualWage      MONEY       NOT NULL,
	DateOfLastRaise DATETIME        NULL,
	RaiseIncrement  FLOAT           NULL,
	BargainingUnit  NVARCHAR(4)     NULL

	CONSTRAINT PK_FULL_TIME
		PRIMARY KEY (Id)

	CONSTRAINT FK_FULL_TIME_PERSON
		FOREIGN KEY (Id)
		REFERENCES PERSON(Id)
)
GO

-- Next, populate it from the imported Excel worksheet.

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

INSERT INTO FULL_TIME
SELECT
	[Person ID]          AS Id,
	[Annual_Hourly Wage] AS AnnualWage,
	[LastRaise]          AS DateOfLastRaise,
	[RaiseIncrement]     AS RaiseIncrement,
	[bargainingUnit]     AS BargainingUnit

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

	JOIN PERSON ON PERSON.Id = [Person ID]

	WHERE EmploymentStatus = 'F'
GO

-- Populate the PART_TIME table, ensuring the subtype table complies with
-- the Metadata Dictionary documentation.  First create it.

CREATE TABLE PART_TIME
(
	Id              INT      NOT NULL,
	HourlySalary    MONEY    NOT NULL,
	DateOfLastRaise DATETIME     NULL,
	RaiseIncrement  FLOAT        NULL

	CONSTRAINT PK_PART_TIME
		PRIMARY KEY (Id)

	CONSTRAINT FK_PART_TIME_PERSON
		FOREIGN KEY (Id)
		REFERENCES PERSON(Id)
)
GO

-- Next, populate it from the imported Excel worksheet.

INSERT INTO PART_TIME
SELECT
	[Person ID]          AS Id,
	[Annual_Hourly Wage] AS HourlySalary,
	[LastRaise]          AS DateOfLastRaise,
	[RaiseIncrement]     AS RaiseIncrement

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

	JOIN PERSON ON PERSON.Id = [Person ID]

	WHERE EmploymentStatus = 'P'
GO