Database normalisation: a multistep process of database (re)structuring/(re)designing to satisfy certain requirements (called normal forms).
Normalisation advantages/properties:
- Easier to guarantee consistency after data modification.
- Easier to extended the database to accommodate new data.
- Reduction of redundant and duplicate data, better database organisation.
- More tables with smaller rows, more compact database.
- Greater flexibility for queries.
- Easier to implement security.
the key exists
A table satisfies the first normal form when:
- Columns (attributes) have unique names.
- No duplicate rows (tuples): it must be possible to define the primary key.
- Values are atomic (single values cannot be further decomposed, no collections - otherwise a NoSQL database).
non-key attributes depend on the whole key
A 1NF-satisfying table satisfies the second normal form when:
- The primary key is a single column.
- Otherwise: identify all non-prime columns. Each non-prime column must depend on all columns of the primary key (not only a part of it).
“[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key”
A 2NF-satisfying table satisfies the third normal form when non-prime columns do not have transitive dependencies.