Fourth Normal Form in Relational Database (May 2012)
Amandeep Kaur, University of Auckland, New Zealand
Abstract—This report is being written to understand the concept of Fourth Normal form in context of relational database. Multivalued dependency and the rules to achieve fourth normal form are explained. The anomalies associated with the fourth normal form are discussed and the consequences of violating 4NF is explained.
HE most important issue in the design of a database is to determine the fields that should be grouped together into the records. In the relational database, fields are grouped into logical structures known as relations. Normal forms basically determines which data fields should be placed together in a relation, this process is known as Normalization which is done to minimize redundancy and dependency. The set of data fields comprising the database is organized into relations in first through fifth normal form (5NF) according to constraints placed upon the relations in each normal form. At any step in the normalization process, we may find that the relations no longer require further reorganization; in that case, we say that final normal form has been achieved. Before we move forward to understand fourth normal form, we need to understand few concepts like Multivalued dependencies (MVD), first normal form (1NF), second Normal form (2NF), third Normal form (3NF), and Boyce Codd Normal form (BCNF). All these forms are discussed briefly in the next section.
NORMAL FORMS AND MULTIVALUED DEPENDENCY
1 First Normal Form
A relational database is said to be in 1NF if each attribute of the relation is atomic i.e. each column must contain only single value. Basic rules for 1NF are as follows: --First, eliminate duplicate column from the same table. --Second, create separate table for a group of related data and distinguish each row with a unique column or set of columns.
2 Second Normal Form
A relational database is in 2NF if it is in 1NF and each attribute that is not a primary key is fully functionally dependent on the entity's primary key; that is, non-primary key attributes are dependent on the entity's entire primary key. When a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF.
3 Third Normal Form
A relational database is in 3NF if it is in 2NF and no non-primary key attribute is functionally dependent on another non-primary key. A non-prime attribute of R is an attribute that does not belong to any candidate key of R. A transitive dependency is a functional dependency in which X → Z (X determines Z) indirectly, by virtue of X → Y and Y → Z (where it is not the case that Y → X).
4 Multivalued Dependency
The multi-valued dependency is said to hold for a relation R(X, Y, Z) if for a given set of value (set of values if X is more than one attribute) for attributes X, there is a set of (zero or more) associated values for the set of attributes Y and the Y values depend only on X values and have no dependence on the set of attributes Z. In other words, the multi-valued dependency relates to the problem when more than one multi-valued attributes exist. MVD is represented by a double arrow --->>. Example of a multi-valued dependency is explained in TABLE I. Because the lecturers attached to the course and the books attached to the course are independent of each other, this database design has a multi-valued dependency; if we were to add a new book to the Database Systems course, we would have to add one record for each of the lecturers on that course, and vice versa. Put formally, there are two multi-valued dependencies in this relation: course—»book and equivalently course—» lecturer. Databases with multivalued dependencies thus exhibit redundancy.
FOURTH NORMAL FORM
A relationship is in Fourth Normal Form (4NF) when it meets...