Cardinality and participation

Part of the free LogicWiz SQL & Databases course, Module 9: ER modelling. The queries and practice run in the app on real Postgres.

Meher adds a course_id column to students. It works fine until Aarav enrols in his second course. Where should "who takes what" really live?

You'll learn to

• Read 1:1, 1:N and M:N relationships
• Put the foreign key on the right side, or build a junction table
• Tell total participation from partial, and enforce it with NOT NULL
• Answer "minimum number of tables" questions

Learn

Cardinality: how many on each side?

Cardinality says how many entities on one side can link to one entity on the other.

• 1:1 (one-to-one): a student has at most one hostel locker, and a locker belongs to at most one student.
• 1:N (one-to-many): a department has many students; each student is in at most one department.
• M:N (many-to-many): a student takes many courses, and a course has many students.

To find it, ask the question twice, once from each side: "One student, how many courses?" and "One course, how many students?"

The college, as an ER diagram

M:N lives in a junction table

SELECT s.name, c.title, e.marks
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c ON c.course_id = e.course_id
WHERE e.student_id IN (101, 102)
ORDER BY s.name, c.title;

enrollments has one row per (student, course) pair, and its primary key is that pair. The relationship's own attributes, semester and marks, live here too: marks belong to neither the student alone nor the course alone.

Participation: must everyone take part?

Total participation means every entity must be in the relationship: every course is offered by some department. It is drawn as a double line, and in SQL it becomes a NOT NULL foreign key (courses.dept_id INT NOT NULL).

Partial participation means some entities may stay out: a student may not have picked a department yet. It is a single line and a nullable foreign key.

Participation is read for each side separately. A department may have no students (partial on that side) even if every student had a department.

Partial participation, on both sides

SELECT 'student with no department' AS what, name
FROM students
WHERE dept_id IS NULL
UNION ALL
SELECT 'department with no students', d.name
FROM departments d
LEFT JOIN students s ON s.dept_id = d.dept_id
WHERE s.student_id IS NULL;

Manish has no department (students.dept_id allows NULL), and Mathematics has no students. Both are legal because participation is partial on both sides of "belongs to".

1:1 is a foreign key plus UNIQUE

CREATE TABLE lockers (
  locker_no   INT PRIMARY KEY,
  student_id  INT UNIQUE REFERENCES students(student_id)
);
INSERT INTO lockers VALUES (1, 101), (2, 102);
INSERT INTO lockers VALUES (3, 101);

The last insert fails: Aarav already has a locker. Without UNIQUE, the same foreign key would allow many lockers per student, which is 1:N. Remove UNIQUE and run it again to see.

Never store an M:N relationship as a column on one side. students.course_id allows only one course per student, and adding a second copy of the student row to fit another course repeats the name, city and CGPA. M:N always needs a junction table whose key is the pair.

Minimum number of tables for two strong entities joined by one relationship (a favourite exam question):

• 1:N → 2 tables (the FK goes into the N side)
• M:N → 3 tables (a junction table)
• 1:1 with partial participation on at least one side → 2 tables
• 1:1 with total participation on both sides → 1 table (merge them)

Each multivalued attribute adds one more table.

In one line

1:N puts the FK on the many side, 1:1 adds UNIQUE, M:N needs a junction table, and total participation means NOT NULL.

Interview corner

How do you model a many-to-many relationship in a relational database?

With a junction (bridge) table that holds the two foreign keys, for example enrollments(student_id, course_id), with the pair as the primary key. Attributes of the relationship itself, like marks or quantity, go in that table.

In a one-to-many relationship, which table gets the foreign key?

The "many" side. Each employee row has one dept_id, so the FK goes in employees. Putting it on the "one" side would allow only one child per parent.

How do you enforce a one-to-one relationship?

A foreign key with a UNIQUE constraint on it (or make the FK also the primary key of the child table). Add NOT NULL if participation on that side is total.