Understanding Multivalued Dependencies and Fourth Normal Form in Databases, Slides of Database Management Systems (DBMS)

Download Understanding Multivalued Dependencies and Fourth Normal Form in Databases and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Multivalued Dependency

Introduction

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

Issue

• Some relation schemas, even though they are

in BCNF, do not seem to be sufficiently

normalized.

• They still contain repetitions

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

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)

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.

4NF

We can use multivalued dependencies to

define the fourth normal form

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

Example

• R relation schema, A and B follow the

multivalued dependency :

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

Example

• Let’s 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

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.

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

Example

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

 Updated table (legal)

Example

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

 Updated table (legal)

 We want Cust_id -->-> cust_street cust_city to hold