Normalisation
Normalisation is a principled way to remove redundancy from tables, preventing the anomalies that occur when the same fact is stored in several places.
Update anomalies
Consider one table Orders(order_id, customer_id, customer_name, customer_city, product, price). A customer's city is repeated in every order, so moving requires many updates; missing one makes data inconsistent (update anomaly). A new customer cannot be recorded until they order (insertion anomaly). Deleting a customer's only order loses the customer entirely (deletion anomaly).
Functional dependencies
A functional dependency X -> Y means that whenever two rows agree on X, they must agree on Y: customer_id -> customer_name, customer_city. Dependencies come from the meaning of the data, not from the current contents. Keys are defined through them: X is a superkey if X determines every attribute.
First normal form
A table is in first normal form (1NF) if every attribute holds atomic values: no lists or repeating groups. Storing 'Maths, Physics' in one courses column violates 1NF; instead, use a separate row per course, typically in a related table. Modern systems offer JSON and array types, which designers use deliberately rather than by accident.
Second normal form
2NF applies to tables with composite keys: no non-key attribute may depend on only part of a key. In Enrolment(student_id, course_id, student_name, grade), student_name depends only on student_id, a partial dependency. Moving student_name to the Student table fixes it.
Third normal form and BCNF
3NF additionally forbids transitive dependencies, where a non-key attribute depends on another non-key attribute: in Employee(emp_id, dept_id, dept_name), dept_name depends on dept_id, not directly on the key. Boyce-Codd normal form (BCNF) is stricter: for every non-trivial dependency X -> Y, X must be a superkey. The informal summary: every attribute depends on the key, the whole key, and nothing but the key.
Decomposition properties
Normalising decomposes tables into smaller ones. A good decomposition is lossless: joining the pieces reproduces exactly the original data, which is guaranteed if the shared attributes form a key of at least one piece. Ideally it is also dependency-preserving, so constraints can be checked within individual tables. BCNF sometimes cannot preserve every dependency, in which case 3NF is a reasonable compromise.
Higher forms and denormalisation
Fourth normal form handles multivalued dependencies, such as independent lists of a person's skills and languages stored together, and fifth normal form handles join dependencies; both are rarer in practice. Sometimes designers deliberately denormalise, storing redundant data such as an order total or a reporting table, to speed up reads, accepting the cost of keeping copies consistent. Analytical data warehouses often use denormalised star schemas.
Worked example
Start with Orders(order_id, customer_id, customer_name, customer_city, product_id, product_name, quantity), key (order_id, product_id). Dependencies: order_id -> customer_id; customer_id -> customer_name, customer_city; product_id -> product_name. Decompose into Customer(customer_id, customer_name, customer_city), OrderHeader(order_id, customer_id), Product(product_id, product_name), and OrderLine(order_id, product_id, quantity). Each fact is now stored once, and the result is in BCNF.
Key takeaways
Redundancy causes update, insertion, and deletion anomalies. Functional dependencies X -> Y express that X determines Y and define keys. 1NF requires atomic values; 2NF removes partial dependencies on composite keys; 3NF removes transitive dependencies; BCNF requires every determinant to be a superkey: the key, the whole key, and nothing but the key. Decompositions should be lossless and, ideally, dependency-preserving. 4NF and 5NF handle rarer dependencies. Denormalisation trades redundancy for read speed, as in data-warehouse star schemas.
Flashcard
Inconsistency from changing one copy of a fact
Check your understanding
Employee(emp_id, dept_id, dept_name) stores dept_name in every employee row. Which normal form does it violate, and why?