rem
rem  +---------------------------------------------------------------------+
rem  |  PAYROLLMANAGEMENT.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Payroll Management Database	   	   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Employee (
employeeNo NUMBER(8) NOT NULL, 
title VARCHAR2(10) NOT NULL, 
firstName VARCHAR2(30) NOT NULL, 
middleName VARCHAR2(30), 
lastName VARCHAR2(30) NOT NULL, 
address VARCHAR2(80) NOT NULL, 
workTelExt VARCHAR2(4) NOT NULL, 
homeTelNo VARCHAR2(20) NOT NULL, 
empEmailAddress VARCHAR2(100), 
socialSecurityNumber VARCHAR2(12) NOT NULL UNIQUE, 
DOB DATE NOT NULL, 
position VARCHAR2(20) NOT NULL, 
sex CHAR NOT NULL, 
salary NUMBER(8,2) NOT NULL, 
dateStarted DATE NOT NULL,
PRIMARY KEY (employeeNo)
);

CREATE TABLE BonusType(
bonusTypeNo NUMBER(2) NOT NULL, 
bonusDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (bonusTypeNo)
);

CREATE TABLE Bonus(
employeeNo NUMBER(8) NOT NULL, 
bonusDate DATE NOT NULL, 
bonusAmount NUMBER(8, 2) NOT NULL, 
bonusTypeNo NUMBER(2) NOT NULL,
PRIMARY KEY (employeeNo, bonusDate),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT bonustypenumber FOREIGN KEY (bonusTypeNo) REFERENCES BonusType(bonusTypeNo)
);

CREATE TABLE DeductType(
deductTypeNo NUMBER(2) NOT NULL, 
deductDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (deductTypeNo)
);

CREATE TABLE Deduction(
employeeNo NUMBER(8) NOT NULL, 
deductDate DATE NOT NULL, 
deductAmount NUMBER(8, 2) NOT NULL, 
deductTypeNo NUMBER(2) NOT NULL,
PRIMARY KEY (employeeNo, deductDate),
CONSTRAINT employeenumber1 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT deducttypenumber FOREIGN KEY (deductTypeNo) REFERENCES DeductType(deductTypeNo)
);

CREATE TABLE Holiday(
employeeNo NUMBER(8) NOT NULL, 
startDate DATE NOT NULL, 
endDate DATE NOT NULL,
PRIMARY KEY (employeeNo, startDate),
CONSTRAINT employeenumber2 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE PayType(
payTypeNo NUMBER(2) NOT NULL, 
payTypeDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (payTypeNo)
);

CREATE TABLE PayDetails(
employeeNo NUMBER(8) NOT NULL, 
startDate DATE NOT NULL, 
routingNumber VARCHAR2(30) NOT NULL, 
accountType VARCHAR2(20) NOT NULL, 
bankName VARCHAR2(40) NOT NULL, 
bankAddress VARCHAR2(80) NOT NULL, 
payTypeNo NUMBER(2) NOT NULL,
PRIMARY KEY (employeeNo, startDate),
CONSTRAINT employeenumber3 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT paytypenumber FOREIGN KEY (payTypeNo) REFERENCES PayType(payTypeNo)
);

CREATE TABLE PayHistory(
payNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
payDate DATE NOT NULL, 
checkNumber NUMBER(4) NOT NULL, 
payAmount NUMBER(8, 2) NOT NULL,
PRIMARY KEY (payNo),
CONSTRAINT employeenumber4 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE SickLeave(
employeeNo NUMBER(8) NOT NULL, 
startDate DATE NOT NULL, 
endDate DATE NOT NULL, 
reason VARCHAR2(40) NOT NULL,
PRIMARY KEY (employeeNo, startDate),
CONSTRAINT employeenumber5 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);