Design Theory for Relational Databases Contents 2 u

Design Theory for Relational Databases

Contents 2 u Functional Dependencies u Rules About Functional Dependencies u Design of Relational Database Schemas u Decomposition: The Good, Bad, and Ugly u Third Normal Form

Note: Dependency between Attributes a movie: title, year, length, genre, studio. Name, star. Name, . . . a movie star: name, address, birthdate, . . . A given value of (title, year) uniquely determines a studio: name, address, president, . . . each value of length, genre, studio. Name title Star Wars Wayne’s World Interstellar Noah The Martian title year Star Wars Wayne’s World Interstellar Noah The Martian 1977 1992 2014 2015 year Star Wars 1977 Wayne’s World 1992 Interstellar 2014 Noah 2014 The Martian 2015 length 127 95 169 139 144 genre sci. Fi comedy sci. Fic drama sci. Fic There are some relationships between attributes, which affects the design of tables 3 title Star Wars Wayne’s World year length genre studio. Name 1977 1992 2014 2015 124 95 169 139 144 sci. Fi comedy sci. Fic drama sci. Fic Fox Paramount Fox year length genre star. Name 1977 1992 124 124 95 95 sci. Fi comedy Carrie Fisher Mark Hammil Harrison Ford Dana Carvey Mike Meyers A given value of (title, year) does not uniquely determine the value of star. Name

Functional Dependencies u Functional dependency (FD) on a relation R A 1 , A 2 , , A n functionally determine B – A 1 A 2 A n B If two tuples of R agree on attributes A 1, A 2, An, then they must also agree in another attribute B title year genre – A 1 A 2 A n B 1 B 2 B m If two tuples of R agree on attributes A 1, A 2, An, then they must also agree on another list of attributes B 1 , B 2 , , Bm 4 A 1 A 2 An B 2. . . A 1 A 2 An B m title year length genre title year length title year genre

Functional Dependencies (cont’d) < Effect of a functional dependency on two tuples > A’s B’s A’s B’s title year length genre t 1 tuple t y 1 l 1 g 1 must be the same t 1 tuple u For a given value in LHS, there is a unique value in RHS If t and u agree here, then they must agree here Relation R satisfies the FD f » a given FD f is true in any instance of relation R 5 y 1 l 2 g 2 FD says something about all possible instances of relation R, not about a particular instance

Functional Dependencies (cont’d) (Ex) title year length genre studio. Name star. Name Star Wars 1977 124 Sci. Fi Fox Carrie Fisher Star Wars 1977 124 Sci. Fi Fox Mark Hamill Star Wars 1977 124 Sci. Fi Fox Harrison Ford Gone With the Wind 1939 231 Drama MGM Vivien Leigh Wayne’s World 1992 95 Comedy Paramount Dana Carvey Wayne’s World 1992 95 Comedy Paramount Mike Meyers Movies 1 (title, year, length, genre, studio. Name, star. Name) title year length genre studio. Name title year star. Name (X) 6 An FD between attributes depend on a particular application semantics An FD can be regarded as a constraint

Functional Dependencies (cont’d) u Keys of relations – a set of attributes {A 1, A 2, An} is a key for a relation R if : (1) Those attributes functionally determine all other attributes of R P i. e. , it is impossible for two distinct tuples of R to agree on all of A 1, A 2, , An (2) No proper subset of {A 1, A 2, An} functionally determines all other attributes of R; P i. e. , a key must be minimal 7

Functional Dependencies (cont’d) (Ex) Consider Moves 1 relation. Movies 1 (title, year, length, genre, studio. Name, star. Name) {title, year, star. Name} is a key » It functionally determines all the other attributes » No proper subset functionally determines all the other attributes If there are more than one keys, » designate one of the keys as the primary key 8

Functional Dependencies (cont’d) u Superkey » short for “superset of a key” – a set of attributes that contains a key every key is a superkey some superkeys are not minimal, i. e. , not keys (ex) Consider Movies 1 » {title, year, star. Name} is a key and is a superkey » {title, year, star. Name, length} is a superkey, but not a key 9

Rules about Functional Dependencies u Reasoning about Functional Dependencies » when a relation R satisfies a set of FD’s, we can deduce that the relation must satisfy certain other FD’s (ex) If a relation R(A, B, C) satisfies A B and B C, we can deduce that R also satisfies A C [Proof] – The splitting/combining rule – Trivial functional dependency – Closure of attributes – Transitive rule – Armstrong’s axiom 10 (a, b 1, c 1), (a, b 2, c 2) by A B: b 1 = b 2 (a, b, c 1), (a, b, c 2) by B C: c 1 = c 2 Thus, two tuples that agree on A also agree on C

