Temple University CIS Dept CIS 331 Principles of

Temple University – CIS Dept. CIS 331– Principles of Database Systems V. Megalooikonomou Database Design and Normalization (based on notes by Silberchatz, Korth, and Sudarshan and notes by C. Faloutsos at CMU)

Overview n Relational model n n n Integrity constraints n n formal query languages commercial query languages (SQL) domain I. C. , foreign keys functional dependencies Functional Dependencies DB design and normalization

Overview - detailed n DB design and normalization n pitfalls of bad design decomposition normal forms

Goal n Design ‘good’ tables n n n sub-goal#1: define what ‘good’ means sub-goal#2: fix ‘bad’ tables in short: “we want tables where the attributes depend on the primary key, on the whole key, and nothing but the key” n Let’s see why, and how:

Pitfalls takes 1 (ssn, c-id, grade, name, address) Ssn c-id Grade Name 123 cs 331 A Address smith Main

Pitfalls ‘Bad’ - why? because: ssn->address, name Ssn c-id 123 Grade Name Address cs 331 A smith Main 123 cs 351 B smith Main 123 cs 211 A smith Main

Pitfalls n Redundancy n n n space (inconsistencies) insertion/deletion anomalies:

Pitfalls n insertion anomaly: n “jones” registers, but takes no class - no place to store his address! Ssn c-id Grade Name Address 123 cs 331 A smith Main … … 234 null … jones Forbes

Pitfalls n deletion anomaly: n delete the last record of ‘smith’ (we lose his address!) Ssn c-id 123 Grade Name Address cs 331 A smith Main 123 cs 351 B smith Main 123 cs 211 A smith Main

Solution: decomposition n split offending table in two (or more), e. g. : Ssn 123 123 ? c-id Grade cs 331 A cs 351 B cs 211 A Name smith Address Main ?

Overview - detailed n DB design and normalization n n pitfalls of bad design decomposition n lossless join dependency preserving normal forms

Decompositions n n there are ‘bad’ decompositions we want: n n lossless and dependency preserving

Decompositions - lossy: R 1(ssn, grade, name, address) R 2(c-id, grade) c-id Grade cs 331 A cs 351 B cs 211 A Ssn 123 234 c-id Grade cs 331 A cs 351 B cs 211 A Name smith jones Address Main Forbes ssn->name, address ssn, c-id -> grade

Decompositions - lossy: can not recover original table with a join! c-id Grade cs 331 A cs 351 B cs 211 A Ssn c-id Grade Name Address 123 cs 331 A smith Main 123 cs 351 B smith Main 234 cs 211 A jones Forbes ssn->name, address ssn, c-id -> grade

Decompositions – lossy: Another example n Decomposition of R = (A, B) into R 1 = (A), R 2 = (B) A B 1 2 1 1 2 A(r) B(r) r A (r) B (r) A B 1 2

Decompositions example of non-dependency preserving S# -> address, status address -> status S# -> address S# -> status

Decompositions is it lossless? S# -> address, status address -> status S# -> address S# -> status

Decompositions - lossless Definition: Consider schema R, with FD ‘F’. R 1, R 2 is a lossless join decomposition of R if we always have: An easier criterion?

Decomposition - lossless Theorem: lossless join decomposition if the joining attribute is a superkey in at least one of the new tables Formally:

Decomposition - lossless example: R 1 Ssn 123 234 c-id Grade cs 331 A cs 351 B cs 211 A ssn, c-id -> grade Ssn c-id Grade Name Address 123 cs 331 A smith Main 123 cs 351 B smith Main 234 cs 211 A jones Forbes R 2 ssn->name, address ssn, c-id -> grade

Overview - detailed n DB design and normalization n n pitfalls of bad design decomposition n lossless join decomp. dependency preserving normal forms

Decomposition - depend. pres. informally: we don’t want the original FDs to span two tables - counter-example: S# -> address, status address -> status S# -> address S# -> status

Decomposition - depend. pres. dependency preserving decomposition: S# -> address, status address -> status S# -> address -> status (but: S#->status ? )

Decomposition - depend. pres. informally: we don’t want the original FDs to span two tables more specifically: … the FDs of the canonical cover Let Fi be the set of dependencies F+ that include only attributes in Ri. n n Preferably the decomposition should be dependency preserving, that is, ( F 1 F 2 … F n ) + = F+ Otherwise, checking updates for violation of functional dependencies may require computing joins expensive

Decomposition - depend. pres. why is dependency preservation good? S# -> address S# -> status (address->status: ‘lost’) S# -> address -> status

