schema.sql

DROP TABLE IF EXISTS registrations;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
    crn TEXT PRIMARY KEY,
    subject TEXT NOT NULL,
    number TEXT NOT NULL,
    title TEXT NOT NULL,
    credits REAL NOT NULL,
    days TEXT,
    time TEXT,
    campus TEXT NOT NULL,
    term TEXT NOT NULL,
    seats_total INTEGER NOT NULL,
    seats_taken INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE registrations (
    student_id INTEGER NOT NULL,
    crn TEXT NOT NULL,
    PRIMARY KEY (student_id, crn),
    FOREIGN KEY(student_id) REFERENCES students(id),
    FOREIGN KEY(crn) REFERENCES courses(crn)
);