Rules about FD (cont’d) every relation instance satisfying S also satisfies T, and vice versa ■ Two sets of FD’s S and T are equivalent » if the set of relation instances satisfying S is exactly the same as the set of relation instances satisfying T ■ A set of FD’s S follows from a set of FD’s T » if every relation instance that satisfies all the FD’s in T also satisfies all the FD’s in S (ex) {A C} follows from {A B, B C} Two sets of FD’s S and T are equivalent, iff S follows from T, and T follows from S How to test whether one FD follows from one or more other FD’s ? 11 compute the closure

Rules about FD (cont’d) u The splitting/combining rule » Let A denote A 1 A 2 An for simplicity A 1 , A 2 , , An are attributes of some objects – Splitting rule » We can replace a functional dependency A B 1 B 2 Bm by a set of FD’s A Bi for i = 1, 2, , m – Combining rule » We can replace a set of FD’s A Bi for i = 1, 2, m by the single functional dependency A B 1 B 2 Bm title year length genre title year length title year genre 12

Rules about FD (cont’d) A constraint is trivial: if it holds for any case, regardless of what other constraints are assumed u Trivial functional dependencies – An FD A 1 A 2 An B 1 B 2 Bm is trivial if the right side is a subset of the left side i. e. , two tuples that agree in all of A 1 A 2 An also agree in a subset of them (ex) title year title t 1 can remove attributes in the RHS that are also in the LHS y 1 y 2 t 1 /* trivial because title */ – An FD A 1 A 2 An B 1 B 2 Bm is equivalent to A 1 A 2 An C 1 C 2 Ck, where the C’s are all those B’s that are not also A’s. An FD is nontrivial if none of the B’s is one of the A’s 13

Note: Many FD’s can be deduced (Ex) Consider Relation R(A, B, C, D, E, F) FD’s: {AB C, BC AD, D E, AB BC BC A BC D AD E BD E ABC C ABC AD CD E ABD C ABC A ED E ABE C ABC D FD E ABF C BCD AD ABD E ABCD C BCD A ACD E ABCE C. . . BCD D. . . AED E. . . AB AC 14 CF B } AB D (∵ AB BC, BC D) AB E (∵ AB D, D E). . .

Rules about FD (cont’d) u Closure of attributes {A 1, A 2, An}: a set of attributes S: a set of FDs – the closure of {A 1, A 2, An} under the FD’s in S : » a set of attributes B such that every relation that satisfies all the FD’s in S also satisfies A 1 A 2 An B. a set of all attributes functionally determined by {A 1, A 2, An} a set of all attributes B such that A 1 A 2 An B holds – {A 1, A 2, An}+: the closure of {A 1, A 2, An} 15

(Ex) Closure of attributes set of attributes{title, year} set S of FD’s: {title year length, title year genre, title year studio. Name} closure C of {title, year}? title Star Wars Wayne’s World Interstellar Noah The Martian title Star Wars Wayne’s World 16 year length genre studio. Name 1977 1992 2014 2015 124 95 169 139 144 sci. Fi comedy sci. Fic drama sci. Fic Fox Paramount Fox year length genre star. Name 1977 1992 124 124 95 95 sci. Fi comedy Carrie Fisher Mark Hammil Harrison Ford Dana Carvey Mike Meyers Any relation satisfying all the FD’s in S also satisfies title year title, year, length, genre, studio. Name C = {title, year, length, genre, studio. Name} star. Name is not in C: because there is a relation that doe not satisfy title year star. Name

Rules about FD (cont’d) Algorithm: Closure of a set of attributes » Input: a set of attributes {A 1, A 2, An}, and a set of FD’s S S: all the FD’s have singleton right sides 1. Let X be a set of attributes that eventually will become the closure. P initially, X = {A 1, A 2, An} 2. Search for some FD, B 1 B 2 Bm C such that {B 1, B 2, Bm } X » Add C to the set X Note that X B 1 B 2 Bm is a trivial FD 3. Repeat Step 2 until no more attributes can be added to X – The set X, after no more attributes can be added to it, is the correct value of {A 1, A 2, An}+ 17 Since X can only grow, eventually nothing more can be added to X and the algorithm ends

(Ex) Closure of attributes (cont’d) (Ex) Consider Movies-R (title, year, length, studio. Name) FD’s: {title year length, title year studio. Name} Compute {title, year}+ : {title, year}, {title, year, length, studio. Name} {title, year}+ = {title, year, length, studio. Name} 18

Rules about FD (cont’d) (Ex) Consider Relation R(A, B, C, D, E, F) FD’s: {AB C, BC AD, D E, CF B } {A, B}+ : {A, B}, {A, B, C, D}, {A, B, C, D, E} Thus, {A, B}+ = {A, B, C, D, E} 19 {A, B}+ {A, B} LHS functionally determines every subset of RHS

