SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Help with 3NF

  1. #1
    SitePoint Member
    Join Date
    Nov 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with 3NF

    Hi there i have been asked to normalise this problem to 3NF:

    Cooking( rID, rname, (ingredientID, ingredientName, amount), cookID, cookName, cookPhNumber);

    These are the functional dependencies:

    cookID -> cookName
    cookID -> cookPhNumber
    rID -> rname
    rID -> cookID
    ingredientID -> ingredientName


    The solution that i have come up with is as follows:

    Cooking( rID, rname)
    Cooks(cookID, cookName, cookPhNumber)
    Ingredients(ingredientID, ingredientName, amount)

    Im just wondering if this is correct?
    Thanks
    Last edited by xtrrr; Nov 27, 2008 at 11:10.

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This homework?

    Anyway, I can help without doing it for you.

    You need to let us see all the columns that you will have or we can't properly 3nf it. you need, for example, to include weights or units of weight/measurement so that 3 eggs doesn't get mixed up with 3 cups/ fl oz and 3 teaspoons of sugar doesn't become 3 cups.
    what about the chefs/cooks (clarity maybe needed) addresses? needed or not.
    once you get that dealt with ~ and attempt to 3nf it ~ we can help more.

    bazz

  3. #3
    SitePoint Member
    Join Date
    Nov 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All i have is the information i gave at the top.

    Yea its homework, but ive done the work jst asking if its right or if not where have i gone wrong etc.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by xtrrr View Post
    These are the functional dependencies:
    are these FDs given as part of the assignment, or did you make them up?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I wish I could teach like Rudy does.

    anyway, I'll try (to help).

    If you group what you have into clearly separate tables (I make that 4 ~ cooks, recipes, measurements and ingredients) it may help you understand the app of 3nf more easily.

    tip:
    you don't need a numerical PK. a Composite PK can work here I think.

    eg chefs

    create table if not exists chefs
    ( chef_name varchar (64) NOT NULL
    , chef_ph_number varcgar(24) NOT NULL
    , PRIMARY KEY (checf_name, chef_ph_number)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    I have mentioned a table for measurements. if you decide to do that it would have these values and more perhaps
    fl oz
    cups
    g
    oz
    kg
    tsp
    dsp
    tbsp
    pinch


    bazz

  6. #6
    SitePoint Member
    Join Date
    Nov 2008
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    are these FDs given as part of the assignment, or did you make them up?
    They were given as part of the assignment, i didnt make them up.

    Thanks IBazz will see how it goes now

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by xtrrr View Post
    They were given as part of the assignment, i didnt make them up.
    in that case you won't want to introduce any new entities or attributes, like units of measure...

    i'll give you a hint about your model...

    you have this: Ingredients(ingredientID, ingredientName, amount)

    in this entity, amount is wrong (unless you stretch it to meant the total amount of an ingredient that you have on hand, like 200 lbs of potatoes in the cooler)

    make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What does rID determine???
    And perhaps if we assume r is short for recipe... (which might suggest there is one more functional dependency - are you sure that's all of them)


Bookmarks

Posting Permissions

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