//
// Ret-A-Car Data
//
// Matthew W. Coan (7:23 PM 12/8/2002)
//

CREATE TABLE Employee
(
	ID		NUMBER(6) PRIMARY KEY, 
	FirstName	VARCHAR2(64) NOT NULL, 
	MiddleName	VARCHAR2(64), 
	LastName	VARCHAR2(64) NOT NULL, 
	StreetAddress	VARCHAR2(128) NOT NULL, 
	City		VARCHAR2(128) NOT NULL, 
	State		CHAR(2) NOT NULL, 
	Zip		CHAR(5) NOT NULL, 
	SSN		CHAR(9) NOT NULL, 
	Type		VARCHAR2(12) NOT NULL
			CHECK(Type IN ('Manager', 'Mechanic', 'Secretary', 'Sales Person')), 
	Salary		NUMBER(10,2) NOT NULL, 
	TaxDeduction	NUMBER(3,2) NOT NULL, 
	PhoneExt	VARCHAR2(16) NOT NULL, 
	BirthDate	DATE NOT NULL, 
	MaritalStatus	VARCHAR2(7)
			CHECK(MaritalStatus IN ('Single', 'Married')) NOT NULL, 
	NameOfSpouse	VARCHAR2(128)
);

CREATE TABLE Branch
(
	ID		NUMBER(6) PRIMARY KEY, 
	Name		VARCHAR2(128) NOT NULL,
	StreetAddress	VARCHAR2(128) NOT NULL, 
	City		VARCHAR2(128) NOT NULL, 
	State		CHAR(2) NOT NULL, 
	Zip		CHAR(5) NOT NULL,
	FaxNumber	VARCHAR2(128) NOT NULL, 
	MainBranchID	NUMBER(6)
			REFERENCES Branch(ID), 
	ManagerID	NUMBER(6) 
			REFERENCES Employee(ID) NOT NULL
);

CREATE TABLE BranchPhoneNumber
(
	ID		NUMBER(6) PRIMARY KEY, 
	PhoneNumber	VARCHAR2(128) NOT NULL
);

CREATE TABLE B_bpn
(
	BranchID	NUMBER(6) 
			REFERENCES Branch(ID) NOT NULL,
	PhoneNumberID	NUMBER(6)
			REFERENCES BranchPhoneNumber(ID) NOT NULL,
	UNIQUE(BranchID, PhoneNumberID)
);

CREATE TABLE OpenDay
(
	ID		NUMBER(6) PRIMARY KEY, 
	Day		NUMBER(1) NOT NULL
			CHECK(Day BETWEEN 1 AND 7)
);

CREATE TABLE OpenHours
(
	ID		NUMBER(6) PRIMARY KEY, 
	StartHour	NUMBER(2) NOT NULL
			CHECK(StartHour BETWEEN 0 AND 23), 
	StartMin	NUMBER(2) NOT NULL
			CHECK(StartMin BETWEEN 0 AND 59),
	EndHour		NUMBER(2) NOT NULL
			CHECK(EndHour BETWEEN 0 AND 23),
	EndMin		NUMBER(2) NOT NULL
			CHECK(EndMin BETWEEN 0 AND 59)
);

CREATE TABLE B_od
(
	BranchID	NUMBER(6) 
			REFERENCES Branch(ID) NOT NULL, 
	DayID		NUMBER(6) 
			REFERENCES OpenDay(ID) NOT NULL,
	UNIQUE(BranchID, DayID)
);

CREATE TABLE Od_oh
(
	OpenDayID	NUMBER(6)
			REFERENCES OpenDay(ID) NOT NULL, 
	OpenHourID	NUMBER(6) 
			REFERENCES OpenHours(ID) NOT NULL,
	UNIQUE(OpenDayID, OpenHourID)
);

CREATE TABLE Certificate
(
	ID		NUMBER(6) PRIMARY KEY, 
	Name		VARCHAR2(128) NOT NULL, 
	CompletionDate	DATE NOT NULL
);

CREATE TABLE Degree
(
	ID		NUMBER(6) PRIMARY KEY, 
	Name		VARCHAR2(128) NOT NULL,	
	CompletionDate	DATE NOT NULL
);

CREATE TABLE Customer
(
	SSN			CHAR(9) PRIMARY KEY, 
	FirstName		VARCHAR2(128) NOT NULL, 
	LastName		VARCHAR2(128) NOT NULL, 
	MI			CHAR(1), 
	BirthDate		DATE NOT NULL, 
	HomePhone		VARCHAR2(128) NOT NULL, 
	WorkPhone		VARCHAR2(128) NOT NULL, 
	StreetAddress		VARCHAR2(128) NOT NULL, 
	City			VARCHAR2(128) NOT NULL, 
	State			CHAR(2) NOT NULL, 
	ZipCode			CHAR(5) NOT NULL, 
	Email			VARCHAR2(128) NOT NULL, 
	MembershipCard		CHAR(1) NOT NULL
				CHECK(MembershipCard IN ('Y', 'N')), 
	DriversLicenseNo	VARCHAR2(128) NOT NULL, 
	DriversLicenceExpDate	DATE NOT NULL,
	DriversLicenceState	CHAR(2) NOT NULL
);