Rules about FD (cont’d) is a consequence of v Test whether A 1 A 2 An B follows from a set of FDs S – Compute {A 1, A 2, An}+ under the set of FD’s S – If B is in {A 1, A 2, An }+ , then A 1 A 2 An B follows from S (ex) FD’s: {AB C, BC AD, D E, CF B } » Test whether AB D follows from the FD’s. · Since {A, B}+ = {A, B, C, D, E}, AB D does follow. » Test whether D A follows. · {D}+ = {D, E}, D A does not follow. 20 {A, B, C}, {A, B, C, D, E}

Rules about FD (cont’d) v Test if A 1, A 2, An is a key for relation R: – {A 1, A 2, An}+ is all attributes, and – for any proper subset Y of {A 1, A 2, An}, Y+ is not all attributes of R 21

Rules about FD (cont’d) u Transitive rule – If A 1 A 2 An B 1 B 2 Bm and B 1 B 2 Bm C 1 C 2 Ck hold, Then, A 1 A 2 An C 1 C 2 Ck also holds. <Proof> all the C’s are in {A 1, A 2, An}+ (ex) Consider another version of Movies’(title, year, length, genre, studio. Name, studio. Addr) Suppose we found the following FD’s: title year studio. Name studio. Addr 22 Then, we can infer title year studio. Addr

Note: Armstrong’s axioms » a sound and complete set of inference rules about FD’s – Reflexivity » If {B 1, B 2, Bm} {A 1, A 2, An}, then A 1 A 2 An B 1 B 2 Bm. – Augmentation » If A 1 A 2 An B 1 B 2 Bm, then for any set of attributes C 1 C 2 Ck, A 1 A 2 An C 1 C 2 C k B 1 B 2 B m C 1 C 2 C k. – Transitivity » If A 1 A 2 An B 1 B 2 Bm and B 1 B 2 Bm C 1 C 2 Ck, then A 1 A 2 An C 1 C 2 C k. 23

Note: Armstrong’s axioms (cont’d) Not in the text (Ex) Show that the following inference rules follow from Armstrong’s axioms – if X AB, then X A » X AB /* given */ » AB A /* by reflexivity */ » thus, X A /* by transitivity */ – if X A and X B, then X AB 24 » X XB /* by augmentation in X B*/ » XB AB /* by augmentation in X A */ » thus, X AB /* by transitivity */ Note that the closure computation can also prove these rules

Rules about FD (cont’d) basis u Closing sets of FD’s – basis for a set of FD’s for a relation » any set of FD’s from which we can infer all the FD’s for a relation – minimal basis B » all the FD’s in B have singleton right sides » no proper subset of B is a basis no redundant FD » for any X A in B, we cannot remove an attribute in X no redundant attribute in the left side (ex) Consider a relation R(A, B, C), and a set of FD’s for R. {A B, A C, B A, B C, C A, C B, AB C, AC B, BC A, A BC} minimal basis: {A B, B A, B C, C B}, {A B, B C, C A} , etc 25

Rules about FD (cont’d) u Projecting functional dependencies » R 1 = πL(R) » S: a set of FD’s for R What FD’s hold in a projected relation of R? – Find a minimal basis for projected relation R 1 Algorithm: Find-Projected-Basis » Find a basis having singleton right sides for a relation R 1 projection of FD’s S onto relation R 1 Find FD’s : - follow from S, and - only attributes in R 1 – For each subset X of the attributes in R 1, » find all X A such that X+ has A under S, and A is an attribute of R 1 26 Compute X+ under S. This computation may involve attributes in R that are not in R 1 The number of X’s is exponential in the number of attributes of R 1 !!!

Note: Minimal basis of Projected FD’s Algorithm: Projecting a Set of Functional Dependencies Find a minimal basis for R 1 – Let T be the output of “Find-Projected-Basis” Find a basis for R 1 – Construct a minimal basis by modifying T as follows: » Remove an FD F from T Find a minimal basis if F follows the other FD’s in T » Replace Y B by Z B, if Z Y, and Z B follows from T » Repeat the above steps until no more changes to T 27 remove redundant FD remove redundant attributes in the left side

Rules about FD (cont’d) A C, A D, . . . (Ex 1) Consider R (A, B, C, D) and FD’s: {A B, B C, C D} » R 1 (A, C, D) = πA, C, D (R) Do we have only C D ? – Find FD’s that hold in R 1 Consider the closure of each eight subsets of {A, C, D} · {A}+= {A, B, C, D}: A C, A D Find a basis for R 1 · {C}+= {C, D}: C D · {D}+= {D} If the closure of X is all attributes, any new FD’s cannot be discovered by closing superset of X · {A, C}+= {A, D}+= {A} + · {C, D}+= {C, D} » thus, FD’s for R 1 is {A C, A D, C D} – Find a minimal basis for R 1 {A C, C D} 28 because A D follows from {A C, C D}