Decomposition - depend. pres. A: eg. , record that ‘Philly’ has status ‘A’ S# -> address S# -> status (address->status: ‘lost’) S# -> address -> status

Decomposition - depend. pres. n To check if a dependency is preserved in a decomposition of R into R 1, R 2, …, Rn we apply the following test (with attribute closure done w. r. t. F) n n n result = while (changes to result) do for each Ri in the decomposition t = (result Ri)+ Ri result = result t If result contains all attributes in , then functional dependency is preserved We apply the test on all dependencies in F to check if a decomposition is dependency preserving The test takes polynomial time Computing F+ and (F 1 F 2 … Fn)+ needs exponential time

Decomposition - conclusions n decompositions should always be lossless n n joining attribute -> superkey whenever possible, we want them to be dependency preserving (occasionally, impossible - see ‘STJ’ example later…)

Normalization using FD n When decomposing a relation schema R with a set of functional dependencies F into R 1, R 2, …, Rn we want: n n n Lossless-join decomposition: otherwise … information loss No redundancy: relations Ri preferably should be in either Boyce-Codd Normal Form or Third Normal Form Dependency preservation: Let Fi be the set of dependencies in F+ that include only attributes in Ri. n n Preferably the decomposition should be dependency preserving, i. e. , ( F 1 F 2 … F n ) + = F+ Otherwise, checking updates for violation of functional dependencies may require computing joins expensive

Normalization using FD - Example n n R = (A, B, C) F = {A B, B C) R 1 = (A, B), R 2 = (B, C) n n n Lossless-join decomposition: R 1 R 2 = {B} and B BC Dependency preserving R 1 = (A, B), R 2 = (A, C) n n Lossless-join decomposition: R 1 R 2 = {A} and A AB Not dependency preserving (cannot check B C without computing R 1 R 2)

Overview - detailed n DB design and normalization n pitfalls of bad design decomposition ( how to fix the problem) normal forms ( how to detect the problem) n n n BCNF, 3 NF, (1 NF, 2 NF)

Normal forms - BCNF We saw how to fix ‘bad’ schemas but what is a ‘good’ schema? Answer: ‘good’, if it obeys a ‘normal form’, i. e. , a set of rules Typically: Boyce-Codd Normal Form (BCNF)

Normal forms - BCNF Defn. : Rel. R is in BCNF w. r. t. F, if n n informally: everything depends on the full key, and nothing but the key semi-formally: every determinant (of the cover) is a candidate key

Normal forms - BCNF Example and counter-example: Ssn 123 234 ssn->name, address c-id Grade cs 331 A cs 351 B cs 211 A Name smith jones Address Main Forbes ssn->name, address ssn, c-id -> grade

Normal forms - BCNF Formally: for every FD a->b in F+ n a->b is trivial (a is a superset of b) or a is a superkey n (or both) n

Normal forms - BCNF Theorem: given a schema R and a set of FD ‘F’, we can always decompose it to schemas R 1, … Rn, so that n n R 1, … Rn are in BCNF and the decomposition is lossless (…but, some decomp. might lose dependencies)

BCNF Decomposition How? …. essentially, break off FDs of the cover eg. TAKES 1(ssn, c-id, grade, name, address) ssn -> name, address ssn, c-id -> grade

Normal forms - BCNF eg. TAKES 1(ssn, c-id, grade, name, address) ssn -> name, address ssn, c-id -> grade name ssn address grade c-id

Normal forms - BCNF Ssn 123 234 c-id Grade cs 331 A cs 351 B cs 211 A ssn, c-id -> grade Ssn c-id Grade Name Address 123 cs 331 A smith Main 123 cs 351 B smith Main 234 cs 211 A jones Forbes ssn->name, address ssn, c-id -> grade

Normal forms - BCNF pictorially: we want a ‘star’ shape name ssn address grade c-id : not in BCNF

Normal forms - BCNF pictorially: we want a ‘star’ shape F B A or C D G E H

Normal forms - BCNF or a star-like: (e. g. , 2 cand. keys): STUDENT(ssn, st#, name, address) name ssn address st# address = st#

Normal forms - BCNF but not: F B A or D C D G E H

BCNF Decomposition result : = {R}; done : = false; compute F+; while (not done) do if (there is a schema Ri in result that is not in BCNF) then begin let be a nontrivial functional dependency that holds on Ri such that Ri is not in F+, and = ; result : = (result – Ri) (Ri – ) ( , ); end else done : = true; Note: each Ri is in BCNF, and decomposition is lossless-join

Normal forms - 3 NF consider the ‘classic’ case: STJ( Student, Teacher, sub. Ject) T-> J S, J -> T is it BCNF? S T J

Normal forms - 3 NF STJ( Student, Teacher, sub. Ject) T-> J S, J -> T How to decompose it to BCNF? S T J

Normal forms - 3 NF STJ( Student, Teacher, sub. Ject) T-> J S, J -> T 1) R 1(T, J) R 2(S, J) (BCNF? - lossless? - dep. pres. ? ) 2) R 1(T, J) R 2(S, T) (BCNF? - lossless? - dep. pres. ? )

