Chapter 10part 3 Functional Dependencies and Normalization for

Chapter 10_part 3 Functional Dependencies and Normalization for Relational Databases

BCNF (Boyce-Codd Normal Form) n 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: n Every 2 NF relation is in 1 NF n Every 3 NF relation is in 2 NF n Every BCNF relation is in 3 NF ¨ There exist relations that are in 3 NF but not in BCNF ¨ The goal is to have each relation in BCNF (or 3 NF) Introduction to Databases 2

BCNF R 1(A, C) R 2(C, B) Introduction to Databases 3

Introduction to Databases 4

BCNF FDs: n {Student, course} Instructor n Instructor Course It is in 3 NF not in BCNF n Decomposing into 2 schemas {Student, Instructor} {Instructor, Course} Introduction to Databases 5

Examples BCNF n R ( Client#, Problem, Consultant _name) R 1 (Client#, Consultant _name) R 2 (Consultant _name, Problem) ■ R (Stud#, Class#, Instructor, Grade) R 1 (Stud#, Instructor, Grade) R 2 (Instructor, Class#) Introduction to Databases 6

Example Consider the following relation for published books: BOOK (Book_title, Author_name, Book_type, Listprice, Author_affil, Publisher) - Author_affil referes to the affiliation of the author. Suppose thefollowing dependencies exist: Book_title -> Publisher, Book_type -> Listprice Author_name -> Author-affil (a) What normal form is the relation in? Explain your answer. (b) Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition. Introduction to Databases 7

Answer BOOK (Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher) (a) The key for this relation is (Book_title, Authorname). This relation is in 1 NF and not in 2 NF as no attributes are Full FD on the key. It is also not in 3 NF. (b) 2 NF decomposition: Book 0(Book_title, Authorname) Book 1(Book_title, Publisher, Book_type, Listprice) Book 2(Authorname, Author_affil) This decomposition eliminates the partial dependencies. 3 NF decomposition: Book 0(Book_title, Authorname) Book 1 -1(Book_title, Publisher, Book_type) Book 1 -2(Book_type, Listprice) Book 2(Authorname, Author_affil) This decomposition eliminates the transitive dependency of Listprice Introduction to Databases 8

Example Given the relation schema Car_Sale (Car#, Salesman#, Date_sold, Commission%, Discount_amt) with the functional dependencies Date_sold -> Discount_amt Salesman# -> Commission% Car# -> Date_sold This relation satisfies 1 NF but not 2 NF (Car# -> Date_sold and Salesman# -> Commission%) so these two attributes are not Full FD on the primary key and not 3 NF Introduction to Databases 9

Answer To normalize, 2 NF: Car_Sale 1 (Car#, Salesman#) Car_Sale 2 (Car#, Date_sold, Discount_amt) Car_Sale 3 (Salesman#, Commission%) 3 NF: Car_Sale 1(Car#, Salesman#) Car_Sale 2 -1(Car#, Date_sold) Car_Sale 2 -2(Date_sold, Discount_amt) Car_Sale 3(Salesman#, Commission%) Introduction to Databases 10