Page 148 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 8: Normalization

Suppose that in Physics the advisor Satish is replaced by Rajesh. This change has to be made in Notes
only one row in the Advisor table (solving the update anomaly).
Suppose to insert a row with the information that Rajeev advises in Computer Science. This can
be done conveniently by adding a row to the effect in Advisor table without necessarily
associating it to a student, as was the case in the Student_Advisor table (resolving the insert
anomaly).
If student number 789 withdraws from school, hence, delete the corresponding row from the
Student relation without losing the information that Praveen advises in English. This may recall
that this was the case in the older Student_Advisor relation, (resolving the delete anomaly).

8.6 Fourth Normal Form

The Fourth Normal form applies to tables with a special characteristic that does not arise often.
This characteristic is called multi-valued dependency. A Multivalued dependency occurs when
in a relational table containing at least three columns, one column has multiple rows whose
values match a value of a single row of one of the other columns.
A multi valued dependency exists if, for each value of an attribute A, there exists a finite set of
values of attribute B that are associated with A and a finite set of values of attribute C that are
also associated with A. Attributes B and C are independent of each other.

4NF – Addressing Multi-valued Dependencies

BranchStaffClient
BranchNumber StaffName ClientName ClientRequirement
B-41 Radha Surya A
B-41 Radha Ravi B
B-41 Smitha Surya B
B-41 Smitha Ravi A

Consider a table called Branch_Staff_Client, which depicts the various clients for a bank branch,
the various staff who address the client’s needs and each client’s various requirements.

It is seen that each client’s name gives rise to a list of staff names. In other words, the multiple
staff names are dependent on a single client name. Similarly, each client’s multiple requirements
are dependent on a single clients name.
Therefore, Client name determines Staff name and Client name determines client requirements.
But staff name and client requirements are independent of each other. This gives rise to the
phenomenon of multi-valued dependencies that can be symbolically represented as:
Clientname StaffName
Clientname ClientRequirements

The Fourth Normal form states that:
The table should be in Boyce-Codd normal form and there should be no multi-valued
dependencies.

The rule of 4NF states that the presence of multi-valued dependencies in the table should be
eliminated. If a table has multi-valued dependencies, decompose the table and move the related

LOVELY PROFESSIONAL UNIVERSITY 141