rem
rem  +---------------------------------------------------------------------+
rem  |  PUBLISHERPRINTING.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Publisher Printing 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 Publisher(
publisherNo NUMBER(8) NOT NULL, 
publisherName VARCHAR2(50) NOT NULL UNIQUE, 
publisherStreet VARCHAR2(40) NOT NULL, 
publisherCity VARCHAR2(15) NOT NULL, 
publisherState VARCHAR2(15) NOT NULL, 
publisherZipCode VARCHAR2(9) NOT NULL, 
pubTelNo VARCHAR2(20) NOT NULL UNIQUE, 
pubFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
pubWebAddress VARCHAR2(100), 
contactName VARCHAR2(50), 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100), 
creditRating CHAR,
PRIMARY KEY (publisherNo)
);

CREATE TABLE PrintJobType(
printJobTypeNo NUMBER(2) NOT NULL, 
printJobTypeDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (printJobTypeNo)
);

CREATE TABLE PrintJob(
printJobNo NUMBER(8) NOT NULL, 
printJobDescription VARCHAR2(40) NOT NULL, 
printJobDateReceived DATE NOT NULL, 
printJobDateCompleted DATE, 
managerEmployeeNo NUMBER(8) NOT NULL, 
publisherNo NUMBER(8) NOT NULL, 
printJobTypeNo NUMBER(2) NOT NULL,
PRIMARY KEY (printJobNo),
FOREIGN KEY (managerEmployeeNo) REFERENCES Employee(employeeNo),
FOREIGN KEY (publisherNo) REFERENCES Publisher(publisherNo),
FOREIGN KEY (printJobTypeNo) REFERENCES PrintJobType(printJobTypeNo)
);

CREATE TABLE EmpPrintJob(
employeeNo NUMBER(8) NOT NULL, 
printJobNo NUMBER(8) NOT NULL, 
jobDate DATE NOT NULL,
PRIMARY KEY (employeeNo, printJobNo),
FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
FOREIGN KEY (printJobNo) REFERENCES PrintJob(printJobNo)
);

CREATE TABLE Item(
itemNo NUMBER(8) NOT NULL, 
itemDescription VARCHAR2(40) NOT NULL, 
itemPrice NUMBER(8,2) NOT NULL, 
itemQuantityInStock NUMBER(6) NOT NULL, 
itemReorderLevel NUMBER(6) NOT NULL,
itemReorderQuantity NUMBER(6) NOT NULL, 
itemReorderLeadTime NUMBER(2) NOT NULL,
PRIMARY KEY (itemNo)
);

CREATE TABLE PurchaseOrder(
purchaseOrderNo NUMBER(8) NOT NULL, 
printJobNo NUMBER(8) NOT NULL, 
purchaseOrderDate DATE NOT NULL,
PRIMARY KEY (purchaseOrderNo),
FOREIGN KEY (printJobNo) REFERENCES PrintJob(printJobNo)
);

CREATE TABLE POItem(
purchaseOrderNo NUMBER(8) NOT NULL, 
itemNo NUMBER(8) NOT NULL, 
quantity NUMBER(6) NOT NULL,
PRIMARY KEY (purchaseOrderNo, itemNo),
FOREIGN KEY (purchaseOrderNo) REFERENCES PurchaseOrder (purchaseOrderNo),
FOREIGN KEY (itemNo) REFERENCES Item(itemNo)
);