Lecture 40: Student Enrollment System

This lecture begins the design of a student enrollment system. Welcome to Part 5 of the series and our final project. We will build a database for a simple student enrollment system. This project will combine everything we have learned, from database design and normalization to creating tables, defining constraints, and writing complex queries.

As always, the first and most important step is to design our database schema.

Step 1: Identifying the Entities

What are the core concepts in a student enrollment system?

• Students: The people attending the school.
• Courses: The classes that are offered.
• Instructors: The people who teach the courses.
• Enrollments: The record that a specific student is taking a specific course.

These will be our main tables.

Step 2: Defining Attributes and Relationships

Now let’s think about the information and connections for each entity.

Instructors Table

This is a simple parent table.

• InstructorID (PK)
• InstructorName
• Email

Courses Table

A course is taught by one instructor, but an instructor can teach many courses. This is a one-to-many relationship.

• CourseID (PK)
• CourseName
• Department
• Credits
• InstructorID (FK to Instructors)

Students Table

Another simple parent table.

• StudentID (PK)
• StudentName
• Major

Enrollments Table

This is the most important table. A student can enroll in many courses, and a course can have many students enrolled in it. This is a many-to-many relationship. To handle this, we need a “junction table”, which is our `Enrollments` table.

• EnrollmentID (PK)
• StudentID (FK to Students)
• CourseID (FK to Courses)
• Grade (e.g., ‘A’, ‘B’, ‘C’)

Our Final Schema

Instructors (InstructorID, Name, Email)

Students (StudentID, Name, Major)

Courses (CourseID, Name, Department, Credits, InstructorID)

Enrollments (EnrollmentID, StudentID, CourseID, Grade)

Relationships:

• Instructors -> Courses (One-to-Many)
• Students -> Enrollments (One-to-Many)
• Courses -> Enrollments (One-to-Many)

In the Next Lecture…

With our design complete, we will move on to implementation. We will write the CREATE TABLE statements for all four tables, making sure to include all the primary keys, foreign keys, and other constraints needed to build a robust and reliable database.

Lecture 39: Ensuring Data Integrity with Constraints