CREATE TABLE Account
(
	ID				NUMBER(10) PRIMARY KEY, 
	FirstName			VARCHAR2(128) NOT NULL, 
	LastName			VARCHAR2(128) NOT NULL, 
	StreetAddress			VARCHAR2(128) NOT NULL, 
	City				VARCHAR2(128) NOT NULL, 
	State				CHAR(2) NOT NULL, 
	Zip				CHAR(5) NOT NULL, 
	DateOpened			DATE NOT NULL, 
	Balance				NUMBER(10,2) NOT NULL, 
	Limit				NUMBER(10,2) NOT NULL, 
	OverDraftLimit			NUMBER(10,2) NOT NULL, 
	InterestRates			NUMBER(10,2) NOT NULL, 
	ServiceCharge			NUMBER(10,2) NOT NULL
);

CREATE TABLE FrequentMileage
(
	ID		NUMBER(6) PRIMARY KEY, 
	Distance	NUMBER(6) NOT NULL
);

CREATE TABLE Acc_fm
(
	AccountID	NUMBER(6)
			REFERENCES Account(ID) NOT NULL, 
	FreqMileID	NUMBER(6)
			REFERENCES FrequentMileage(ID) NOT NULL,
	UNIQUE(AccountID, FreqMileID)
);

CREATE TABLE CreditCard
(
	ID		NUMBER(6) PRIMARY KEY, 
	CardNo		VARCHAR2(32) NOT NULL, 
	ExpDate		DATE NOT NULL, 
	StreetAddress	VARCHAR2(128) NOT NULL, 
	City		VARCHAR2(128) NOT NULL, 
	State		CHAR(2) NOT NULL, 
	Zip		CHAR(5) NOT NULL
);

CREATE TABLE Acc_cc
(
	AccountID	NUMBER(6)
			REFERENCES Account(ID) NOT NULL, 
	CreditCardID	NUMBER(6)
			REFERENCES CreditCard(ID) NOT NULL,
	UNIQUE(AccountID, CreditCardID)
);


CREATE TABLE Mech_problems
(
	ID		NUMBER(6) PRIMARY KEY, 
	Description	LONG NOT NULL
);

CREATE TABLE Oil_change
(
	ID		NUMBER(6) PRIMARY KEY, 
	StreetAddress	VARCHAR2(128) NOT NULL, 
	City		VARCHAR2(128) NOT NULL, 
	State		CHAR(2) NOT NULL, 
	Zip		CHAR(5) NOT NULL, 
	WhenDate	DATE NOT NULL, 
	Mileage		NUMBER(6) NOT NULL
);

CREATE TABLE Insurance
(
	ID			NUMBER(6) PRIMARY KEY, 
	AmountOfCoverage	NUMBER(10,2) NOT NULL, 
	ExpirationDate		DATE NOT NULL, 
	NameOfInsuranceCompany	VARCHAR2(128) NOT NULL
);

CREATE TABLE Vehicle
(
	ID			NUMBER(6) PRIMARY KEY, 
	CarPlateNo		VARCHAR2(32) NOT NULL, 
	CarStateRegistered	CHAR(2) NOT NULL, 
	CarYear			NUMBER(4) NOT NULL, 
	CurrentMileage		NUMBER(6) NOT NULL, 
	Class			VARCHAR2(32)
				CHECK(Class IN ('Compact', 'Economy', 'Luxury', 'Pickup', 'Van')) NOT NULL, 
	Features		VARCHAR2(16) NOT NULL
				CHECK(Features IN ('2 dors', '4 dors')), 
	Make			VARCHAR2(64) NOT NULL, 
	Color			VARCHAR2(64) NOT NULL,
	OriginalPrice		NUMBER(20,2) NOT NULL, 
	LastOilChangeID		NUMBER(10) 
				REFERENCES Oil_change(ID) NOT NULL, 
	CollisionDamageWaiver	NUMBER(10,2) NOT NULL, 
	LiabilityProtection	NUMBER(10,2) NOT NULL, 
	PersonalInsurance	NUMBER(10,2) NOT NULL, 
	InsuranceNo		NUMBER(6) 
				REFERENCES Insurance(ID)
);

