Databases series: 1. Tables and basic SQL · 2. ERD, joins, GROUP BY · 3. Normalization · 4. Library exercise · 5. Indexes and query performance · 6. Transactions and locking
Post 1 worked with one table at a time, but real data lives in several related tables: a course has chapters, a learner enrols in many courses. This post goes from design (ERDs, 1-1/1-n/n-n relationships, foreign keys, ON DELETE) to querying (JOINs, GROUP BY/HAVING, the logical order a SELECT runs in), with four exercises on an online course platform. Read post 1 first if CREATE TABLE, primary keys and NULL aren’t familiar yet; the dialect is MySQL 8 (InnoDB), and where PostgreSQL differs in a way that matters, I say so.
1. Modelling: the ERD
Key idea: an ERD is a drawing of entities, attributes and relationships, made before you write CREATE TABLE. Each relationship on it later becomes a foreign key or a junction table.
An ERD (Entity-Relationship Diagram) has three building blocks:
- Entity: a thing you need to store: Student, Course, Teacher…
- Attribute: a property of an entity: id, name, date of birth…
- Relationship: how entities connect: 1-1, 1-n, n-n.
The slide scenario is an online course platform, with the entities Teacher, Student, Course, Chapter, Lesson and Admin. Exercise 1 draws the full model.
1.1. Three kinds of relationship
| Relationship | Example | How to implement |
|---|---|---|
| 1-1 | A student has exactly one student card | FK on one side plus UNIQUE |
| 1-n | A course has many chapters | FK on the many side (chapter.course_id) |
| n-n | Students take many subjects; subjects have many students | A junction table with two FKs |
erDiagram
STUDENT ||--|| STUDENT_CARD : "has"
COURSE ||--o{ CHAPTER : "contains"
STUDENT ||--o{ STUDENT_SUBJECT : "enrolls"
SUBJECT ||--o{ STUDENT_SUBJECT : "is taken in"
1.2. Relational databases, primary keys and foreign keys
- Relational database: data is split into tables, each table is an entity, and tables are linked by keys.
- Primary key (PK): uniquely identifies each row in a table.
- Foreign key (FK): a column in one table that points to the PK (or a UNIQUE column) of another, the “common ground” that links two tables. The DBMS refuses an FK value that points to a non-existent row; that’s referential integrity.
CREATE TABLE student (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- 1-1: the UNIQUE on student_id is what turns 1-n into 1-1
CREATE TABLE student_card (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
student_id BIGINT NOT NULL UNIQUE,
issued_on DATE NOT NULL,
CONSTRAINT fk_card_student FOREIGN KEY (student_id) REFERENCES student(id)
);
-- 1-n: FK on the "many" side
CREATE TABLE book (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
borrower_id BIGINT NULL,
CONSTRAINT fk_book_student FOREIGN KEY (borrower_id) REFERENCES student(id)
);
-- n-n: junction table with a composite PK to prevent duplicate enrolments
CREATE TABLE subject (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE student_subject (
student_id BIGINT NOT NULL,
subject_id BIGINT NOT NULL,
enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, subject_id),
KEY idx_ss_subject (subject_id),
CONSTRAINT fk_ss_student FOREIGN KEY (student_id) REFERENCES student(id),
CONSTRAINT fk_ss_subject FOREIGN KEY (subject_id) REFERENCES subject(id)
);
Where the slides fall short, and what to fix when you do it yourself:
- The slides’ junction table uses an auto-increment
IDas PK but noUNIQUE(student_id, subject_id), so a student can enrol in the same subject twice. Use a composite PK as above, or keep the surrogate id (a generated id with no business meaning) and add a unique constraint. - Column order in a composite PK matters:
(student_id, subject_id)is great for “subjects of student X”. The reverse lookup “students of subject Y” needs an extra index on(subject_id), likeidx_ss_subject. MySQL creates an index for an FK automatically if none exists; PostgreSQL doesn’t.
DDL typos and the Student → Address relationship on the slides
- Some slide DDL has typos (
create table chaper, a trailing comma before), a Course table with aSUBJECT_IDcolumn, a Chapter table withBOOK_ID). Run it as-is and you’ll get syntax errors. - One slide variant implements a 1-1 Student → Address with
address_idon student, again withoutUNIQUE, so it’s really n-1. Its sample data also puts Hanoi’s Hà Đông district in Ho Chi Minh City: a small slip, but wrong sample data lets tests “pass” on meaningless data.
1.3. ON DELETE and ON UPDATE
The slides don’t mention it, but interviewers like to ask: what happens to child rows when a parent row is deleted?
| Option | Behaviour |
|---|---|
RESTRICT / NO ACTION (default) |
Block deleting the parent while children exist |
CASCADE |
Delete the children too |
SET NULL |
Set the children’s FK to NULL |
1.4. Two ways to draw an ERD: Chen and crow’s foot
The slides draw ERDs in Chen notation (rectangles, ellipses, diamonds). The mermaid diagrams in this blog use crow’s foot, the default in MySQL Workbench and dbdiagram.io. Same model, different drawing.
Chen vs crow’s foot symbols side by side
| Meaning | Chen (slides) | Crow’s foot (mermaid in this blog) |
|---|---|---|
| Entity | Rectangle | Box with the table name |
| Attribute | Ellipse attached to the entity; key attributes are underlined | A line inside the box, marked PK/FK |
| Relationship | Diamond with a verb: study, teach, have, borrow | A connecting line, with the verb as its label |
| Cardinality | 1, N, M written next to the line |
Symbols at the line ends: || exactly one, |o zero or one, }| one or many, }o zero or many |
The slides’ library example (a student has one card, borrows many books and takes many subjects), redrawn in Chen style:
flowchart LR
SUB[Subject] ---|N| STUDY{study}
STUDY ---|N| STU[Student]
STU ---|1| BORROW{borrow}
BORROW ---|N| BOOK[Book]
STU ---|1| HAVE{have}
HAVE ---|1| CARD[Card]
STU --- SID([student_id - key])
BOOK --- BID([book_id - key])
And in crow’s foot, which is explicit about mandatory vs optional: a book may not be borrowed by anyone (|o), and a student may not have a card yet (o|):
erDiagram
SUBJECT }o--o{ STUDENT : study
STUDENT |o--o{ BOOK : borrows
STUDENT ||--o| CARD : has
2. JOINs: reading from several tables
Key idea: a JOIN pairs up rows from two tables using the condition in ON. Pick the join type by asking “do I need to keep rows that don’t match?”
flowchart LR
subgraph INNER["INNER JOIN"]
i1["Only rows that match in both tables"]
end
subgraph LEFT["LEFT JOIN"]
l1["Every row of the left table + matching right rows (NULL if none)"]
end
subgraph RIGHT["RIGHT JOIN"]
r1["Every row of the right table + matching left rows (NULL if none)"]
end
-- INNER JOIN: students and the books they've borrowed (only those who borrowed)
SELECT s.name, b.title
FROM student s
INNER JOIN book b ON b.borrower_id = s.id;
-- LEFT JOIN: every student; title is NULL for those who borrowed nothing
SELECT s.name, b.title
FROM student s
LEFT JOIN book b ON b.borrower_id = s.id;
-- Every book; name is NULL for books nobody has borrowed.
-- You could write a RIGHT JOIN, but swapping the tables and using LEFT JOIN reads better.
SELECT b.title, s.name
FROM book b
LEFT JOIN student s ON s.id = b.borrower_id;
Results with sample data (An borrowed 2 books, Binh and Chi borrowed none, SICP is on the shelf):
| Query | Result |
|---|---|
| INNER | (An, Clean Code), (An, DDIA) |
| LEFT from student | (An, Clean Code), (An, DDIA), (Binh, NULL), (Chi, NULL) |
| LEFT from book | (Clean Code, An), (DDIA, An), (SICP, NULL) |
The slides define RIGHT JOIN as “returns rows from the left table and matching rows from the right”. That’s wrong: a RIGHT JOIN keeps every row of the right table. The slide’s example code is correct, though.
Common stumbling points:
FULL OUTER JOIN: PostgreSQL has it, MySQL doesn’t. In MySQL, emulate it withLEFT JOIN ... UNION ... RIGHT JOIN.CROSS JOIN(Cartesian product, every pair of rows): N × M rows. Forget the join condition and that’s what you get.- Self join: a table joined to itself, e.g.
employee e JOIN employee m ON e.manager_id = m.id.
MySQL mostly executes joins as nested loop joins: for each outer row, look up the inner table; with an index on the join column, each lookup is one B+tree descent. Since 8.0.18 MySQL also has a hash join for non-indexed joins, and PostgreSQL also has merge joins. This is why FK columns almost always need an index.
How these three algorithms run, what each needs in terms of indexes or memory, and when the planner picks which, with real PostgreSQL plans and a Java simulation, is in How joins run: nested loop, hash join, merge join.
3. GROUP BY and HAVING
Key idea: GROUP BY collapses rows with the same values into groups and computes aggregates (COUNT, SUM, AVG, MAX, MIN) per group. WHERE filters rows before grouping; HAVING filters groups by their aggregate results.
-- Count books per publishing city
SELECT publisher_city, COUNT(*) AS book_count
FROM book
GROUP BY publisher_city;
-- Subjects where every exam score is above 7, with the number of attempts
SELECT subject_name, COUNT(*) AS attempts
FROM exam_result
GROUP BY subject_name
HAVING MIN(score) > 7;
-- WHERE and HAVING together: only paid orders,
-- then only customers who spent more than 10M
SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE status = 'PAID'
GROUP BY customer_id
HAVING SUM(total) > 10000000;
A handy trick: HAVING MIN(score) > 7 means “every row in the group satisfies it”, while HAVING MAX(score) > 7 means “at least one row does”.
The golden rule of GROUP BY: every column in SELECT must be in GROUP BY, inside an aggregate, or functionally dependent on a grouped column (its value is uniquely determined by that column). For example, grouping by the PK teacher.id lets you select teacher.name. MySQL 8 enables ONLY_FULL_GROUP_BY by default to enforce this; PostgreSQL also accepts the PK-dependency case.
4. The logical execution order of SELECT
Key idea: we write SELECT first, but the DBMS logically processes FROM first and SELECT near the end. That order decides which clauses can see aliases and aggregates.
flowchart LR
A["1. FROM + JOIN"] --> B["2. WHERE"] --> C["3. GROUP BY"] --> D["4. HAVING"] --> E["5. SELECT<br/>(expressions, aliases, window functions)"] --> F["6. DISTINCT"] --> G["7. ORDER BY"] --> H["8. LIMIT / OFFSET"]
WHEREcan’t use an alias fromSELECTor an aggregate, because SELECT hasn’t run and groups don’t exist yet. Filter on aggregates withHAVING.ORDER BYcan use aliases because it runs afterSELECT.LIMITruns last:LIMIT 10doesn’t make the grouping/sorting cheaper unless an index lets the engine stop early.- Dialects: MySQL allows aliases in
GROUP BYandHAVING. PostgreSQL allows them inGROUP BY/ORDER BYbut not inHAVING/WHERE. Repeat the expression inHAVINGto stay portable.
This is the logical order. The optimizer is free to change the physical order (push predicates down early, use an index to skip sorting) as long as the result is the same.
Exercises
These restate the course exercises in my own words. All solutions are mine and were run on MySQL 8.4.
Exercise 1: ERD for an online course platform
Task: design the database for an online course platform: learners enrol in courses and leave ratings and reviews; the platform manages teachers and courses. Go through four steps: identify entities, add attributes, define relationships, create the tables.
Step 1, entities: Teacher, Learner, Course, Chapter, Lesson, Admin. Plus two entities that “come out of” relationships: Enrollment (a learner in a course, with rating/review) and LessonProgress (progress per lesson).
Steps 2 and 3, attributes and relationships:
erDiagram
TEACHER ||--o{ COURSE : teaches
COURSE ||--o{ CHAPTER : has
CHAPTER ||--o{ LESSON : has
LEARNER ||--o{ ENROLLMENT : makes
COURSE ||--o{ ENROLLMENT : receives
LEARNER ||--o{ LESSON_PROGRESS : tracks
LESSON ||--o{ LESSON_PROGRESS : "is tracked in"
TEACHER {
int id PK
string name
}
COURSE {
int id PK
string title
int teacher_id FK
}
CHAPTER {
int id PK
int course_id FK
string title
int position
}
LESSON {
int id PK
int chapter_id FK
string title
int position
}
LEARNER {
int id PK
string name
string status
}
ENROLLMENT {
int learner_id PK
int course_id PK
int rating
datetime enrolled_at
}
LESSON_PROGRESS {
int learner_id PK
int lesson_id PK
string status
datetime updated_at
}
Admin is a management role with no data relationship to courses, so it’s left off the diagram. Real systems usually merge Admin/Teacher/Learner into one user table plus a role table.
Teacher and Course: 1-n or n-n? The slide’s step 3 diagram draws Teacher n-n Course, but the drills below and the e-learning design in the ORM lessons use course.teacher_id (1-n). I went with 1-n because “each course has one responsible teacher” is the common rule.
Exercise 1 solution, step 4: full DDL and design decisions
CREATE TABLE teacher (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE learner (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
status VARCHAR(10) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE course (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL,
teacher_id INT NULL, -- a course may not be assigned yet
FOREIGN KEY (teacher_id) REFERENCES teacher(id)
);
CREATE TABLE enrollment (
learner_id INT NOT NULL,
course_id INT NOT NULL,
rating TINYINT NULL CHECK (rating BETWEEN 1 AND 5),
enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (learner_id, course_id),
KEY idx_enroll_course (course_id),
FOREIGN KEY (learner_id) REFERENCES learner(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
CREATE TABLE chapter (
id INT AUTO_INCREMENT PRIMARY KEY,
course_id INT NOT NULL,
title VARCHAR(200) NOT NULL,
position INT NOT NULL,
FOREIGN KEY (course_id) REFERENCES course(id)
);
CREATE TABLE lesson (
id INT AUTO_INCREMENT PRIMARY KEY,
chapter_id INT NOT NULL,
title VARCHAR(200) NOT NULL,
position INT NOT NULL,
FOREIGN KEY (chapter_id) REFERENCES chapter(id)
);
CREATE TABLE lesson_progress (
learner_id INT NOT NULL,
lesson_id INT NOT NULL,
status ENUM('IN_PROGRESS', 'COMPLETED') NOT NULL,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (learner_id, lesson_id),
KEY idx_progress_lesson (lesson_id),
FOREIGN KEY (learner_id) REFERENCES learner(id),
FOREIGN KEY (lesson_id) REFERENCES lesson(id)
);
Design decisions worth pointing out:
- “Not started” is not stored as a row. No row in
lesson_progressmeans the learner hasn’t started, so enrolling in a course doesn’t insert thousands of rows. - A
positioncolumn orders chapters and lessons. Don’t rely onid, because teachers will want to reorder them. - The slides list two sample queries for this step. “Names of active learners”:
SELECT name FROM learner WHERE status = 'ACTIVE';. “Courses of learner 1”:SELECT c.* FROM enrollment e JOIN course c ON c.id = e.course_id WHERE e.learner_id = 1;.
Exercise 2: Three queries on the student/card/subject model
Using student, student_card, subject and student_subject from section 1.2.
- (a) Each student’s details and how many subjects they’ve taken.
- (b) Each student with their card details.
- (c) Each subject with the number of students who took it.
Exercise 2 solutions
(a)
SELECT s.id, s.name, COUNT(ss.subject_id) AS subject_count
FROM student s
LEFT JOIN student_subject ss ON ss.student_id = s.id
GROUP BY s.id, s.name;
You need the LEFT JOIN and COUNT(ss.subject_id) (not COUNT(*)), so students with no subjects show up with 0. COUNT(*) would count the NULL row produced by the LEFT JOIN and return 1, which is wrong.
(b)
SELECT s.id, s.name, c.id AS card_id, c.issued_on
FROM student s
LEFT JOIN student_card c ON c.student_id = s.id;
Thanks to UNIQUE(student_id), each student produces at most one row. Use an INNER JOIN if you only want students who already have a card.
(c)
SELECT sub.id, sub.name, COUNT(ss.student_id) AS student_count
FROM subject sub
LEFT JOIN student_subject ss ON ss.subject_id = sub.id
GROUP BY sub.id, sub.name
ORDER BY student_count DESC;
The idx_ss_subject (subject_id) index stops this from scanning the whole junction table.
Exercise 3: Nine GROUP BY/HAVING drills on the course platform
Using the schema from Exercise 1. Sample data: 4 teachers (Quang teaches nothing), 6 courses (Kafka has no teacher yet), 3 learners.
- Number of courses assigned to each teacher.
- Same, with the teacher’s id and name; only teachers with at least one course.
- Same as 3.2 but paginated, at most 2 rows per page, return page 2.
- Same as 3.2 but including teachers with no courses.
- Every learner with all the courses they’ve enrolled in.
- Average rating per course, only courses averaging ≥ 4.0.
- Learners, their courses, and each course’s teacher.
- Learners, their courses, the chapters/lessons of those courses, and each learner’s status per lesson.
- For each lesson: course/chapter/lesson info plus how many learners completed it, are in progress, and haven’t started. (No solution on the slides.)
Exercise 3.1–3.5 solutions: counting, pagination, LEFT JOIN
3.1.
SELECT teacher_id, COUNT(*) AS course_count
FROM course
WHERE teacher_id IS NOT NULL
GROUP BY teacher_id;
Without WHERE teacher_id IS NOT NULL you get a NULL group holding the unassigned courses.
3.2.
SELECT t.id, t.name, COUNT(c.id) AS course_count
FROM teacher t
JOIN course c ON c.teacher_id = t.id
GROUP BY t.id, t.name;
The INNER JOIN already drops teachers with no courses, so “≥ 1” holds automatically. If the threshold were 2, add HAVING COUNT(c.id) >= 2.
3.3.
SELECT t.id, t.name, COUNT(c.id) AS course_count
FROM teacher t
JOIN course c ON c.teacher_id = t.id
GROUP BY t.id, t.name
ORDER BY t.id
LIMIT 2 OFFSET 2; -- OFFSET = (page - 1) * size
The slide’s solution has no ORDER BY, so “page 2” isn’t stable between calls. On large tables OFFSET also gets slower with every page. The alternative (keyset pagination) is in the indexes and query performance post.
3.4.
SELECT t.id, t.name, COUNT(c.id) AS course_count
FROM teacher t
LEFT JOIN course c ON c.teacher_id = t.id
GROUP BY t.id, t.name;
Quang shows up with course_count = 0. The key is driving from teacher and using a LEFT JOIN.
3.5.
SELECT l.id, l.name, c.title
FROM learner l
JOIN enrollment e ON e.learner_id = l.id
JOIN course c ON c.id = e.course_id
ORDER BY l.id, c.id;
Exercise 3.6–3.9 solutions: ratings, multi-level joins, conditional aggregation
3.6.
SELECT c.id, c.title,
ROUND(AVG(e.rating), 2) AS avg_rating,
COUNT(e.rating) AS rating_count
FROM course c
JOIN enrollment e ON e.course_id = c.id
GROUP BY c.id, c.title
HAVING AVG(e.rating) >= 4.0;
I added rating_count: a 5.0 average from one rating is less trustworthy than 4.6 from 2,000. Real systems usually need a minimum (HAVING COUNT(e.rating) >= 10) or a weighted (Bayesian) average. The slide’s solution is missing the S in SELECT.
3.7.
SELECT l.name AS learner, c.title AS course, t.name AS teacher
FROM learner l
JOIN enrollment e ON e.learner_id = l.id
JOIN course c ON c.id = e.course_id
LEFT JOIN teacher t ON t.id = c.teacher_id
ORDER BY l.id, c.id;
LEFT JOIN teacher so a course without a teacher doesn’t make the enrolment disappear.
3.8.
SELECT l.name AS learner,
c.title AS course,
ch.title AS chapter,
ls.title AS lesson,
COALESCE(p.status, 'NOT_STARTED') AS status
FROM enrollment e
JOIN learner l ON l.id = e.learner_id
JOIN course c ON c.id = e.course_id
JOIN chapter ch ON ch.course_id = c.id
JOIN lesson ls ON ls.chapter_id = ch.id
LEFT JOIN lesson_progress p
ON p.lesson_id = ls.id
AND p.learner_id = e.learner_id
ORDER BY l.id, c.id, ch.position, ls.position;
The slide’s solution has two instructive problems:
- After the
LEFT JOINto the progress table, itINNER JOINs the student table on a column from the progress table, which drops every “not started” lesson (NULL column), so the LEFT JOIN achieves nothing. - It has a
GROUP BYwith no aggregates, which is just a disguisedDISTINCT.
The right approach: start from enrollment (who takes which course) to produce every (learner, lesson) pair, then LEFT JOIN progress with both lesson_id and learner_id in the ON clause.
3.9.
SELECT c.title AS course,
ch.title AS chapter,
ls.title AS lesson,
COUNT(e.learner_id) AS enrolled,
COUNT(CASE WHEN p.status = 'COMPLETED' THEN 1 END) AS completed,
COUNT(CASE WHEN p.status = 'IN_PROGRESS' THEN 1 END) AS in_progress,
COUNT(e.learner_id) - COUNT(p.learner_id) AS not_started
FROM course c
JOIN chapter ch ON ch.course_id = c.id
JOIN lesson ls ON ls.chapter_id = ch.id
LEFT JOIN enrollment e ON e.course_id = c.id
LEFT JOIN lesson_progress p ON p.lesson_id = ls.id AND p.learner_id = e.learner_id
GROUP BY c.id, c.title, ch.id, ch.title, ch.position, ls.id, ls.title, ls.position
ORDER BY c.id, ch.position, ls.position;
The technique is conditional aggregation: COUNT(CASE WHEN ... THEN 1 END) counts conditionally in a single pass instead of three subqueries. “Not started” is enrolled learners minus learners with a progress row. Sample output:
| course | chapter | lesson | enrolled | completed | in_progress | not_started |
|---|---|---|---|---|---|---|
| Java Core | Basics | Variables | 2 | 2 | 0 | 0 |
| Java Core | Basics | Loops | 2 | 0 | 1 | 1 |
| Java Core | OOP | Classes | 2 | 0 | 0 | 2 |
| SQL | Select | WHERE | 2 | 1 | 0 | 1 |
MySQL lets you write the shorter SUM(p.status = 'COMPLETED'), but that returns NULL (not 0) when every row is NULL, and it doesn’t run on PostgreSQL (which has its own COUNT(*) FILTER (WHERE ...)). The COUNT(CASE ...) form works on both.
Remember
- Can you explain why 1-1 needs
UNIQUEon the FK, and why an n-n junction table needs a composite PK plus an index on the second FK? - Do you know when to use
ON DELETE CASCADEand when to keepRESTRICT? - Can you write a LEFT JOIN with
COUNT(right-table column)that keeps zero-count rows, and say why right-table conditions belong inON? - Can you recite the logical order of SELECT and work out which clauses can use an alias?
- Can you spot fan-out when joining two 1-n child tables, and fix it by aggregating each child first?
Interview questions
Junior
What’s the difference between WHERE and HAVING?
WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY.
WHERE can’t use aggregates, while HAVING can use COUNT, SUM and so on. Anything you can filter in WHERE should go there, because it shrinks the data before grouping.
INNER JOIN vs LEFT JOIN?
INNER keeps only rows that match on both sides; LEFT keeps every left row and fills the right-hand columns with NULL when there’s no match.
“List every customer with their order count, including customers who never bought anything” needs a LEFT JOIN with COUNT(o.id).
How do you implement a many-to-many relationship?
With a junction table holding two FKs to the two tables, and a composite PK (or UNIQUE) on the pair.
The composite PK prevents duplicates; add an index on the second FK for reverse lookups. The junction table often carries attributes of the relationship itself, like an enrolment date or a rating.
How do you read TEACHER ||--o{ COURSE in a crow’s foot ERD, and how does it differ from Chen notation?
Each course has exactly one teacher and a teacher teaches zero or more courses; as tables, teacher_id is an FK on course.
In crow’s foot you read the symbol at each end of the line: on the TEACHER end || (exactly one), on the COURSE end o{ (zero or many). Chen draws the same thing as two rectangles joined through a “teach” diamond, with 1 and N on the lines and attributes as ellipses. Crow’s foot is closer to the real tables, so it’s the usual choice for schema design.
Mid
Why do COUNT(*) and COUNT(column) differ after a LEFT JOIN?
COUNT(*) counts every row, including the NULL-filled rows the LEFT JOIN produces; COUNT(col) skips NULLs.
Counting “orders per customer” with COUNT(*) returns 1 for customers with no orders. Count a column from the right-hand table, usually its PK.
What’s the logical execution order of SELECT, and why can’t you use an alias in WHERE?
FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT; aliases are created in the SELECT step, after WHERE.
So WHERE can’t see them, while ORDER BY runs later and can. MySQL leniently allows aliases in HAVING; PostgreSQL doesn’t.
What happens if you LEFT JOIN and then filter the right table in WHERE?
The result is identical to an INNER JOIN: the unmatched rows are dropped.
Unmatched rows have NULLs in the right-hand columns, so a condition like WHERE r.status = 'X' evaluates to UNKNOWN and drops them. To keep the left rows, move the condition into ON.
When would you use ON DELETE CASCADE?
When the child rows are an inseparable part of the parent with no business value of their own, e.g. the lines of a temporary shopping cart.
Avoid it for financial, historical or audit data: one delete can spread much further than intended. Prefer RESTRICT and deliberate deletion or archiving.
Senior
A JOIN fans out and SUM comes out wrong. How do you fix it?
Aggregate each child table by the parent key first (subquery/CTE), then join the aggregated results.
Joining a parent to two 1-n child tables at once makes the row count the product of both sides, so totals get multiplied. After aggregating, each child contributes one row per parent. A correlated subquery in SELECT also works. Verify by comparing COUNT(*) before and after the join.
How does a database execute a JOIN, and why do FK columns need an index?
Mostly as a nested loop join: for each outer row, look up the inner table. With an index each lookup is O(log n); without one it’s a full scan, so O(n × m) overall.
MySQL 8.0.18+ adds hash joins for the no-index case; PostgreSQL also has merge joins. An index on the FK also speeds up deleting or updating parent rows, because the database has to check the child table. Postgres doesn’t create FK indexes automatically, which is a common performance bug.
Useful phrases for interviews
- “I’d start by identifying the core entities and their relationships, then turn that ERD into tables.”
- “This is a one-to-many relationship, so the foreign key goes on the ‘many’ side.”
- “For a many-to-many relationship I’d add a junction table with a composite primary key on both foreign keys.”
- “Since we need customers with zero orders as well, this has to be a LEFT JOIN, and I’d count the order id rather than star.”
- “WHERE filters rows before grouping; HAVING filters the groups afterwards.”
- “Logically the query runs FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT last.”
- “Before summing across two child tables, I’d aggregate each one separately to avoid fan-out.”
- “I’d put the condition on the right-hand table in the ON clause, so the LEFT JOIN still keeps unmatched rows.”
Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.