CMPUT 391: Database Management Systems

Assignment 1

CMPUT 391 (Winter 2002)

Due Date (in class): Monday February 4th, 2002.
Percentage overall grade: 3%
Penalties: 20% off a day for late assignments
Maximum Marks: 100

Consider the following database schema containing two tables, which describe a car rental enterprise: 

CarRental = (
ContractId,
BeginDate,
EndDate,
Distance,
FromOffice,
ToOffice,
DriverName,
DriverBirthday,
DriverStreet,
DriverCity,
DriverZip,
DriverCountry,
DriverLicenceNr,
DriverCustomerCategory,
VehiclePlate,
VehicleBrand,
VehicleModelNo,
VehicleCategory,
VehicleNoSeats,
VehicleHomeOffice,
VehicleFreeKm,
VehiclePricePerDay,
PricePerKm
ExtraCharges
DiscountPercent
) Foreign Key FromOffice, ToOffice, VehicleHomeOffice References Office

Office = (
OfficeId,
OfficeStreet,
OfficeCity,
OfficeZip,
OfficeCountry,
OfficePhone,
OfficeFax,
OfficeManager,
OfficeRegionCode
)

The given attribute names are mostly self-explaining. The intuition behind the given schema is the following:

  • The ContractId identifies a car rental.
  • A car is rented for a certain number of days: from BeginDate to EndDate, and it is used to drive a certain Distance (in km). The rent for the whole period is not stored explicitly in the table, but computed from the information about the used km (Distance), and the price per day, the free km and the price per km for a given vehicle. However, additional charges and a certain discount may also be applied. These are stored in the fields ExtraCharges and DiscountPercent.
  • The car is picked-up from the office FromOffice and returned to the office ToOffice. Both FromOffice and ToOffice are names to identify offices, which have a phone, a fax, a manager, and an address that consists of street, city, zip, and country.
  • The information about the Driver who rents a vehicle consists of name, birthday, licence number, and an address, again consisting of street, city, zip, and country. The combination of driver name and birthday is assumed to be unique. Each driver belongs to a customer category, which will be assigned based on how often he rents vehicles from the given company.
  • Each Vehicle has a unique plate and a unique model number. Further attributes that describe that describe a vehicle are its brand name, category (sedan, minivan, etc), number of seats, the office to which it belongs (HomeOffice), and the number free km (FreeKm) included in its PricePerDay.
  • A manager is possibly the manager of more than one office. In fact, each manager manages all the offices in certain region, i.e. offices having the same region code have the same manager.
  • The price per day, the free number of km, and the price per km for a given vehicle are dependent only on the brand name, the category and number of seats of the vehicle.
  • The discount that may be applied to compute the price of a car rental is determined by the drivers customer category.
  • The extra charges in a car rental are determined by the birthday (containing implicitly the information about the age) of the driver and its customer category.

Deliverables:

Your task is to normalize the given schema by a step-wise decomposition of relations using a functional dependency that violates one of the normal forms (1NF, 2NF, 3NF, BCNF), i.e., in each step you decompose exactly one table into two tables. The target is a lossless join decomposition into BCNF. Starting with the given schema, and for each intermediate schema that you obtain, give the following information:

  1. All non-trivial functional dependencies (FDs) that hold over the relations of the schema.
  2. For each of these FDs: which normal forms does it violate - if any.
  3. The "highest/greatest" normal form that the current schema satisfies (1st < 2nd < 3rd < BCNF).
  4. If the schema was obtained by a decomposition (i.e., all of your schemas, except for the given initial schema) give an argument that shows whether the decomposition was loss-less join and dependency-preserving.
  5. For each table: underline a primary key, and give all foreign key constraints.
Note that we want every decomposition step explained and not just the final schema.

NOTE:

  1. Be sure to follow these instructions, i.e., give a step-wise solution including all the requested information, because the marks for this assignment are distributed according to this list!
  2. To make marking easier, do NOT change the given attribute names (e.g. into something like CI, BD, etc.). If a table does not change from one step to the next, however, you don't have to write down the complete schema for the unchanged table, the name of this table and a note indicating that this table is not changed from the previous step is sufficient.
  3. The assignment should be written with a word processor or editor. No hand-written assignments will be accepted.
HOME     Activities