Normalize a relational database

Identify what determines each fact

A primary key identifies a row, while a functional dependency says that one set of attributes determines another. If OrderID determines CustomerID and CustomerID determines CustomerName, repeating the name in every order creates a transitive dependency.

Normalization separates related facts to reduce update, insertion, and deletion anomalies. Keep a key connecting the tables after decomposition. Splitting a table without preserving its relationships does not produce a useful design.

Ask SQL for the required result

SELECT retrieves data; JOIN relates tables; WHERE filters rows; GROUP BY forms groups for aggregates. The example counts orders per customer. Check whether customers with no orders should appear before choosing an inner or outer join.

A database management system also enforces constraints and manages concurrent changes. Related updates, such as reducing stock and recording a sale, should not leave an inconsistent partial result when one operation fails.

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID;

Check yourself