Re: normalisation

Hi again,

this is the situation I have to solve:

Car Rental Booking Department.

"Each of our clients is assigned a unique client number. Other information we store about clients includes their name, address, telephone number and discount category. There are three discount categories which we use in conjunction with the standard rate for the car to calculate the final price for a rental booking. When a client makes a booking for a car, we store the following information for the booking: client number, car registration number, date delivered, date due back, date returned, kilometres when delivered, kilometres when returned, rental rate (depends on the type of the car), drivers license number of the driver (since our clients may be companies), and amount due. When I look up information on available cars, I need to view the following information: car registration number, year of manufacture, manufacturer and model, kilometres travelled and comments on the condition of the car. We keep all the booking data for the cars in the system, not just bookings which are currently active ( i.e. bookings for cars which have not yet been returned)."

I came up with following relations

client (client_id(PK), name, address, telephone, discount)

car (reg_number(PK), year_manufactured, manufacturer, model, car_type,

km_travelled, last_serviced, due_serviced, comments)

rental_booking (rental_booking_id(PK), client_id, reg_number, car_type,

rent_date, due_date, return_date, deliever_km,

returned_km, rental_rate, drivers_license,

amount_due)

Thanks a lot

Regards

Chris

"Bob Hairgrove" <rhairgroveNoSpam_at_Pleasebigfoot.com> wrote in message news:3ccaabf4.2995427_at_news.ch.kpnqwest.net...
> On Sat, 27 Apr 2002 13:06:47 GMT, "Chris" <c_at_coelle.net> wrote:
>
> >Hi,
> >
> >I've got the following relation
> >
> >rental_booking (rental_booking_id, client_id, reg_number, car_type,
> > rent_date, due_date, return_date,
> >deliever_km,
> > returned_km, rental_rate,
> >drivers_license,
> > amount_due)
> >
> >Coul someone please tell me what the repeating group is?
> >
>
> Not sure about "repeating groups", but it looks like there are some
> redundancies:
>
> (1) Can't you calculate amount_due from the other data given?
>
> (2) Assuming "drivers_license" is unique to the client, you should
> have either client_id or drivers_license (but not both) in this table.
>
> (3) Is reg_number unique to each car? Then you shouldn't have
> "car_type" in the table either, but look it up in the table of cars.
>
> (4) Presumably rental_rate is an attribute of the type of car; there
> might also be discounts depending on holidays, clients, days rented,
> etc. Presumably this could also be looked up and shouldn't be in the
> table.
>
>
> Bob Hairgrove
> rhairgroveNoSpam_at_Pleasebigfoot.com
Received on Sat Apr 27 2002 - 16:35:10 CEST