Question: Putting Data Into 3rd Normal Form

From: confused <confused_at_no-email.com>
Date: Sat, 14 Apr 2001 20:50:21 +0100
Message-ID: <9ba9kq$rm9$1@newsg1.svr.pol.co.uk>

Thanks in advance for any suggestions and constructive criticisms on what I have attempted so far.
( and yes, it does relate to a college assignment )

A vehicle-rental company

The full scenario includes data on staff, offices and so on but, I want to ask about one part of this.

The company rents out 2 types of vehicle - "car" and "van"

For each Vehicle - the following data is collected

  Registration Number
  Manufacturer Name
  Model
  Engine Size
  Passenger Capacity
  Current Mileage
  current location
  daily hire rate
  date of next MOT inspection

( In Britain - MOT inspection is a mandatory annual inspection all vehicles
have to undergo from its 3rd year )

My attempt to produce 3rd Normal Form has produced the following tables :

TBL_VEHICLE
  Reg_number (PRIMARY KEY)
  engine_size
  date_MOT_due
  current_mileage
  current_location
  model_code

TBL_MODEL_DETAILS
model_code (COMPOSITE PRIMARY KEY) Manufacturer_name (COMPOSITE PRIMARY KEY) Model_name (COMPOSITE PRIMARY KEY)

  passenger_capacity
  daily_hire_rate_code
  vehicle_type ( C for "car" V for "van" )

TBL_HIRE_RATE
daily_hire_rate_code (PRIMARY KEY)
amount

Feel free to criticise :)

Specific questions:

It is "better" to include an additional table, linking hire rate to vehicle model - something like:

TBL_HIRE_RATE_FOR_MODEL

  model_code                (COMPOSITE PRIMARY KEY)

daily_hire_rate_code (COMPOSITE PRIMARY KEY)

2: What would be the best way of separating "manufacturer" into its own table ?

3: Is it "better" to include an additional table for vehicle_type, or to leave it as an attribute of TBL_MODEL_DETAILS, given that there are only 2 types ? Received on Sat Apr 14 2001 - 14:50:21 CDT