Re: Question: Putting Data Into 3rd Normal Form

Replies in-line marked witn "--->"

"confused" <confused_at_no-email.com> wrote in message news:9ba9kq$rm9$1_at_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" )

---> Including passenger capacity and the rate code in this table only works if these values are always the same for a particular model code. For example, in the U.S., we have some vans that hold 8 pasengers, but if configured differntly, may hold 5 or 11 passengers, yet they are still the same (nameplate) model. Basically this is a _business_ issue that needs to be specified in your assumptions.
>
>
> TBL_HIRE_RATE
> daily_hire_rate_code (PRIMARY KEY)
> amount

---> What about tiered rates? Daily, weekend, weekly, monthly... What about special rates (governement, corporate, frequent users)?
>
>
> Feel free to criticise :)
>
>
>
> Specific questions:
>
> 1: 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)

---> This may make it easier to implement the multiple rates I refrred to above.
>
>
>
> 2: What would be the best way of separating "manufacturer" into its own
> table ?

---> Too easy to answer.

>
>
> 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 ?
>
---> You will find that in this line of work, there is only one answer, which is, "It depends."
Normalizing will introduce performance issues, but it depends on what data you need to look up, how often, etc. It then may cause a dependency on sufficient hardware. It goes on and on. You need to think about the problem and consider everything you can think of. the "best" solution is the one that is right for your particular set of circumstances. If you were to point a gun to my head and demand an answer( ooops, I forgot, you don't have guns in GB (double oops, unless your the IRA)), I would say do not further normalize the vehicle type. Theorists may give you a dogmatic answer, but the reality is that it really does depend.
>
>
Received on Mon Apr 16 2001 - 17:08:15 CEST