Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2004
    Posts
    78

    normalisation help

    hi guys

    i really need you tech boffins help for normalising some tables for my a database for my taxi company that i have brought. i'm quite a technical inthusiast and i have read on many websites that when designing a database it is important to have normalised tables. so can any one help me normalise my fields i have it in unormalised form. (below). i have looked at tutorials on websites and they don't seem relevant to my database and i would like to learn how to do this for my database. so could some one show me how to do first normal form and then second normal form and then third normal for please.

    here is unnormalised form.


    we have a number of offices located in the main cities of Scotland

    office number
    address
    street city
    postcode
    phoneno.

    Each office has four kinds of staff: a Manager, several taxi owners, administrators and a number of drivers. Staff can be located at more than one office


    manager
    first name
    last name
    address
    salary
    d.o.b


    An owner provides one or more taxis

    taxi owner
    first name
    last name
    address
    salary
    d.o.b

    administrator
    first name
    last name
    address
    salary
    d.o.b

    driver
    first name
    last name
    address
    salary
    d.o.b

    taxi
    registration no.
    model
    make
    colour
    milage
    M.O.T due date
    capacity


    taxis are not available for hire by the public hailing a taxi in the street but must be requested by first phoning the us to attend a given address. There are two kinds of clients-private and business


    The business provided by private clients is on an ad hoc basis. The details of private clients are collected on the first booking of a taxi

    Private customer
    First name
    last name
    telephone no.
    client no.


    business provided by business clients is more formal and involves agreeing a contract of work with the business.A contract stipulates the maximum number of jobs that Critter-Cabs will undertake for a fixed fee


    business customer
    max jobs
    fixed fee
    start date
    end date
    Description //whether it is a night job or a day job
    name
    address
    telephone no.
    fax
    contract no.

    When a job comes in the appropriate details are recorded for either a private client or a business client.

    If the job is requested by a business client then the contract number is also recorded

    jobs
    contract no.
    pickup date
    pick up time
    pick up address
    drop off address
    milage used
    charge made
    job completed / yes or no

  2. #2
    Join Date
    Feb 2004
    Posts
    8

    Addresses

    Does the Address for the manager mean their home address or the address of the office?
    If a manager can be associated with more than one office then you need a relationship between a unique identifier for the Manager, maybe a staff id and an a code id for the office.

    It seems to me that as yet you haven't really identified the fields you wish to
    hold yet. The design is getting there though.

    How will you uniquely label members of staff? Once you have all this down then group your tuples in a table design.

    Work out what is primary key is each set of tuples and what refers to the other sets, the foriegn keys. The primary keys will probably be things like staff id, office id, taxi id, client/customer id.

    Do you really need to store salary in this table? Who will be the main users? If it's just for storing bookings then that info isn't really needed.

    Write down your schema. Then you can begin to see what normal form it is in and apply higher levels to remove redundancy. Once you have your tuples write them down in table form and we can see what normal form they are in.

    Nick

  3. #3
    Join Date
    Feb 2004
    Posts
    78
    yes the address for the manager means home address and i think your idea to have a staff id is good

    it is necessary to store salary in these tables for my records- i will be the main user who will be inputting the paper records into the database so i can keep track of everyting and work out our strenght and weaknesses and generally keep n top of things.

    here are the tables i have though of- i think i might have just done 1st normal form below- what do you think?

    * = primary key ** = foriegn key// i was'nt sure what to put down for foriegn keys ,so i don't think they are all there
    -----------------
    *office id
    street
    city
    postcode
    phoneno

    ----------------

    *Staff_id
    Staff Type
    first name
    last name
    address
    salary
    d.o.b
    ------------------

    *taxi_id
    registration no.
    model
    make
    colour
    milage
    M.O.T due date
    capacity

    --------------------
    //private clients

    *client no.
    First name
    last name
    telephone no.

    -------------------
    //business clients

    *contract no.
    max jobs
    fixed fee
    start date
    end date
    Description //whether it is a night job or a day job
    name
    address
    telephone
    fax
    ------------------------------

    *job_id
    **contract no.
    pickup date
    pick up time
    pick up address
    drop off address
    milage used
    charge made
    job completed / yes or no

  4. #4
    Join Date
    Feb 2004
    Posts
    8
    Yep that in 1st Normal Form.
    Well the address worries me a bit. You need a standard format for it. On one table it's all in one field and in the other it's split in several fields.

    It may even be in 2nd Normal form if we can sort out the address storage.

  5. #5
    Join Date
    Feb 2004
    Posts
    78
    is this better? what about the primary/foriegn keys


    * = primary key ** = foriegn key// i was'nt sure what to put down for foriegn keys ,so i don't think they are all there
    -----------------
    *office id
    street
    city
    postcode
    phoneno

    ----------------

    *Staff_id
    Staff Type
    first name
    last name
    street
    city
    postcode
    salary
    d.o.b
    ------------------

    *taxi_id
    registration no.
    model
    make
    colour
    milage
    M.O.T due date
    capacity

    --------------------
    //private clients

    *client no.
    First name
    last name
    telephone no.

    -------------------
    //business clients

    *contract no.
    max jobs
    fixed fee
    start date
    end date
    Description //whether it is a night job or a day job
    name
    street
    city
    postcode
    telephone
    fax
    ------------------------------

    *job_id
    **contract no.
    pickup date
    pick up time
    pick up address
    drop off address
    milage used
    charge made
    job completed / yes or no

  6. #6
    Join Date
    Feb 2004
    Posts
    8
    Getting there. How do link the taxi used to the journey? I assume that will have to appear in the table with the start and end times.

    If you have a table that has a primary key that isn't referenced in another table then you need to ask why you have that table. At the moment what links the job to the driver and taxi used?

    In a true relational design you can't have a null value in a column so if you want to represent a private client then the business account field will be null at present. You may wish to have another relationship/table with a tuple of job id and business account id.

    Then if this table has no row you know it's a private account.

    Go through the tables and make sure that the primary key determines all the other values. If so you're looking good for 3rd normal form.

  7. #7
    Join Date
    Feb 2004
    Posts
    78
    sorry but i think i have missed out 2nd normal form- what would my tables look like in second normal form is it as above.

  8. #8
    Join Date
    Feb 2004
    Posts
    8
    It is probably in 2nd Normal Form.

    The only thing that could give you repeated blocks of information is the address fields and it's unlikely you'll store so much information that the City implies the state.

    By writing it down in a tuple form or table then you force 1 NF. You've split the information into seperate relations and I can't see any repeated groups that would require you to split it further.

    You might wish to have an address table with a unique address id. Then all addresses could be stored in there and any addresses in the tables could be keyed off that. Useful if you have you pick up from the same address as the client is registed to.
    Then you're in 2 nd Normal Form.

    Looking at the tables it appears that the primary key does drive everything else in the table. You just need to link things properly.
    In which case you're in 3rd Normal form if I understand your data correctly.

    Does the driver imply the car? Can one driver have more than one car?
    Is a particular job linked to a car, a driver or both?

    A job id implies the pick up times but it may map to zero or 1 contract numbers so split the Job Id and Client Id tuple off into a separate table.

    A good thing to do now is start filling these tables with real data and see if you could store a valid booked journey in them. With the additional foriegn keys I've suggested I believe you could.

    You could then post a Use Case that I can sanity check.

  9. #9
    Join Date
    Feb 2004
    Posts
    78
    sorry for the late reply but i have been looking at it the last few days and still quite work out for sure where i have 1st 2nd and 3rd normal form can u show me please

    i know this sounds wierd but i would like to know so that i can create a good set of documentation for this database, if i need to sell this company on i need people to know i made a good go at it.

  10. #10
    Join Date
    Feb 2004
    Posts
    8
    Read through something like

    http://www.databasejournal.com/sqlet...6861_1428511_4

    If you can put your rows in a table then you're in first normal form. Don't worry about it.

    I can't tell from your column names alone any more info than that.
    You have to look at the data to see if when you put it into your tables that there is no replication which is what 2nd and 3rd normal form are all about removing. I think you're there or nearly so.
    Without having access to your data I can do no more.

    Try a dry run putting in the data you think the tables should hold and working out if you can create queries that will work for what you want. Then it will become more obvious if any of the tables need decomposing further.

  11. #11
    Join Date
    Feb 2004
    Location
    Hyderabad
    Posts
    2

    Solution to "normalisatoin help" - evilz35

    Hi

    Please check the attachment in doc format. Hope the solution is upto thelevel of your expectataions. Please give the status of the solution.


    Have a nice time
    Attached Files Attached Files

  12. #12
    Join Date
    Feb 2004
    Posts
    78
    thanks for that- i have now done what i think is the solution and set it out in UNF 1NF 2NF 3NF for you to check. i have also included some sample data that we currently have from our paper system.
    Attached Files Attached Files

  13. #13
    Join Date
    Feb 2004
    Posts
    78
    has n e 1 had a chance to have a look at it yet

  14. #14
    Join Date
    Feb 2004
    Posts
    78
    Originally posted by bootsy
    Read through something like

    http://www.databasejournal.com/sqlet...6861_1428511_4

    If you can put your rows in a table then you're in first normal form. Don't worry about it.

    I can't tell from your column names alone any more info than that.
    You have to look at the data to see if when you put it into your tables that there is no replication which is what 2nd and 3rd normal form are all about removing. I think you're there or nearly so.
    Without having access to your data I can do no more.

    Try a dry run putting in the data you think the tables should hold and working out if you can create queries that will work for what you want. Then it will become more obvious if any of the tables need decomposing further.
    hey ithink i've done what you've asked now can u see the file attachment in this post to see if i've done i right please
    Attached Files Attached Files

  15. #15
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    Looking at your data, it is not normalized. There are many violations of second normal form. For example you should have a make table and a model table because your make and model data repeats. Having a city table would be good normalization as well.

    I want to point out that achieving 3rd normal form is not the ultimate goal. You have to balance the transaction efficiency of a normalized database against the reporting efficiency of a denormalized database. However, normalizing your data first is the right step. Normalize it to the limit, then denormalize it until the number of joins needed for reports is efficient.
    Last edited by certus; 02-23-04 at 17:44.

Posting Permissions

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