3NF

• A relation is in 3NF if it is in 2NF and there are no non-key attributes determined by another non-key attribute
• In R(a,b, c,d) the following can not be
  • c → d
  • d → c
• Consider this table
  • STUDENT_HOUSING(StudentID, Building, BuildingFee)
  • But the building fee is set by building so
  • Building → BuildingFee

    StudentID	Building	Building Fee
    100	Randolph	3200
    200	Ingersoll	3400
    300	Randolph	3200
    400	Randolph	3200
    500	Pitkin	3500
    600	Ingersoll	3400
    700	Ingersoll	3400
    800	Pitkin	3500

  • Note, we can still create any form of anomaly by
    • Mistyping BuildingName or BuildingFee.
    • We need a fake student id if we add a new building.
    • We Might lose a building by deleting too many students.
• We fix this as before by decomposing into two tables.
  • STUDENT_HOUSING(StudentID, Building)
  • BUILDING_FEE(Building,HousingFee)

BCNF

• A relation is in BCNF if and only if it is in 3NF and every determinant is a candidate key.
• The example is somewhat complex
  • A student can have one or more majors
  • A Major can have one or more advisers
  • Thus a student may have multiple advisers
  • Faculty advise in only one area.

  • StudentID	Subject	AdvisorName
    100	Math	Cauchy
    200	Psychology	Jung
    300	Math	Riemann
    400	Math	Cauchy
    500	Psychology	Perls
    600	English	Austin
    700	Math	Riemann
    700	Psychology	Perls
    800	Math	Cauchy
    800	Psychology	Jung

  • He proposes two schemas
    • STUDENT_ADVISORS(StudentID, Major, AdvosorName)
    • STUDENT_ADVISORS(StudentID, Major, AdvosorName)
    • StudentID will not do as a student can have two majors
    • Major will not do as there are many students assigned to a major.
    • Advisor will not do for the same reason.
    • In this case, the two proposed candidate keys are overlapping candidate keys
    • They note that it is in 2NF because all attributes are part of a candidate key, so there are no non-key attributes.
    • But it does have a 2NF like problem AdvisorName → Subject.
• The solution is as before
  • STUDENT_ADVISOR(StudentID,AdvisorName)
  • ADVISOR_SUBJECT(AdvisorName,Subject)
• UMMMh,
  • " I swear to construct my tables so that all non-key columns are dependent on the key, the whole key and nothing but the key, so help me Codd!"
  • Put your tables in BCNF.
• Procedure
  1. Identify every functional dependence
  2. Identify every candidate key
  3. If there is a functional dependency that has a determinate that is not a candidate key
     1. Move the columns of that function dependency into a new relation
     2. Make the determinant of that functional dependency the primary key of the new relation.
     3. Leave a copy of the determinant as a foreign key in the original relation
     4. Create a referential integrity constraint between the original relation and the new relation.
  4. Repeat step 3 until every determinant of every relation is a candidate key.