rem
rem  +---------------------------------------------------------------------+
rem  |  REALESTATEAGENCY.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Real Estate Agency Database	   	   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Branch(
branchNo NUMBER(8) NOT NULL, 
branchStreet VARCHAR2(40) NOT NULL, 
branchCity VARCHAR2(15) NOT NULL, 
branchState VARCHAR2(15) NOT NULL, 
branchZipCode VARCHAR2(9) NOT NULL, 
branchTelNo VARCHAR2(20) NOT NULL UNIQUE, 
branchFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
managerEmployeeNo NUMBER(8),
PRIMARY KEY (branchNo)
);

CREATE TABLE Employee(
employeeNo NUMBER(8) 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, 
typingSpeed NUMBER(3), 
dateStarted DATE NOT NULL, 
branchNo NUMBER(8) NOT NULL,
PRIMARY KEY (employeeNo),
CONSTRAINT branchnumber FOREIGN KEY (branchNo) REFERENCES Branch(branchNo)
);

ALTER TABLE Branch
ADD CONSTRAINT manageremployeenumber FOREIGN KEY (managerEmployeeNo) REFERENCES Employee(employeeNo);

CREATE TABLE AllocatedEmp(
superviseeEmployeeNo NUMBER(8) NOT NULL, 
supervisorEmployeeNo NUMBER(8) NOT NULL, 
secretaryEmployeeNo NUMBER(8) NOT NULL,
PRIMARY KEY (superviseeEmployeeNo),
CONSTRAINT superviseeemployeenumber FOREIGN KEY (superviseeEmployeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT supervisoremployeenumber FOREIGN KEY (supervisorEmployeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT secretaryemployeenumber FOREIGN KEY (secretaryEmployeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE BusinessOwner(
ownerNo NUMBER(8) NOT NULL, 
businessName VARCHAR2(50) NOT NULL UNIQUE, 
businessAddress VARCHAR2(80) NOT NULL, 
businessTelNo VARCHAR2(20) NOT NULL UNIQUE, 
businessFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
contactName VARCHAR2(50) NOT NULL, 
contactTelNo VARCHAR2(20), 
contactFaxNo VARCHAR2(20), 
contactEmailAddress VARCHAR2(100),
PRIMARY KEY (ownerNo)
);

CREATE TABLE PrivateOwner(
ownerNo NUMBER(8) NOT NULL, 
ownerName VARCHAR2(50) NOT NULL, 
ownerAddress VARCHAR2(80) NOT NULL, 
ownerTelNo VARCHAR2(20) NOT NULL,
PRIMARY KEY (ownerNo)
);

CREATE TABLE PropertyType(
propTypeNo NUMBER(2) NOT NULL, 
propTypeDescription VARCHAR2(40) NOT NULL,
PRIMARY KEY (propTypeNo)
);

CREATE TABLE PropertyForRent	(
propertyNo NUMBER(8) NOT NULL, 
propStreet VARCHAR2(40) NOT NULL, 
propCity VARCHAR2(15) NOT NULL, 
propState VARCHAR2(15) NOT NULL, 
propZipCode VARCHAR2(9) NOT NULL, 
noRooms NUMBER(1) NOT NULL, 
rent NUMBER(5, 1) NOT NULL, 
propTypeNo NUMBER(2) NOT NULL, 
ownerNo NUMBER(8) NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
branchNo NUMBER(8) NOT NULL,
PRIMARY KEY (propertyNo),
CONSTRAINT proptypenumber FOREIGN KEY (propTypeNo) REFERENCES PropertyType(propTypeNo),
CONSTRAINT ownernumber FOREIGN KEY (ownerNo) REFERENCES PrivateOwner(ownerNo),
CONSTRAINT ownernumber1 FOREIGN KEY (ownerNo) REFERENCES BusinessOwner(ownerNo),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo),
CONSTRAINT branchnumber1 FOREIGN KEY (branchNo) REFERENCES Branch(branchNo)
);

CREATE TABLE Renter(
renterNo NUMBER(8) NOT NULL, 
rFName VARCHAR2(30) NOT NULL, 
rLName VARCHAR2(30) NOT NULL, 
rAddress VARCHAR2(80) NOT NULL, 
rTelNo VARCHAR2(20), 
maxRent NUMBER(5, 1), 
prefTypeNo NUMBER(2) NOT NULL,
PRIMARY KEY (renterNo),
CONSTRAINT proptypenumber1 FOREIGN KEY (prefTypeNo) REFERENCES PropertyType(propTypeNo)
);

CREATE TABLE Lease(
leaseNo NUMBER(8) NOT NULL, 
rentStart DATE NOT NULL, 
rentFinish DATE NOT NULL, 
depositPaid CHAR NOT NULL, 
renterNo NUMBER(8) NOT NULL, 
propertyNo NUMBER(8) NOT NULL,
PRIMARY KEY (leaseNo),
CONSTRAINT renternumber FOREIGN KEY (renterNo) REFERENCES Renter(renterNo),
CONSTRAINT propertynumber FOREIGN KEY (propertyNo) REFERENCES PropertyForRent(propertyNo)
);

CREATE TABLE Viewing(
propertyNo NUMBER(8) NOT NULL, 
renterNo NUMBER(8) NOT NULL, 
dateView DATE NOT NULL, 
comments VARCHAR2(50) NOT NULL,
PRIMARY KEY (propertyNo, renterNo, dateView),
CONSTRAINT propertynumber1 FOREIGN KEY (propertyNo) REFERENCES PropertyForRent(propertyNo),
CONSTRAINT renternumber1 FOREIGN KEY (renterNo) REFERENCES Renter(renterNo)
);