Appendices
Appendix H. Sample University Database Schema and Data
The book's running example, complete: the DDL (PostgreSQL form, canonical), the full dataset, the loading notes, and the quick facts every chapter's expected output is built on. The book's "current time" is Fall 2026: sections 11, 12, and 13 run in Fall 2026, and their enrollments are in progress (grade IS NULL); all other sections are past and fully graded.
Student IDs begin with the admission year (21100001 was admitted in 2021). GPA values are cumulative registrar figures and include courses not itemized in enrollment, so they need not equal the average of the listed grades.
DDL (PostgreSQL form; the canonical schema)
CREATE TABLE department (
dept_id INTEGER PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL UNIQUE,
building VARCHAR(30),
budget NUMERIC(12,2) CHECK (budget >= 0) -- MySQL: DECIMAL(12,2)
);
CREATE TABLE student (
student_id INTEGER PRIMARY KEY,
full_name VARCHAR(60) NOT NULL,
major_dept_id INTEGER REFERENCES department(dept_id),
admission_year SMALLINT,
total_credits INTEGER NOT NULL DEFAULT 0,
gpa NUMERIC(3,2) CHECK (gpa BETWEEN 0.00 AND 4.00)
);
CREATE TABLE instructor (
instructor_id INTEGER PRIMARY KEY,
full_name VARCHAR(60) NOT NULL,
dept_id INTEGER REFERENCES department(dept_id),
hire_date DATE,
salary NUMERIC(10,2) CHECK (salary > 0)
);
CREATE TABLE course (
course_id VARCHAR(8) PRIMARY KEY,
title VARCHAR(80) NOT NULL,
dept_id INTEGER REFERENCES department(dept_id),
credits SMALLINT CHECK (credits BETWEEN 1 AND 6)
);
CREATE TABLE course_section (
section_id INTEGER PRIMARY KEY,
course_id VARCHAR(8) NOT NULL REFERENCES course(course_id),
semester VARCHAR(6) CHECK (semester IN ('Fall', 'Spring', 'Summer')),
section_year SMALLINT,
room VARCHAR(20),
capacity SMALLINT DEFAULT 40,
instructor_id INTEGER REFERENCES instructor(instructor_id)
);
CREATE TABLE enrollment (
student_id INTEGER NOT NULL REFERENCES student(student_id),
section_id INTEGER NOT NULL REFERENCES course_section(section_id),
grade CHAR(2),
PRIMARY KEY (student_id, section_id),
CHECK (grade IN ('A+','A','A-','B+','B','B-','C+','C','C-','D','F')
OR grade IS NULL)
);MySQL notes: write NUMERIC as DECIMAL (synonyms, but DECIMAL is the MySQL spelling used in practice); string comparisons and CHAR(2) behave the same for these values; both platforms enforce the CHECK constraints shown (MySQL 8.0.16+). The canonical schema uses explicit integer keys everywhere — no SERIAL/AUTO_INCREMENT — so all expected outputs in the book are deterministic; Chapter 9 explains this choice.
Dataset (load in this order)
INSERT INTO department (dept_id, dept_name, building, budget) VALUES
(1, 'Computer Science and Engineering', 'Academic Building D', 3500000.00),
(2, 'Electrical and Electronic Engineering', 'Academic Building C', 3200000.00),
(3, 'Business Administration', 'Academic Building A', 2800000.00),
(4, 'Mathematics', 'Academic Building B', 1900000.00),
(5, 'English', 'Academic Building A', 1400000.00);
INSERT INTO instructor (instructor_id, full_name, dept_id, hire_date, salary) VALUES
(101, 'Ahmed Kabir', 1, DATE '2018-01-15', 120000.00),
(102, 'Farhana Rahman', 1, DATE '2020-08-01', 105000.00),
(103, 'Nazmul Chowdhury', 2, DATE '2015-03-10', 130000.00),
(104, 'Sharmin Ahmed', 3, DATE '2019-02-20', 98000.00),
(105, 'Mahmudul Islam', 4, DATE '2016-09-05', 110000.00),
(106, 'Tahmina Karim', 5, DATE '2021-01-10', 92000.00);
INSERT INTO student (student_id, full_name, major_dept_id, admission_year, total_credits, gpa) VALUES
(21100001, 'Nusrat Jahan', 1, 2021, 102, 3.75),
(21100002, 'Rakib Hasan', 1, 2021, 104, 3.42),
(21100003, 'Sadia Afrin', 2, 2021, 96, 3.88),
(21100004, 'Imran Hossain', 3, 2022, 72, 3.15),
(21100005, 'Farhan Akter', 4, 2022, 66, 2.98),
(21100006, 'Sumaiya Tabassum', 5, 2023, 48, 3.60),
(21200001, 'Tanvir Alam', 1, 2022, 84, 3.55),
(21200002, 'Mehjabin Chowdhury', 2, 2022, 78, 3.70),
(21300003, 'Arif Mahmud', 1, 2023, 60, 3.90),
(21300004, 'Nabil Khan', 3, 2023, 54, 3.05),
(21500002, 'Shahriar Islam', 2, 2025, 24, 3.25),
(21600001, 'Zara Hossain', 4, 2026, 0, NULL); -- GPA not yet computed
INSERT INTO course (course_id, title, dept_id, credits) VALUES
('CSE215', 'Programming Language II', 1, 3),
('CSE221', 'Database Systems', 1, 3),
('CSE251', 'Data Structures', 1, 3),
('CSE321', 'Web Application Development', 1, 3),
('EEE163', 'Electrical Circuits I', 2, 3),
('EEE221', 'Signals and Systems', 2, 3),
('MAT116', 'Calculus I', 4, 4),
('MAT214', 'Linear Algebra', 4, 3),
('BBA101', 'Principles of Management', 3, 3),
('ENG105', 'Academic Writing', 5, 2);
INSERT INTO course_section (section_id, course_id, semester, section_year, room, capacity, instructor_id) VALUES
(1, 'CSE215', 'Fall', 2024, 'SAC-304', 40, 101),
(2, 'CSE215', 'Spring', 2025, 'SAC-305', 40, 102),
(3, 'CSE221', 'Fall', 2024, 'SAC-401', 35, 102),
(4, 'CSE251', 'Spring', 2025, 'SAC-303', 35, 101),
(5, 'CSE321', 'Spring', 2026, 'SAC-402', 30, 102),
(6, 'EEE163', 'Fall', 2024, 'EAB-201', 45, 103),
(7, 'EEE221', 'Spring', 2025, 'EAB-205', 40, 103),
(8, 'MAT116', 'Fall', 2024, 'MAB-102', 60, 105),
(9, 'MAT214', 'Spring', 2025, 'MAB-201', 40, 105),
(10, 'BBA101', 'Spring', 2025, 'AAB-105', 50, 104),
(11, 'ENG105', 'Fall', 2026, 'AAB-210', 30, 106),
(12, 'CSE221', 'Fall', 2026, 'SAC-401', 35, 102),
(13, 'CSE251', 'Fall', 2026, 'SAC-303', 35, 101);
INSERT INTO enrollment (student_id, section_id, grade) VALUES
(21100001, 1, 'A'),
(21100001, 3, 'A-'),
(21100001, 8, 'B+'),
(21100002, 1, 'B+'),
(21100002, 3, 'B'),
(21100002, 8, 'A'),
(21100003, 6, 'A'),
(21100003, 8, 'A-'),
(21100004, 10, 'B'),
(21100004, 11, NULL),
(21100005, 8, 'C+'),
(21100005, 9, 'A'),
(21100006, 10, 'B'),
(21100006, 11, NULL),
(21200001, 2, 'A-'),
(21200001, 4, 'B+'),
(21200001, 7, 'A'),
(21200002, 6, 'A'),
(21200002, 7, 'A-'),
(21200002, 4, 'B'),
(21300003, 1, 'A'),
(21300003, 12, NULL),
(21300003, 13, NULL),
(21300004, 10, 'B+'),
(21300004, 11, NULL),
(21500002, 11, NULL),
(21500002, 12, NULL),
(21600001, 13, NULL);Quick facts (rely on these for consistent outputs)
- Counts: 5 departments, 6 instructors, 12 students, 10 courses, 13 sections, 28 enrollments.
- 11 students have a GPA;
21600001(Zara Hossain) hasgpa IS NULL— the book's NULL example. - Fall 2026 sections are 11 (
ENG105), 12 (CSE221), 13 (CSE251) with 8 in-progress enrollments (grade IS NULL); the other 20 enrollments are graded. - Section 5 (
CSE321, Spring 2026) has no enrollments — the outer-join and anti-join example. - Instructor 101 (Ahmed Kabir) teaches the most sections (1, 4, 13); instructor 106 (Tahmina Karim) teaches only section 11.
- Highest GPA: 21300003 Arif Mahmud (3.90). Lowest non-NULL GPA: 21100005 Farhan Akter (2.98).
- Department budgets: CSE 3.5M > EEE 3.2M > BBA 2.8M > MAT 1.9M > ENG 1.4M. Average instructor salary by department: EEE highest (130000), ENG lowest (92000).
- Grade-point scale used throughout the book: A = 4.0, A- = 3.7, B+ = 3.3, B = 3.0, B- = 2.7, C+ = 2.3, C = 2.0, C- = 1.7, D = 1.0, F = 0.0 (A+ also counts 4.0 for GPA purposes).
Verification queries
SELECT (SELECT COUNT(*) FROM department) AS departments,
(SELECT COUNT(*) FROM instructor) AS instructors,
(SELECT COUNT(*) FROM student) AS students,
(SELECT COUNT(*) FROM course) AS courses,
(SELECT COUNT(*) FROM course_section) AS sections,
(SELECT COUNT(*) FROM enrollment) AS enrollments;
-- 5 | 6 | 12 | 10 | 13 | 28
SELECT COUNT(*) FROM enrollment WHERE grade IS NULL; -- 8
SELECT COUNT(*) FROM student WHERE gpa IS NULL; -- 1Every table, row, and expected output in Parts III–VI of this book is consistent with this dataset; chapters cite tables by these exact names and columns.