Not in the text Note: Find a minimal basis v Find a minimal basis for relation R » Let F be a set of FD’s for relation R, where all the FD’s in F have singleton right sides remove redundant FD – For each X A in F, » remove X A from F, if X A is redundant i. e. , X A is implied by other FD’s If X+ under F‒{X A} has A, X A is redundant remove redundancy in LHS – For each X A in F, » remove B from X, if B in X is redundant If (X-{B})+ under F has A, B in X is redundant – The resulting set is a minimal basis for R 29 (ex) F = {CB A, C B} {C}+ = {C, B, A} under F has A, so B in CB A is redundant

Relational Database Design u Anomaly » problems such as redundancy that occur when we try to cram too much attributes into a single relation (ex) Consider the relation title year → length genre studio. Name key = {title, year, star. Name} Movies 1 (title, year, length, genre, studio. Name, star. Name) – Redundancy » Information may be repeated unnecessarily in several tuples (ex) For each movie, length and genre are repeated in several tuples 30

Relational Database Design (cont’d) There is a partial dependency (ex) Consider the relation Movies 1 below. title year length Star Wars 1977 124 Star Wars 1977 Star Wars genre title year length genre studio. Name star. Name gender studio. Name star. Name gender Sci. Fi Fox Carrie Fisher F 124 Sci. Fi Fox Mark Hamill M 1977 124 Sci. Fi Fox Harrison Ford M Gone With the Wind 1939 231 Drama MGM Vivien Leigh F Wayne’s World 1992 95 Comedy Paramount Dana Carvey M Wayne’s World 1992 95 Comedy Paramount Mike Meyers M Movies 1 (title, year, length, genre, studio. Name, star. Name, gender) key = {title, year, star. Name) 31

Relational Database Design (cont’d) – Update anomaly » We may change information in one tuple, but leave the same information unchanged in other tuples (ex) change the length of “Star Wars, 1977” in the first tuple, but not in the second or third tuples. Change the length of “Stars Wars, 1977” from 124 to 125 where Carrie Fisher appeared – Deletion anomaly » deletion of a value in one attribute may cause to lose information in other attributes as a side effect. (ex) If we delete a tuple of Vivien Leigh, we lose all the other information about the movie “Gone With the Wind”. (ex) If we delete a tuple of “Gone With the Wind”, we lose all the other information about the star Vivien Leigh 32

In-class only Note: Insertion anomaly, Deletion anomaly (Ex) Movies. Studio (title, year, length, genre, studio. Name, studio. Addr) » title year studio. Name, studio. Name studio. Addr » Key: {title, year} There is a transitive dependency 33 title year length Star Wars 1977 124 Gone With the Wind 1939 Wayne’s World Adams Family genre studio. Name studio. Addr Sci. Fi Fox Hollywood 231 Drama MGM Buena Vista 1992 95 Comedy Paramount Hollywood 1991 102 Comedy Paramount Hollywood

In-class only Note: Insertion/Deletion anomaly (cont’d) Insertion Anomaly – Suppose a new studio has started, but initially the studio has not produced any movie – Then, the tuple for this studio cannot be inserted into the table Movies. Studio because the key {title, year} will have null Deletion Anomaly – Consider there is only one movie m in some studio s and we want to delete m from Movies. Studio for some reason – Deletion of movie m from Movies. Studio » causes loss of other information about the studio s 34

Relational Database Design (cont’d) u Decomposing Relations » decompose relations to eliminate anomalies – Given a relation R(A 1, A 2, , An), we may decompose R into two relations S(B 1, B 2, , Bm) and T(C 1, C 2, , Ck) such that » {A 1, A 2, , An} = {B 1, B 2, , Bm} U {C 1, C 2, , Ck} » S = π B 1, B 2, , Bm (R) » T = π C 1, C 2, , Ck (R) 35

Relational Database Design (cont’d) Movies 1 (title, year, length, genre, studio. Name, star. Name) (Ex) Decompose Movies 1 into Movies 2 and Movies 3 Movies 2 Movies 3 Not redundant. (title, year) form a key, i. e. no more succinct way to represent a movie 36 title year length genre Star Wars 1977 124 Sci. Fi Fox Gone With the Wind 1939 231 Drama MGM Wayne’s World 1992 95 Comedy Paramount title year star. Name Star Wars 1977 Carrie Fisher Star Wars 1977 Mark Hamill Star Wars 1977 Harrison Ford Gone With the Wind 1939 Vivien Leigh Wayne’s World 1992 Dana Carvey Wayne’s World 1992 Mike Meyers studio. Name This decomposition eliminates anomalies in Movies. - redundancy - update anomaly - deletion anomaly Why this decomposition is good or bad?

