SQL Exercises 1

Question 1

Customer
(memberId(PK), name, address, licenseNo, dob, email, mobile)

Vehicle
(vid(PK), regState, plateNo, brand, model, year, mileage, rentalPx, rentalClass)

Reservation
(reserveNo(PK), memberId, rentalClass, dateStart, dateReturn)

In a car rental company database, the database have 3 relations: Customer, Vehicle, and Reservation. There are anomalies with the above design such as a vehicle can change from one rentalClass to another based on demand and availability of the class of vehicles. Example, a Nissan Sentra may be reclassified as Economy from Mid Size during peak rental season to make more cars available for that rental class. If such is the case, the rental price (rentalPx) also need to be updated in the Vehicle relation.

For functional dependency analysis, rental price (rentalPx) is functionally dependent on rentalClass.

Transform the database into 3NF and explain if your transformation fulfills BCNF

Question 2

For the Car Rental database shown:

Customer
(memberId(PK), name, address, licenseNo, dob, email, mobile)

Vehicle
(vid(PK), regState, plateNo, brand, model, year, mileage, rentalPx, rentalClass)

Reservation
(reserveNo(PK), memberId, rentalClass, dateStart, dateReturn)

Write SQL that will calculate estimated cost of rental for each reservation. Include the following columns:

memberId, name, reserveNo, rentalClass, daily rate, no of days, estimated total cost

where:

• rentalPx is the daily rate
• no of days is the days difference between dateReturn and dateStart
• estimated total cost = daily rate X no of days