rem
rem  +---------------------------------------------------------------------+
rem  |  COURSEMANAGEMENT.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Course 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 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 PaymentMethod(
pMethodNo NUMBER(2) NOT NULL, 
paymentMethod VARCHAR2(40) NOT NULL,
PRIMARY KEY (pMethodNo)
);

CREATE TABLE Delegate(
delegateNo NUMBER(8) NOT NULL, 
delegateTitle VARCHAR2(10) NOT NULL, 
delegateFName VARCHAR2(30) NOT NULL, 
delegateLName VARCHAR2(30) NOT NULL, 
delegateStreet VARCHAR2(40) NOT NULL, 
delegateCity VARCHAR2(15) NOT NULL, 
delegateState VARCHAR2(15) NOT NULL, 
delegateZipCode VARCHAR2(9), 
attTelNo VARCHAR2(20) NOT NULL, 
attFaxNo VARCHAR2(20), 
attEmailAddress VARCHAR2(100), 
clientNo NUMBER(8) NOT NULL,
PRIMARY KEY (delegateNo),
CONSTRAINT clientnumber FOREIGN KEY (clientNo) REFERENCES Client(clientNo)
);

CREATE TABLE Location(
locationNo NUMBER(2) NOT NULL, 
locationName VARCHAR2(40) NOT NULL, 
maxSize NUMBER(5),
PRIMARY KEY (locationNo)
);

CREATE TABLE CourseType(
courseTypeNo NUMBER(2) NOT NULL, 
courseTypeDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (courseTypeNo)
);

CREATE TABLE Course(
courseNo NUMBER(8) NOT NULL, 
courseName VARCHAR2(40) NOT NULL, 
courseDescription VARCHAR2(100) NOT NULL, 
startDate DATE NOT NULL, 
startTime NUMBER(4, 2) NOT NULL, 
endDate DATE NOT NULL, 
endTime NUMBER(4, 2) NOT NULL, 
maxDelegates NUMBER(5) NOT NULL, 
confirmed CHAR, 
delivererEmployeeNo NUMBER(8) NOT NULL, 
courseTypeNo NUMBER(2) NOT NULL,
PRIMARY KEY (courseNo),
CONSTRAINT delivereremployeenumber FOREIGN KEY (delivererEmployeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT coursetypenumber FOREIGN KEY (courseTypeNo) REFERENCES CourseType(courseTypeNo)
);

CREATE TABLE Booking(
bookingNo NUMBER(8) NOT NULL, 
bookingDate DATE NOT NULL, 
locationNo NUMBER(2) NOT NULL, 
courseNo NUMBER(8) NOT NULL, 
bookingEmployeeNo NUMBER(8) NOT NULL,
PRIMARY KEY (bookingNo),
CONSTRAINT locationnumber FOREIGN KEY (locationNo) REFERENCES Location(locationNo),
CONSTRAINT coursenumber FOREIGN KEY (courseNo) REFERENCES Course(courseNo),
CONSTRAINT bookingemployeenumber FOREIGN KEY (bookingEmployeeNo) REFERENCES Employee(employeeNo)
);		

CREATE TABLE CourseFee(
courseFeeNo NUMBER(8) NOT NULL, 
feeDescription VARCHAR2(40) NOT NULL, 
fee NUMBER(6, 2), 
courseNo NUMBER(8) NOT NULL,
PRIMARY KEY (courseFeeNo),
CONSTRAINT coursenumber1 FOREIGN KEY (courseNo) REFERENCES Course(courseNo)
);

CREATE TABLE Registration(
registrationNo NUMBER(8) NOT NULL, 
registrationDate DATE NOT NULL, 
delegateNo NUMBER(8) NOT NULL, 
courseFeeNo NUMBER(8) NOT NULL, 
registerEmployeeNo NUMBER(8) NOT NULL, 
courseNo NUMBER(8) NOT NULL,
PRIMARY KEY (registrationNo),
CONSTRAINT delegatenumber FOREIGN KEY (delegateNo) REFERENCES Delegate(delegateNo),
CONSTRAINT coursefeenumber FOREIGN KEY (courseFeeNo) REFERENCES CourseFee(courseFeeNo),
CONSTRAINT registeremployeenumber FOREIGN KEY (registerEmployeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT coursenumber2 FOREIGN KEY (courseNo) REFERENCES Course(courseNo)
);

CREATE TABLE Invoice(
invoiceNo NUMBER(8) NOT NULL, 
dateRaised DATE NOT NULL, 
datePaid DATE, 
creditCardNo VARCHAR2(20), 
holdersName VARCHAR2(50), 
expiryDate DATE, 
registrationNo NUMBER(8) NOT NULL, 
pMethodNo NUMBER(2) NOT NULL,
PRIMARY KEY (invoiceNo),
CONSTRAINT registrationnumber FOREIGN KEY (registrationNo) REFERENCES Registration(registrationNo),
CONSTRAINT paymentmethodnumber FOREIGN KEY (pMethodNo) REFERENCES PaymentMethod(pMethodNo)
);