(Ex) Concept of Decomposition (Event) Studio-1 produced a movie, titled “abcde”, in 1998 Movie-1 Studio-1 produced <Attributes related with the event> title, year, length, genre, studio. Name, producer#, studio. Address, pres. C#, star. Name-1, . . . Movies(title, year, length, genre, studio. Name, producer. C#, studio. Name, studio. Address, studio. Pres. C#) All the attributes in one table Movies(title, year, length, genre, studio. Name, producer. C#) Studio(name, address, pres. C#) 37 Original information is decomposed into multiple tables

Relational Database Design (cont’d) u Boyce-Codd Normal Form (BCNF) – A relation R is in BCNF iff : whenever there is a nontrivial FD A 1 A 2 An B for R, {A 1, A 2, , An} is a superkey for R. The left side of every nontrivial FD is a superkey Every determinant is a superkey (ex) Any two-attribute relation R(A, B) is in BCNF. » when there is no nontrivial FD: surely BCNF » when A B hold, but B A does not: A is the only key. Thus, BCNF » when both A B and B A hold: both A and B are keys. Thus, BCNF 38

Relational Database Design (cont’d) Why redundancy occurs in Movies 1? - FD viewpoint: there is a partial dependency - ER-model viewpoint: there is a multivalued attribute in an entity set (Ex) Consider relation Movies 1. title year length Star Wars 1977 124 Star Wars 1977 Star Wars genre studio. Name star. Name Sci. Fi Fox Carrie Fisher 124 Sci. Fi Fox Mark Hamill 1977 124 Sci. Fi Fox Harrison Ford Gone With the Wind 1939 231 Drama MGM Vivien Leigh Wayne’s World 1992 95 Comedy Paramount Dana Carvey Wayne’s World 1992 95 Comedy Paramount Mike Meyers » FD: title year length genre studio. Name » Key: {title, year, star. Name} – Movies 1 is not in BCNF 39 Movies 1 is in 1 NF

Relational Database Design (cont’d) (Ex) Consider relation Movies 2. title year length genre studio. Name Star Wars 1977 124 Sci. Fi Fox Gone With the Wind 1939 231 Drama MGM Wayne’s World 1992 95 Comedy Paramount » FD: title year length genre studio. Name » Key: {title, year} neither title nor year by itself functionally determines any of the other attributes. – Movies 2 is in BCNF 40

Relational Database Design (cont’d) u Decomposition into BCNF – we can break any relation schema into a collection of subsets of its attributes with the following properties. » each relation is in BCNF, and » the original relation can be reconstructed from the decomposed relation 41

Relational Database Design (cont’d) Algorithm: BCNF decomposition 1. For each nontrivial X A where X is not a superkey for R, These steps are applied recursively Pthe right side A is all the attributes that are functionally determined by X, i. e. , A = X+ – X decompose R into R 1 and R 2 such that R 1 = (X, A) and /* R 1 = X+ */ R 2 = (R – A) /* R 2 = R – (X+ – X) */ 2. Recursively decompose R 1 and R 2 until no such FD /* Compute FD’s for R 1 and R 2 before applying this recursive step PS 1 = the set of FD’s for R 1 PS 2 = the set of FD’s for R 2 42 a basis */ takes O(2 n) in the # of attributes n in the projection

Relational Database Design (cont’d) (Ex) Movies 1(title, year, length, genre, studio. Name, star. Name) » Key: {title, year, star. Name} – FD: title year length genre studio. Name » {title, year} is not a super key. Decompose Movies 1 into Movies 2 (title, year, length, genre, studio. Name) Movies 3 (title, year, star. Name) In this example, the problem is due to the existence of a partial dependency (or the existence of a multi-valued attribute) 43

Relational Database Design (cont’d) No partial dependency, but a transitive dependency: 2 NF (Ex) Consider relation R(title, year, studio. Name, president, pres. Addr) key: {title, year} » FD’s: title year studio. Name, studio. Name president, president pres. Addr BCNF violations – Decompose R » Start with studio. Name president pres. Addr R 1 = {studio. Name, president, pres. Addr}, R 2 = {title, year, studio. Name} PFD’s for R 1: {studio. Name president, president pres. Addr}, key: {studio. Name} » R 1 is not in BCNF because president pres. Addr R 3 = {president, pres. Addr}, R 4 = {studio. Name, president} 44 » Final result = R 2, R 3, R 4

