Third Normal Form

Third Normal Form
? Definition
? Transitive functional dependency – if there a set of atribute Z that are neither a primary or candidate key and both X ? Z and Y ? Z holds.
? Examples:
? SSN ? DMGRSSN is a transitive FD since
SSN ? DNUMBER and DNUMBER ? DMGRSSN hold
? SSN ? ENAME is non-transitive since there is no set of
attributes X where SSN ? X and X ? ENAME
3rd Normal Form

A relation schema R is in third normal form (3NF) if it is in 2NF and no non-prime attribute A in R is transitively dependent on the primary key
BCNF (Boyce-Codd Normal Form)
? A relation schema R is in Boyce-Codd Normal Form (BCNF) if whenever an FD X ? A holds in R, then X is a superkey of R
? Each normal form is strictly stronger than the previous one:
? Every 2NF relation is in 1NF
? Every 3NF relation is in 2NF
? Every BCNF relation is in 3NF
? There exist relations that are in 3NF but not in BCNF
? The goal is to have each relation in BCNF (or 3NF)


BCNF
? {Student,course} ? Instructor
? Instructor ? Course
? Decomposing into 2 schemas
? {Student,Instructor} {Student,Course}
? {Course,Instructor} {Student,Course}
? {Course,Instructor} {Instructor,Student}

Example
? Given the relation
Book(Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher)
The FDs are
Book_title ??Publisher, Book_type
Book_type ??Listprice
Authorname ?Author_affil

Example
? What normal form the relation in?