The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Relational model and design

Normalization

What happens if you do not split the table?

Here are two designs. In one, the member name and address are stored in the orders table as well. In the other, members and orders are split into separate tables. Do the same operations on both, and you will see that data goes out of step or is lost only when everything is packed into one table.

Checked against real execution · The same tables were created in SQLite and the results were checked

Concepts in this lab

Normalization and denormalization

Normalization is the design process of splitting tables so that the same data is not stored redundantly in several places. With redundancy, a change may be made in only one place, and the data then becomes inconsistent. Denormalization intentionally allows redundancy or merges tables for read performance. It reduces joins, but it adds the burden of updating the redundant data together.

Functional dependency

A relationship in which the value of one column (or set of columns) determines exactly one value of another column. For example, a member number determines the member's name. Normal forms are defined in terms of this relationship. Depending on only part of the primary key is called a partial dependency, and depending through a non-key column is called a transitive dependency. They are removed in second normal form and third normal form, respectively.

Primary key, unique key, foreign key

A primary key (PK) is the column that uniquely identifies each row of a table. Its values cannot be duplicated or empty, and a table has only one. A unique key is a unique constraint, which prevents duplicate values, and a table can have several. A foreign key (FK) is a column that references the primary key (or a unique key) of another table. It guarantees that the referenced row really exists.

Try these

• Default settings — Changing an address in the table before normalization. You must change as many rows as there are orders.
• A mistake that changes only one row — The same member ends up with two different addresses (an update anomaly).
• The same change after normalization — The address is stored in only one place, so changing one row is enough.
• A member with no orders — Before normalization, there is no place to put the member (an insertion anomaly).
• Deleting the last order — Before normalization, the member information is lost as well (a deletion anomaly).
• The cost of normalization — After normalization, reading the data needs a join.

Running with the default settings

1. This is the initial state. Every order row also holds the member name and address. Alice has 3 orders, so the address "Seoul" is stored 3 times. The order ID determines the member ID, and the member ID determines the name and address (a chain of functional dependencies).
2. Found every row whose member ID is 1 and changed its address. Rows changed: 3. The more orders there are, the more rows you have to change.
3. Querying Alice's address again shows "Busan" in every row. This time no row was missed, so nothing went out of step.

Result of this run

• Changing the address of one member meant changing 3 rows. This is because the same fact is stored in several rows. If even one row is missed, the data goes out of step.
• No problem showed up this time, but that is not because the structure prevents it. The person doing the work avoided it by being careful.

Code

-- Before normalization: order and member data in one table
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  member_id   INT,
  member_name VARCHAR(20),   -- determined by member_id
  member_addr VARCHAR(50),   -- determined by member_id
  item        VARCHAR(30),
  amount      INT
);

UPDATE orders SET member_addr = 'Busan'
 WHERE member_id = 1;

Assumptions of this model

This is a small example reduced to 3 members and a few orders. The table before normalization has a chain of functional dependencies, order ID → member ID → member name and address, so it does not satisfy third normal form. After normalization, orders refer to members through a foreign key. The definitions of the anomalies follow the standard explanation in relational database theory. Tables with the same structure were created in a real SQL engine (SQLite) and checked. When only one row was changed, the same member's address was returned as two different values; an INSERT of a member without an order ID was rejected; and after the last order was deleted, the number of rows still holding that member's information was 0. With the tables split, none of the three cases caused a problem.

Previous lab: ER modeling · Next lab: SELECT processing order