1 / 21

Multivalued Dependency

Multivalued Dependency. Tamer Abuelata. Introduction. Goal in Databases: BCNF (Boyce Codd Normal Form) Losslessness Dependency preservation. Remember…. Boyce Codd Normal Form (BCNF) eliminates all redundancy that can be discovered based on functional dependencies. Issue.

omer
Télécharger la présentation

Multivalued Dependency

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related