Not in the text Note: Computing BCNF Decomposition (ex) R = {B, O, I, S, Q, D} FD’s: {S D, I B, IS Q, B O}. key: {I, S} SD key: {I, S}, Consider I B O BOISQ key: {I}, Consider B O BO ISQ In order to check BCNF-violating FD, compute the projection of FD’s onto R 1 IB BCNF: {S, D}, {B, O}, {I, B}, {I, S, Q} 45 all the attributes determined by I, i. e. , {I}+ - I Consider S D BOISQD Find a basis Find a key the set of leaf nodes is a BCNF decomposition The result may not be unique - can be more than one sequences of choosing BCNF-violating functional dependencies

Not in the text Note: Problem due to a partial dependency i. e. , the determinant is not a key u Partial dependency Key X A » X A where X is a proper subset of a key Key X A � 1 NF: all attributes have atomic values ( 1 x 1 a 1 ) ( 2 x 1 a 1 ) ( 3 x 1 a 1 ) no multivalued attribute, no composite attribute � 2 NF: 1 NF and every nonprime attribute is fully dependent on a key nonprime attribute (or nonkey attribute): attribute that is not part of a key 46 How about key attribute? ? ? Let X be a key attribute: Since X X, X is not fully dependent on a key

In-class only (Ex) Problem - partial dependency There is a partial dependency (ex) Consider the relation Movies 1 below. title year length Star Wars 1977 124 Star Wars 1977 Star Wars genre title year length genre studio. Name star. Name gender studio. Name star. Name gender Sci. Fi Fox Carrie Fisher F 124 Sci. Fi Fox Mark Hamill M 1977 124 Sci. Fi Fox Harrison Ford M Gone With the Wind 1939 231 Drama MGM Vivien Leigh F Wayne’s World 1992 95 Comedy Paramount Dana Carvey M Wayne’s World 1992 95 Comedy Paramount Mike Meyers M Movies 1 (title, year, length, genre, studio. Name, star. Name, gender) key = {title, year, star. Name) 47

In-class only Note: Problem due to a Multivalued attribute main cause of redundancy, v Multi-valued attribute in an entity set Movies – consider attribute star. Name in Movies entity set · {title, year}: would be a key for movie objects » star. Name has multiple values for each value of (title, year) needs to be part of a key, cause a partial dependency title year Movies studio. Name length Movies 1 48 star. Name title year length genre studio. Name key: {title, year, star. Name} Note: a partial dependency occurs genre title year length genre studio. Name star. Name Star Wars 1977 124 Sci. Fi Fox Carrie Fisher Star Wars 1977 124 Sci. Fi Fox Mark Hamill Star Wars 1977 124 Sci. Fi Fox Harrison Ford Not even 2 NF

Not in the text Note: Problem due to a transitive dependency i. e. , the determinant is not a key u Transitive dependency » X A where X is not a proper subset of any key Key X A Key X Not allowed in 3 NF A Some determinant exists in the middle of a key and a determinee Key A X A is a key attribute: Allowed in 3 NF � 3 NF: 2 NF and every nonkey attribute is not transitively dependent on a key 49 BCNF and 3 NF - There is no partial dependency - There is no dependency among non-key attributes

In-class only Note: Problem due to a transitive dependency Redundancy due to transitive dependency (Ex) Movies. Studio (title, year, length, genre, studio. Name, studio. Addr) » Key: {title, year} » title year studio. Name, studio. Name studio. Addr transitive dependency 2 NF, but not 3 NF 50 title year length Star Wars 1977 124 Gone With the Wind 1939 Wayne’s World Adams Family Not 3 NF because of a transitive dependency genre studio. Name studio. Addr Sci. Fi Fox Hollywood 231 Drama MGM Buena Vista 1992 95 Comedy Paramount Hollywood 1991 102 Comedy Paramount Hollywood

In-class only Note: Problem due to transitive dependency (cont’d) (Ex) Decompose Movies. Studio into Movie. Studio 1 and Movie. Studio 2 Movie. Studio 1 Movie. Studio 2 51 title year length Star Wars 1977 124 Sci. Fi Fox Gone With the Wind 1939 231 Drama MGM Wayne’s World 1992 95 Comedy Paramount Adams Family 1991 102 Comedy Paramount Studio. Name studio. Addr Fox Hollywood MGM Buena Vista Paramount Hollywood genre studio. Name

Note: 1 NF, 2 NF and 3 NF v Not in the text 1 NF, 2 NF and 3 NF – 1 NF: every attribute has atomic values 2 NF: only historical interest – 2 NF: there is no partial dependency Key X Key A X partial dependency: Not allowed A Key A X transitive dependency: Allowed – 3 NF: every determinant is a superkey, or the right-side is prime X 52 Key A X Allowed

Note: Various Normal Forms �Relationship among normal forms relations in 1 NF relations in 2 NF relations in 3 NF relations in BCNF 53

