Lecture 41: Final Project – Creating the Tables

This lecture covers creating the tables for the student enrollment system.

With our database schema designed, it’s time to translate it into SQL. We will write the CREATE TABLE statements for our four tables: `Instructors`, `Students`, `Courses`, and `Enrollments`.

Remember, we must create the parent tables before the child tables that reference them. The correct order is `Instructors` and `Students` first, then `Courses` (which references `Instructors`), and finally `Enrollments` (which references both `Students` and `Courses`).

1. The `Instructors` Table

This is a simple parent table with no foreign keys.

CREATE TABLE Instructors (
    InstructorID INTEGER PRIMARY KEY,
    InstructorName TEXT NOT NULL,
    Email TEXT NOT NULL UNIQUE
);

2. The `Students` Table

This is another parent table, similar to `Instructors`.

CREATE TABLE Students (
    StudentID INTEGER PRIMARY KEY,
    StudentName TEXT NOT NULL,
    Major TEXT
);

3. The `Courses` Table

This table references the `Instructors` table, creating a one-to-many relationship.

CREATE TABLE Courses (
    CourseID INTEGER PRIMARY KEY,
    CourseName TEXT NOT NULL,
    Department TEXT NOT NULL,
    Credits INTEGER CHECK(Credits > 0),
    Instructor_ID INTEGER,
    FOREIGN KEY (Instructor_ID) REFERENCES Instructors(InstructorID)
);

4. The `Enrollments` Table

This is our junction table that connects `Students` and `Courses`, creating a many-to-many relationship.

CREATE TABLE Enrollments (
    EnrollmentID INTEGER PRIMARY KEY,
    Student_ID INTEGER NOT NULL,
    Course_ID INTEGER NOT NULL,
    Grade TEXT CHECK(Grade IN ('A', 'B', 'C', 'D', 'F', 'W')),
    FOREIGN KEY (Student_ID) REFERENCES Students(StudentID),
    FOREIGN KEY (Course_ID) REFERENCES Courses(CourseID)
);

We’ve used a CHECK constraint on the `Grade` column to ensure that only valid letter grades (or a ‘W’ for Withdrawn) can be entered. This is a great way to enforce business rules at the database level.

In the Next Lecture…

Our database structure is now fully implemented. In the next lecture, we will populate these tables with sample data, which will allow us to start writing realistic and interesting queries against our new system.

Lecture 40: Student Enrollment System