Databases: from ER model to SQL

Today is Saturday 19 September 2026 and the first-semester lectures have only just begun. Writing about databases now may seem early: in most universities the exam is held in the January winter session, four months from here. But that is exactly why this post is coming out now. Databases is an exam you lose through accumulation, not through difficulty: the ER model you study in October is the one you need in December to write queries, and anyone who skips it starts again from zero in the exam. Many courses also put a mid-term test in November. If yours has one, the useful window is only six to eight weeks away, not four months.

The blog already has C and pointers, Python and React, but nothing on databases and SQL. That is a strange gap, because Databases is one of the most cross-disciplinary exams: you meet it in computer engineering, computer science, statistics, and economics with a computing curriculum. This post covers the whole exam: conceptual model, translation into a relational schema, normalisation, SQL.

The conceptual model: entities, relationships, cardinality

The Entity-Relationship (ER) model describes the reality the database has to represent, before you write a single line of SQL. The building blocks are few:

Entity: an object of the domain that makes sense to treat on its own — a Student, a Course, a Book.
Attributes: the properties of an entity (name, email, year of publication). Some of them form the key: the minimal subset of attributes that uniquely identifies an instance.
Relationships: links between entities — a Student enrols in a Course, an Author writes a Book.
Cardinality: how many instances of one entity can be associated with how many instances of the other. The three forms are 1:1, 1:N and N:N.

The part where people really go wrong is not the syntax of the diagram: it is reading the specification in natural language and deciding the right cardinality. “A lecturer teaches several courses, a course has a single lecturer in charge” is 1:N. “A student enrols in several courses, a course has several enrolled students” is N:N. A practical trick: for each side of the relationship ask yourself “can one element here be associated with more than one element over there?” Yes from both sides means N:N; yes from one side only means 1:N, with the N on the side that answers yes.

Two more concepts come back in every exam:

Generalisation/specialisation: a parent entity (for example Person) with child entities that inherit its attributes and add their own (Student, Lecturer).
Weak entity: an entity that has no key of its own and is identified only together with the entity it depends on (for example the Instalments of a Payment, identified by the instalment number together with the payment they belong to).

From ER to relational schema: mechanical rules

Translating an ER diagram into tables is not an art, it is the application of fixed rules. Learning them by heart removes half the exam mistakes, because all the difficulty moves to the ER model upstream.

1:N relationship → the primary key of the “1” side becomes a foreign key in the table on the “N” side. For a Course (1) and its enrolled Students (N) through a lecturer in charge: the Lecturer’s key goes in the Course table, not the other way round.
N:N relationship → you need a bridge table whose key is composed of the primary keys of the two entities involved (plus any attributes of the relationship itself, such as the enrolment date).
1:1 relationship → either you merge the two entities into a single table, or you put the key of one as a foreign key in the other with a uniqueness constraint (UNIQUE), to stop it from becoming a de facto 1:N.
Generalisation → three strategies: collapse everything into the parent with a column that distinguishes the type; one table for each child with the inherited attributes duplicated (no parent); or parent plus one table for each child in 1:1. It depends on how much the children’s attributes differ — if a lot, the last strategy avoids columns full of NULLs.

Normalisation: removing anomalies, not applying rules by rote

Normalisation only makes sense if you see it at work on a table that hurts. Let us start from a denormalised table of university enrolments:

student_number	student_name	student_email	course_code	course_name	lecturer	credits	grade

The functional dependencies (what determines what) are:

student_number → student_name, student_email
course_code → course_name, lecturer, credits
(student_number, course_code) → grade

This table is already in 1NF (atomic values, no lists inside a cell), but not in 2NF: course_name, lecturer and credits depend only on course_code, that is on part of the composite key (student_number, course_code), not on the whole key. The concrete anomalies:

Update anomaly: if a course changes lecturer, you must update the row of every student enrolled in that course — forgetting one leaves the database inconsistent.
Insertion anomaly: you cannot record a new course (name, lecturer, credits) until at least one student enrols in it, because the course has no row of its own.
Deletion anomaly: if the only student enrolled in a course withdraws and their row is deleted, the information about the course itself is lost too.

