Database design and normalisation

Imagine that course registration is kept in one big Excel sheet: every row has the student's name, city, the course title, the teacher and the score. When a teacher changes their surname, it must be fixed in hundreds of rows — miss one and the database contradicts itself. A new course can't be added until someone enrols, and when the last student leaves a course, the course itself disappears. In this lesson we learn to design a database so that every fact is stored in exactly one place. The scientific name for this is normalisation, and our practice database is built by exactly these rules.

The ER model: entities, relationships, cardinality

Design starts not with tables but with a conceptual model. An entity is a thing we store data about: a student, a course, a product. An attribute is one of its properties: a name, a price. A relationship connects entities: a student enrols in a course, a customer orders a product. Peter Chen proposed this model in 1976 in the form of entity–relationship (ER) diagrams.

Functional dependencies and keys

The core idea of normalisation is the functional dependency (FD). X → Y means “the value of X uniquely determines the value of Y”: course_id → teacher — if we know the course, we know the teacher. An FD is not an accident of the data but a business rule: we learn it from domain experts, while the current rows of a table can only refute it.

A set of attributes that determines all the other attributes is a superkey; a superkey from which no attribute can be removed is a candidate key. One of the candidate keys is chosen as the primary key. An attribute that belongs to some candidate key is called a prime attribute — we will need this distinction to tell 3NF and BCNF apart.

Anomalies: why redundancy is dangerous

• Update anomaly — the same fact sits in several rows, and when one copy changes the others stay old.
• Insert anomaly — one fact can't be recorded without another (adding a course that has no enrolments yet).
• Delete anomaly — deleting one fact destroys another (the course's teacher is lost together with the last enrolment).

-- a denormalised copy: the teacher is repeated in every row
CREATE TABLE report_flat AS
SELECT e.student_id, s.first_name, e.course_id, c.title, c.teacher, e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses  AS c ON c.id = e.course_id;

-- the name is corrected in one row only
UPDATE report_flat SET teacher = 'R. Safarov'
WHERE course_id = 1 AND student_id = 1;

SELECT course_id, teacher, COUNT(*) AS rows_with_it
FROM report_flat
WHERE course_id = 1
GROUP BY course_id, teacher
ORDER BY teacher;

course_id | teacher | rows_with_it
1 | R. Safarov | 1
1 | Ramin Səfərov | 3

Normal forms: from 1NF to BCNF

A decomposition must be lossless: joining the parts must give back the original table with no extra and no missing rows. For a split into two parts we check this with Heath's theorem: the common columns must be a key of one of the parts.

When denormalisation is right

Normalisation is ideal for everyday transactions (OLTP): many small writes, accuracy, no contradictions. Analytical warehouses (OLAP), however, are mostly read, so they are denormalised on purpose: a “star schema” has a fact table (sales) in the centre surrounded by wide dimension tables (product, customer, date), and reports need only a few JOINs. The golden rule: design in 3NF first, and denormalise only when there is a measured performance problem — and always together with a synchronisation mechanism (a trigger, a view, a nightly refresh).

SELECT s.first_name, s.city, c.title, c.teacher, e.score
FROM enrollments AS e
-- join students and courses
WHERE c.title = 'Mechanics'
ORDER BY e.score DESC;

first_name | city | title | teacher | score
Fidan | Naxçıvan | Mechanics | Ülviyyə Axundova | 94
Murad | Gəncə | Mechanics | Ülviyyə Axundova | 81
Rəşad | Bakı | Mechanics | Ülviyyə Axundova | 77
Elvin | Sumqayıt | Mechanics | Ülviyyə Axundova | 68

SELECT teacher
       -- number of different subjects
FROM courses
GROUP BY teacher
ORDER BY teacher;

teacher | subjects
Elnur Qasımov | 1
Nərmin Vəliyeva | 1
Ramin Səfərov | 1
Samir Hacıyev | 1
Sara Mitchell | 1
Ülviyyə Axundova | 1