FACTS for a Database for Vehicle Rental
The purpose of the Web Site is to support a Vehicle Rental System.
A Vehicle Rental Company. The full scenario includes data on staff, offices and so on but, the Data Model addresses 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_codeTBL_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 Specific questions: 1: It is "better" to include an additional table, linking hire rate to vehicle model - something like: TBL_HIRE_RATE_FOR_MODEL 1 model_code (COMPOSITE PRIMARY KEY) 2 daily_hire_rate_code (COMPOSITE PRIMARY KEY) 2: What would be the best way of separating "manufacturer" into its owntable ? 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 ?
Clarification is required of the appropriate Keys (primary, foreign, composite etc.) A good approach for Primary Keys is to make them 'surrogate' keys, which are simply unique integers, which are constantly increasing. These are implemented as Sequences in Oracle and Identity Fields in Microsoft SQL Server. This is especially appropriate for Patients, where it is useful to have use a number which can be automatically generated in a straightforward manner. For Tables of Reference or Static Data, such as Levels of Patient Care, there is usually an obvious Primary Key,such as the Level Number, which can be used as the Primary Key. There are other situations, frequently referring to fixed assets or situations, where there may be an obvious Primary Key. A good example is the Rooms in the Nursing Home, where it might be sensible to use the Room Number as the Primary Key of the Rooms Entity or Table. It is necessary to be sure that Rooms will always be referred to in the same way in the future. If they are not integers, such as Room 213, then the field can be defined as a character string. By choosing a length, such as 10 characters, provides a substantial amount of flexibility for the future. A length of 5 might also be adequate for the present, and migrating in the future is a straightfoward DBA task. Barry Williams Principal Consultant Database Answers 15th. April 2001