A GWMSIST graduate research project demonstrating iterative database administration, design and modeling.

create.sql

CREATE TABLE VEHICLE
(
  VIN VARCHAR(30) NOT NULL,
  Make VARCHAR(30) NOT NULL,
  Model VARCHAR(30) NOT NULL,
  Year INT NOT NULL,
  Color CHAR(30) NOT NULL,
  LicensePlate VARCHAR(20) NOT NULL,
  Mileage INT NOT NULL,
  PRIMARY KEY (VIN),
  UNIQUE (LicensePlate)
);

CREATE TABLE EMPLOYEE
(
  EmployeeFName VARCHAR(100) NOT NULL,
  EmployeeID INT NOT NULL,
  EmployeeLName VARCHAR(100) NOT NULL,
  Title VARCHAR(100) NOT NULL,
  SSN INT NOT NULL,
  PRIMARY KEY (EmployeeID),
  UNIQUE (SSN)
);

CREATE TABLE ACCESSORY
(
  SerialNumber INT NOT NULL,
  Type VARCHAR(50) NOT NULL,
  Cost DECIMAL(20,2) NOT NULL,
  Quantity INT NOT NULL,
  PRIMARY KEY (SerialNumber)
);

CREATE TABLE CUSTOMER
(
  CustomerID INT NOT NULL,
  CustomerFName VARCHAR(100) NOT NULL,
  CustomerLName VARCHAR(100) NOT NULL,
  CustomerEmail VARCHAR(200) NOT NULL,
  DriversLicense VARCHAR(50) NOT NULL,
  RewardNumber INT,
  PRIMARY KEY (CustomerID),
  UNIQUE (DriversLicense)
);

CREATE TABLE CUSTOMER_PHONE_NO
(
  CustomerPhoneNo VARCHAR(20) NOT NULL,
  CustomerID INT NOT NULL,
  PRIMARY KEY (CustomerPhoneNo),
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);

CREATE TABLE RENTAL_OFFICE_LOCATION
(
  RentalOfficeID INT NOT NULL,
  StreetAddress VARCHAR(200) NOT NULL,
  City VARCHAR(100) NOT NULL,
  State VARCHAR(100) NOT NULL,
  ZipCode INT NOT NULL,
  PRIMARY KEY (RentalOfficeID)
);

CREATE TABLE MAINTENANCE_LOG
(
  Maintenance_ID INT NOT NULL,
  EmployeeID INT NOT NULL,
  VIN VARCHAR(30) NOT NULL,
  Maintenance_Date DATE NOT NULL,
  Maintenance_Procedure VARCHAR(200) NOT NULL,
  PRIMARY KEY (Maintenance_ID),
  FOREIGN KEY (VIN) REFERENCES VEHICLE(VIN),
  FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID)
);

CREATE TABLE RESERVATION
(
  ReservationID INT NOT NULL,
  Pick_Up_Date DATE NOT NULL,
  Return_Date DATE NOT NULL,
  Daily_Rate DECIMAL(20,2) NOT NULL,
  Total DECIMAL(20,2) NOT NULL,
  CustomerID INT NOT NULL,
  RentalOfficeID INT NOT NULL,
  PRIMARY KEY (ReservationID),
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID),
  FOREIGN KEY (RentalOfficeID) REFERENCES RENTAL_OFFICE_LOCATION(RentalOfficeID)
);

CREATE TABLE EMPLOYING
(
  RentalOfficeID INT NOT NULL,
  EmployeeID INT NOT NULL,
  PRIMARY KEY (RentalOfficeID, EmployeeID),
  FOREIGN KEY (RentalOfficeID) REFERENCES RENTAL_OFFICE_LOCATION(RentalOfficeID),
  FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID)
);

CREATE TABLE RESERVES
(
  VIN VARCHAR(30) NOT NULL,
  ReservationID INT NOT NULL,
  PRIMARY KEY (VIN, ReservationID),
  FOREIGN KEY (VIN) REFERENCES VEHICLE(VIN),
  FOREIGN KEY (ReservationID) REFERENCES RESERVATION(ReservationID)
);

CREATE TABLE INCLUDES
(
  SerialNumber INT NOT NULL,
  ReservationID INT NOT NULL,
  PRIMARY KEY (SerialNumber, ReservationID),
  FOREIGN KEY (SerialNumber) REFERENCES ACCESSORY(SerialNumber),
  FOREIGN KEY (ReservationID) REFERENCES RESERVATION(ReservationID)
);