Results 1 to 7 of 7

Thread: normalization

  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Unanswered: normalization

    i everyone , not sure is this is the correct place to post this topic but it was the best suited area i could find.

    I am currently working on a database project and i am stuck on databse normalisation , although apparently its simple i cant get my head around it....... arghh!!

    **First of i have to list the unnormalised attributes and show the chosen key field.

    **moving onto first normal form i then have to split the attributes into seperate data groups identifying the keys of each group and any foregin key.

    **onto the second normal form i have to further data groups by braking out part key dependincies and idenifying keys of each group and any foregin key links

    **third normal form i have to further data groups by braking out part key dependincies and all groups having key and foregin key links.


    so far i have got the UNF :

    UNF 1NF????? 2NDNF???? 3RDNF????

    • Customer ID < PRIMARY KEY
    • Name
    • Address
    • Telephone Number
    • Sex
    • Order Number < PRIMARY KEY
    • Order Date
    • Description
    • Price
    • Stock Qty
    • Total
    • Company name
    • Address 1
    • Address 2
    • Town
    • Tel Number
    • Item Code < PRIMARY KEY
    • Item Name
    • Cost
    • Quantity In Stock
    • Employees ID number < PRIMARY KEY
    • Employees Name




    i am quite new to database design so any help on this will be much appreciated , its driving me insane.



    thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at r937's site whihc may be of assistance

  3. #3
    Join Date
    Nov 2005
    Posts
    3
    hi,

    thanks for your reply , i took a look at that site you gave me and have used it to try and normalise what i have got. Are you familiar with normalisation ?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by bobstery
    ....Are you familiar with normalisation ?
    Um, well I guess so

  5. #5
    Join Date
    Nov 2005
    Posts
    3
    hi,

    well , would you mind if i asked you to take a look at what i have done to see if i am going along the correct lines?

    I have done it in a table format in word...

    thanks


    mick

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    The usual: shpelling mistakes

    You have probably got to the first stage of the process but you have not normalised your design

    Although you have defined primary keys there seems no concept of how the disparate data stores tie in together.

    You entity Order needs a great deal of devlopment. How do you propose to associate an order with a customer. Yes an order is a discrete data element, but it doesn't exist without a customer.

    You are potentially storing the same data in 2 different tables. for instance a customer has an address, your order has an address. OK its arguable that your customer may request delivery to a different address, or its conceivable that the company may have an invoice address and a different (or even multiple delivery addresses) - equally one company may have mulitple invoice addresses AND multiple delivery addresses.

    you are storing price in your order entity, does an order have a price? do your orders allow for multiple items per order - in any event how do you propose to associate an item to an order

    so in short do you think you have you followed the methodology suggested by Paul Litwin in the earlier reference and normalised your tables? If its a gap in understanding then may be your teacher could assist.

    in your table itemcode you refer to cost. do you actually mean cost (ie cost to the organistion of buying / producing the product) or do you mean price (the cost of the item to the purchaser). If you do truly mean cost how do you know how much an item costs to the customer.

    I'm guessing that your model is designed to support one customer can order one item. but why duplicate data, assuming that item name equates to description in your order, why are you storing a description in your order. what reason do you believe that a descrition will change between different orders for the same product.

    On a different note there is a possibility that you have a naming problem in your table design.also its a good practise not to use spaces in row and table names - you can get away with it in Access/JET but not in a server backend.

    you define address as having 2 lines followed by a town, as a design stylisitic it may work for US but its not appropriate as a generic form of storing information. you don't have a a post code or zipcode as a separate item -which for some posting systems you will need to do.
    Last edited by healdem; 11-29-05 at 05:59.

  7. #7
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Before I start, please note that many folk in this forum (including myself) don't like to simply hand out answers to students so there's always a fine line between giving an answer versus helping someone understand a specific point. Hence responses might seem cryptic at times rather than direct.

    Quote Originally Posted by bobstery
    **moving onto first normal form i then have to split the attributes into seperate data groups identifying the keys of each group and any foregin key.
    I don't think so (although I might be mis-interpreting what you are saying). Have you looked at the link suggested by Healdem ?

    In 1NF:
    - You can't have sets of data stored in a single column e.g. hammer, screwdriver, pencil all stored under ProductOrdered.
    - You can't duplicate the same data in several columns- so you can't have columns called ProductOrdered1, ProductOrdered2, ProductOrdered3 etc (the link explains why!).
    - You must have a primary key

    Does your list given at the top of this thread comply with all these points ? If it doesn't then you need to sort it. If it does then you are already in 1NF.

    I think your primary key is wrong imho. You have four attributes in it whereas I think you can uniquely identify each line by having a primary key that consists of only two attributes. This is important as it is important to generating the 2NF (or at least understand how to derive 2NF).

    You mention tables in Word but I can't see any attachments so I don't know what you are talking about.

    Chris

Posting Permissions

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