Database normalization is a series of steps followed to obtain a database design that allows for consistent storage and efficient access of data in a relational database. These steps reduce data redundancy and the risk of data becoming inconsistent.
However, many relational DBMSs lack sufficient separation between the logical database design and the physical implementation of the data store, such that queries against a fully normalized database often perform poorly. In this case denormalization is sometimes used to improve performance, at the cost of reduced consistency guarantees.
Overview
A table in a relational database is said to be in a certain normal form if it satisfies certain constraints. Edgar F. Codd's original work defined three such forms but there are now other generally accepted normal forms. We give here a short informal overview of the most common ones. Each normal form below represents a stronger condition than the previous one (in the order below). For most practical purposes, databases are considered normalized if they adhere to third normal form.
First Normal Form (or 1NF) requires that all column values in a table are atomic (e.g., a number is an atomic value, while a list or a set is not). For example, normalization eliminates repeating groups by putting each into a separate table and connecting them with a primary key-foreign key relationship.
Second Normal Form (or 2NF) requires that there are no non-trivial functional dependencies of a non-key attribute on a part of a candidate key.
Third Normal Form (or 3NF) requires that there are no non-trivial functional dependencies of non-key attributes on something else than a superset of a candidate key.
Boyce-Codd Normal Form (or BCNF) requires that there are no non-trivial functional dependencies of attributes on something else than a superset of a candidate key. At this stage, all attributes are dependent on a key, a whole key and nothing but a key (excluding trivial dependencies, like A->A).
Fourth Normal Form (or 4NF) requires that there are no non-trivial multi-valued dependencies of attribute sets on something else than a superset of a candidate key.
Fifth Normal Form (or 5NF or PJ/NF) requires that there are no non-trivial join dependencies that do not follow from the key constraints.
Domain-Key Normal Form (or DK/NF) requires that all constraints follow from the domain and the key constraints.
However, many relational DBMSs lack sufficient separation between the logical database design and the physical implementation of the data store, such that queries against a fully normalized database often perform poorly. In this case denormalization is sometimes used to improve performance, at the cost of reduced consistency guarantees.
Overview
A table in a relational database is said to be in a certain normal form if it satisfies certain constraints. Edgar F. Codd's original work defined three such forms but there are now other generally accepted normal forms. We give here a short informal overview of the most common ones. Each normal form below represents a stronger condition than the previous one (in the order below). For most practical purposes, databases are considered normalized if they adhere to third normal form.
First Normal Form (or 1NF) requires that all column values in a table are atomic (e.g., a number is an atomic value, while a list or a set is not). For example, normalization eliminates repeating groups by putting each into a separate table and connecting them with a primary key-foreign key relationship.
Second Normal Form (or 2NF) requires that there are no non-trivial functional dependencies of a non-key attribute on a part of a candidate key.
Third Normal Form (or 3NF) requires that there are no non-trivial functional dependencies of non-key attributes on something else than a superset of a candidate key.
Boyce-Codd Normal Form (or BCNF) requires that there are no non-trivial functional dependencies of attributes on something else than a superset of a candidate key. At this stage, all attributes are dependent on a key, a whole key and nothing but a key (excluding trivial dependencies, like A->A).
Fourth Normal Form (or 4NF) requires that there are no non-trivial multi-valued dependencies of attribute sets on something else than a superset of a candidate key.
Fifth Normal Form (or 5NF or PJ/NF) requires that there are no non-trivial join dependencies that do not follow from the key constraints.
Domain-Key Normal Form (or DK/NF) requires that all constraints follow from the domain and the key constraints.
No comments:
Post a Comment