1 / 19

Chapter 15

Chapter 15. Normalization for Relational Databases. Lecture # 15 July 24 ,2012. General Definitions of Second and Third Normal Forms. Boyce-Codd Normal Form. Every relation in BCNF is also in 3NF Relation in 3NF is not necessarily in BCNF Difference:

lev
Download Presentation

Chapter 15

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 15 • Normalization for Relational Databases • Lecture # 15 July 24,2012

  2. General Definitions of Secondand Third Normal Forms

  3. Boyce-Codd Normal Form • Every relation in BCNF is also in 3NF • Relation in 3NF is not necessarily in BCNF • Difference: • Condition which allows A to be prime is absent from BCNF • Most relation schemas that are in 3NF are also in BCNF

  4. Assume the following FD: Student, Course ->Instructor Instructor->Course

  5. Multivalued Dependencyand Fourth Normal Form • Multivalued dependency (MVD) • Consequence of first normal form (1NF) • Notes: • X->> Y implies X->>Z • t1,t2,t3,t4 are not necessarily distinct.

  6. Multivalued Dependencyand Fourth Normal Form (cont’d.) • Relations containing nontrivial MVDs • They tend to be all-key relations • Fourth normal form (4NF) • Violated when a relation has undesirable multivalued dependencies

  7. Join Dependenciesand Fifth Normal Form • Join dependency • Multiway decomposition into fifth normal form (5NF) • Very peculiar semantic constraint • Normalization into 5NF is very rarely done in practice

  8. Join Dependenciesand Fifth Normal Form (cont’d.)

  9. 4th Normal Form Example

  10. 5th Normal Form Example

  11. Set Closure

  12. Exercise 1 Consider a relation R(A, B, C, D), with FDs AB -> C, BC -> D, CD -> A. • (a) Find the closure of AB. • (b) Is R a good schema? • (c) If we decompose R as R1(A,B,C) and R2(A,C,D). Is it a good decomposition?

  13. Exercise 2 • Consider relation R(A,B,C,D,E) with the following functional dependencies: AB -> C, D -> E, DE -> B. • Is R in BCNF? If not, decompose R into a collection of BCNF relations.

  14. Exercise 3 • “Any two-attribute relation is in BCNF.” Is it correct?

  15. Exercise 4 Compute the closure of the following set F of functional dependencies for relation schema R = {A, B, C, D, E}. A -> BC CD -> E B -> D E -> A List the candidate keys for R.

  16. Exercise 5 Consider a relation R(A,B,C,D,E) with the following dependencies: {AB-> C, CD -> E, DE -> B} List all candidate keys.

  17. Exercise 6 R(A,B,C,D) and FDs {AB -> C, C -> D, D -> A}. (1) List all nontrivial FDs that can be inferred from the given FDs. (2) Find all candidate keys. (3) Find all BCNF violations. (4) Decompose R into relations in BCNF. (5) What FDs are not preserved by BCNF.

More Related