3NF

• In the last set of notes, we noted a problem with this table:
• PROPERTY_OWNER(PropertyNo, Address,Rent, OwnerNo, Owner)

  PropertyNo	Address	Rent	OwnerNo	Owner
  PG4	6 Lawrence St, Glasgow	350	CO40	Tina Murphy
  PG16	5 Novar Dr, Glasgow	450	CO93	Tony Shaw
  PG36	2 Manor Rd St, Glasgow	375	CO93	Tony Shaw

• It has a transitive dependency
  • OwnerNo → Owner
  • Or an non-key attribute determines another non-key attribute.
  • Or If A, B, and C are attributes of a relation such that A→B and B→C, then C is transitively dependent on A via B, provided A is not functionally dependent on B or C.
  • Our book says: No non-key attributes determine any other non-key attributes.
  • In the above example, OwnerNo → Owner
• A relation is in Third Normal Form (3NF) if it is in 2NF and no non-primary-key attribute is transitively dependent on the primary key.
• In the above table, PropertyNo → OwnerNo → Owner
• To fix the above, we again produce a new relation.
• PROPERTY_OWNER(PropertyNo, Address,Rent, OwnerNo)

  PropertyNo	Address	Rent	OwnerNo
  PG4	6 Lawrence St, Glasgow	350	CO40
  PG16	5 Novar Dr, Glasgow	450	CO93
  PG36	2 Manor Rd St, Glasgow	375	CO93

• OWNER(OwnerNo,Owner)

  OwnerNo	Owner
  CO40	Tina Murphy
  CO93	Tony Shaw

• Consider the following
  • STUDENT_HOUSING(StudentID, Building, HousingFee)

  • StudentID	Building	HousingFee
    100	Randolph	3200.00
    200	Ingersoll	3400.00
    300	Randolph	3200.00
    400	Randolph	3200.00
    500	Pitkin	3500.00
    600	Ingersoll	3400.00
    700	Ingersoll	3400.00
    800	Pitkin	3500.00

  • Place this table into 3NF

BCNF

• Boyce-Codd normal form.
• A stronger statement of 3NF
• A table is in BCNF if and only if every determinant is a candidate key.
• The tables we have just worked through are all in BCNF.
• Connolly states that Violations of BCNF from 3NF tables are quite rare.
  • The relation must contain two or more composite candidate keys.
  • The candidate keys overlap
• He gives the following example:
  • CLIENTINTERVIEW(ClientNo,InterviewDate, InterviewTime, StaffNo, RoomNo)

  • ClientNo	InterviewDate	InterviewTime	StaffNo	RoomNo
    CR76	13-May-02	10:30	SG5	G101
    CR56	13-May-02	12:00	SG5	G101
    CR74	13-May-02	12:00	SG37	G102
    CR56	1-Jun-02	10:30	SG5	G102

• He identifies the following dependencies
  • (ClientNo, InterviewDate) → (InterviewTime, StaffNo, RoomNo)
  • (StaffNo, InterviewDate, InterviewTime) → (ClientNo, RoomNo)
  • (RoomNo, InterviewDate, InterviewTime) → (StaffNo, ClientNo)
  • (StaffNo, InterviewDate) → RoomNo
• The first three are all candidate keys.
• The third, in my opinion is a stretch, but I suppose a staff member might be assigned a room for a day.
• (But this is a condition that they place on it)
• So this violates BCNF but not 3NF.
• The solution is:
  • INTERVIEW(ClientNo,InterviewDate,InterviewTime,StaffNo)

  • ClientNo	InterviewDate	InterviewTime	StaffNo
    CR76	13-May-02	10:30	SG5
    CR56	13-May-02	12:00	SG5
    CR74	13-May-02	12:00	SG37
    CR56	1-Jun-02	10:30	SG5

  • STAFF_ROOM(StaffNo,InterviewDate,RoomNo)

  • StaffNo	InterviewDate	RoomNo
    SG5	13-May-02G101
    SG37	13-May-02G102
    SG5	1-Jun-02G102

• Note: We have lost a dependency in this transformation
  • (RoomNo, InterviewDate, InterviewTime) → (StaffNo, ClientNo)
  • Since RoomNo is not in a table with the others any longer.
  • If this is an important dependency, we might not want to go to BCNF
  • If it is not, then we do.
• Consider

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

  • Two possible candidate keys:
    • (StudentID, Major) → AdvisorName
    • (StudentID, AdvisorName) → Major
    • These are overlapping candidate keys since they share an attribute.
    • Also Note: AdvisorName → Major
  • Solve this by
    • STUDENT_ADVISOR(StudentNo, AdvisorName)
    • ADVISOR_SUBJECT(AdvisorName, Subject)