Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    4

    Help! Normalisation

    Im a student and we have been given a task of normalising the following information. Im not asking for the answers im just looking for a point in the right direction as im really struggling.

    Normalise the following relation to 3rd Normalform:

    Retail( rID, rlocation, (staffID, staffName, staffPhNumber), (productID, productName, amount));

    These are the functional dependencies:

    staffID -> staffName
    staffID -> staffPhNumber
    rID -> rlocation
    productID -> productName

    Your solution should show the relations, steps and justifications for 1NF, 2NF and 3NF.

    Can you tell me if im anywhere near the right answer with this solution

    1NF ( No repeating groups)

    Retail( rID, rlocation)
    Staff (rID, staffID, staffName, staffPhNumber)
    Prouduct (staffID, productID, productName, amount)

    2NF ( No repeating groups and no partial key dependencies)

    Retail( rID, rlocation)
    Staff (rID, staffID)
    Name ( staffID, staffName, staffPhNumber)
    Prouduct (staffID, productID, productName)
    Pname ( productID, productName, amount)

    3NF ( contains no repeating groups, no partial functional dependencies, and no transitive functional dependencies )

    Retail( rID, rlocation)
    Staff (rID, staffID)
    Name ( staffID, staffName, staffPhNumber)
    Phone ( staffName, staffPhNumber )
    Prouduct (staffID, productID, productName)
    Pname ( productID, productName, amount)

    Any help with this would be greatly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Aaron09 View Post
    Any help with this would be greatly appreciated.
    would you like us just to correct your work for you?

    or did you have a question about anything in particular?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There are errors.

    You should identify your keys at each stage. Without the keys, how do you identify partial dependencies, or transitive dependencies between non-prime attributes?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Oct 2010
    Posts
    4

    Smile

    Im not asking for the answers i would just like to know what area i need to be looking in, i.e is 1NF ok? but 2NF isnt

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well, if you look at the requirement for 1NF you need a primary key, so no.

    You must identify your keys - everything is driven by keys and dependencies.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Oct 2010
    Posts
    4

    suldowd often

    1NF ( No repeating groups)

    Retail( rID, rlocation)
    Staff (rID, staffID, staffName, staffPhNumber)
    Prouduct (staffID, productID, productName, amount)

    2NF ( No repeating groups and no partial key dependencies)

    Retail( rID, rlocation)
    Staff (rID, staffID)
    Name ( staffID, staffName, staffPhNumber)
    Prouduct (staffID, productID, productName)
    Pname ( productID, productName, amount)

    3NF ( contains no repeating groups, no partial functional dependencies, and no transitive functional dependencies )

    Retail( rID, rlocation)
    Staff (rID, staffID)
    Name ( staffID, staffName, staffPhNumber)
    Phone ( staffName, staffPhNumber )
    Prouduct (staffID, productID, productName)
    Pname ( productID, productName, amount)

    Everything in bold and underlined being my primary keys or composite keys. sorry if i seem really dumb but i have only been doing this subject for about 4 weeks and im finding it quite tough.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No that's fine.
    EDIT:
    1NF is wrong (so are 2NF and 3NF).
    All you need for 1NF is to identify a key. You don't actually need to decompose your tables in to three. It is a more useful exercise when learning to not do so.

    So, for 1NF just copy and paste what you have been given and identify a key.
    Last edited by pootle flump; 10-29-10 at 12:26.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm muddying the waters here a bit but I've just looked at the original assignment a little deeper.
    Is there any more accompanying text? It doesn't make complete sense to me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2010
    Posts
    4
    Thanks for your quick replies. No there is no more text with the question. its funny you say its confusing, I spoke to another lecturer at my uni and he didnt think it was a great question and was confused himself.

    The reason i have decomposed the tables to get into 1NF is because that is how we were shown and the lecturers want us to show each individual step.

Posting Permissions

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