The decomposition into 3NF separates the entities that depend on different keys:

Student(student_number PK, student_name, student_email)
Course(course_code PK, course_name, lecturer, credits)
Enrolment(student_number FK, course_code FK, grade, PRIMARY KEY(student_number, course_code))

Each anomaly disappears with the step that concerns it: separating Student removes the update anomaly on the email; separating Course removes both the insertion anomaly (a course exists even with no enrolled students) and the deletion anomaly (deleting an enrolment does not touch the Course table). This schema is also in Boyce-Codd normal form (BCNF), because the only remaining dependency in Enrolment has the whole key as its determinant.

SQL: from SELECT to subqueries

The logical order of a query

Before the syntax, one fact that settles half of the doubts about aliases: a query is conceptually evaluated in this order, not in the order in which it is written:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

That is why an alias defined in the SELECT cannot be used in the WHERE (the WHERE is evaluated before the alias exists) but can be used in the ORDER BY (which is evaluated afterwards).

JOIN: INNER and LEFT

The INNER JOIN returns only the rows that find a match in both tables. The LEFT JOIN returns all the rows of the left table, with NULL in place of the right table’s fields when there is no match. The difference matters every time you need to include “also those who have done nothing” — a user with no loans, a course with no enrolled students: with the INNER JOIN those rows vanish, with the LEFT JOIN they stay, with the missing fields set to NULL.

GROUP BY, HAVING, and why it is not like WHERE

WHERE filters rows before they are grouped; HAVING filters groups after aggregation. “Students with more than 3 exams taken” is a condition on an aggregate (COUNT(*)), so it goes in HAVING, not in WHERE — putting COUNT(*) > 3 inside a WHERE is a syntax error, because at that point of the evaluation the aggregation does not exist yet.

Subqueries, EXISTS, and the NOT IN trap with NULLs

A subquery can sit in the WHERE (correlated, if it refers to a column of the outer query, or uncorrelated) or in the FROM as a derived table. EXISTS only checks that the subquery returns at least one row, without looking at the values — often more efficient than IN on large tables. The most insidious trap is NOT IN: if the subquery returns even a single NULL value, the whole NOT IN condition stops producing results, because comparing anything with NULL yields UNKNOWN, never TRUE. The robust solution is NOT EXISTS, which does not have this problem.

Two exercises like the real ones

(a) From a case in words to the schema and the queries

Brief: a small library manages members, books, authors and loans. A book can have several authors and an author can have written several books. A member can borrow several books over time, even the same book on different dates; each loan has a start date and, if the book has been returned, a return date.

ER model: entities Member, Book, Author; N:N relationship Writes between Author and Book; the association between Member and Book is not a simple N:N relationship, because the same member can borrow the same book several times — it has to be modelled with an entity of its own, Loan, with its own key and attributes.

Relational schema:

Member(member_id PK, name, email)
Book(isbn PK, title, publication_year)
Author(author_id PK, name)
Writes(author_id FK, isbn FK, PRIMARY KEY(author_id, isbn))
Loan(loan_id PK, isbn FK, member_id FK, loan_date, return_date)

Writes is the bridge table of the N:N relationship; Loan has its own key (loan_id) because the member-book pair alone would not be enough to tell two different loans of the same book apart.

Query 1 — title of the book and name of the member for every loan still open (not returned):

SELECT b.title, m.name
FROM Loan l
JOIN Book b ON l.isbn = b.isbn
JOIN Member m ON l.member_id = m.member_id
WHERE l.return_date IS NULL;

Query 2 — for each member, the total number of loans made, including those who have never borrowed anything:

SELECT m.name, COUNT(l.loan_id) AS loan_count
FROM Member m
LEFT JOIN Loan l ON m.member_id = l.member_id
GROUP BY m.member_id, m.name
ORDER BY loan_count DESC;

The LEFT JOIN is essential here: with a plain JOIN, members with no loans would disappear from the result instead of showing up with 0. And COUNT(l.loan_id), not COUNT(*): the difference is the next exercise.

