rem
rem  +---------------------------------------------------------------------+
rem  |  ASSETMANAGEMENT.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Asset Management Database			   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE AssetCategory(
assetCategoryNo NUMBER(2) NOT NULL, 
assetCategoryDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (assetCategoryNo)
);

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 Status(
statusNo NUMBER(2) NOT NULL, 
statusDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (statusNo)
);

CREATE TABLE Asset(
assetNo NUMBER(8) NOT NULL, 
assetDescription VARCHAR2(40) NOT NULL, 
serialNo VARCHAR2(20) NOT NULL UNIQUE, 
dateAcquired DATE NOT NULL, 
purchasePrice NUMBER(8, 2) NOT NULL, 
currentValue NUMBER(8,2) NOT NULL, 
dateSold DATE, 
nextMaintenanceDate DATE, 
employeeNo NUMBER(8) NOT NULL, 
assetCategoryNo NUMBER(2) NOT NULL, 
statusNo NUMBER(2) NOT NULL,
PRIMARY KEY (assetNo),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT assetcategorynumber FOREIGN KEY (assetCategoryNo) REFERENCES AssetCategory(assetCategoryNo),
CONSTRAINT statusnumber FOREIGN KEY (statusNo) REFERENCES Status(statusNo)
);

CREATE TABLE ServiceAgent(
agentNo NUMBER(8) NOT NULL, 
agentName VARCHAR2(50) NOT NULL UNIQUE, 
agentStreet VARCHAR2(40) NOT NULL, 
agentCity VARCHAR2(15) NOT NULL, 
agentState VARCHAR2(15) NOT NULL, 
agentZipCode VARCHAR2(9) NOT NULL, 
agentTelNo VARCHAR2(20) NOT NULL UNIQUE, 
agentFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
agentEmailAddress VARCHAR2(100), 
agentWebAddress VARCHAR2(100), 
contactName VARCHAR2(50) NOT NULL, 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100),
PRIMARY KEY (agentNo)
);

CREATE TABLE Maintenance(
maintenanceNo NUMBER(8) NOT NULL, 
maintenanceDate DATE NOT NULL, 
maintenanceDescription VARCHAR2(40) NOT NULL, 
maintenanceCost NUMBER(6, 2) NOT NULL, 
assetNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
agentNo NUMBER(8) NOT NULL,
PRIMARY KEY (maintenanceNo),
CONSTRAINT assetnumber FOREIGN KEY (assetNo) REFERENCES Asset(assetNo),
CONSTRAINT employeenumber1 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT agentnumber FOREIGN KEY (agentNo) REFERENCES ServiceAgent(agentNo)
);

CREATE TABLE Valuation(
valuationNo NUMBER(8) NOT NULL, 
valuationDate DATE NOT NULL, 
valuationPrice NUMBER(8, 2) NOT NULL, 
assetNo NUMBER (8) NOT NULL, 
employeeNo NUMBER (8) NOT NULL,
PRIMARY KEY (valuationNo),
CONSTRAINT assetnumber1 FOREIGN KEY (assetNo) REFERENCES Asset(assetNo), 
CONSTRAINT employeenumber2 FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);