1 / 10

Chapter 10_part3

Chapter 10_part3. Functional Dependencies and Normalization for Relational Databases. 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

jriddle
Download Presentation

Chapter 10_part3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 10_part3 Functional Dependencies and Normalization for Relational Databases

  2. 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) Introduction to Databases

  3. BCNF R1(A,C) R2(C,B) Introduction to Databases

  4. Introduction to Databases

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

  6. ExamplesBCNF • R ( Client#,Problem, Consultant _name) R1 (Client#, Consultant _name) R2 (Consultant _name, Problem) • R (Stud#,Class#, Instructor, Grade) R1 (Stud#, Instructor, Grade) R2 (Instructor, Class#) Introduction to Databases

  7. 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 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

  8. 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 1NF and not in 2NF as no attributes are Full FD on the key. It is also not in 3NF. (b) 2NF decomposition: Book0(Book_title, Authorname) Book1(Book_title, Publisher, Book_type, Listprice) Book2(Authorname, Author_affil) This decomposition eliminates the partial dependencies. 3NF decomposition: Book0(Book_title, Authorname) Book1-1(Book_title, Publisher, Book_type) Book1-2(Book_type, Listprice) Book2(Authorname, Author_affil) This decomposition eliminates the transitive dependency of Listprice Introduction to Databases

  9. 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 1NF but not 2NF (Car# -> Date_sold and Salesman# -> Commission%) so these two attributes are not Full FD on the primary key and not 3NF Introduction to Databases

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

More Related