Setting Up DB Schema
University Schema
This walkthrough builds a relational schema for a fictional university so you can practice creating tables, defining constraints, and loading reference data before running analytical queries. The model illustrates common patterns such as departmental hierarchies, instructor ownership, and the many-to-many relationship between students and courses.
Entity Relationship Diagram
The diagram highlights a few important design calls:
- Departments own both instructors and courses, keeping organizational context in one place.
- Students optionally link to departments, which is useful for undeclared majors.
- Enrollment acts as a bridge table to model the many-to-many relationship between students and courses while preserving semester and grading details.
Create Tables
Run the DDL from top to bottom so foreign key targets exist before dependencies. If you are re-running the script, reset the environment by dropping the existing tables from the leaf nodes inward.
Drop existing tables safely
DROP TABLE IF EXISTS enrollment;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS instructor;
DROP TABLE IF EXISTS department;
1. Department
Each department stores a unique name and serves as the parent table for instructors, courses, and (optionally) students.
CREATE TABLE department (
dept_id INTEGER PRIMARY KEY,
dept_name TEXT NOT NULL UNIQUE
);
2. Student
Students capture contact details plus the year of first enrollment. The dept_id foreign key is nullable so you can represent undeclared majors or exploratory students.
CREATE TABLE student (
student_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT UNIQUE,
dept_id INTEGER,
enrollment_year INTEGER,
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
3. Instructor
Instructors attach to exactly one department. Email is optional but should be unique when provided.
CREATE TABLE instructor (
instructor_id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT UNIQUE,
dept_id INTEGER NOT NULL,
FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
4. Course
Courses belong to a department and, when assigned, reference the primary instructor. Default credits are set to three to reduce boilerplate for standard lecture courses.
CREATE TABLE course (
course_id INTEGER PRIMARY KEY,
course_code TEXT NOT NULL UNIQUE,
course_title TEXT NOT NULL,
credits INTEGER NOT NULL DEFAULT 3,
dept_id INTEGER NOT NULL,
instructor_id INTEGER,
FOREIGN KEY (dept_id) REFERENCES department(dept_id),
FOREIGN KEY (instructor_id) REFERENCES instructor(instructor_id)
);
5. Enrollment
Enrollment records connect students to the specific course offerings they take each term. The grade column allows nulls so in-progress courses can be tracked alongside completed work.
CREATE TABLE enrollment (
enroll_id INTEGER PRIMARY KEY,
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
semester TEXT NOT NULL,
grade TEXT,
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (course_id) REFERENCES course(course_id)
);
Sample Data
Use the following inserts to seed a richer dataset. The rows are ordered so surrogate keys line up with the relationships illustrated above. Wrap the statements in a transaction if you want an easy rollback while experimenting.
Departments
INSERT INTO department (dept_name) VALUES
('Computer Science'),
('Information Technology'),
('Electrical Engineering'),
('Business Administration'),
('Mechanical Engineering'),
('Mathematics');
Instructors
INSERT INTO instructor (first_name, last_name, email, dept_id) VALUES
('Rakesh', 'Mehta', 'rakesh.mehta@univ.local', 1),
('Sonia', 'Kapoor', 'sonia.kapoor@univ.local', 1),
('Imran', 'Khan', 'imran.khan@univ.local', 2),
('Meera', 'Nair', 'meera.nair@univ.local', 4),
('Vivek', 'Rao', 'vivek.rao@univ.local', 3),
('Anjali', 'Gupta', 'anjali.gupta@univ.local', 1),
('Farah', 'Siddiqui', 'farah.siddiqui@univ.local', 6),
('Neeraj', 'Batra', 'neeraj.batra@univ.local', 5);
Students
INSERT INTO student (first_name, last_name, email, dept_id, enrollment_year) VALUES
('Aman', 'Sharma', 'aman.sharma@students.local', 1, 2024),
('Priya', 'Singh', 'priya.singh@students.local', 1, 2023),
('Rohit', 'Verma', 'rohit.verma@students.local', 2, 2024),
('Kiran', 'Patel', 'kiran.patel@students.local', 4, 2022),
('Sana', 'Ali', 'sana.ali@students.local', 1, 2025),
('Deepak', 'Joshi', 'deepak.joshi@students.local', 3, 2023),
('Alisha', 'Fernandez', 'alisha.fernandez@students.local',5, 2022),
('Tarun', 'Iyer', 'tarun.iyer@students.local', 3, 2024),
('Nisha', 'Desai', 'nisha.desai@students.local', 6, 2025),
('Harish', 'Kulkarni', 'harish.kulkarni@students.local',1, 2023),
('Gauri', 'Menon', 'gauri.menon@students.local', 4, 2024),
('Kabir', 'Sethi', 'kabir.sethi@students.local', 2, 2022);
Courses
INSERT INTO course (course_code, course_title, credits, dept_id, instructor_id) VALUES
('CS101', 'Introduction to Programming', 4, 1, 1),
('CS201', 'Data Structures', 4, 1, 2),
('IT105', 'Database Systems', 3, 2, 3),
('BA110', 'Principles of Management', 3, 4, 4),
('CS210', 'Computer Networks', 3, 1, 1),
('EE120', 'Circuits and Systems', 3, 3, 5),
('CS305', 'Algorithms', 4, 1, 6),
('MATH115','Discrete Mathematics', 3, 6, 7),
('ME130', 'Thermodynamics', 3, 5, 8),
('IT210', 'Cloud Infrastructure', 3, 2, 3),
('BA205', 'Business Analytics', 3, 4, 4);
Enrollments
INSERT INTO enrollment (student_id, course_id, semester, grade) VALUES
(1, 1, 'Spring 2025', 'A'),
(1, 2, 'Spring 2025', 'B+'),
(1, 7, 'Fall 2025', NULL),
(2, 1, 'Spring 2025', 'A-'),
(2, 3, 'Spring 2025', 'B'),
(2, 10, 'Fall 2025', NULL),
(3, 3, 'Fall 2024', 'B'),
(3, 10, 'Spring 2025', 'A'),
(3, 5, 'Spring 2025', 'B+'),
(4, 4, 'Fall 2024', 'A'),
(4, 11, 'Spring 2025', 'B'),
(5, 1, 'Spring 2025', NULL),
(5, 5, 'Spring 2025', NULL),
(6, 6, 'Spring 2025', 'A-'),
(6, 8, 'Spring 2025', 'B'),
(7, 9, 'Fall 2024', 'B+'),
(7, 6, 'Spring 2025', NULL),
(8, 6, 'Fall 2024', 'A-'),
(8, 5, 'Spring 2025', 'B+'),
(9, 8, 'Spring 2025', 'A'),
(9, 7, 'Spring 2025', 'A-'),
(10, 1, 'Spring 2024', 'A'),
(10, 7, 'Spring 2025', 'B+'),
(11, 4, 'Spring 2025', 'A-'),
(11, 5, 'Spring 2025', NULL),
(12, 3, 'Fall 2024', 'B+'),
(12,10, 'Spring 2025', 'A-');
Once the seed data is in place, try joining student, enrollment, and course to confirm the relationships, or group enrollments by department to practise aggregate queries.