A lossless decomposition, checked – Databases Practice

Split a table into third normal form, and prove that nothing was lost.

What to build

Three normalized tables made from enroll_info, their sizes, and the original rows rebuilt by a join.

Requirements

Create and fill students, courses and enrollments from enroll_info.
Show the number of rows in each of the three tables, in one row.
Join the three tables back together and show the rows with the original column names, sorted by course and then student id.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE enroll_info (
  student_id INTEGER NOT NULL,
  student_name TEXT NOT NULL,
  course TEXT NOT NULL,
  course_title TEXT NOT NULL,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);

Output

Two results: columns students, courses and enrollments, then columns student_id, student_name, course, course_title and grade. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Each student has one name and each course one title throughout the table.

Example

When a test starts with:

INSERT INTO enroll_info VALUES (1, 'Rahim', 'CSE101', 'Programming', 'A'), (2, 'Nadia', 'CSE101', 'Programming', 'A+'), (3, 'Karim', 'CSE101', 'Programming', 'B'), (2, 'Nadia', 'MAT101', 'Calculus', 'A');

your SQL should print:

students  courses  enrollments
--------  -------  -----------
3         2        4
(1 row)

student_id  student_name  course  course_title  grade
----------  ------------  ------  ------------  -----
1           Rahim         CSE101  Programming   A
2           Nadia         CSE101  Programming   A+
3           Karim         CSE101  Programming   B
2           Nadia         MAT101  Calculus      A
(4 rows)

Why it works

Splitting on the keys student_id and course is lossless: each enrollment row joins to exactly one student and one course, so the join gives back exactly the original rows, while each name and title is now stored once.

Examples

Example 1 · Input

CREATE TABLE enroll_info (
  student_id INTEGER NOT NULL,
  student_name TEXT NOT NULL,
  course TEXT NOT NULL,
  course_title TEXT NOT NULL,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);
INSERT INTO enroll_info VALUES (1, 'Rahim', 'CSE101', 'Programming', 'A'), (2, 'Nadia', 'CSE101', 'Programming', 'A+'), (3, 'Karim', 'CSE101', 'Programming', 'B'), (2, 'Nadia', 'MAT101', 'Calculus', 'A');

Expected output

students  courses  enrollments
--------  -------  -----------
3         2        4
(1 row)

student_id  student_name  course  course_title  grade
----------  ------------  ------  ------------  -----
1           Rahim         CSE101  Programming   A
2           Nadia         CSE101  Programming   A+
3           Karim         CSE101  Programming   B
2           Nadia         MAT101  Calculus      A
(4 rows)

Example 2 · Input

CREATE TABLE enroll_info (
  student_id INTEGER NOT NULL,
  student_name TEXT NOT NULL,
  course TEXT NOT NULL,
  course_title TEXT NOT NULL,
  grade TEXT,
  PRIMARY KEY (student_id, course)
);
INSERT INTO enroll_info VALUES (1, 'A', 'X', 'Art', NULL);

Expected output

students  courses  enrollments
--------  -------  -----------
1         1        1
(1 row)

student_id  student_name  course  course_title  grade
----------  ------------  ------  ------------  -----
1           A             X       Art           NULL
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Hint 1

Make `students(id, name)`, `courses(code, title)` and `enrollments(student_id, course, grade)`, each with the right primary key.

Hint 2

Fill the first two with SELECT DISTINCT and the third with every row.

Hint 3

Count the rows of each table with three scalar subqueries in one SELECT, then join the three tables back.

Solution

Show one correct solution (try it yourself first)

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course TEXT REFERENCES courses(code),
  grade TEXT,
  PRIMARY KEY (student_id, course)
);

INSERT INTO students (id, name) SELECT DISTINCT student_id, student_name FROM enroll_info;
INSERT INTO courses (code, title) SELECT DISTINCT course, course_title FROM enroll_info;
INSERT INTO enrollments (student_id, course, grade) SELECT student_id, course, grade FROM enroll_info;

SELECT (SELECT COUNT(*) FROM students) AS students,
  (SELECT COUNT(*) FROM courses) AS courses,
  (SELECT COUNT(*) FROM enrollments) AS enrollments;

SELECT s.id AS student_id, s.name AS student_name, c.code AS course, c.title AS course_title, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.code = e.course
ORDER BY course, student_id;

Lesson: Normalization →