CREATE TABLE Transaction
(
	ID			NUMBER(12) PRIMARY KEY, 
	StartDateTime		DATE NOT NULL, 
	EndDateTime		DATE, 
	Description		LONG, 
	StartingMile		NUMBER(6) NOT NULL, 
	PricePerDay		NUMBER(6,2) NOT NULL, 
	Discount		NUMBER(6,2) NOT NULL, 
	Tax			NUMBER(6,2) NOT NULL, 
	Insurance		NUMBER(7,2) NOT NULL, 
	Balance			NUMBER(6,2), 
	DropOffBranchID		NUMBER(6)
				REFERENCES Branch(ID), 
	RentalBranchID		NUMBER(6)
				REFERENCES Branch(ID) NOT NULL, 
	EndMile			NUMBER(6)
);

CREATE TABLE Accidents
(
	ID		NUMBER(6) PRIMARY KEY, 
	DateAndTime	DATE NOT NULL, 
	LocationLat	NUMBER(3,3), 
	LocationLng	NUMBER(3,3), 
	ExtentOfDamage	LONG NOT NULL, 
	CostOfDamage	NUMBER(6,2)
);

CREATE TABLE B_e
(
	BranchID	NUMBER(6) 
			REFERENCES Branch(ID) NOT NULL, 
	EmpID		NUMBER(6)
			REFERENCES Employee(ID) NOT NULL,
	UNIQUE(BranchID, EmpID)
);

CREATE TABLE B_v
(
	BranchID	NUMBER(6) 
			REFERENCES Branch(ID) NOT NULL, 
	VehID		NUMBER(6)
			REFERENCES Vehicle(ID) NOT NULL,
	UNIQUE(BranchID, VehID)
);

CREATE TABLE E_c
(
	EmpID	NUMBER(6)
		REFERENCES Employee(ID) NOT NULL, 
	CertID	NUMBER(6)
		REFERENCES Certificate(ID) NOT NULL,
	UNIQUE(EmpID, CertID)
);

CREATE TABLE E_d
(
	EmpID	NUMBER(6)
		REFERENCES Employee(ID) NOT NULL, 
	DegID	NUMBER(6)
		REFERENCES Degree(ID) NOT NULL,
	UNIQUE(EmpID, DegID)
);

CREATE TABLE E_a
(
	EmpID	NUMBER(6)
		REFERENCES Employee(ID) NOT NULL, 
	AccID	NUMBER(6)
		REFERENCES Account(ID) NOT NULL,
	UNIQUE(EmpID, AccID)
);

CREATE TABLE C_a
(
	CustID	CHAR(9)
		REFERENCES Customer(SSN) NOT NULL, 
	AccID	NUMBER(6)
		REFERENCES Account(ID) NOT NULL,
	UNIQUE(CustID, AccID)
);

CREATE TABLE C_i
(
	CustID	CHAR(9)
		REFERENCES Customer(SSN) NOT NULL, 
	InsID	NUMBER(6)
		REFERENCES Insurance(ID) NOT NULL,
	UNIQUE(CustID, InsID)
);

CREATE TABLE V_m
(
	VehID	NUMBER(6)
		REFERENCES Vehicle(ID) NOT NULL, 
	MecID	NUMBER(6)
		REFERENCES Mech_problems(ID) NOT NULL,
	UNIQUE(VehID, MecID)
);

CREATE TABLE V_o
(
	VehID		NUMBER(6)
			REFERENCES Vehicle(ID) NOT NULL, 
	OilChgID	NUMBER(6)
			REFERENCES Oil_change(ID) NOT NULL,
	UNIQUE(VehID, OilChgID)
);

CREATE TABLE T_b
(
	TransID		NUMBER(12)
			REFERENCES Transaction(ID) NOT NULL, 
	BranchID	NUMBER(6)
			REFERENCES Branch(ID) NOT NULL,
	UNIQUE(TransID, BranchID)
);

CREATE TABLE T_c
(
	TransID	NUMBER(12)
		REFERENCES Transaction(ID) NOT NULL, 
	CustID	CHAR(9)
		REFERENCES Customer(SSN) NOT NULL,
	UNIQUE(TransID, CustID)
);

CREATE TABLE T_v
(
	TransID	NUMBER(12)
		REFERENCES Transaction(ID) NOT NULL, 
	VehID	NUMBER(6)
		REFERENCES Vehicle(ID) NOT NULL,
	UNIQUE(TransID, VehID)
);

CREATE TABLE Ac_c
(
	AccidentID	NUMBER(6)
			REFERENCES Accidents(ID) NOT NULL, 
	CustID		CHAR(9)
			REFERENCES Customer(SSN) NOT NULL,
	UNIQUE(AccidentID, CustID)
);

CREATE TABLE Ac_v
(
	AccidentID	NUMBER(6)
			REFERENCES Accidents(ID) NOT NULL, 
	VehID		NUMBER(6)
			REFERENCES Vehicle(ID) NOT NULL,
	UNIQUE(AccidentID, VehID)
);
