Fourth Normal Form and Multi-Value Dependencies, Slides of Database Management Systems (DBMS)

Download Fourth Normal Form and Multi-Value Dependencies and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

4 TH^ NORMAL FORM

REVIEW OF NFs

• 1NF  All values of the columns are atomic.

That is, they contain no repeating values.

• 2NF  it is in 1NF and every non-key column

is fully dependent upon the primary key.

4th Normal Form

A Boyce Codd normal form relation is in fourth normal form if

(a) there is no multi value dependency in the relation or

(b) there are multi value dependency but the attributes, which are multi value dependent on a specific attribute, are dependent between themselves.

4 th^ Normal Form Cont…

This is best discussed through mathematical notation.

Assume the following relation

R(a:pk1, b:pk2, c:pk3)

Recall that a relation is in BCNF if all its determinant are candidate keys, in other words each determinant can be used as a primary key. Because relation R has only one determinant (a, b, c) , which is the composite primary key and since the primary is a candidate key therefore R is in BCNF.

4th Normal Form Cont…

Many-to-many relationships

Fourth Normal Form applies to situations

involving many-to-many relationships.

In relational databases, many-to-many

relationships are expressed through cross-

reference tables.

Note about FDs and MVDs

• Every Functional Dependency is a MVD

(if A 1 A 2 …A n → B 1 B 2 …B n , then A 1 A 2 …A n ⇒ B 1 B 2 …B n )

• FDs rule out certain tuples (i.e. if A → B then two tuples will not have the same value for A and different values for B)
• MVDs do not rule out tuples. They guarantee that certain tuples must exist.

Example 1

Consider a case of class enrollment. Each student

can be enrolled in one or more classes and each class can contain one or more students.

Clearly, there is a many-to-many relationship

between classes and students. This relationship can be represented by a Student/Class cross- reference table:

{StudentID, ClassID}

Example 1 Cont…

• The key for this table is the combination of StudentID and ClassID. To avoid violation of 2NF, all other information about each student and each class is stored in separate Student and Class tables, respectively.
• Note that each StudentID determines not a unique ClassID, but a well-defined, finite set of values. This kind of behavior is referred to as multi-valued dependency of ClassID on StudentID.

Example 2 Cont…

• However, the business rules do not constrain this relationship in any way—the combination of StudentID and TeacherID does not contain any additional information beyond the information implied by the student/class and class/teacher relationships.
• Consequentially, the student/class and class/teacher relationships are independent of each other—these relationships have no additional constraints. The following table is, then, in violation of 4NF:

{StudentID, ClassID, TeacherID}

4 th^ NF and Anomalies

• As an example of the anomalies that can occur,

realize that it is not possible to add a new class taught by some teacher without adding at least one student who is enrolled in this class.

• To achieve 4NF, represent each independent many-

to-many relationship through its own cross-reference table.

case 2: Assume the following relation with multi-value dependency:

Employee (Eid:pk1, Languages:pk2, Skills:pk3) Eid --->> Languages Eid --->> Skills

Languages and Skills are dependent. This says an employee speak several languages and has several skills. However for each skill a specific language is used when that skill is practiced.

4th Normal Form and anomalies

Cont…

Thus employee 100 when he/she teaches speaks English but when he cooks speaks French. This relation is in fourth normal form and does not suffer from any anomalies.

Eid Language Skill

100 English Teaching

100 Kurdish Politic

100 French Cooking

200 English Cooking

200 Arabic Singing

4th Normal Form and anomalies

Cont…

Eid Language Skill

100 English Teaching

100 Kurdish Politic

100 English Politic

100 Kurdish Teaching

200 Arabic Singing

This relation is not in fourth normal form and suffers from all three types of anomalies.

Insertion anomaly: To insert row (200 English Cooking) we have to insert two extra rows (200 Arabic cooking), and ( English Singing) otherwise the database will be inconsistent. Note the table will be as follow:

Eid Language Skill 100 English Teaching 100 Kurdish Politics 100 English Politics 100 Kurdish Teaching 200 Arabic Singing 200 English Cooking 200 Arabic Cooking 200 English Singing Docsity.com