RECAP But So far we have seen: How do we make sure the - PDF document

RECAP But So far we have seen: • How do we make sure the decompositions are lossless (equivalence preserving)? • How to use functional dependencies to guide the design of relations • Are some decompositions better than others? • How to modify/decompose relations to achieve 1NF, 2NF and 3NF relations • What if there are multiple candidate keys? 1 2 Why is losslessness important? An Instance of Relation NEWS • No information is lost or added implicitly by S# SNAME STATUS CITY mistake. S1 Smith 20 London S2 Jones 10 Paris • Any information that can be derived from the original relation can also be derived from the S3 Blake 10 Paris relations that result from the decomposition and S4 Clark 20 London vice versa. S5 Adams 30 Athens • In other words, you get the same answers to your queries. 3 4 Suppose we decompose NEWS into: R1 R2 NEWS (S#, SNAME, STATUS, CITY) S# Sname City Status CITY → → STATUS → → S1 Smith London 20 S# → → SNAME, STATUS, CITY → → S2 Jones Paris 10 R1(S#, Sname) S3 Blake Athens 30 R2(City, Status) So no attributes are lost. What is the status or city of Smith? 5 6

NEWS (S#, SNAME, STATUS, CITY) CITY → → → STATUS → Would it be good enough if the 2 S# → → → → SNAME, STATUS, CITY relations were to have at least one R1(S#, Sname, Status) attribute in common? R2(City, Status) So no attributes are lost and the R1 and R2 have an attribute in common. ?????? Do you see any problems ??????? 7 8 R1 R2 R1 R2 S# Sname Status City Status S# Sname Status City Status S1 Smith 20 London 20 S1 Smith 20 London 20 S2 Jones 10 Paris 10 S2 Jones 10 Paris 10 S3 Blake 30 Athens 30 S3 Blake 30 Athens 30 Rome 20 9 10 So still not good enough. R1 R2 R R1 S# Sname Status City Status R2 S1 Smith 20 London 20 • Do not loose any attributes. S2 Jones 10 Paris 10 • Make sure R1 and R2 have some S3 Blake 30 Athens 30 attribute(s) in common. Rome 20 • Some extra condition on the shared What is the city of Smith? London or Rome? attribute(s) to ensure losslessness. 11 12

Losslessness Natural Join – very briefly Definition: Lossless decomposition Teaches Class A decomposition of a relation R into relations Lecturer Course Course class R1, ..., Rn is lossless (nonloss) if and only if fs logic logic msc for every instance of R and the Ri the natural jm ai ai msc join of R1, ..., Rn gives the relation R. sd C++ ai meng2 13 14 Teaches JOIN Class Teaches Class Lecturer Course Class Lecturer Course Course Class fs logic msc fs logic logic msc jm ai msc jm ai ai msc jm ai meng2 sd C++ ai meng2 15 16 Another Example of JOIN A Lossy Decomposition Teaches Class-number R Lecturer Course Number Class fs logic 60 msc A B C jm ai 100 meng2 a1 b1 c1 Teaches JOIN Class-number a2 b1 c2 Lecturer Course Number Class a2 b2 c2 fs logic 60 msc fs logic 100 meng2 jm ai 60 msc jm ai 100 meng2 17 18

Suppose we decomposed R into R1 and R2. Now consider the JOIN of R1 and R2. R1 R2 R1 R2 A B B C A B B C a1 b1 b1 c1 a1 b1 b1 c1 a2 b1 b1 c2 a2 b1 b1 c2 a2 b2 b2 c2 a2 b2 b2 c2 19 20 Theorem: sufficient condition R1 JOIN R2 versus R for losslessness Suppose R is a relation scheme and F is a set of R1 JOIN R2 R functional dependencies on R. Let R1 and R2 be A B C A B C projections of R such that the union of the sets of a1 b1 c1 a1 b1 c1 attributes of R1 and R2 is equal to the set of attributes of R. a1 b1 c2 a2 b1 c2 a2 b1 c1 a2 b2 c2 This decomposition of R is lossless if at least one of a2 b1 c2 the following fds is in F+: • R1 ∩ ∩ ∩ ∩ R2 → → → R1 → a2 b2 c2 • R1 ∩ ∩ ∩ ∩ R2 → → R2 → → 21 22 Example: Lecturer DB Example cntd. Lecturer Course Number Class What normal form is Lecturer DB in? fs logic 60 msc Lecturer(Lecturer,Course,Number,Class) fs logic 100 meng2 Decompose to: jm ai 60 msc Degree(Class,Number) jm ai 100 meng2 Teaches(Lecturer,Course,Class) Class → Number Degree ∩ ∩ ∩ ∩ Teaches = Class Only key: (Lecturer,Course,Class) Class → → Degree → → 23 24

So far we have done: Dependency Preservation � How do we make sure the decompositions are lossless (equivalence preserving)? It is often possible to decompose a relation in different ways. Amongst the lossless decompositions some may be better than • Are some decompositions better than others. others? • What if there are multiple candidate keys? 25 26 Here is an instance of relation NEWS . S# SNAME STATUS CITY Example S1 Smith 20 London S2 Jones 10 Paris NEWS (S #, SNAME, STATUS, CITY) S3 Blake 10 Paris S4 Clark 20 London S# → → SNAME, STATUS, CITY → → S5 Adams 30 Athens CITY → → → → STATUS NEWS is in 2NF, but not in 3NF. It can be transformed to 3NF by two alternative decompositions. 27 28 Instance of A A: Supplier (S#, SNAME, CITY) Supplier City-info City-info (CITY, STATUS) S# SNAME CITY CITY STATUS S1 Smith London London 20 B: Supplier (S#, SNAME, CITY) S2 Jones Paris Paris 10 Status-info (S#, STATUS) S3 Blake Paris Athens 30 S4 Clark London Rome 50 Both decompositions are lossless. All resulting S5 Adams Athens relations are in 3NF. 29 30

Instance of B Which one is better? A or B? Supplier Status-info Example: S# SNAME CITY S# STATUS Consider the update S1 Smith London S1 20 “Change CITY of supplier S1 from London to S2 Jones Paris S2 10 Paris.” S3 Blake Paris S3 10 S4 Clark London S4 20 What does this involve in A? S5 Adams Athens S5 30 What does this involve in B? 31 32 In A In A : All we have to do is change the relevant value in In Supplier : relation Supplier . Change <S1, Smith, London> to In B: <S1, Smith, Paris> . We have to update both relations Supplier and Status-info to ensure that the functional dependency CITY → STATUS is maintained. 33 34 In B • In Supplier : Supplier City-info S# SNAME CITY CITY STATUS Change <S1, Smith, London> to S1 Smith London London 20 <S1, Smith, Paris> . • In Supplier find a row with City=Paris, S2 Jones Paris Paris 10 and read its S#. S3 Blake Paris Athens 30 • In Status-info find the Status of that S#. S4 Clark London Rome 50 • In Status-info change the value of the S5 Adams Athens Status of S1 to this new Status. 35 36

Another problem with B is that: Supplier Status-info (Assuming the Entity Integrity Rule) S# SNAME CITY S# STATUS We cannot insert in B the information that a S1 Smith London S1 20 given city has a given status, unless some S2 Jones Paris S2 10 supplier is located in that city. S3 Blake Paris S3 10 S4 Clark London S4 20 S5 Adams Athens S5 30 37 38 In A In A, it is the transitive dependency A: Supplier (S#, SNAME, CITY) S # → → STATUS → → City-info (CITY, STATUS) which is an inter-relational constraint. This constraint S# → → SNAME → → is maintained automatically as long as the S# → → CITY constraints → → S # → → CITY → → S# → → → → STATUS CITY → → STATUS → → CITY → → STATUS → → are maintained in each relation, and these are just primary key constraints in each relation of A. 39 40 Formalisation/Generalisation of This In B Intuition B: Supplier (S#, SNAME, CITY) R (F) Status-info (S#, STATUS) S# → → → SNAME → S# → → CITY → → S# → → → → STATUS CITY → → STATUS → → R1 R2 …… …. Rn The problem with B is that the dependency F1 F2 …… …. Fn CITY → → → → STATUS has become an inter-relational constraint. 41 42

Definition: Dependency-Preserving Let F´ = F1 ∪ F2 ∪ ….. ∪ Fn A decomposition R1, ..., Rn of R is dependency- preserving if and only if F + = F´ + , where F and F´ are defined as above. In general F ≠ F´. Example: In the NEWS example A is dependency But if F + = F´ + , then to check F we only need to preserving, but B is not. check F´. 43 44 Example: In A F supplier = {S# → → → SNAME, CITY} → NEWS (S #, SNAME, STATUS, CITY) S# → → → → SNAME, STATUS, CITY F city-info = { CITY → → → STATUS} → CITY → → → STATUS → So F’ = F supplier ∪ ∪ F city-info . ∪ ∪ A: Supplier (S#, SNAME, CITY) City-info (CITY, STATUS) So clearly F + =F’ + . B: Supplier (S#, SNAME, CITY) Status-info (S#, STATUS) 45 46 In B So far we have done: F supplier = {S# → → → → SNAME, CITY} � How do we make sure the decompositions are lossless (equivalence preserving)? F Status-info = { S# → → STATUS} → → So F’ = F supplier ∪ ∪ ∪ ∪ F Status-info . � Are some decompositions better than others? CITY → → → → STATUS is in F + but not in F’ + . So F + ≠ ≠ ≠ F’ + . ≠ • What if there are multiple candidate keys? 47 48