Normal forms - 3 NF STJ( Student, Teacher, sub. Ject) T-> J S, J -> T 1) R 1(T, J) R 2(S, J) (BCNF? Y+Y - lossless? N - dep. pres. ? N ) 2) R 1(T, J) R 2(S, T) (BCNF? Y+Y - lossless? Y - dep. pres. ? N )

Normal forms - 3 NF STJ( Student, Teacher, sub. Ject) T-> J S, J -> T in this case: impossible to have both n BCNF and n dependency preservation Welcome 3 NF (…a weaker normal form)!

Normal forms - 3 NF STJ( Student, Teacher, sub. Ject) T-> J S, J -> T S T J informally, 3 NF ‘forgives’ the red arrow in the can. cover

Normal forms - 3 NF STJ( Student, Teacher, sub. Ject) T-> J S, J -> T for every a->b in F+: • it is trivial or S T J Formally, a rel. R with FDs ‘F’ is in 3 NF if: • a is a superkey or • each b-a attr. : part of a cand. key

Normal forms - 3 NF n n n R = (J, K, L) F = {JK L, L K} Two candidate keys = JK and JL R is not in BCNF Any decomposition of R will fail to preserve JK L n BCNF decomposition has (JL) and (LK) n Testing for JK L requires a join n R is in 3 NF JK L L K JK is a superkey K is contained in a candidate key There is some redundancy in this schema…

Normal forms - 3 NF TESTING FOR 3 NF n n n Optimization: Need to check only FDs in F, need not check all FDs in F+ Use attribute closure to check, for each dependency , if is a superkey If is not a superkey, we have to verify if each attribute in is contained in a candidate key of R n n n this test is more expensive; it involves finding candidate keys testing for 3 NF is NP-hard Interestingly, decomposition into 3 NF (described shortly) can be done in polynomial time

Decomposition into 3 NF Let Fc be a canonical cover for F; The i : = 0; dependencies are preserved for each functional dependency in Fc do by building explicitly a if none of the schemas Rj, 1 j i contains schema for then begin each given dependency i : = i + 1; Ri : = end Guarantees a if none of the schemas Rj, 1 j i contains a lossless-join decomposition candidate key for R by having at least one then begin schema i : = i + 1; containing a candidate key Ri : = any candidate key for R; for the schema end being decomposed return (R , . . . , R )

Normal forms - 3 NF how to bring a schema to 3 NF? In short …. for each FD in the cover, put it in a table

Normal forms - 3 NF vs BCNF n n If ‘R’ is in BCNF, it is always in 3 NF (but not the reverse) In practice, aim for n n BCNF; lossless join; and dep. preservation if impossible, we accept n n 3 NF; but insist on lossless join and dep. preservation 3 NF has problems with transitive dependecies

3 NF vs BCNF (cont. ) n Example of problems due to redundancy in 3 NF n R = (J, K, L) F = {JK L, L K} J L K j 1 l 1 k 1 j 2 l 1 k 1 j 3 l 1 k 1 null l 2 k 2 A schema that is in 3 NF but not in BCNF has the problems of n repetition of information (e. g. , the relationship l 1, k 1) n need to use null values (e. g. , to represent the relationship l 2, k 2 where there is no corresponding value for J).

Normal forms - more details n n why ‘ 3’NF? what is 2 NF? 1 NF: attributes are atomic (i. e. , no setvalued attr. , a. k. a. ‘repeating groups’) not 1 NF

Normal forms - more details 2 NF: 1 NF and non-key attr. fully depend on the key counter-example: TAKES 1(ssn, c-id, grade, name, address) ssn -> name, address ssn, c-id -> grade ssn grade name address c-id

Normal forms - more details n n 3 NF: 2 NF and no transitive dependencies counter-example: D A B C in 2 NF, but not in 3 NF

Normal forms - more details n n 4 NF, multivalued dependencies etc: later… in practice, E-R diagrams usually lead to tables in BCNF

Overview - conclusions DB design and normalization n pitfalls of bad design decompositions (lossless, dep. preserving) normal forms (BCNF or 3 NF) “everything should depend on the key, the whole key, and nothing but the key”