✏️ Explanatory Question
Level: Expert — A foundational design topic; interviewers expect you to explain 1NF through BCNF clearly.
Normalization is the process of organizing data in a database to reduce redundancy and eliminate anomalies (insertion, update, and deletion anomalies). It splits large tables into smaller, related tables connected by keys.
| Form | Rule | Removes |
|---|---|---|
| 1NF | Atomic values, no repeating groups | Multi-valued columns |
| 2NF | 1NF + no partial dependency | Partial key dependency |
| 3NF | 2NF + no transitive dependency | Non-key → non-key dependency |
| BCNF | Every determinant is a super key | Remaining key anomalies |
-- UNNORMALIZED: repeating group of phone numbers
-- students(id, name, phone1, phone2) -- BAD
-- 1NF: atomic values, one phone per row
CREATE TABLE student_phones (
student_id INT,
phone VARCHAR(15),
PRIMARY KEY (student_id, phone)
);
-- 2NF: split so non-key columns depend on the WHOLE key
-- Bad: order_items(order_id, product_id, product_name)
-- product_name depends only on product_id (partial)
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- 3NF: remove transitive dependency
-- Bad: employees(emp_id, dept_id, dept_name)
-- dept_name depends on dept_id (non-key), not emp_id
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);