Third Normal Form (3NF)

• 3NF mandates that no non-prime attribute \(A\) in \(R\) is transitively dependent on the primary key
• In somewhat poetic terms, each attribute must tell something about a key, a whole (candidate) key, and nothing but that key
• 1NF guarantees that there is a key. 2NF guarantees that every attribute tells something about a whole key. 3NF guarantees that every attribute tells something about a key, and about nothing else in the table.
• If \(X \to A\) holds in \(R\), then either
  • \(X\) is a superkey of \(R\), or
  • \(A\) is a prime attribute of \(R\)

Note that normalisation is only necessary if \(A\) is a non-prime attribute of r. Consider the scenario with the relation Employee(SSN, emp_ID, salary). In this, while SSN -> emp_ID and emp_ID -> salary, this is not a concern because both are valid candidate keys

The previous example can be converted to 3NF as follows

The functional dependencies are

The schemas are Lot - (property_ID, state, lot, lot_area, base_price) Tax - (state, tax_rate)

lot_area is not a candidate key. base_price is not directly dependent on property_ID. So, it does not obey 3NF, and we will add it to a new table property_ID -> lot, lot_area, state lot, lot_area -> base_price state -> tax_rate

The new schema is Prices - (lot_area, price)