rem
rem  +---------------------------------------------------------------------+
rem  |  CUSTOMERORDERENTRY.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Customer Order Entry Database		   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Customer(
customerNo NUMBER(8) NOT NULL, 
customerName VARCHAR2(50) NOT NULL, 
customerStreet VARCHAR2(40) NOT NULL, 
customerCity VARCHAR2(15) NOT NULL, 
customerState VARCHAR2(15) NOT NULL,
customerZipCode VARCHAR2(9), 
custTelNo VARCHAR2(20) UNIQUE NOT NULL, 
custFaxNo VARCHAR2(20) UNIQUE NOT NULL, 
DOB DATE, 
maritalStatus CHAR, 
creditRating CHAR,
PRIMARY KEY (customerNo)
);

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 Order1(
orderNo NUMBER(8) NOT NULL, 
orderDate DATE NOT NULL, 
billingStreet VARCHAR2(40) NOT NULL, 
billingCity VARCHAR2(15) NOT NULL, 
billingState VARCHAR2(15) NOT NULL, 
billingZipCode VARCHAR2(9), 
promisedDate DATE NOT NULL, 
status CHAR NOT NULL, 
customerNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL,
PRIMARY KEY (orderNo),
CONSTRAINT customernumber FOREIGN KEY (customerNo) REFERENCES Customer(customerNo),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE Invoice(
invoiceNo NUMBER(8) NOT NULL, 
dateRaised DATE NOT NULL, 
datePaid DATE, 
creditCardNo VARCHAR2(20), 
holdersName VARCHAR2(50), 
expiryDate DATE, 
orderNo NUMBER(8) NOT NULL, 
pMethodNo NUMBER(2) NOT NULL,
PRIMARY KEY (invoiceNo),
CONSTRAINT ordernumber FOREIGN KEY (orderNo) REFERENCES Order1(orderNo),
CONSTRAINT pmethodnumber FOREIGN KEY (pMethodNo) REFERENCES PaymentMethod(pMethodNo)
);


CREATE TABLE Product(
productNo NUMBER(8) NOT NULL, 
productName VARCHAR2(40) NOT NULL, 
serialNo VARCHAR2(20) NOT NULL UNIQUE, 
unitPrice NUMBER(8,2) NOT NULL, 
quantityOnHand NUMBER(6) NOT NULL, 
reorderLevel NUMBER(6) NOT NULL, 
reorderQuantity NUMBER(6) NOT NULL, 
reorderLeadTime NUMBER(2) NOT NULL,
PRIMARY KEY (productNo)
);

CREATE TABLE OrderDetail(
orderNo NUMBER(8) NOT NULL, 
productNo NUMBER(8) NOT NULL, 
quantityOrdered NUMBER(6) NOT NULL,
PRIMARY KEY (orderNo, productNo),
CONSTRAINT ordernumber1 FOREIGN KEY (orderNo) REFERENCES Order1(orderNo),
CONSTRAINT productnumber FOREIGN KEY (productNo) REFERENCES Product(ProductNo)
);

CREATE TABLE ShipmentMethod(
sMethodNo NUMBER(2) NOT NULL, 
shipmentMethod VARCHAR2(40) NOT NULL,
PRIMARY KEY (sMethodNo)
);

CREATE TABLE Shipment(
shipmentNo NUMBER(8) NOT NULL, 
quantity NUMBER(6) NOT NULL, 
shipmentDate DATE NOT NULL, 
completeStatus CHAR NOT NULL, 
orderNo NUMBER(8) NOT NULL, 
productNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
sMethodNo NUMBER(2) NOT NULL,
PRIMARY KEY (shipmentNo),
CONSTRAINT orderproduct FOREIGN KEY (orderNo, productNo) REFERENCES OrderDetail(orderNo, productNo),
CONSTRAINT employeenumber1 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT smethodnumber FOREIGN KEY (sMethodNo) REFERENCES ShipmentMethod(sMethodNo)
);