CSci 275 Assignment 3

Due: Thursday, March 1, 2018.

A. Given the following table with repeating groups in braces, go from this unnormalized form (UNF)
     to third normal form (3NF), showing all steps and functional dependencies used.
     Does the result match the relations below?


      LocationID, LAddress, LCity, LPhone,
        { RentalID, CustomerID, CName, CAddress, CPhone, DOB, Province, MasterNumber, Expiry,
            VIN, Category Id, CategoryName, Manufacturer, Model, Year, Color, Prov, Licence,
            Fee, CreditCard, Expiry, CCV, FuelStatus, PickupDate, PickupTime,
            DropoffDate, DropoffTime, { ReturnFeeReasonNumber, Charge, Description } }

B. Use the following relational schema from Assignment 2 to generate relational algebra solutions to the queries below:
 

Location ( LocationID, Address, City, Phone )
Vehicle ( VIN, CategoryID, Manufacturer, Model, Year, Color, Province, License )
Category ( CategoryID, Name )
Customer ( CustomerID, Name, Address, Phone, DOB, Province, MasterNumber, Expiry )
Rates ( LocationID, CategoryID, DailyRate, WeekendRate, WeeklyRate, MonthlyRate )
Rental ( RentalID, CustomerID, CategoryID, VIN, Fee, CreditCard, Expiry, CCV, FuelStatus)
Pickup ( RentalID, LocationID, PickupDate, Time)
Dropoff ( RentalID, LocationID, DropoffDate, Time)
ReturnFee ( RentalID, ReasonNumber, Charge, Description)
  1. List the name of all renters associated with the car with VIN 12345678.
     
  2. List the name for all renters who have a vehicle reserved during some part of reading week (i.e. 19/02/2018 to 23/02/2018).
     
  3. List the addresses of all locations that have at least one of each category of vehicle listed in the Category table; that is, that have an entry in the Rates table for each of the categories listed in the category table.
     
  4. List the location address and daily rate for all vehicles of category 'SUV' in 'Halifax'.
     
  5. List the names and addresses of all renters who have a return fee for fuel charge (ReasonNumber 10), giving details of their rental, including dropoff location address and city, dropoff date, and return fee fuel charge.