Car Rental Database Schema With Normalization And Functional Dependencies

Tables and Fields

Consider a simplified version E-R model on the car rental activity, where you only deal with three relations Customer, Rental and Car (ignore all other relations) based on the ER model provided for assignment 1 sample solution. Using MySQL Workbench to create a database with the above three relations, include specifying the primary key and foreign
key where it is necessary, and populate the necessary sample data,For on-campus students, demonstrate your implementation as required above in Week 10.

Week 11 or Week 12 tutorial time to your local tutor during the scheduled tutorial session. You don’t need to submit a separate file on this part.For distance students, include the database/or data file of your implementation in a zip file and email it to the Unit coordinator by the end of Week 11.

Important points
• You must work individually on this assignment.
• The model solution to this assignment will be released approximately two (2) weeks after the due date.
• Assignments that are submitted after the release of the model solution will receive zero marks.
• You must state in your word document the details of all materials and sources that you used to complete this assignment. This may be as simple as ‘This assignment was completed using only the COIT 20247 unit materials’.
• Extension requests (by filling in an online form) should be submitted before the assignment due date.
• Before submitting your assignment, review the marking criteria provided on the last page of this document. Ensure that all the criteria are addressed in your submission.
• Penalties related to late submission and plagiarism will be applied as per University policy.

a) Relations in 3NF

Customer (customerID, firstName, lastName, birthdate, address, phone, licenseNO, customerTye)

• Primary key (customerID)

Individual (CustomerID, personalLifeInsured)

• Primary key (customerID)
• Foreign key (CustomerID) references customer (customerID)

Corporate (customerID, organizationName, ABN, travelSpecialDeal )

• Primary key (customerID)
• Foreign key (CustomerID) references customer (customerID)

Booking (ReservationID, description, bookingDate, customerID, employeeID, categoryID)

• Primary key (reservationID)
• Foreign key (customerID) references customer (customerID)
• Foreign key (employeeID) references employee (employeeID)
• Foreign key (categoryID) references vehicle_category (categoryID)

Rental (rentalID, startDate, endDate,cost, pickupLocationID, DropOffLocationID, regNO, reservationID)

• Primary key (rentalID)
• Foreign key (regNO) references car (regNO)
• Foreign key (reservationID) references Booking (reservationID)
• Foreign key (pickupLocationID) references location (locationID)
• Foreign key (dropOffLocationID) references location (locationID)

Location ( LocationID, locationName, address, isMainOffice)

• Primary key (locationID)

Car (regNo, make, model, numOfSeats, manufacturedYear, carImage, categoryID)

• Primary key (regNO)
• Foreign key (categoryID) references vehicle_category (categoryID)

Vehicle_Category ( CategoryID, categoryName, DailyHireRate)

• Primary key (categoryID)

Insurance (policyNO, insuranceName, premium, startDate, expiryDate,regNO)

• Primary key (policyNO)
• Foreign key (regNO) references car (regNO)

Employee (EmployeeID, firstName, lastName, birthDate, phone,skillLevel, annnualSalary,locationID)

• Primary key (emplloyeeID)
• Foreign key (locationID) references location (locationID)

1. Functional dependencies and 3NF demonstration

Customer (customerID, firstName, lastName, birthdate, address, phone, licenseNO, customerTye)

Functional dependencies:

(CustomerID) à (firstName, lastName, birthdate, address, phone, licenseNO, customerTye)

Demonstration of normalization

1. The primary key for the customer entity is customerID. This relation has no repeating groups because a customer has only one first name, last name, birth date, address, phone, license number and is either a individual customer or corporate customer. Thus this relation is in 1NF.
2. The primary key is customerID. CustomerID is the only candidate key since a two customers can have matching first name, last name, birth date, address, phone number, license number and can be of the same type thus these attributes cannot be guaranteed to be unique thus the entity is in 2NF.

• The primary key customerID is the only functionally determiner and determines all the other attributes. The first name, last name, birth date, address, phone number, license number and customer type cannot be used to functionally determine the other attributes because their uniqueness is not guaranteed for every record. Thus this entity is in 3NF.

Location ( LocationID, locationName, address, isMainOffice)

Functional dependencies:

(locationID)àlocationName, address, isMainOffice)

Demonstration of normalization

1. locationID is the only primary key for the location entity. A location has only one locationName, one address and one status of whether it’s the main office or not. Thus the entity has no repeating groups which means its already in 1NF.
2. LocationID is the only candidate key. Two locations can have the same location name, same address and the same main office status thus these attributes cannot be used as candidate keys since their uniqueness is not guaranteed. Thus this relation is in 2NF.

• The locationName cannot be used to functionall determine the other attributes. Neither can the address and isMainOffice relation thus there is only key attribute locationID which functionally determines the locationName, address and isMainOffice attributes. Thus this relation is in 3NF.
• Implementation Report

This section discusses my experience while undertaking the assignment. My experience with the task was both informative and challenging. While doing the assignment I discovered two important aspects while undertaking the task. These are;

• Writing of complex queries involving multiple tables by using inner join. This helped get the perfect demonstration of what primary and foreign keys are for in a relational database. By associating the foreign key and the primary key of two tables, you are able to join the two tables as one. This can be nested over multiple tables forming one table and then filtering the fields you want from the joint table. This concept of using join is very helpful as it helps make queries easier to write if it’s properly understood.
• Another interesting concept about databases was database integrity. When creating relationships between tables, I realized that integrity of the data between tables that are related can be enforced by applying either cascading update or cascading delete to make sure that any changes that happen in the parent table are also updated in the child table. This help maintains data integrity and consistency as no records in the child table can exist for a record that is not in the parent table.

