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 -gt 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 -gtcust_street cust_city
• But we still want to enforce
• cust_id -gt 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 --gt-gt B at least
  one of the following holds
• A --gt-gt 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 multivalued
  dependency
• A --gt-gt B
• The relationship between A and B is independent
  of the relation between A and R B
• If A --gt-gt B is satisfied by all relations on R
  then
• A --gt-gt B is a trivial multivalued dependency

14
Example

• Lets reconsider
• R2 (loan_number, cust_id, cust_street,
  cust_city)

loan_number cust_id cust_street cust_city
L-23 99-123 North Rye
L-23 99-123 Main Manchester
L-93 15-106 Lake Horseneck
15
Example
loan_number cust_id cust_street cust_city
L-23 99-123 North Rye
L-23 99-123 Main Manchester
L-93 15-106 Lake Horseneck
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
loan_number cust_id cust_street cust_city
L-23 99-123 North Rye
L-23 99-123 Main Manchester
L-93 15-106 Lake Horseneck
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

loan_number cust_id cust_street cust_city
L-23 99-123 North Rye
L-27 99-123 Main Manchester
18
Example

• Therefore this relation is illegal

loan_number cust_id cust_street cust_city
L-23 99-123 North Rye
L-27 99-123 Main Manchester

• To make it legal we should add tuples
• (L23, 99-123, Main, Manchester) and
• (L27, 99-123, North, Rye)

19
Example

• Updated table (legal)

loan_number cust_id cust_street cust_city
L-23 99-123 North Rye
L-27 99-123 Main Manchester
L-23 99-123 Main Manchester
L-27 99-123 North Rye
20
Example

• Updated table (legal)

loan_number cust_id cust_street cust_city
L-23 99-123 North Rye
L-27 99-123 Main Manchester
L-23 99-123 Main Manchester
L-27 99-123 North Rye

• We want Cust_id --gt-gt 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