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!

4N and the Normalization of

Multivalue Dependency

Rules

• Fourth Normal Form
  • if R is 3N and BCNF…
  • given the “non-trivial” MVD: A 1 A 2 …A (^) n ⇒ B 1 B 2 …B (^) n {A 1 A 2 …A (^) n } is a superkey
• A MVD: A 1 A 2 …A (^) n ⇒ B 1 B 2 …B (^) n for a Relation R is “non-trivial” if:
  1. none of the Bs are among the As
  2. Not all of the attributes of R are among the As and Bs
• A MVD is “trivial” if it contains all the variations of A 1 A 2 …A (^) n x B 1 B 2 …B (^) n.

or α ß is ß ⊆ α or ß ∪ α

Multivalued Dependency in 4N

• A table with a multivalued dependency is one

where the existence of more than one

independent many-to-many relationships in a

table causes redundancy; and it is this

redundancy which is removed by fourth

normal form.

Example

Restaurant Pizza Variety Delivery Area

Elite Pizza Thin Crust Capital City

Elite Pizza Stuffed Crust Capital City

A1 Pizza Thick Crust Springfield

A1 Pizza Thick Crust Shelbyville

A1 Pizza Thick Crust Capital City

A1 Pizza Stuffed Crust Springfield

A1 Pizza Stuffed Crust Shelbyville

A1 Pizza Stuffed Crust Capital City

The Fix

Restaurant Pizza Variety Delivery Area

Elite Pizza Thin Crust Capital City

Elite Pizza Stuffed Crust Capital City

A1 Pizza Thick Crust Springfield

A1 Pizza Thick Crust Shelbyville

A1 Pizza Thick Crust Capital City

A1 Pizza Stuffed Crust Springfield

A1 Pizza Stuffed Crust Shelbyville

A1 Pizza Stuffed Crust Capital City

Restaurant Pizza Variety

Elite Pizza Thin Crust

Elite Pizza Stuffed Crust

A1 Pizza Thick Crust

A1 Pizza Stuffed Crust (^) Restaurant Delivery Area

Elite Pizza Capital City A1 Pizza Springfield A1 Pizza Shelbyville A1 Pizza Capital City

A:

Then the original three-column table would

satisfy 4NF

Course Book Lecturer

AHA Silberschatz John D

AHA Nederpelt John D

AHA Silberschatz William M

AHA Nederpelt William M

AHA Silberschatz Christian G

AHA Nederpelt Christian G

OSO Silberschatz John D

OSO Silberschatz William M Docsity.com

Conclusions

• Databases with multivalued dependencies

exhibit redundancy.

• In database normalization, fourth normal

form requires that databases have no

multivalued dependencies.

Extra

• Transitivity, Reflexivity, Complementation,

Replication, Augmentation and a few more are

all properties of 4 th^ normal form