The task was not smooth throughout the way as I experienced some challenges while undertaking the task. Some of the challenges include;

• Writing complex queries involving multiple joins. This part of the task was a bit confusing at first as I had to research and understand on the syntax of multi-table joins. At first it seemed very complex to grasp but after watching videos and reading from different tutorials I understood the concept and was able to apply it to solve all the queries.
• Part B

CREATE DATABASE IF NOT EXISTS `car_rental`;

CREATE TABLE `Car` (

  `regNO` VARCHAR(6) NOT NULL,

  `make` VARCHAR(255),

  `model` VARCHAR(255),

  `numOfSeats` INTEGER,

  `ManufacturedYear` INTEGER,

  `carImage` VARCHAR(255),

  `categoryID` INTEGER

INSERT INTO `Car` (`regNO`, `make`, `model`, `numOfSeats`, `ManufacturedYear`, `carImage`, `categoryID`) VALUES (‘abc123’, ‘Toyota’, ‘Noah’, 7, 2018, ‘image1.jpg’, 1);

INSERT INTO `Car` (`regNO`, `make`, `model`, `numOfSeats`, `ManufacturedYear`, `carImage`, `categoryID`) VALUES (‘acd321’, ‘Nissan’, ‘Vanette’, 2, 2017, ‘image2.jpg’, 2);

INSERT INTO `Car` (`regNO`, `make`, `model`, `numOfSeats`, `ManufacturedYear`, `carImage`, `categoryID`) VALUES (‘asd456’, ‘Chevrolet’, ‘Camaro’, 5, 2016, ‘image3.jpg’, 4);

INSERT INTO `Car` (`regNO`, `make`, `model`, `numOfSeats`, `ManufacturedYear`, `carImage`, `categoryID`) VALUES (‘qwe123’, ‘Mitsubishi’, ‘Pajero’, 5, 2015, ‘image4.jpg’, 5);

CREATE TABLE `Customer` (

  `customerID` INTEGER NOT NULL,

  `firstName` VARCHAR(255) NOT NULL,

  `lastName` VARCHAR(255) NOT NULL,

  `birthDate` DATETIME,

  `address` VARCHAR(255),

  `phone` VARCHAR(255),

  `licenseNO` VARCHAR(255),

  `customerType` VARCHAR(255)

INSERT INTO `Customer` (`customerID`, `firstName`, `lastName`, `birthDate`, `address`, `phone`, `licenseNO`, `customerType`) VALUES (1, ‘Peter’, ‘Griffin’, ‘2000-07-05 00:00:00’, ‘1st av rhode island’, ‘+44 343343 324’, ‘23434324’, ‘individual’);

INSERT INTO `Customer` (`customerID`, `firstName`, `lastName`, `birthDate`, `address`, `phone`, `licenseNO`, `customerType`) VALUES (2, ‘Lois’, ‘Griffin’, ‘2990-06-12 00:00:00’, ‘2nd av rhode island’, ‘+4334343 344’, ‘433434 32’, ‘corporate’);

INSERT INTO `Customer` (`customerID`, `firstName`, `lastName`, `birthDate`, `address`, `phone`, `licenseNO`, `customerType`) VALUES (3, ‘Stewie’, ‘Griffin’, ‘2002-07-10 00:00:00’, ‘3rd av rhode island’, ‘+324 32434’, ‘4234324’, ‘individual’);

CREATE TABLE `Rental` (

  `rentalID` INTEGER NOT NULL,

  `startDate` DATETIME,

  `endDate` DATETIME,

  `cost` DECIMAL(19,4),

  `pickupLocationID` INTEGER,

  `dropOffLocationID` INTEGER,

  `regNO` VARCHAR(6),

  `reservationID` INTEGER

INSERT INTO `Rental` (`rentalID`, `startDate`, `endDate`, `cost`, `pickupLocationID`, `dropOffLocationID`, `regNO`, `reservationID`) VALUES (1, ‘2018-09-13 00:00:00’, ‘2018-09-14 00:00:00’, 435, 1, 1, ‘abc123’, 1);

INSERT INTO `Rental` (`rentalID`, `startDate`, `endDate`, `cost`, `pickupLocationID`, `dropOffLocationID`, `regNO`, `reservationID`) VALUES (2, ‘2018-09-14 00:00:00’, ‘2018-09-17 00:00:00’, 4321, 2, 2, ‘qwe123’, 2);

INSERT INTO `Rental` (`rentalID`, `startDate`, `endDate`, `cost`, `pickupLocationID`, `dropOffLocationID`, `regNO`, `reservationID`) VALUES (3, ‘2018-09-14 00:00:00’, ‘2018-09-22 00:00:00’, 2330, 3, 3, ‘abc123’, 3);

INSERT INTO `Rental` (`rentalID`, `startDate`, `endDate`, `cost`, `pickupLocationID`, `dropOffLocationID`, `regNO`, `reservationID`) VALUES (4, ‘2018-09-21 00:00:00’, ‘2018-09-22 00:00:00’, 300, 1, 1, ‘abc123’, 4);