1 / 11

Today’s class

Today’s class. Introduction to Normalization BCNF Decomposition (Boyce-Codd). Normalization. Consider the following Relational table ‘Movies’. Anomalies : Redundancy, Update Anomalies, Deletion Anomalies. Example 1. ‘Movies’ has following schema

brant
Download Presentation

Today’s class

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. Today’s class Introduction to Normalization BCNF Decomposition (Boyce-Codd)

  2. Normalization Consider the following Relational table ‘Movies’ Anomalies: Redundancy, Update Anomalies, Deletion Anomalies

  3. Example 1 • ‘Movies’ has following schema • (Title, Year, Length, FilmType, StudioName, StarName) • Expected FD • Title Year  Length, FilmType, StudioName • { Title, Year, StarName } is the only Key • Movies relation contains ‘redundancy’ • Due to ‘multi-valued attribute’ StarName • Title, Year, Length, FilmType, StudioName repeated for every star of a movie

  4. Movies1 Movies2 ‘Movies’ is divided into two tables ‘Movies1’ and ‘Movies2’ This decomposition has resulted in removal of ‘Anomalies’ It’s BCNF Decomposition

  5. Loss-less Join Decomposition Condition: When R is divided into R1, R2 the decomposition is loss-less join if R1  R2  R1 or R1  R2  R2

  6. BCNF • A relation R is in BCNF iff every non-trivial FD X  Y is such that X is a superkey • X should contain a key, any key • BCNF decomposition • Identify A non-trivial BCNF violating FD, X  Y • If Y = X+ fine, otherwise replace Y by X+ • Divide R into R1 with attributes X  Y and R2 with attributes X  (R-X-Y) • The above is a loss-less join decomposition • Note: Any 2-attribute relation is in BCNF

  7. Back to Example 1 • ‘Movies’ has following schema • (Title, Year, Length, FilmType, StudioName, StarName) • Expected FD • Title Year  Length, FilmType, StudioName • { Title, Year, StarName } is the only Key • The FD violates BCNF condition • Doing BCNF decomposition on ‘Movies’ results in • Movies1 (Title, Year, Length, FilmType, StudioName) • Movies2 (Title, Year, StarName)

  8. Example 2 : MovieStudios Given FD’s Title Year  Length, FilmType, StudioName StudioName  StudioAddr { Title, Year } is the only key Unlike Example1 all attributes here are single-valued but still redundancy exists due to ‘transitive dependency’

  9. Example 3 • Let R=R(A,B,C,D,E) with FDs F={ ABC, CD, DE) • {A,B} is the only key • C  D violates the BCNF condition • C+ = {D,E} therefore consider C  DE • BCNF Decomposition • R1={C,D,E} with C  D, D  E ( {C} is the key) • R2={C,A,B} with AB  C ( {A,B} is the key) • D  E in R1 violates the BCNF • R1’={D,E} with D  E ({D} is the key) • R1’’={D,C} with C  D ({C} is the key) • Final Decomposition of R is R1’, R1’’, R2 which are all in BCNF

  10. Dependency Preserving • A decomposition of R into R1 and R2 is dependency preserving if F+ = (FR1 FR2)+ where ‘FS’ (S⊆R) represents projection of F on S • Is BCNF decomposition dependency preserving? • NO

  11. Example 4 • Let R=R(A,B,C) with FDs F={ BC, ACB} • Keys: {A,C}, {A,B} • BC violates BCNF condition • R1={B,C} with BC ({B} is the key) • R2={B,A} • What about ACB • BCNF decomposition is not Dependency Preserving

More Related