(b) The COUNT(*) trap with a LEFT JOIN

Let us go back to query 2 above, but with a different goal: which members have made at least one loan. A version that looks correct at first sight:

SELECT m.name
FROM Member m
LEFT JOIN Loan l ON m.member_id = l.member_id
GROUP BY m.member_id, m.name
HAVING COUNT(*) > 0;

The result of this query is wrong: it returns all the members, including those with no loans. The reason is that COUNT(*) counts the rows of the group, and a LEFT JOIN with no match still produces a row (with the Loan fields set to NULL) — so every member has at least one row in their group, and COUNT(*) is always at least 1.

The correct version uses COUNT on a column that can be NULL, typically the key of the table on the right of the LEFT JOIN:

SELECT m.name
FROM Member m
LEFT JOIN Loan l ON m.member_id = l.member_id
GROUP BY m.member_id, m.name
HAVING COUNT(l.loan_id) > 0;

COUNT(column) ignores NULLs, so for a member with no loans it is 0 and the row is excluded by the HAVING. It is the same principle as the NOT IN trap: the NULLs produced by a LEFT JOIN are silent, and COUNT(*) does not see them as missing.

The mistakes that come back every session

Aggregating without GROUP BY. Mixing in the SELECT a non-aggregated column with a function such as COUNT or AVG without grouping: some engines allow it and return an arbitrary value, others (like PostgreSQL) reject it with an error — learn to read that error, not to switch it off.
Putting a condition on the aggregate in WHERE instead of HAVING. Seen above: at the time of the WHERE the aggregation does not exist yet.
Forgetting DISTINCT after a JOIN that duplicates rows. A JOIN on an N:N relationship multiplies the rows of the outer query: counting or summing without DISTINCT (where needed) inflates the result.
“Relational division”: questions like “find the students who have taken all the exams of a given study programme” are not solved with a simple filter, but with the double negation — “there is no exam in the programme that the student has NOT taken”:

SELECT s.student_number
FROM Student s
WHERE NOT EXISTS (
  SELECT 1 FROM Course c
  WHERE c.programme = 'Computer Science'
  AND NOT EXISTS (
    SELECT 1 FROM Enrolment e
    WHERE e.student_number = s.student_number AND e.course_code = c.course_code
  )
);

COUNT(*) versus COUNT(column): the first counts rows, the second counts the non-NULL values of that column. They are equal only if the column never has NULLs — otherwise they are not, as in exercise (b).

A study method that really works

You learn Databases by doing, not by reading the theory a second time. Two habits are worth more than any other:

Design at least three complete schemas by hand before the exam sitting — from the written brief down to the relational schema with primary and foreign keys, as in exercise (a). It is the only way to train the reading of cardinalities, which is where most points are lost.
Really write the queries on a database, not just on paper. SQLite is enough and installs in two minutes: create the tables, insert a few fake rows, and check that the query returns exactly what you thought. A syntax error you find straight away; a logic error (like the COUNT(*) one above) you find only by running it and checking the result row by row.

How we work in lessons

With people preparing for Databases I almost always start from the ER model, not from SQL: if the reading of cardinalities is not solid, every query built on a wrong schema produces a result that looks plausible and is not. The lessons are mostly exercises — a brief, a schema to draw together, queries to write and really run — because that is where reading mistakes come out, not in the explanation of the theory.

For now there is no timed practice test of Databases or computer science on the site: the university practice test that I built covers maths and physics, not databases — I say so honestly, without promising a practice test that does not exist yet. The sentence I repeat for all the others still holds: the correct answer is free, the guided solution unlocks with the first lesson.

If the ER model or the queries with JOINs and subqueries are where you get stuck, or if the November mid-term is already close, write to me and we will talk: the first lesson is to work out whether the problem is the conceptual model, the translation into tables or the SQL syntax — they are three different blocks and they are unblocked in different ways. The full overview of the computer science lessons also covers C, Python and web development, for those who have more than one exam to line up this semester.

Databases: from ER model to relational schema and SQL