Computer Science 455

Fifth Exercise Set

Due: Tuesday, March 30.

Consider a moving violation form (such as might be written up for you if you were exceeding the speed limit) with the following characteristics:

• Your name, driver's licence (assume this as a key), address, telephone
• Your car's make, year, type, VIN and licence plate number. Suppose that make and year are a function of VIN, but that a car with the same VIN may have more than one licence plate number over time. The type is a function of the car's make.
• The citing officer's badge number and name
• The date, time, and location of the event
• For each of several possible citations
  • A citation code
  • A brief description of the citation type (this will depend on the citation code)
  • A brief description of this event

Following carefully the scheme outline in class,

1. Write down the form as a raw table.
2. Identify functional dependencies (and draw a FD diagram)
3. Put the form into 1NF by removing repeating and compound fields (assume that names are a compound field formed by first and last names and a middle initial). Describe the tables that result from this process (table names and fields, indicating keys and foreign keys for each table)
4. Put the form into 2NF by removing any partial key dependencies. Describe the tables that result from this process (table names and fields, indicating keys and foreign keys for each table)
5. Put the form into 3NF by removing any transitive dependencies. Describe the tables that result from this process (table names and fields, indicating keys and foreign keys for each table).
6. Verify that the resulting tables are in BCNF.