University database example: how it works

What a university database is

A university database stores information about the people, courses, and activities that make up a campus. It keeps records of students, instructors, classes, enrollments, and rooms. The data is organized in tables that are linked by keys. When a student registers for a class, the database records that link without duplicating the same student or course details.

How the tables are built and linked

The simplest design uses four tables:

Table	Primary key	Main fields
Students	student_id	name, major, year
Instructors	instructor_id	name, department
Courses	course_id	title, credits, instructor_id
Enrollments	enrollment_id	student_id, course_id, semester

Students holds one row per person who studies at the university. Instructors holds one row per teacher. Courses lists each class that can be offered; it includes a foreign key instructor_id that points to the instructor who teaches the course. Enrollments records each time a student signs up for a class; it contains two foreign keys, student_id and course_id, that connect the enrollment to the appropriate student and course.

Step-by-step example with small numbers

Imagine a tiny campus with two students, one instructor, and two courses.

Students

• 101, Alice, Computer Science, 2nd year
• 102, Bob, Mathematics, 1st year

Instructors

• 201, Dr. Lee, Computer Science Department

Courses

• CS101, Intro to Programming, 3 credits, instructor 201
• MATH101, Calculus I, 4 credits, instructor 201 (shared teaching load for illustration)

Enrollments

• 301, student 101, course CS101, Fall 2024
• 302, student 102, course MATH101, Fall 2024
• 303, student 101, course MATH101, Fall 2024

A query that asks for all courses taken by Alice follows the links:

1. Find Alice’s student_id (101) in Students.
2. Look in Enrollments for rows with student_id 101. Those rows are 301 and 303.
3. Take the course_id values from those rows (CS101 and MATH101).
4. Retrieve the course titles from Courses using those course_ids. The result shows Alice is enrolled in Intro to Programming and Calculus I.

Normalizing the design

Textbooks start with a single flat list that repeats student names, course titles, and instructor names for every enrollment. That layout violates the first normal form because the same student appears many times. By separating the data into the four tables above we achieve:

• 1NF – each column holds atomic values.
• 2NF – non-key attributes depend on the whole primary key of their table.
• 3NF – no transitive dependencies; for example, the instructor’s department is stored only in Instructors, not repeated in Courses. Normalization reduces storage, eliminates update anomalies, and makes the schema easier to maintain.

Running a simple query without code blocks

Suppose a registrar wants to list every student together with the titles of the courses they are taking in the current semester. The logical steps are:

1. Identify the semester of interest (e.g., Fall 2024).
2. From Enrollments, pick rows whose semester matches.
3. For each selected row, follow the student_id to Students to obtain the student name.
4. Follow the course_id to Courses to obtain the course title.
5. Combine the name and title into a two-column result set. Because the database engine can join the tables internally, the operation is fast even when thousands of rows are involved.

Common beginner mistakes

Mistake	Why it matters
Storing the same data in multiple tables	Leads to inconsistency when one copy is updated but another is not.
Using a non-unique field as a primary key	Makes it impossible to identify a single row reliably.
Forgetting to define foreign keys	Allows rows that refer to non-existent students or courses, breaking the logical model.
Mixing up many-to-many and one-to-many relationships	Results in redundant rows or missing connections; the enrollment table is the correct way to model many-to-many between students and courses.

Students often try to put the instructor name directly in the Courses table instead of a foreign key. That works for a single instructor but fails when a course has multiple instructors or when the instructor changes departments.

Performance tip: indexes

When the database grows, searching the Enrollments table for a particular student_id can become slow. Adding an index on student_id (and optionally on course_id) lets the DBMS locate matching rows without scanning the whole table. Indexes are small auxiliary structures that store the key values in sorted order, dramatically reducing lookup time for frequent queries such as “what courses is a student taking?”

Real-world example: registration portal

A typical campus registration portal uses the same schema behind the scenes. When a student logs in, the system queries the Enrollments table to display current registrations, then joins to Courses to show titles, credits, and meeting times. When the registrar opens a new semester, they insert fresh rows into Enrollments while re-using the existing Students, Instructors, and Courses records. The separation of concerns means that adding a new course does not require touching any student rows, and dropping a student from a class only removes a single enrollment row.

Check yourself

1. Question: If a new student Carol (student_id 103) enrolls in CS101, which tables receive new rows? Answer: A new row is added to Students for Carol (if she was not already recorded) and a new row is added to Enrollments linking 103 to CS101.
2. Question: What problem arises if the instructor_id column is removed from Courses and the instructor name is stored instead? Answer: Changing an instructor’s name or moving them to another department requires updating every course row that contains that name, increasing the chance of errors.
3. Question: How does the database prevent a student from being enrolled in a non-existent course? Answer: By defining course_id in Enrollments as a foreign key that references the primary key of Courses. The DBMS rejects any enrollment row with a course_id that does not exist in Courses.

For a deeper look at the same topic, see the Relata course, which walks through ER modelling, normalization, and SQL queries step by step.