SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    database normalization

    does this table fit for the 2NF? if not? why and how to corrrect it.

    Code:
    studentnumber   name     age      course   credit   
    
    1               tom            21      math       3
    
    2               jim             20          english    4
    ....

  2. #2
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This tute on database normalisation should help you decide if it does.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by runeveryday View Post
    does this table fit for the 2NF? if not? why and how to corrrect it.
    no, it doesn't

    i'm going to speculate that there is no restriction that says each student can take only one course

    thus if tom were to take english, then the rows would look like this --
    Code:
    studentnumber   name   age   course   credit   
         1          tom    21    math       3
         2          tom    21    english    2
         2          jim    20    english    4
    now the primary key of this table has to be studentnumber plus course

    and thus we can see that name and age are dependent only on studentnumber, not on the entire primary key, so this table fails 2NF

    to fix it, you need a separate table for students, in which to record the age and name, with primary key studentnumber

    then this table, with the course credits, will have only three columns -- studentnumber and course as primary key, and credit as the data attribute
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    how could you know the primary key of that table has to be studentnumber plus course? thank you.


    studentnumber name age
    1 tom 21
    2 jim 20

    course credit studentnumber
    math 3 1
    english 2 2
    the course id the pK. studentnumber is the foreign key. now, does the table fit for the 3NF?

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    "how could you know the primary key of that table has to be studentnumber plus course? thank you."

    to get unique values for the primary key

    Because more than one student will take each course.

    Remember, you don't just look at the data SAMPLE you have, you must also consider the meaning of the data and the scenario where it will be used.


    "the course id the pK. studentnumber is the foreign key. now, does the table fit for the 3NF?"
    no, you need the joint key, no it is not 3nf


    BECAUSE, you may also have a course table, remember. And now think of those non-mathematicians who take a course called maths - will it be the exact same course as that taken by the mathematicians? What's the PK for the course table then? How should you now modify the table which currently has course, credit, and studentnumber? Even assuming there is only one course called maths, and all course names are unique, it needs modifying.

  6. #6
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    THINK
    what is the determination for credit?


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
  •