Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    2
    e
    Last edited by helango; 09-09-03 at 16:15.

  2. #2
    Join Date
    Jul 2003
    Posts
    18
    Don't take this the wrong way, but your schema is simply unworkable :P. I shall take you through the process by which I would design the schema and hopefully you'll see the methodology by which a good schema can be created.

    When designing a DB schema it is best to start by identifying the logical elements that are present in the functional requirements. In this case I would initially identofy the following:

    clients
    cars
    bookings

    So we shall make a table for each of these we end up with:

    clients
    client_number (PK)
    name
    address
    phone
    discount_category

    cars
    registration_number (PK)
    manufacturer
    model
    car_type
    year_manufactured
    km_traveled
    condition

    bookings
    booking_id (PK)
    client_number (FK)
    registration_number (FK)
    date_required
    date_delivered
    date_due
    date_returned
    km_on_delivery
    km_on_return
    rental_rate
    license_number

    This is the first step in creating a properly normalized database. We have just created a table structure where all tables contain a set of unique records.

    However we are not finished. There are a few corrections that need to be made to this in order to satisfy the business model more appropriately.

    Firstly, we are told that the rental rate depends on the type of car. Seeing as cars don't change type, it would be more logical to move that field from the bookings table into the cars table, and a booking can reference it from there as it is associated with the car that the booking is referenced to.

    Secondly, if we are looking at a booking at a later date, there is no way to find out what the discount category was, as it may have changed for a customer due to them perhaps being older than when they first rented, or having a better or worse insurance rating. The solution is to add discount_category to the bookings table as well.

    So we end up with this:

    clients
    client_number (PK)
    name
    address
    phone
    discount_category

    cars
    registration_number (PK)
    manufacturer
    model
    car_type
    year_manufactured
    km_traveled
    condition
    * rental_rate

    bookings
    booking_id (PK)
    client_number (FK)
    registration_number (FK)
    * discount_category
    date_required
    date_delivered
    date_due
    date_returned
    km_on_delivery
    km_on_return
    license_number

    I have asterisked the changes.

    The table 'cars' can be keyed on registration_number as it is unique and we will assume it doesn't change.

    This DB is now in 2NF, as there are no duplicate records (1NF) and all columns are dependent on the PK of their respective tables. However if we look at rental_rate in the cars table, we have been told that it depends on the car_type. As car_type is not part of the key, this table fails to satisfy the requirements of 3NF.

    So what we do is put car_type into a new table:

    types
    car_type
    rental rate

    This is a good idea anyway, as now the company can add more types to their range with no problem, and change rates without having to change more than one record.

    This presents the problem that if rates DO change, then each booking now has no way of determining what the rate was at the time the booking was made. So we need to add rental_rate back to the booking table.

    (Note that the reason I removed it earlier and then added it again here was because I wanted to illustrate the process by which schema development logically should follow. Once you become familiar with the process you'll be able to see straight away which fields are needed without going through all of these steps.)

    So we now have:

    clients
    client_number (PK)
    name
    address
    phone
    discount_category

    cars
    registration_number (PK)
    manufacturer
    model
    car_type
    year_manufactured
    km_travelled
    condition

    bookings
    booking_id (PK)
    client_number (FK)
    registration_number (FK)
    discount_category
    date_required
    date_delivered
    date_due
    date_returned
    km_on_delivery
    km_on_return
    licence_number
    * rental_rate

    types
    car_type (PK)
    rental rate

    Note that rental rate is now not in cars but in a separate types table. Thus the DB is now in 3NF as there are no duplicate records, all attributes are dependent on the primary key (PK) of each table and there are no transient dependencies.

    Given that some clients may be companies, and may have more than one driver, we'll need to add another table for drivers, so that multiple drivers can be associated with each booking.

    To do this we will need to have two more tables; a drivers table and a driver_booking_links table to satisfy the many to many relationship (several drivers may be associated with a single booking, and a single driver may also have made several bookings over a period of time. So:

    drivers
    drivers_license_number (PK)
    name
    date_of_birth

    driver_booking_links
    drivers_license_number (PK)
    booking_id (PK)

    This allows for there to be multiple drivers per booking, and for a driver to make multiple bookings. Note that there is no need to link the driver and the booking instance to a client, each driver will have that information stored via the bookings.client_number field. Another positive effect of this is that a driver may make bookings under his company or personally, and the database can be queried to return all the booking a particular driver has made in total or to filter it for those he made with his company or individually.

    Now for brownie points and to go that little bit extra, I would put car condition into a log for each car so you can log a car's history. This is done by creating another table that simply makes a log entry for each car whenever an incident involving a car's condition occurs like this:


    logbooks
    registration_number (PK)
    datetime_of_entry (PK)
    condition

    This will allow you to log the condition of each car over a period. The combination of date/time with the car's registration number is a unique combination guaranteed, allowing us to use them together as a primary key.

    So, we can now do a full statement of the schema that results from the process of looking at the requirements of your homework assignment sheet :P Here it is, I hope you find it helpful:

    clients
    client_number (PK)
    name
    address
    phone
    discount_category

    cars
    registration_number (PK)
    manufacturer
    model
    car_type
    year_manufactured
    km_travelled
    condition

    logbooks
    registration_number (PK)
    datetime_of_entry (PK)
    condition

    bookings
    booking_id (PK)
    client_number (FK)
    registration_number (FK)
    discount_category
    date_required
    date_delivered
    date_due
    date_returned
    km_on_delivery
    km_on_return
    license_number
    rental_rate

    types
    car_type (PK)
    rental_rate

    drivers
    drivers_license_number (PK)
    name
    date_of_birth

    driver_booking_links
    drivers_license_number (PK)
    booking_id (PK)

    Some assumptions I have made:
    1. Discount category details are constant. As there are only 3 of them, I assumed that the application front end will be able to have the effect each has on the booking price hard coded into it, rather than soft coding an algorithm that reads data from a discount_categories table and calculates the overall rate. This would require an additional final_rate field in bookings to allow for historical data in the table to remain true in the event that discount category details changed.
    2. I have assumed that discount categories are only associated to clients, and not drivers. This schema will not allow a driver to be associated with a discount category, unless they create their own client_number record.
    3. I have assumed that there does not need to be any details of the condition of the car associated with each booking beyond the odometer readings. Therefor the only way to check the condition of the car for a specific booking is to look at the time the log was made and then compare it with the bookings made with regard to that car at that time or vice versa.

    Booking query rules:
    1. To find open bookings that have been made but not net delivered then simply look for all bookings records where date_delivered is NULL.
    2. To find bookings where the car is still out look for all bookings records where date_returned is NULL
    3. To find all bookings where the car was overdue look for all bookings records where date_returned is after date_due
    4. To find all bookings where the car is still out and is now overdue then look for all bookings records where date_due is before the current date AND date_returned is NULL.
    5. To find all bookings that were served late look for all bookings records where date_delivered is after date_required.
    6. To find all bookings that were made and never filled look for all bookings where date_due is before the current date and date_delivered is NULL.

    Those are some rules you can use to find bookings and filter them by what happened. I will not bother writing plain English queries like this for all possible queries that may need to be done as I have a lot of other things I need to do, so I hope you get the general idea by now

    Finally, I have not had time to do a thorough audit of this, so if anyone else has time feel free to point out any mistakes I may have made in normalization or structural dependency derivation.

    I'm allowed 10,000 characters per post. This is 9499, including this line

    Hope this helps.
    - Naz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •