Higher Normal Forms: Eliminating Data Redundancies and Multi-valued Dependencies, Slides of Database Management Systems (DBMS)

Download Higher Normal Forms: Eliminating Data Redundancies and Multi-valued Dependencies and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Higher Normal Forms

Preface

• Background
• Why and What of Normalization
• Quick Overview of Lower Normal Forms
• Higher Order Normal Forms

Normalization

• Purpose: Remove points of inconsistencies and data redundancies, which are cause by modification anomalies.
• 1NF, 2NF, 3NF & BCNF focus on eliminating data redundancies based on undesirable functional dependencies.
• Higher Normal Forms deal with data redundancies that occur for other reasons.

1NF < 2NF < 3NF < BCNF

• 1NF - Legal atomic values only
• 2NF - if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key. (A non-prime attribute is an attribute that does not occur in any candidate key.)
• 3NF - All non key attributes are dependent upon the primary key.
• BCNF - If every determinant is a candidate key.
  • Determinant: an attribute on which some other attribute is fully functionally dependent

4 th^ Order Normal Form

• Fourth Normal Form(4NF)
  • Eliminates data redundancy caused by Multi-valued dependencies. (MVD)
  • A given relations in 4NF may not contain more than one multi- valued dependency.

4 th^ Normal Form (cont’)

• MVD?

Multi-value Dependencies (XY) hold in a relation R if when ever we have two tuples of R that agree on all the attributes of X, then we can swap their Y components and get two tuples that are also in R.

4 th^ Normal Form(cont’)

• What is so bad about having a table with multiple multi-valued dependencies?
• Example:  Consider R(Departments, Jobs , Resources Used) The table has the following MVDs  department  Parts  department  Jobs

Department ------------------------------------------------- Job Part#

•   DepartmentDepartment d1d2 works on jobsworks on jobs j1j3, j4 , and, and j2 with parts j5 with parts p1 and p2 and p2 p
•  Department d3 works on job j2 only with parts p5 and p
  • d1 j1 p
  • d1 j2 p d1 j1 p2 Department  Job
  • d1 j2 p
  • d2 j3 p d2 j3 p2 Department  Part
  • d2 j4 p
  • d2 j4 p
  • d2 j5 p
  • d2 j5 p
  • d3 j2 p
  • d3 j2 p

5 th^ Normal Form

A relation R is in 5NF if for all join dependencies at least one of the following holds.

(a) (R1, R2, ..., Rn) is a trivial join-dependency. (b) Every Ri is a candidate key for R.

5 th^ Normal Form

• A table is said to be in the 5NF iff it is in 4NF and every join dependency in it is implied by the candidate keys. - Sometimes its impossible to break the table into 2 tables, that is when you can use the rules of 5NF to normalize. - Generally a table in 4th^ NF is always in 5th^ NF, but sometimes real world constraint will cause the Relation to be not comply with 5th^ NF.

5 th^ Normal Form(cont’)

The psychiatrist is able to offer reimbursable treatment to patients who suffer from the given condition and who are insured by the given insurer. Psychiatrist-to- Insurer-to-Condition is necessary in order to model the situation correctly.

5 th^ Normal Form(cont’)

• Suppose, however, that the following rule applies: When a psychiatrist is authorized to offer reimbursable treatment to patients insured by Insurer P, and the psychiatrist is able to treat condition C, then – in the event that the Insurer P covers condition C – it must be true that the psychiatrist is able to provide treatment to patients who suffer from condition C and are insured by Insurer P.

5 th^ (cont’)

• Only in rare situations does a 4NF table not conform to 5NF. These are situations in which a complex real-world constraint governing the valid combinations of attribute values in the 4NF table is not implicit in the structure of that table. If s

DKNF

• DKNF offers a complete solution to the problem of avoiding modification abnormalities
• Domain/key normal form (DKNF). A key uniquely identifies each row in a table.
• By enforcing key and domain restrictions, the database is assured of being freed from any modification inconsistency.