Decomposition: What are problems? v Properties that decompositions need to have 1. Elimination of Anomalies » Can we remove all the anomalies? 2. Recoverability of Information » Can we recover the original relation from the tuples in its decomposition? 3. Preservation of Dependencies » Are all the FD’s in the original relation preserved in the relations of the decomposition? 54

In-class only Note: Recoverability (ex) Decompose R(A, B, C) into R 1(A, B) and R 2(B, C). A B C A B B C 1 2 3 1 2 2 3 4 2 5 4 2 2 5 R (A, B, C) R 1 ⋈ R 2 55 R 1 (A, B) A B C 1 2 3 1 2 5 4 2 3 4 2 5 Can we recover the original tuples ? (A, B, C) = {(1, 2, 3), (4, 2, 5)} R 2 (B, C) spurious tuples There is no way we can recover the original relation !

In-class only Note: Dependency preservation zipcode-1 zipcode-2 zipcode-3 street-b zipcode-5 street-k zipcode-4 city-A zipcode-6 zipcode-7 zipcode-8 street-1 street-2 street-c (Ex) Zip. Code(zip, city, street) » zip city street-b street-a /* BCNF violation */ » street city zip » Keys: {street, city}, {zip, street} city-B A city is divided by a few areas Each area - has a unique zip code - may have several streets A city name is unique A street name is unique in a city, but can occur in more than one city BCNF: Zip. City(zip, city), Zip. Street(zip, street) street city zip » not preserved any more 56 Tuples that are not consistent with our original intention can appear when joining decomposed relations

Decomposition: The Good, Bad, and Ugly u Recovering information from a decomposition – After decomposition of the relation, the original relation can only be recovered by joining the decomposed relations. Pcareless decomposition may fail to reconstruct (ex) Decompose R(A, B, C) into R 1(A, B) and R 2(B, C). A B C A B B C 1 2 3 1 2 2 3 4 2 5 4 2 2 5 R (A, B, C) R 1 (A, B) R 2 (B, C) A B C 1 2 3 1 2 5 4 2 3 4 2 5 R’ (A, B, C) < loss of information ! > 57

Decomposition: The Good, Bad, and Ugly (cont’d) u Lossless join decomposition – Let R 1, R 2, , Rn be a decomposition of relation R e. g. , R 1(A, B) and R 2(B, C) is a decomposition of R(A, B, C) » the union of all the attributes in R 1, , Rn is equal to the attributes in R » each Ri has the projection of R onto the attributes of Ri – If the join of R 1, R 2, , Rn is equal to R, » this decomposition is called a lossless join decomposition For convenience, we use Ri to denote both the relation scheme and its instance 58 The decomposition is lossless. The decomposition is a lossless join decomposition. The decomposition has a lossless join.

Note: Lossless Join Decomposition S (A, B, C), FD’s = {A B, A C} b 1 c 1 a 1 b 1 c 1 a 2 b 2 c 2 a 3 b 1 c 3 R (A, B, C), FD’s = {B C} 59 S 1(A, B), S 2(B, C) a 1 S S 1⋈S 2 Not in the text B is a join attribute. B C does not hold: For a b-value, more than one c-values can occur R 1(A, B), R 2(B, C) a 1 b 1 c 1 a 2 b 2 c 2 a 3 b 1 c 1 R = R 1⋈R 2 B is a join attribute. B C holds: For each b-value, there is only one c-value

Note: Simple test for Lossless Join Not in the text The set of common attributes is a key for at least one relation Testing a lossless join decomposition – Let R 1 and R 2 be a decomposition of R. This decomposition has a lossless join iff R 1 R 2 R 1, or R 1 R 2. set of common attributes in R 1 and R 2 (ex) R = {A, B, C}, FD’s: { A B } » R 1 = {A, B}, R 2 = {A, C}: lossless Let R 1 R 2 be X. » R 1 = {A, B}, R 2 = {B, C}: not lossless » R 1 = {A, C}, R 2 = {B, C}: not lossless This test may not be easily applied if decomposed into more than two relations 60 t 1 : t 2 : R 1 -X X R 2 -X a a 2 b b c 1 c Tableau for R

Decomposition: The Good, Bad, and Ugly (cont’d) u Dependency preservation » Dependency may not be preserved in a BCNF decomposition – The result of joining decomposed relations satisfies the original FDs (Ex) Bookings (theater, city, title) /* A movie is currently being shown at some theater in some city */ » theater city /* BCNF violation */ » title city theater Each theater is only in one city In one city, a movie is shown only in one theater » Keys: {title, city}, {theater, title} theater 3 theater 1 title 1 61 theater 2 city-A title 1 theater 4 city-B

