Contents Chapter 7 Objectives Functional Dependencies (review) - PDF document

Summary of Chapter 6 ■ Domain Constraints ■ Referencial Integrity CMPT 354 Database Systems and Structures ■ Assertions ■ Triggers Osmar R. Zaïane ■ Functional Dependencies Summer 1998 CMPT354 - Ch7 - summer98 CMPT354 - Ch7 - summer98 Contents Chapter 7 Objectives ■ Functional Dependencies (review) ■ Pitfalls in Relational Database Design ■ Decomposition Database Design ■ Normalization ■ Normalization using Functional Dependencies ■ Normalization using Multivalued Dependencies Understand problems associated with redundant information; Learn the purpose of normalization. CMPT354 - Ch7 - summer98 CMPT354 - Ch7 - summer98 Functional Dependencies Closure of a set of Functional Dependencies Given a set F of functional dependencies, there are certain other Functional dependencies play an important role in database design. functional dependencies that are logically implied by F. They describe relationships between attributes. The set of all functional dependencies logically implied by F is the closure of F denoted by F + . They require that the value for a certain set of attributes determines uniquely the value for another set of attributes. F + can be found by applying Armstrong’s Axioms: α ⊆ R β ⊆ R β ⊆ α α → β Let and reflexifity if , then α → β γα → γβ augmentation if , then α → β holds on R if α → γ α → β β → γ transitivity if and , then ∈ ∀ t1, t2 r where r(R), if t 1 [ α ] = t 2 [ α ] then t 1 [ β ]=t 2 [ β ] these rules are sound and complete. CMPT354 - Ch7 - summer98 CMPT354 - Ch7 - summer98 1

Closure of a set of Functional Dependencies Closure of Attribute Sets We can further simplify the computation of F + by using additional The closure of an attribute set α under F (denoted by α + ) is the set of rules: attributes that are functionally determined by α under F. is in F + ⇔ α → β β ⊆ α + α → β α → γ if holds and ,then holds union α → βγ α → β α → γ if holds, then and hold decomposition Algorithm to compute α + : α → β γβ → δ αγ → δ pseudotransitivity if and holds, then holds result := α ; these rules can be inferred from Armstrong’s axioms. while (changes to result ) do β → γ for each in F do begin ∪ γ β ⊆ if result then result := result ; end CMPT354 - Ch7 - summer98 CMPT354 - Ch7 - summer98 Canonical Cover Database Design ■ Pitfalls in Relational Database Design To compute a canonical cover for F: ● Repetition of information ● Inability to represent certain information repeat ● Loss of information use the union rule to replace any dependencies in F α → β α → β α → β β and with 1 1 1 2 1 1 2 ■ To find desirable collection of relation schemas we should follow these find a functional dependency with an α → β goals: extraneous attribute either in α or in β ● avoid redundant data α → β if an extraneous attribute is found, delete it from ● represent relationships among attributes until F does not change ● facilitate the checking of updates for violation of database integrity constraints CMPT354 - Ch7 - summer98 CMPT354 - Ch7 - summer98 Ship(S#, sname, status, city, P#, pname, colour, weight, qty, date) Consider a database containing shipments of parts from suppliers. Problems?? Ship(S#, sname, status, city, P#, pname, colour, weight, qty, date) INSERT We can not enter a new suplier in the DB until the supplier ships a part. S# sname status city P# pname colour weight qty date We can not enter a new part until it is shiped by one supplier. S1 Smith 20 London P1 Nut Red 12 200 980620 S1 Smith 20 London P1 Nut Red 12 700 980625 DELETE S2 Jones 10 Paris P3 Screw Blue 17 400 980620 If we delete the only tuple of a particular supplier, we destroy not only S2 Jones 10 Paris P5 Bolt Green 17 300 980620 the shipment information but also the information that the supplier is S2 Jones 10 Paris P2 Screw Red 14 200 980621 S3 Clark 20 Rome P1 Nut Red 12 300 980612 from a particular city. (example S5 and P1) S3 Clark 20 Rome P6 Cog Red 19 600 980612 S4 Blake 30 Athens P4 Cam Blue 12 200 980619 UDATE S4 Blake 30 Athens P1 Nut Red 12 300 980619 The city of a supplier may appear many times in Ship. If we change the S4 Blake 30 Athens P3 Screw Blue 17 100 980620 value of the city in one tuple but not all the other, it creates S5 Alex 10 Paris P1 Nut Red 12 250 980626 inconsistency in the database. CMPT354 - Ch7 - summer98 CMPT354 - Ch7 - summer98 2

Supplier(S#, sname, status, city) The solution is to decompose Ship into three relations Shipment, city status → Parts(P#, pname, colour, weight) Supplier, and Parts Supplier(S#, sname, status, city) Shipment(S#, P#, qty, date) Problems?? Parts(P#, pname, colour, weight) INSERT Shipment(S#, P#, qty, date) We can not enter the fact that a particular city has a particular status S# sname status city until we have a supplier from that city S1 Smith 20 London S# P# qty date S2 Jones 10 Paris DELETE S3 Clark 20 Rome S1 P1 200 980620 If we delete the only tuple with a particuar city, we destroy not only S4 Blake 30 Athens S1 P1 700 980625 the upplier information but also the information that that city has that S5 Alex 10 Paris S2 P3 400 980620 particular status. S2 P5 300 980620 P# pname colour weight S2 P2 200 980621 P1 Nut Red 12 UDATE S3 P1 300 980612 P2 Screw Red 14 S3 P6 600 980612 The city of a supplier may appear many times in Supplier (ther relation P3 Screw Blue 17 S4 P4 200 980619 still contains redundancy). If we change the status for Paris, we have to P4 Cam Blue 12 S4 P1 300 980619 P5 Bolt Green 17 change it for all tuples his Paris as the city or we face inconsistency. S4 P3 100 980620 P6 Cog Red 19 S5 P1 250 980626 CMPT354 - Ch7 - summer98 CMPT354 - Ch7 - summer98 The solution is to decompose Supplier into 2 relations Supplier and Relation Decomposition Status Supplier(S#, sname, city) Status(city, status) Parts(P#, pname, colour, weight) Shipment(S#, P#, qty, date) In order to solve the previous problems we dcomposed the S# sname city city status relations into smaller relations. S1 Smith London London 20 S# P# qty date S2 Jones Paris Paris 10 Relation decomposition can be dangerous we we can loose S3 Clark Rome S1 P1 200 980620 Rome 20 S4 Blake Athens information. S1 P1 700 980625 Athens 30 S5 Alex Paris S2 P3 400 980620 S2 P5 300 980620 Loss-less join decomposition P# pname colour weight S2 P2 200 980621 P1 Nut Red 12 S3 P1 300 980612 If we decompose a relation R into smaller relations, the join of P2 Screw Red 14 S3 P6 600 980612 P3 Screw Blue 17 those relations should return R, not more, not less. S4 P4 200 980619 P4 Cam Blue 12 S4 P1 300 980619 P5 Bolt Green 17 S4 P3 100 980620 P6 Cog Red 19 S5 P1 250 980626 CMPT354 - Ch7 - summer98 CMPT354 - Ch7 - summer98 Normalization First Normal Form Normilization is a technique for producing a set of relations An unnormalized form : a table that contains one or more with desirable properties, given the data requirments of an repeating groups enterprise. First Normal Form: a relation in which the intersection of each row and column contains one and only one value. 5NF 4NF BCNF 3NF 2NF 1NF CMPT354 - Ch7 - summer98 CMPT354 - Ch7 - summer98 3