Many-to-many relationship

Jan 16, 2022

Overview

Convert to two one-to-many relationships adding a junction table:

STUDENT           ENROLLMENT        COURSE
student_id  ―――→  student_id        title
name              course_id  ←――――  course_id
                  grade

Implementation in sqlite:

PRAGMA foreign_keys = ON;

CREATE TABLE student (
    student_id INTEGER PRIMARY KEY,
	name TEXT NOT NULL UNIQUE
);

CREATE TABLE course (
    course_id INTEGER PRIMARY KEY,
	title TEXT NOT NULL UNIQUE
);

CREATE TABLE enrollment (
    student_id INTEGER NOT NULL REFERENCES student(student_id),
    course_id INTEGER NOT NULL REFERENCES course(course_id),
    PRIMARY KEY (student_id, course_id)
);

Add students and courses

INSERT INTO student(name) VALUES ('John');
INSERT INTO course(title) VALUES ('Biology');

INSERT INTO student(name) VALUES ('Paul'), ('Mary'), ('Peter');
INSERT INTO course(title) VALUES ('Math'), ('History');

Rename

UPDATE student SET name='John Smith' WHERE name='John';
UPDATE course SET name='Biology 1' WHERE title='Biology';

Remove

DELETE FROM student WHERE name='John Smith';
DELETE FROM course WHERE title='Biology 1';

Enroll a student to a course

INSERT INTO enrollment(student_id, course_id)
VALUES (
    (SELECT student_id FROM student WHERE name='John'),
    (SELECT course_id FROM course WHERE title='Biology')
);

Unenroll a student from a course

DELETE FROM enrollment WHERE
student_id=(SELECT student_id FROM student WHERE name='John') AND
course_id=(SELECT course_id FROM course WHERE title='Biology');

Unenroll a student from all courses

DELETE FROM enrollment WHERE
student_id=(SELECT student_id FROM student WHERE name='John');

Unenroll all students from a course

DELETE FROM enrollment WHERE
course_id=(SELECT course_id FROM course WHERE title='Biology');

Report

All enrolled courses and their enrolled students:

SELECT title, name FROM student
JOIN enrollment USING (student_id)
JOIN course USING (course_id);

Courses without students:

SELECT title FROM course
WHERE course_id NOT IN
(SELECT course_id FROM enrollment);

Students not enrolled in any courses:

SELECT name FROM student
WHERE student_id NOT IN
(SELECT student_id FROM enrollment);