Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    need Help with Table Normalization

    Hello. I have to normalize a tables in database, I would like to ask anyone experienced with databased if below normalization is correct for 1F,2F,3F. thanks for reply

    Normalization

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

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

    1NF:

    RECIPE:
    rID primary key
    rName

    INGREDIENT:
    ingredientID primary key
    ingredientName
    amount

    COOK
    cookID primary key
    cookName
    cookPhNumber

    I created separate table for each group of related data and identified each row with unique data primary key.

    2NF:

    RECIPE
    rID primary key
    rname

    INGREDIENT
    ingredientID primary key
    ingredientName
    amount

    COOK
    cookID primary key
    cookName

    COOKPH
    cookID primary key
    cookPhNumber

    3NF:

    RECIPE
    rID primary key
    rname

    INGREDIENT
    ingredientID primary key
    ingredientName
    amount

    COOK
    cookID primary key
    cookName

    COOKPH
    cookID primary key
    cookPhNumber

    COOKRECIPE
    cookID primary key
    rID

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm never very good at school projects because I never know quite what they want.
    As far as purely normalising the entity it is a reasonable effort but not spot on.

    To start, could you explain at 2NF why you decomposed COOK? It might help you (and me) if you refer to the definition of 2NF in your answer
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    1 The column naming has a few errors:
    • Do not prefix the column name with the [shortened] table name. It is completely redundant in SQL, just write the code for a joined couple of tables and you will see why. Whenever the column name is ambiguous, the table name prefixes the column name to resolve the ambiguity.
    • Use full column names for all Keys (PKs are a special case which need to be identified as FKs when used as such). rId will be a problem when you add a table starting with 'R'.

    2 Since you are discussing Normal Forms, you are dealing with the Logical level. However, you have assumed:
    • that every table needs a Surrogate key, an Identity column, and
    • (worse!) it will be the PK in every table
    That is a Physical implementation issue, and an additional index. At the Logical level, stick with natural Relational keys (people do not think in terms of a Recipe Id). You can always add your additional surrogate keys and indices later, on a table basis (they will not always be required).

    Another way of stating the same point is, the PKs are all incorrect. In order to prevent duplicates, and to allow people to find rows by identifiable means, you will need unique indices on the following columns (which may well be the real PKs, the Surrogate keys which may be added later being the Alternate, nor Primary, Keys):
    Recipe.Name
    Ingredient.Name
    Cook.Name
    CookPhone.PhoneNumber
    CookRecipe.CookId+RecipeId

    3 As stated at 3NF, Cook and CookPhone have the same dependencies, so they are [both] not at 3NF [in the same database].
    • Either: a cook has only one PhoneNumber, and therefore it should be placed in Cook; CookPhone is eliminated
    • Or: a cook has more than one PhoneNumber; and the PK for CookPhone is (CookId, PhoneNumber), (CookId) will fail on entry of the second PhoneNumber. Again to prevent duplicate entries.
    • The use Surrogate keys do not eliminate compound keys; compound keys are normal.

    4 Ingredient is broken at 3NF, but I suspect the resolution was not complete at 2NF:
    • What you have is fine for the reference table (one row per Ingredient) and therefore Amount is meaningless and can be removed.
    • There is nothing that relates Recipe to Ingredient (ie. there is a segment missing at 2NF).
    • The Amount of Ingredient is specific to a Recipe
    • The resolution is one row per Recipe-Ingredient, at 3NF:
    RecipeIngredient (
    RecipeId -- PK.1
    IngredientId -- PK.2
    Amount
    )
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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