Multivalued Dependency

1. Multivalued Dependency Tamer Abuelata

2. Introduction • Goal in Databases: • BCNF (Boyce Codd Normal Form) • Losslessness • Dependency preservation

3. Remember… Boyce Codd Normal Form (BCNF) eliminates all redundancy that can be discovered based on functional dependencies.

4. Issue • Some relation schemas, even though they are in BCNF, do not seem to be sufficiently normalized. • They still contain repetitions

5. Case study Consider the bank database schema: cust_loan = (loan_number, cust_id, cust_name, cust_street, cust_city) This is BCNF because of the functional dependency: cust_id -> cust_name, cust_street cust_city And because cust_id is not a key for cust_loan

6. Case Study • But what if some customers have several addresses? We no longer wish to enforce the func. dependency: cust_id ->cust_street cust_city But we still want to enforce cust_id -> cust_name

7. Case Study • Following BCNF decomposition algorithm we get: R1 = (cust_id, cust_name) R2 = (loan_number, cust_id, cust_street, cust_city) (both in BCNF)

8. Case Study The issue Despite R2 in BCNF, there is redundancy. We repeat the address of each residence for each loan that the customer has.

9. Case Study We can therefore decompose further into: loan_cust_id = (loan_number, cust_id) cust_residence = (cust_id, cust_street, cust_city) But there is no constraint that lead us to do that. To deal with this, we need a few form of constraint: 4NF.

10. 4NF We can use multivalued dependencies to define the fourth normal form

11. 4NF A relation schema R is in fourth normal form with respect to a set D of functional and multivalued dependencies if, for all multivlued dependencies in D+ of the form A -->-> B at least one of the following holds: • A -->-> B is a trivial multivalued dependency • A is a superkey for schema R

12. Multivalued Dependency • Requires that other tuples of a certain form be present in the relation. • Also referred to as: tuple-generating dependency

13. Example • R relation schema, A and B follow the multivalueddependency: A -->-> B • The relationship between A and B is independent of the relation between A and R – B • If A -->-> B is satisfied by all relations on R then A -->-> B is a trivial multivalued dependency

14. Example • Let’s reconsider R2 = (loan_number, cust_id, cust_street, cust_city)

15. Example We must repeat the loan number once for each address a customer has and we must repeat the address for each loan a customer has.

16. Example We must repeat the loan number once for each address a customer has and we must repeat the address for each loan a customer has. This repetition is unnecessary since the relationship between a customer and his address is independent of the relationship between that customer and a loan.

17. Example • Therefore this relation is illegal

18. Therefore this relation is illegal Example • To make it legal we should add tuples (L23, 99-123, Main, Manchester) and (L27, 99-123, North, Rye)

19. Example • Updated table (legal)

20. Example • Updated table (legal) • We want Cust_id -->-> cust_street cust_city to hold

21. Conclusion We can use multivalued dependencies • To test relations to determine whether they are legal under a given set of functional and multivalued dependencies • To specify constraints on the set of legal relations