Database normalization
Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by British computer scientist Edgar F. Codd as part of his relational model.
Normalization entails organizing the columns and tables of a database to ensure that their dependencies are properly enforced by database integrity constraints. It is accomplished by applying some formal rules either by a process of synthesis or decomposition.
Objectives
A basic objective of the first normal form defined by Codd in 1970 was to permit data to be queried and manipulated using a "universal data sub-language" grounded in first-order logic. An example of such a language is SQL, though it is one that Codd regarded as seriously flawed.The objectives of normalization beyond 1NF were stated by Codd as:
When an attempt is made to modify a relation, the following undesirable side effects may arise in relations that have not been sufficiently normalized:
;Insertion anomaly: There are circumstances in which certain facts cannot be recorded at all. For example, each record in a "Faculty and Their Courses" relation might contain a Faculty ID, Faculty Name, Faculty Hire Date, and Course Code. Therefore, the details of any faculty member who teaches at least one course can be recorded, but a newly hired faculty member who has not yet been assigned to teach any courses cannot be recorded, except by setting the Course Code to null.
;Update anomaly: The same information can be expressed on multiple rows; therefore updates to the relation may result in logical inconsistencies. For example, each record in an "Employees' Skills" relation might contain an Employee ID, Employee Address, and Skill; thus a change of address for a particular employee may need to be applied to multiple records. If the update is only partially successful – the employee's address is updated on some records but not others – then the relation is left in an inconsistent state. Specifically, the relation provides conflicting answers to the question of what this particular employee's address is.
;Deletion anomaly: Under certain circumstances, the deletion of data representing certain facts necessitates the deletion of data representing completely different facts. The "Faculty and Their Courses" relation described in the previous example suffers from this type of anomaly, for if a faculty member temporarily ceases to be assigned to any courses, the last of the records on which that faculty member appears must be deleted, effectively also deleting the faculty member, unless the Course Code field is set to null.
Minimize redesign when extending the database structure
A fully normalized database allows its structure to be extended to accommodate new types of data without changing existing structure too much. As a result, applications interacting with the database are minimally affected.Normalized relations, and the relationship between one normalized relation and another, mirror real-world concepts and their interrelationships.
Normal forms
Codd introduced the concept of normalization and what is now known as the first normal form in 1970. Codd went on to define the second normal form and third normal form in 1971, and Codd and Raymond F. Boyce defined the Boyce–Codd normal form in 1974.Ronald Fagin introduced the fourth normal form in 1977 and the fifth normal form in 1979. Christopher J. Date introduced the sixth normal form in 2003.
Informally, a relational database relation is often described as "normalized" if it meets third normal form. Most 3NF relations are free of insertion, updation, and deletion anomalies.
The normal forms are:
| Constraint | UNF | 1NF | 2NF | 3NF | EKNF | BCNF | 4NF | ETNF | 5NF | DKNF | 6NF |
| Unique rows | |||||||||||
| Scalar columns | |||||||||||
| Every non-prime attribute has a full functional dependency on each candidate key | |||||||||||
| Every non-trivial functional dependency either begins with a superkey or ends with a prime attribute | |||||||||||
| Every non-trivial functional dependency either begins with a superkey or ends with an elementary prime attribute | |||||||||||
| Every non-trivial functional dependency begins with a superkey | |||||||||||
| Every non-trivial multivalued dependency begins with a superkey | |||||||||||
| Every join dependency has a superkey component | |||||||||||
| Every join dependency has only superkey components | |||||||||||
| Every constraint is a consequence of domain constraints and key constraints | |||||||||||
| Every join dependency is trivial |
Example of a step-by-step normalization
Normalization is a database design technique, which is used to design a relational database table up to higher normal form. The process is progressive, and a higher level of database normalization cannot be achieved unless the previous levels have been satisfied.That means that, having data in unnormalized form and aiming to achieve the highest level of normalization, the first step would be to ensure compliance to first normal form, the second step would be to ensure second normal form is satisfied, and so forth in order mentioned above, until the data conforms to sixth normal form.
However, normal forms beyond 4NF are mainly of academic interest, as the problems they exist to solve rarely appear in practice.
''The data in the following example was intentionally designed to contradict most of the normal forms. In practice it is often possible to skip some of the normalization steps because the data is already normalized to some extent. Fixing a violation of one normal form also often fixes a violation of a higher normal form. In the example, one table has been chosen for normalization at each step, meaning that at the end, some tables might not be sufficiently normalized.''
Initial data
Let a database table exist with the following structure:| Title | Author | Author Nationality | Format | Price | Subject | Pages | Thickness | Publisher | Publisher Country | Genre ID | Genre Name | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Beginning MySQL Database Design and Optimization | Chad Russell | American | Hardcover | 49.99 | For this example it is assumed that each book has only one author. A table that conforms to the relational model has a primary key which uniquely identifies a row. In our example, the primary key is a composite key of :
|