Decomposition: The Good, Bad, and Ugly (cont’d) – BCNF decomposition: {theater, city}, {theater, title} » Only “theater city” is preserved relations satisfying “theater city” theater Guild Park city Menlo Park theater title Guild Park Antz theater R 1⋈ R 2 Guild Park city Menlo Park title Antz “title city theater” is violated If we want to keep “title city theater”, costly to implement with R 1(theater, city) and R 2(theater, title) Lossless Join: Join of decomposed relations is equal to the original relation Dependency preservation: Join of decomposed relations satisfies the original FDs i. e. , a tuple violating the original intention does not appear when joining decomposed relations 62 FD-violating tuple

Third Normal Form Every determinant is a superkey, or the right-side is prime u Third Normal Form – A relation R is in third normal form (3 NF) if : Whenever A 1 A 2 An B is a nontrivial FD, either {A 1, A 2, An} is a superkey, or B is prime (attribute): an attribute is a member of some key relax BCNF requirement Key A v 63 X We can always have a lossless join and dependency preserving 3 NF decomposition

Third Normal Form (cont’d) Lossless join and Dependency Preservation Algorithm: 3 NF Decomposition » F: a set of FD’s that hold for relation R. » G: a minimal basis for F. – For each X A in G, create a relation scheme XA » may create a relation scheme (X, A 1, , Am) R(A, B, C, D): AB C, C B, A D X A 1, X A 2 X A 1 A 2 Thus, in (X, A 1, , Am), no dependency among A 1, , Am » If there are relations R 1 and R 2 such that R 2 R 1, R 2 may be removed – If none of the relation schemes contains a key for R, add a relation scheme consisting of a key for R. /* handles attributes not involved in any FD */ Since there can be more than one minimal bases, the result may not be unique 64 {X A 1, X A 2, A 1 A 2} - not minimal basis ∵ X A 2 is redundant

Third Normal Form (cont’d) 3 NF violation (Ex) R(A, B, C, D, E); F = {AB C, C B, A D} » keys: {A, E, B} and {A, E, C} E does not appear in any FD – Make sure F is a minimal basis – Take the attributes of each FD as a relation schema i. e. , R 1(A, B, C), R 2(B, C) and R 3(A, D) – Remove a relation that is a subset of another relation i. e. , remove R 2(B, C) – Since none of the relation schemes contains a key of R, Add a relation whose schema is a key i. e. , add one of keys, {A, B, E} or {A, C, E} Result: R 1(A, B, C), R 3(A, D), R 4(A, B, E). 65

Third Normal Form (cont’d) (Ex. cont’d) Check if F is a minimal basis. R(A, B, C, D, E) F = {AB C, C B, A D} » need to verify two things: 1) Remove redundant FD’s. » AB C If this has C, AB C is implied by other FD’s For each FD, check if other FD’s imply this FD Compute {A, B}+, using F-{AB C} {A, B}+F-{AB C} = {A, B, D} thus, AB C is not implied by F-{AB C} » C B : {C}+F-{C B} = {C} » A D : {A}+F-{A D} = {A} No FD can be eliminated, i. e. , not implied by any other FD’s 66

Third Normal Form (cont’d) R(A, B, C, D, E) F = {AB C, C B, A D} (Ex. cont’d) 2) Remove redundant attributes from the left side of the FD’s. » AB C Eliminate A, and check if B C is implied by F P{B}+F = {B}, so B C does not follow from F Eliminate B, and check if A C is implied by F P{A}+F = {A, D}, so A C does not follow from F No attribute can be eliminated from a left side of FD’s 67 If F implies B C, then A in AB C is redundant

Note: Summary of Good Decomposition v Properties that decompositions need to have 1. Elimination of Anomalies 2. Recoverability of Information 3. Preservation of Dependencies Various Normal Forms and the above properties » there is no way to get all these three at once – BCNF: gives 1 and 2, but not necessarily 3 – 3 NF: gives 2 and 3, but not necessarily 1 68

Note: 3 NF and BCNF Not in the text A relation that is in 3 NF, but is not in BCNF – when relations are not in BCNF, there will be some redundancy in the schema (Ex) Consider Bookings (theater, city, title) again. » FD’s : {theater city, title city theater} » Keys: {title, city}, {theater, title} theater repetition of information 69 city Bookings is in 3 NF, but is not in BCNF title Guild Menlo Park Star Wars Guild Menlo Park Rocky BCNF and 3 NF - There is no partial dependency - There is no dependency among non-key attributes

Note: 1 NF, 2 NF and 3 NF v Not in the text 1 NF, 2 NF and 3 NF – 1 NF: every attribute has atomic values 2 NF: only historical interest every nonprime attribute is fully dependent on a key – 2 NF: there is no partial dependency Key X Key A A X partial dependency: Not allowed Key A X transitive dependency: Allowed – 3 NF: every determinant is a superkey, or the right-side is prime X 70 Key A X Allowed