Relational Database Design Theory Part II CPS 196.3 Introduction - - PDF document

▶

Jul 15, 2023 216 likes •305 views

Relational Database Design Theory Part II CPS 196.3 Introduction to Database Systems 2 Announcement ! Project proposal/progress report due today ! Midterm next Thursday in class " Everything up to todays lecture, with a focus on the

SLIDE 1

1 Relational Database Design Theory Part II

CPS 196.3 Introduction to Database Systems

Announcement

! Project proposal/progress report due today ! Midterm next Thursday in class

" Everything up to today’s lecture, with a focus on the materials covered by the first two homework assignments " Open book, open notes

! Will assign an optional problem set tonight as a study

guide for midterm

" Entirely optional " If you turn it in on Tuesday in class, you can use its grade to replace your lowest homework grade so far " Solution will be posted on Tuesday midnight

! Graded Homework #2 will be available on Tuesday

Review

! Functional dependencies

" X → Y: If two rows agree on X, they must agree on Y #A generalization of the key concept

! Non-key functional dependencies: a source of redundancy

" No trivial X → Y where X is not a superkey #Called a BCNF violation

! BCNF decomposition: a method for removing redundancies

" Given R(X, Y, Z) and a BCNF violation X → Y, decompose R into R1(X, Y) and R2(X, Z) #A lossless join decomposition

! Schema in BCNF has no redundancy due to FD’s

SLIDE 2

2

! 3NF (BCNF is too much) ! Multivalued dependencies: another source of

redundancy

! 4NF (BCNF is not enough)

Motivation for 3NF

! Address (street_address, city, state, zip)

" street_address, city, state → zip " zip → city, state

! Keys ! BCNF?

To decompose or not to decompose

Address1 Address2

! FD’s in Address1 ! FD’s in Address2 ! Hey, where is street_address, city, state → zip?

" Cannot check without joining Address1 and Address2 back together

! Problem: Some lossless join decomposition is not

dependency-preserving

! Dilemma: Should we get rid of redundancy at the expense

f making constraints harder to enforce?

SLIDE 3

3 3NF

! R is in Third Normal Form (3NF) if for every non-trivial

FD X → A (where A is single attribute), either

" X is a superkey of R, or " A is a member of at least one key of R #Intuitively, BCNF decomposition on X → A would “break” the key containing A

! So Address is already in 3NF ! Tradeoff:

" Can enforce all original FD’s on individual decomposed relations " Might have some redundancy due to FD’s

BNCF = no redundancy?

! Student (SID, CID, club)

" Suppose your classes have nothing to do with the clubs you join " FD’s? " BNCF? " Redundancies?

SID CID club 142 CPS196 ballet 142 CPS196 sumo 142 CPS114 ballet 142 CPS114 sumo 123 CPS196 chess 123 CPS196 golf ... ... ...

Multivalued dependencies

! A multivalued dependency (MVD) has the form

X ! Y, where X and Y are sets of attributes in a relation R

! X ! Y means that whenever two rows in R agree on

all the attributes of X, then we can swap their Y components and get two new rows that are also in R

X Y Z a b1 c1 a b2 c2 ... ... ... X Y Z a b1 c1 a b2 c2 a b1 c2 a b2 c1 ... ... ...

Must be in R too

SLIDE 4

4 MVD examples

Student (SID, CID, club)

! SID ! CID

Complete MVD + FD rules

! FD reflexivity, augmentation, and transitivity ! MVD complementation:

If X ! Y, then X ! attrs(R) – X – Y

! MVD augmentation:

If X ! Y and V ⊆ W, then XW ! YV

! MVD transitivity:

If X ! Y and Y ! Z, then X ! Z – Y

! Replication (FD is MVD):

If X → Y, then X ! Y

! Coalescence:

If X ! Y and Z ⊆ Y and there is some W disjoint from Y such that W → Z, then X → Z

Try proving things using these!

An elegant solution: chase

! Given a set of FD’s and MVD’s D, does another

dependency d (FD or MVD) follow from D?

! Procedure

" Start with the hypotheses of d, and treat them as “seed” tuples in a relation " Apply the given dependencies in D repeatedly

If we apply an FD, we infer equality of two symbols
If we apply an MVD, we infer more tuples

" If we infer the conclusion of d, we have a proof " Otherwise, if nothing more can be inferred, we have a counterexample

SLIDE 5

5 Proof by chase

! In R(A, B, C, D), does A ! B and B ! C imply

that A ! C?

A B C D a b1 c1 d1 a b2 c2 d2 A B C D a b1 c2 d1 a b2 c1 d2

Have Need

Another proof by chase

! In R(A, B, C, D), does A → B and B → C imply

that A → C?

Have Need c1 = c2 In general, both new tuples and new equalities may be generated

A B C D a b1 c1 d1 a b2 c2 d2

Counterexample by chase

! In R(A, B, C, D), does A ! BC and CD → B imply

that A → B?

A B C D a b1 c1 d1 a b2 c2 d2

Have Need b1 = b2

SLIDE 6

6 4NF

! A relation R is in Fourth Normal Form (4NF) if

" For every non-trivial MVD X ! Y in R, X is a superkey " That is, all FD’s and MVD’s follow from “key → other attributes” (i.e., no MVD’s and no FD’s besides key functional dependencies)

! 4NF is stronger than BCNF

" Because every FD is also a MVD

4NF decomposition algorithm

! Find a 4NF violation

" A non-trivial MVD X ! Y in R where X is not a superkey

! Decompose R into R1 and R2, where

" R1 has attributes X ∪ Y " R2 has attributes X ∪ Z (Z contains attributes not in X or Y)

! Repeat until all relations are in 4NF ! Almost identical to BCNF decomposition algorithm ! Any decomposition on a 4NF violation is lossless

4NF decomposition example

Student (SID, CID, club)

SID CID club 142 CPS196 ballet 142 CPS196 sumo 142 CPS114 ballet 142 CPS114 sumo 123 CPS196 chess 123 CPS196 golf ... ... ...

SLIDE 7

7 3NF, BCNF, 4NF, and beyond

! Of historical interests

" 1NF: All column values must be atomic " 2NF: There is no partial functional dependency (a non- trivial FD X → A where X is a proper subset of some key)

1