rem
rem  +---------------------------------------------------------------------+
rem  |  HUMANRESOURCEMANAGEMENT.SQL 					   |
rem  |                                                                     |
rem  |  Create tables for the Human Resource Management Database	   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Department(
departmentNo NUMBER (8) NOT NULL, 
departmentName VARCHAR2(40) NOT NULL, 
deptLocation VARCHAR2(20) NOT NULL, 
managerEmployeeNo NUMBER(8),
PRIMARY KEY (departmentNo)
);

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,
dateLeft DATE, 
departmentNo NUMBER(8) NOT NULL, 
supervisorEmployeeNo NUMBER(8),
PRIMARY KEY (employeeNo),
CONSTRAINT departmentnumber FOREIGN KEY (departmentNo) REFERENCES Department(departmentNo),
CONSTRAINT supervisornumber FOREIGN KEY (supervisorEmployeeNo) REFERENCES Employee(employeeNo)
);

ALTER TABLE Department
ADD CONSTRAINT manageremployeenumber FOREIGN KEY (managerEmployeeNo) REFERENCES Employee(employeeNo);

CREATE TABLE PositionType(
positionTypeNo NUMBER(2) NOT NULL, 
positionTypeDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (positionTypeNo)
);

CREATE TABLE Grade(
gradeNo NUMBER(2) NOT NULL, 
validFromDate DATE NOT NULL, 
validToDate DATE NOT NULL, 
gradeDescription VARCHAR2(40) NOT NULL, 
gradeSalary NUMBER(8, 2) NOT NULL, 
noDaysLeaveEntitlement NUMBER(2) NOT NULL, 
positionTypeNo NUMBER(2) NOT NULL,
PRIMARY KEY (gradeNo, validFromDate),
CONSTRAINT positiontypenumber FOREIGN KEY (positionTypeNo) REFERENCES PositionType(positionTypeNo)
);

CREATE TABLE Post(
postNo NUMBER(8) NOT NULL, 
availableFromDate DATE NOT NULL, 
availableToDate DATE NOT NULL, 
postDescription VARCHAR2(40) NOT NULL, 
salariedHourly NUMBER(6, 2) NOT NULL, 
fullPartTime CHAR NOT NULL, 
temporaryPermanent CHAR NOT NULL, 
freeLaborStandardsActExempt CHAR NOT NULL, 
departmentNo NUMBER(8)  NOT NULL,
PRIMARY KEY (postNo, availableFromDate),
CONSTRAINT departmentnumber1 FOREIGN KEY (departmentNo) REFERENCES Department(departmentNo)
);

CREATE TABLE GradePost(
gradeNo NUMBER(2) NOT NULL, 
validFromDate DATE NOT NULL,
postNo NUMBER(8) NOT NULL,
availableFromDate DATE NOT NULL, 
PRIMARY KEY (gradeNo, validFromDate, postNo, availableFromDate),
CONSTRAINT gradenumber FOREIGN KEY (gradeNo, validFromDate) REFERENCES Grade(gradeNo, validFromDate),
CONSTRAINT postnumber FOREIGN KEY (postNo, availableFromDate) REFERENCES Post(postNo, availableFromDate)
);

CREATE TABLE Institution(
institutionNo NUMBER(8) NOT NULL, 
institutionName VARCHAR2(50) NOT NULL UNIQUE, 
instAddress VARCHAR2(80) NOT NULL, 
instTelNo VARCHAR2(20) NOT NULL UNIQUE, 
instFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
instWebAddress VARCHAR2(100), 
contactName VARCHAR2(50) NOT NULL, 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100),
PRIMARY KEY (institutionNo)
);

CREATE TABLE Position(
employeeNo NUMBER(8) NOT NULL, 
postNo NUMBER(8) NOT NULL, 
startDate DATE NOT NULL, 
endDate DATE NOT NULL,
PRIMARY KEY (employeeNo, postNo, startDate),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT postnumber1 FOREIGN KEY (postNo, startDate) REFERENCES Post(postNo, availableFromDate)
);

CREATE TABLE PrevCompany(
prevCompanyNo NUMBER(8) NOT NULL, 
pCompanyName VARCHAR2(50) NOT NULL UNIQUE, 
pCompanyStreet VARCHAR2(40) NOT NULL, 
pCompanyCity VARCHAR2(15) NOT NULL, 
pCompanyState VARCHAR2(15) NOT NULL, 
pCompanyZipCode VARCHAR2(9), 
pCompanyTelNo VARCHAR2(20) NOT NULL UNIQUE, 
pCompanyFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
pCompanyWebAddress VARCHAR2(100), 
contactName VARCHAR2(50), 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100),
PRIMARY KEY (prevCompanyNo)
);

CREATE TABLE Qualification(
qualificationName VARCHAR2(50) NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
gradeObtained VARCHAR2(10), 
startQualDate DATE NOT NULL, 
endQualDate DATE, 
gpa NUMBER(4, 2), 
institutionNo NUMBER(8) NOT NULL,
PRIMARY KEY (qualificationName, employeeNo),
CONSTRAINT employeenumber1 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT institutionnumber FOREIGN KEY (institutionNo) REFERENCES Institution(institutionNo)
);

CREATE TABLE Review(
revieweeEmployeeNo NUMBER(8) NOT NULL, 
reviewerEmployeeNo NUMBER(8) NOT NULL, 
reviewDate DATE NOT NULL, 
comments VARCHAR2(100) NOT NULL,
PRIMARY KEY (revieweeEmployeeNo, reviewerEmployeeNo, reviewDate),
CONSTRAINT revieweeemployeenumber FOREIGN KEY (revieweeEmployeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT revieweremployeenumber FOREIGN KEY (reviewerEmployeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE WorkHistory(
prevCompanyNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
prevPosition VARCHAR2(30) NOT NULL, 
prevGrade VARCHAR2(30) NOT NULL, 
prevSalary NUMBER(8, 2) NOT NULL, 
prevLocation VARCHAR2(30) NOT NULL, 
prevResponsibilities VARCHAR2(50) NOT NULL,
PRIMARY KEY (prevCompanyNo, employeeNo),
CONSTRAINT prevcompanynumber FOREIGN KEY (prevCompanyNo) REFERENCES PrevCompany(prevCompanyNo),
CONSTRAINT employeenumber2 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);