RDBMS Concepts and Normalization

What a relational database actually is#

A relational database stores facts in tables. A table is a set of rows, and every row has the same columns. That's all. The "relational" part comes from the fact that rows in one table can point at rows in another using a shared value, usually a key.

Think of a table as a spreadsheet with two strict rules: every column has one declared type, and every row can be identified uniquely. The server enforces both. A spreadsheet will happily let you type "banana" into a price column. MySQL won't.

The vocabulary you'll hear daily#

• Table: one kind of thing (customers, orders, products).
• Row (record, tuple): one instance of that thing.
• Column (field, attribute): one property, with a type.
• Primary key: the column(s) that identify a row uniquely.
• Foreign key: a column that holds another table's primary key value.

This is the shop schema we'll use for most of the course. One customer has many orders, one order has many items, and each item refers to one product.

Why normalization exists#

Here's what beginners build first:

Asha's email is stored twice, and it already disagrees with itself. Which one is right? Nobody knows. That's an update anomaly, and it's the core problem normalization solves: one fact should live in exactly one place.

The products column is worse. You can't count how many pens were sold without string parsing, and you can't index it usefully.

The three normal forms, in plain terms#

First normal form (1NF): every cell holds one value. No comma-separated lists, no repeating columns like product1, product2. Split those into rows.

Second normal form (2NF): every non-key column depends on the whole key. This only matters when the key has multiple columns. In order_items(order_id, product_id, product_name, qty), product_name depends only on product_id, so it belongs in products.

Third normal form (3NF): non-key columns depend on the key and nothing else. If customers has city and postal_code, and the postal code determines the city, then city depends on postal code, not on the customer. Move it out, or accept the duplication on purpose.

When to break the rules#

Normalization is a default, not a religion. Reporting tables often duplicate data deliberately so reads are fast. The difference between a good and bad denormalization is whether you chose it knowing the cost: now you have to keep copies in sync.

Common mistakes#

• Storing lists in a text column because "it's easier". It's easier once, then painful forever.
• Using a person's name or email as the primary key. Both change.
• Normalizing past the point where every query needs six joins for no benefit.

Practice#

Take this row: (1, "Ravi", "Pune", "411001", "Laptop, Mouse", "2025-01-10"). Split it into tables that satisfy 3NF. Write down each table's columns, its primary key, and which column is the foreign key. Then explain in one sentence which anomaly each split prevents.