Relational Design Theory BoyceCodd Normal Form Jennifer Widom

Relational Design Theory Boyce-Codd Normal Form Jennifer Widom

Relational design by decomposition BCNF § “Mega” relations + properties of the data § System decomposes based on properties § Final set of relations satisfies normal form – No anomalies, no lost information § Functional dependencies Boyce-Codd Normal Form § Multivalued dependences Fourth Normal Form Jennifer Widom

Decomposition of a relational schema BCNF Jennifer Widom

Decomposition Example #1 BCNF Student(SSN, s. Name, address, HScode, HSname, HScity, GPA, priority) Jennifer Widom

Decomposition Example #2 BCNF Student(SSN, s. Name, address, HScode, HSname, HScity, GPA, priority) Jennifer Widom

Relational design by decomposition BCNF § “Mega” relations + properties of the data § System decomposes based on properties v “Good” decompositions only v Into “good” relations Jennifer Widom

Boyce-Codd Normal Form BCNF Relation R with FDs is in BCNF if: For each Ᾱ B, Ᾱ is a key Jennifer Widom

BCNF? Example #1 BCNF Student(SSN, s. Name, address, HScode, HSname, HScity, GPA, priority) SSN s. Name, address, GPA priority HScode HSname, HScity Jennifer Widom

BCNF? Example #2 BCNF Apply(SSN, c. Name, state, date, major) SSN, c. Name, state date, major Jennifer Widom

Relational design by decomposition BCNF § “Mega” relations + properties of the data § System decomposes based on properties v “Good” decompositions only v Into “good” relations Jennifer Widom

BCNF decomposition algorithm BCNF Input: relation R + FDs for R Output: decomposition of R into BCNF relations with “lossless join” Compute keys for R Repeat until all relations are in BCNF: Pick any R’ with A B that violates BCNF Decompose R’ into R 1(A, B) and R 2(A, rest) Compute FDs for R 1 and R 2 Compute keys for R 1 and R 2 Jennifer Widom

BCNF Decomposition Example BCNF Student(SSN, s. Name, address, HScode, HSname, HScity, GPA, priority) SSN s. Name, address, GPA HScode HSname, HScity GPA priority Jennifer Widom

BCNF decomposition algorithm BCNF Input: relation R + FDs for R Output: decomposition of R into BCNF relations with “lossless join” Compute keys for R Repeat until all relations are in BCNF: Pick any R’ with A B that violates BCNF Decompose R’ into R 1(A, B) and R 2(A, rest) Compute FDs for R 1 and R 2 Compute keys for R 1 and R 2 Jennifer Widom

Does BCNF guarantee a good decomposition? BCNF § Removes anomalies? § Can logically reconstruct original relation? Too few or too many tuples? Jennifer Widom

Does BCNF guarantee a good decomposition? BCNF § Removes anomalies? § Can logically reconstruct original relation? Too few or too many tuples? § Some shortcomings discussed in later video Jennifer Widom