rem
rem  +---------------------------------------------------------------------+
rem  |  INVENTORYCONTROL.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Inventory Control 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 ProductCategory	(
categoryNo NUMBER(2) NOT NULL, 
categoryDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (categoryNo)
);

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, 
categoryNo NUMBER(8) NOT NULL,
PRIMARY KEY (productNo),
CONSTRAINT categorynumber FOREIGN KEY (categoryNo) REFERENCES ProductCategory(categoryNo)
);

CREATE TABLE Supplier(
supplierNo NUMBER(8) NOT NULL, 
supplierName VARCHAR2(40) NOT NULL UNIQUE, 
supplierStreet VARCHAR2(40) NOT NULL, 
supplierCity VARCHAR2(15) NOT NULL, 
supplierState VARCHAR2(15) NOT NULL, 
supplierZipCode VARCHAR2(9) NOT NULL, 
suppTelNo VARCHAR2(20) NOT NULL UNIQUE, 
suppFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
suppEmailAddress VARCHAR2(100), 
suppWebAddress VARCHAR2(100), 
contactName VARCHAR2(50) NOT NULL, 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100), 
paymentTerms VARCHAR2(10),
PRIMARY KEY (supplierNo)
);

CREATE TABLE PurchaseOrder(
purchaseOrderNo NUMBER(8) NOT NULL, 
purchaseOrderDescription VARCHAR2(40) NOT NULL, 
orderDate DATE NOT NULL, 
dateRequired DATE NOT NULL, 
shippedDate DATE, 
freightCharge NUMBER(8, 2) NOT NULL, 
supplierNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL,
PRIMARY KEY (purchaseOrderNo),
CONSTRAINT suppliernumber FOREIGN KEY (supplierNo) REFERENCES Supplier(supplierNo),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE Transaction(
transactionNo NUMBER(8) NOT NULL, 
transactionDate DATE NOT NULL, 
transactionDescription VARCHAR2(40) NOT NULL, 
unitPrice NUMBER(8, 2) NOT NULL, 
unitsOrdered NUMBER(6) NOT NULL, 
unitsReceived NUMBER(6) NOT NULL, 
unitsSold NUMBER(6) NOT NULL, 
unitsWastage NUMBER(6) NOT NULL, 
productNo NUMBER(8) NOT NULL, 
purchaseOrderNo NUMBER(8) NOT NULL,
PRIMARY KEY (transactionNo),
CONSTRAINT productnumber FOREIGN KEY (productNo) REFERENCES Product(productNo),
CONSTRAINT purchaseordernumber FOREIGN KEY (purchaseOrderNo) REFERENCES PurchaseOrder(purchaseOrderNo)
);