rem
rem  +---------------------------------------------------------------------+
rem  |  PROJECTMANAGEMENT.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Project Management Database	   	   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Client(
clientNo NUMBER(8) NOT NULL, 
clientName VARCHAR2(50) NOT NULL UNIQUE, 
clientStreet VARCHAR2(40) NOT NULL, 
clientCity VARCHAR2(15) NOT NULL, 
clientState VARCHAR2(15) NOT NULL, 
clientZipCode VARCHAR2(9), 
clientTelNo VARCHAR2(20) NOT NULL UNIQUE, 
clientFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
clientWebAddress VARCHAR2(100), 
contactName VARCHAR2(50) NOT NULL, 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100),
PRIMARY KEY (clientNo)
);

CREATE TABLE Role(
roleNo NUMBER(2) NOT NULL, 
roleDescription VARCHAR2(40) NOT NULL, 
billingRate NUMBER(8, 2) NOT NULL,
PRIMARY KEY (roleNo)
);

CREATE TABLE Employee(
employeeNo NUMBER(8) NOT NULL, 
dateStartRole DATE 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, 
roleNo NUMBER(2) NOT NULL,
PRIMARY KEY (employeeNo),
CONSTRAINT rolenumber FOREIGN KEY (roleNo) REFERENCES Role(roleNo)
);

CREATE TABLE Project(
projectNo NUMBER(8) NOT NULL, 
projectName VARCHAR2(40) NOT NULL, 
plannedStartDate DATE NOT NULL, 
plannedEndDate DATE NOT NULL, 
actualStartDate DATE, 
actualEndDate DATE, 
projectedCost NUMBER(10,2), 
actualCost NUMBER(10,2), 
clientNo NUMBER(8) NOT NULL, 
managerEmployeeNo NUMBER(8) NOT NULL,
PRIMARY KEY (projectNo),
CONSTRAINT clientnumber FOREIGN KEY (clientNo) REFERENCES Client(clientNo),
CONSTRAINT manageremployeenumber FOREIGN KEY (managerEmployeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE WorkPackage(
workPackageNo NUMBER(8) NOT NULL, 
plannedStartDate DATE NOT NULL, 
plannedEndDate DATE NOT NULL, 
actualStartDate DATE, 
actualEndDate DATE, 
projectedCost NUMBER(10, 2), 
actualCost NUMBER(10, 2), 
projectNo NUMBER(8) NOT NULL,
PRIMARY KEY (workPackageNo),
CONSTRAINT projectnumber FOREIGN KEY (projectNo) REFERENCES Project(projectNo)
);

CREATE TABLE Document(
documentNo NUMBER(8) NOT NULL, 
documentTitle VARCHAR2(50) NOT NULL, 
documentDate DATE NOT NULL, 
versionNo VARCHAR2(5) NOT NULL, 
workPackageNo NUMBER(8) NOT NULL, 
approvedByEmployeeNo NUMBER(8) NOT NULL,
PRIMARY KEY (documentNo),
CONSTRAINT workpackagenumber FOREIGN KEY (workPackageNo) REFERENCES WorkPackage(workPackageNo),
CONSTRAINT approvedbyemployeenumber FOREIGN KEY (approvedByEmployeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE DocumentAuthor(
documentNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL,
PRIMARY KEY (documentNo, employeeNo),
CONSTRAINT documentnumber FOREIGN KEY (documentNo) REFERENCES Document(documentNo),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE ExpenseType(
expenseTypeNo NUMBER(2) NOT NULL, 
expenseTypeDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (expenseTypeNo)
);

CREATE TABLE Expense(
expenseNo NUMBER(8) NOT NULL, 
expenseDate DATE NOT NULL, 
expenseDescription VARCHAR2(40) NOT NULL, 
expenseAmount NUMBER(6,2) NOT NULL, 
workPackageNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
expenseTypeNo NUMBER(2) NOT NULL,
PRIMARY KEY (expenseNo),
CONSTRAINT alternatekey1 UNIQUE(workPackageNo, employeeNo, expenseDate),
CONSTRAINT workpackagenumber1 FOREIGN KEY (workPackageNo) REFERENCES WorkPackage(workPackageNo),
CONSTRAINT employeenumber1 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT expensetypenumber FOREIGN KEY (expenseTypeNo) REFERENCES ExpenseType(expenseTypeNo)
);

CREATE TABLE TimeBooked(
workPackageNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
dateStartWork DATE NOT NULL, 
dateStopWork DATE NOT NULL, 
timeWorked NUMBER(3, 1) NOT NULL,
PRIMARY KEY (workPackageNo, employeeNo),
CONSTRAINT workpackagenumber2 FOREIGN KEY (workPackageNo) REFERENCES WorkPackage(workPackageNo),
CONSTRAINT employeenumber2 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);