Worked example: an order table in SQL

Start with one wide table:

OrderLine(OrderID, ProductID, OrderDate, CustomerID, CustomerName, ProductName, CurrentCatalogPrice, Quantity, UnitPrice)

1. Write down the business rules as FDs

• Each order has one date and one customer: OrderID → OrderDate, CustomerID
• Each customer has one name: CustomerID → CustomerName
• Each product has one name and one current price: ProductID → ProductName, CurrentCatalogPrice
• Each product in an order has one quantity and one price charged: (OrderID, ProductID) → Quantity, UnitPrice

The candidate key is (OrderID, ProductID), assuming a product appears at most once per order.

2. Remove partial dependencies (2NF)

OrderDate and CustomerID depend only on OrderID. ProductName and CurrentCatalogPrice depend only on ProductID. Move each to the table of its own key:

• Orders(OrderID, OrderDate, CustomerID, CustomerName)
• Products(ProductID, ProductName, CurrentCatalogPrice)
• OrderLine(OrderID, ProductID, Quantity, UnitPrice)

UnitPrice stays in OrderLine. It's the price charged on that order, which can differ from today's catalog price.

3. Remove transitive dependencies (3NF)

In Orders, OrderID → CustomerID → CustomerName. Move the name to its own table:

• Customers(CustomerID, CustomerName)
• Orders(OrderID, OrderDate, CustomerID)

4. The result