opal
Uploaded by
7 SLIDES
153 VIEWS
70LIKES

Database Normalization and Decomposition Algorithms

DESCRIPTION

Learn about the BCNF and 4NF decomposition algorithms, inference rules for FDs and MVDs, and how to apply them in database schema design.

1 / 7

Download Presentation

Database Normalization and Decomposition Algorithms

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

  2. Missing Info?

  3. Closure of Attributes X Start with X+ = X Repeat until no change in X+ If there is Y -> Z with Y  X+, then Add Z to X+

  4. BCNF Decomposition Algorithm For any R in the schema If (X -> Y holds on R AND X -> Y is non-trivial AND X does not contain a key), then 1) Compute X+ (X+: closure of X) 2) Decompose R into R1(X+) and R2(X, Z) // X becomes common attributes // Z: all attributes in R except X+ Repeat until no more decomposition

  5. Class(cnum, ta, sid) Class 143 TA: Tony, James Students: 100, 101, 103 Class 248 TA: Tony, Susan Students: 100, 102

  6. Inference rules for FDs and MVDs • Reflexivity: If Y  X, then X -> Y • Augmentation: If X -> Y, then ZX -> ZY • Transitivity: If X -> Y and Y -> Z, then X -> Z • Complementation: If X ->> Y, then X ->> R – (YX) • MVD augmentation: If X ->> Y and W  Z, then ZX ->> WY • MVD transitivity: If X ->> Y and Y ->> Z, then X ->> Z – Y • Replication: If X -> Y, then X ->> Y • Coalescence: If X ->> Y, W -> Y (Z  Y, WY=), then X -> Z The set of above rules are sound and complete

  7. 4NF Decomposition Algorithm For any R in the schema If (non-trivial X ->> Y holds on R AND X does not contain a key), then Decompose R into R1(X,Y) and R2(X,Z) // X becomes common attributes // Z: all attributes in R except (X, Y) Repeat until no more decomposition

More Related