Lecture 7 Topics for today: --------------------- 4th Normal Form ER Diagrams Fourth Normal Form ------------------------------------ Students(RIN, Name, Email, Hobby, PhoneNumber) RIN-> Name Email x not in BCNF Key: RIN, Hobby, PhoneNumber Student can have multiple hobbies Student can have multiple phones BCNF Decomposition: Students(RIN, Name, Email) {RIN-> Name, Email}, Key: RIN, in BCNF StudentInfo(RIN, Hobby, PhoneNumber) {}, Key: all attributes, in BCNF RIN Hobby PhoneNumber 1 Running 1234 1 Running 4567 1 Baking 1234 1 Baking 4567 2 Joomchi 3456 2 Joomchi 0954 2 Lampworking 0954 2 Lampworking 3456 Multi-valued attributes RIN ->> Hobby RIN ->> PhoneNumber (RIN, Hobby) (RIN, PhoneNumber) A multi-valued dependency of the form A1 ... An ->> B1 ... Bm means that for all pairs of tuples t1 and t2 that agree on A, we can find a tuple v in R such that v agrees with t1 and t2 on A's v agrees with t1 on B’s v agrees with t2 on the remaining attributes (not A's or B's) hero weaponsused hobby tuple lara ice axe relic collecting t1 lara compound bow motorcycling t2 lara ice axe motorcycling lara compound bow relic collecting v Inference rules Trivial MVDs ------------ A1 … An =>> B1 … Bm is true if {B1, …,Bm } ⊆ {A1,…,An} If all the attributes of relation R are A1 … An, B1 … Bm, then A1 … An =>> B1 … Bm holds in R. Transitive rule --------------- A1 … An =>> B1 … Bm and B1 … Bm =>> C1 … Ck implies A1 … An =>> C1 … Ck (where A, B, C are sets of attributes). Any C's that are also A's must be deleted from the right side. Note that the splitting part of the splitting/combining rule does not apply to MVDs. Combining rule -------------- If A1 … An =>> B1 … Bm and A1 … An =>> =>> C1 … Ck then A1 … An =>> B1 … Bm C1 … Ck Augmentation rule ----------------- If A1 … An =>> B1 … Bm then If A1 … An C1 … Ck =>> B1 … Bm FD promotion ------------ Every FD is also an MVD. If A1 … An -> B1 … Bm then A1 … An =>> B1 … Bm is also true. Complementation rule -------------------- If A1 … An =>> B1 … Bm is true and C1 … Ck are all the attributes in R that are not As or Bs then A1 … An =>> C1 … Ck is also true. Fourth Normal Form ------------------ A relation is in fourth normal form iff whenever A1 … An =>> B1 … Bm is a non-trivial MVD, then A1 … An is a superkey. The notions of keys and superkeys depend on f.d.s only; adding MVDs does not change the definition of "key". To decompose a relation into fourth normal form, use an algorithm similar to BCNF decomposition algorithm using MVDs. Relations in 4NF ⊆ Relations in BCNF ⊆ Relations in 3NF. 4NF decomposition ----------------- Given a relation R where A1 … An =>> B1 … Bm violates the 4NF, decompose R into: R1(A1,…,An,B1,..,Bm) R2 contains all attributes except for B1,…,Bm. If the resulting relations are not in 4NF, then continue decomposing until they are. Discovering MVDs ---------------- Similar to chase algorithm for regular f.d.s. Relation R and a set of f.d.s F. Checking if X->Y is a regular f.d. 1. Construct a canonical relation with two tuples that agree on X. 2. Run the chase algorithm on the canonical relation using F. 3. If in the end two tuples agree on all attributes of Y, then X->Y holds; otherwise it does not. R(A, B, C, D, E, F) F = {AB->C, BC->AD, D->E, CF->B} Does AB->D hold? A B C D E F a b c1 d1 e1 f1 a b c2 d2 e2 f2 AB->C: A B C D E F a b c1 d1 e1 f1 a b c1 d2 e2 f2 BC->AD: A B C D E F a b c1 d1 e1 f1 a b c1 d1 e2 f2 D->E: A B C D E F a b c1 d1 e1 f1 a b c1 d1 e1 f2 Two tuples agree on the D attribute, so AB->D holds Extension for MVDs: ------------------- R(A,B,C,D) F = {A->B} MVD: B->>C Check if A->>C holds: A B C D a b1 c d1 a b c2 d Target tuple: (a, b, c, d) Apply A->B: A B C D a b c d1 a b c2 d Apply MVD B->>C: A B C D a b c d1 a b c2 d a b c2 d1 a b c d A->>C holds! ------------- Entity-Relationship Diagrams (ER Diagrams) ------------------------------------------- Data Modeling: both a science and an art - ER diagrams are not standard - ER diagrams are object oriented - ER diagrams are visual - ER diagrams are highly iterative ---------------- Entities: are classes of objects that we are storing information Each entity must have a key Each entity must be in BCNF, all attributes of an entity are implied by the key Entities with a rectangle Attributes with an ellipse (attached to the entity) Attributes in a key are underlined ---> Only attach attributes that are about that entity, do not put attributes that reference another entity ---> Attributes that are multivalued are not put in as an attribute -------------- Relationships: define how entities are linked to each other Represented with a diamond: connects the entities that it relates to Read as a sentence: Students have majors Students are advised by faculty Faculty/Staff work in departments Many relationships are binary, but ternary or higher are possible Multiple different relationships may exist between different entities Recursive relationships are between the same entity Is it an entity or an attribute? -- If more than one entity will refer to it with a relationship, then use an entity Participation constraints: Min - Does it have to have any? (Should a faculty be working in a specific department? Yes, 1) Should a faculty be affiliated with at least one department? No, 0 Max - How many can it have? (How many departments can a faculty work in? - 1) How many departments can a faculty be affiliated with? N Given the participation constraints (using max values only) : Define: Many to many : N on both sides Many to one : N on one side and 1 on one side One to one : 1 on both sides