To Notes

Normalize Tables

Practice Exercises

Identify which of these tables is 3NF? Normalize those tables that are not normalized so that they are. For each table, the name of the primary key is marked with *

  1. kids Table
    *name gender age
    Jason M 9
    Chloe F 12
    Susan F 11
    Jerrod M 10

  2. Rails tables usually include an autogenerated key named id that serves as the primary key.
    kids Table
    *id name gender age
    1 Jason M 9
    2 Chloe F 12
    3 Susan F 11
    4 Jerrod M 10

    Ans: The kids table is normalized.

  3. students Table
    *id name year major fulltime email
    1 Julie 3 Sociology Yes julieh@supermail.com
    2 David 4 Art Yes davidr@supermail.com
    3 Carolyn 1 Accounting Yes carolynw@supermail.com
    4 Bill 3 Biology No billk@supermail.com
    5 Arthur 2 Mathematics Yes arthurj@supermail.com
    6 Nancy 4 Sociology No nancya@supermail.com

    Ans: The students table is normalized.

  4. advisors Table
    *id id_numstudent_name advisor_num advisor_name
    1 32901Julie 05283 Patel
    2 90878David 09483 Chen
    3 73770Carolyn 09483 Chen
    4 10977Bill04484 Morgan
    5 65618Arthur 05283 Patel
    6 44541Nancy 09483 Chen

    Ans: The advisors table is not normalized because advisor_name depends on advisor_num, not on student. The table should be split like this:
    students (secondary) Table
    *id id_numstudent_name advisor_id
    1 32901Julie 1
    2 90878David 2
    3 73770Carolyn 2
    4 10977Bill3
    5 65618Arthur 1
    6 44541Nancy 2
    		 
    advisors (primary) Table
    *id advisor_num advisor_name
    105283 Patel
    2 09483 Chen
    3 04484 Morgan

  5. patients table
    *id patient_num name doc_num doc_name doc_phone
    1 11111 Sally 2222 Johnston 312/546-3492
    2 33333 Winston 4444 Murry 312/546-9382
    3 55555 Marge 4444 Murry 312/546-9382
    4 77777 Mark 2222 Johnston 312/546-3492

    Ans: The table is not normalized because doc_name and doc_phone depend on doc_num, not on the patient. The table should be split like this:
    patients (secondary) table
    *id patient_num name doc_id
    1 11111 Sally 1
    2 33333 Winston 2
    3 55555 Marge 2
    4 77777 Mark 1
    		 
    doctors (primary) table
    *id doc_num doc_name doc_phone
    1 2222 Johnston 312/546-3492
    2 4444 Murry 312/546-9382
  6. reporters Table
    *id station channel network address reporter type
    1 WBBM 2 CBS 22 W. Washington St Rob Johnson Anchor
    2 WBBM 2 CBS 22 W. Washington St Megan Mawicke Sports
    3 WBBM 2 CBS 22 W. Washington St Steve Baskerville Sports
    4 WMAQ 5 NBC 454 N Columbus Dr Stefan Holt Anchor
    5 WMAQ 5 NBC 454 N Columbus Dr Kye Martin Weather
    6 WMAQ 5 NBC 454 N Columbus Dr Peggy Kusinski Sports
    7 WLS 7 ABC N. State St Ron Majers Anchor
    8 WLS 7 ABC N. State St Jim Rose Sports
    9 WLS 7 ABC N. State St Cheryl Scott Weather

    Ans: The table is not normalized because channel, network, and address depend on station, not on the reporter. This table should be split like this:
    tv_stations (primary) Table
    *id station channel network address
    1 WBBM 2 CBS 22 W. Washington St
    2 WMAQ 5 NBC 454 N Columbus Dr
    3 WLS 7 ABC N. State St
    		 
    reporters (secondary)Table
    *id reporter type station_id
    1 Rob Johnson Anchor 1
    2 Megan Mawicke Sports 1
    3 Steve Baskerville Sports 1
    4 Stefan Holt Anchor 2
    5 Kye Martin Weather 2
    6 Peggy Kusinski Sports 2
    7 Ron Majers Anchor 3
    8 Jim Rose Sports 3
    9 Cheryl Scott Weather 3

  7. name_meanings Table
    *id last_name first_name phone first_name_meaning first_name_language
    1 Miller Steven 312/234-3456 Crown Greek
    2 Baker Jill 312/342-8388 Young Latin
    3 Turner Glenda 312/342-9127 Holy and good Welsh
    4 Maxwell Steven 312/234-5232 Crown Greek
    5 Morgan Jill 312/736-2736 Young Latin

    Ans: This table is not normalized because first_name_meaning and first_name_language depend only on first_name, not on the person (which consists of both last_name and the first_name). The table should be split like this:
    persons (secondary) Table
    *id last_name first_name phone name_meaning_id
    1 Miller Steven 312/234-3456 1
    2 Baker Jill 312/342-8388 2
    3 Turner Glenda 312/342-9127 3
    4 Maxwell Steven 312/234-5232 1
    5 Morgan Jill 312/736-2736 2
    		 
    name_meanings (primary) Table
    *id first_name_meaning first_name_language
    1 Crown Greek
    2 Young Latin
    3 Holy and good Welsh