Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2010
    Posts
    7

    Post Help With Normalization

    hi, im currently studying a database module at university and as part of our coursework we have to normalize this to 3NF so i am not looking for the answers to this by any means. i have a couple of questions about my 1NF:

    PROBLEM:
    Retail(RID, Rlocation(StaffID, staffname, StaffPHNumber), (ProductID, ProductName, amount))

    Functional Dependances:
    RID > Rlocation
    StaffID > StaffName
    StaffID > StaffPHNumber
    ProductID > ProductName

    1NF - No repeating Groups

    Retail Location(RID, Rlocation)
    Staff(StaffID, StaffName, StaffPHNumber, RID)
    Product(ProductID, ProductName, amount, RID)

    there are now no repeating groups and all functional dependances are present, the RID is displayed as a foreign key in staff and product to allow the tables to be joined.

    however my question is have i done too much with this only being 1NF?
    and am i allowed to introduce foreign keys in 1NF?, if not when would i and how would they be represented here
    Last edited by db_; 11-02-10 at 10:28.

  2. #2
    Join Date
    Nov 2010
    Posts
    7

    ADD TO: help with normalisation

    2NF - No Partical key dependances
    Retail Location(RID, Rlocation)
    Staff(StaffID, StaffName, StaffPHNumber, RID)
    Product(ProductID, ProductName, amount, RID)

    There are no P.K.D's and this is already in 2NF

    3NF - no T.K.D
    Retail Location(RID, Rlocation)
    Staff(StaffID, StaffName, RID)
    StaffPHNo(StaffID,StaffPHNumber)
    Product(ProductID, ProductName, amount, RID)

    There was a transitive dependancy from StaffName > StaffPHNumber and this is moved to a new table staffphno.

    As far as i can see this is now normalized to 3NF, but one thing im learning from uni is: if u think its right, its probably wrong, and if you think its wrong then its deffinetly wrong

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    what is staffPHNumber, if its a unique value and always known at the time creating the row and doens't change over time then that has the feel of a candidate primary key. if hgowever its a phoine number then you need to think about what is relevant about phone numbers
    howmany phone numbers do you need per person, are thise numbers shared, and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2010
    Posts
    7

    RE: help with normalization

    Quote Originally Posted by healdem View Post
    what is staffPHNumber, if its a unique value and always known at the time creating the row and doens't change over time then that has the feel of a candidate primary key. if hgowever its a phoine number then you need to think about what is relevant about phone numbers
    howmany phone numbers do you need per person, are thise numbers shared, and so on.
    staffPHNumber is the phone number and it would have to be a many:many relationship as one person can have many phone numbers but one house phone can belong to many people.
    however for normalisation i didnt realise this would be relevent?

    although i asume its because ive made a seperate table for phone numbers? but i need that seperate table or else staffname > phonenumbers is a TKD and isnt in 3NF
    im probably way off but thats the way i see it?

    EDIT: Just noticed someone has just posted about this same question on this forum, yet hes got a completly different answer from me which really doesnt fill me with confidence on my answer, especially seen as how i seemed to have already had it in 2NF.
    Last edited by db_; 11-02-10 at 15:08. Reason: noticed an earlier post

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by db_ View Post
    EDIT: Just noticed someone has just posted about this same question on this forum...
    what do you think the chances are that your teacher/professor will have a look around the various database forums (by searching on table and column names in the assignment) to see who is trying to get homework help?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    what makes you think that they are right and you are wrong
    what makes you think there is only one right answer
    the fact is that often in the systems world there are many wrong answers, several right answers, and some which are correct based on the assumptions and limitations inherent in the requirement

    the point of your assignment is for you to document the process you've gone through, including any blind alleys, because what you are supposed to be doing is proving that you have understood the principles of normalisation. often the narrative is just as important as the end product.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2010
    Posts
    7
    Quote Originally Posted by r937 View Post
    what do you think the chances are that your teacher/professor will have a look around the various database forums (by searching on table and column names in the assignment) to see who is trying to get homework help?

    i think the chances of that are pretty damn high, however as you see i am not asking for any answers im a looking for guidence due to the limited amount of time lectures have to give you assistance in university
    i am sure they would be absoloutly fine with what i have asked on this forum.

  8. #8
    Join Date
    Nov 2010
    Posts
    7
    Quote Originally Posted by healdem View Post
    what makes you think that they are right and you are wrong
    what makes you think there is only one right answer
    the fact is that often in the systems world there are many wrong answers, several right answers, and some which are correct based on the assumptions and limitations inherent in the requirement

    the point of your assignment is for you to document the process you've gone through, including any blind alleys, because what you are supposed to be doing is proving that you have understood the principles of normalisation. often the narrative is just as important as the end product.
    totally true and i feel i do understand the principles behind normalization, however putting into practice what i have learnt seems to be a whole different ball game, and i feel that there is no point learning something if you cannot implement it properly

Tags for this Thread

Posting Permissions

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