Data Definition and Constraints

-- The worst school database ever!
CREATE TABLE students (
    id INTEGER,
    name VARCHAR(100),
    email VARCHAR(100),
    age INTEGER
);

CREATE TABLE courses (
    id INTEGER,
    name VARCHAR(100),
    teacher_id INTEGER
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    grade INTEGER
);

-- Insert duplicate student IDs
INSERT INTO students VALUES (1, 'Alice', 'alice@school.com', 15);
INSERT INTO students VALUES (1, 'Bob', 'bob@school.com', 16);  -- Same ID!

-- Insert invalid age
INSERT INTO students VALUES (2, 'Charlie', 'charlie@school.com', -5);  -- Negative age!

-- Enroll non-existent student
INSERT INTO enrollments VALUES (999, 1, 85);  -- Student 999 doesn't exist!

-- Give impossible grade
INSERT INTO enrollments VALUES (1, 1, 200);  -- 200%? Really?

-- Better: Tables with PRIMARY KEYS
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INTEGER
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    teacher_id INTEGER
);

CREATE TABLE teachers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    subject VARCHAR(50)
);

-- This works
INSERT INTO students VALUES (1, 'Alice', 'alice@school.com', 15);

-- This fails! (Duplicate primary key)
INSERT INTO students VALUES (1, 'Bob', 'bob@school.com', 16);
-- Error: PRIMARY KEY constraint failed

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 5 AND age <= 25)
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    grade INTEGER CHECK (grade >= 0 AND grade <= 100),
    enrollment_date DATE DEFAULT CURRENT_DATE
);

-- These fail with CHECK constraints:
INSERT INTO students VALUES (2, 'Baby', 'baby@school.com', 2);  -- Too young!
INSERT INTO students VALUES (3, 'Grandpa', 'old@school.com', 80);  -- Too old!

-- This fails with UNIQUE constraint:
INSERT INTO students VALUES (4, 'Twin1', 'same@school.com', 15);
INSERT INTO students VALUES (5, 'Twin2', 'same@school.com', 16);  -- Duplicate email!

-- Complete schema with all constraints
CREATE TABLE teachers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subject VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    teacher_id INTEGER NOT NULL,
    max_students INTEGER DEFAULT 30,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 5 AND age <= 25),
    enrollment_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    grade INTEGER CHECK (grade >= 0 AND grade <= 100),
    enrollment_date DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id),  -- Composite key!
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Insert teachers first
INSERT INTO teachers VALUES (1, 'Ms. Smith', 'Mathematics', '2020-09-01');
INSERT INTO teachers VALUES (2, 'Mr. Johnson', 'Science', '2019-08-15');

-- Now add courses (teacher must exist!)
INSERT INTO courses VALUES (1, 'Algebra 101', 1, 25);  -- Works!
INSERT INTO courses VALUES (2, 'Physics', 3, 30);  -- Fails! Teacher 3 doesn't exist

-- Try to delete a teacher who has courses
DELETE FROM teachers WHERE id = 1;  -- Fails! Has dependent courses

-- Add a grades history table
CREATE TABLE grade_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    old_grade INTEGER,
    new_grade INTEGER NOT NULL,
    changed_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reason VARCHAR(200),
    FOREIGN KEY (student_id, course_id) 
        REFERENCES enrollments(student_id, course_id),
    CHECK (new_grade != old_grade)  -- Must be different!
);

-- Add attendance tracking
CREATE TABLE attendance (
    student_id INTEGER,
    course_id INTEGER,
    class_date DATE,
    status VARCHAR(10) CHECK (status IN ('present', 'absent', 'late')),
    PRIMARY KEY (student_id, course_id, class_date),
    FOREIGN KEY (student_id, course_id) 
        REFERENCES enrollments(student_id, course_id)
);

CREATE TABLE classrooms (
    -- Your constraints here!
);

CREATE TABLE schedule (
    -- How do you prevent double-booking?
);

-- Error: FOREIGN KEY constraint failed
INSERT INTO enrollments VALUES (5, 10, 95);

-- Recreate courses with CASCADE
CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    teacher_id INTEGER,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
        ON DELETE SET NULL  -- If teacher leaves, course remains
);

-- Enrollments with CASCADE DELETE
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    grade INTEGER CHECK (grade >= 0 AND grade <= 100),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id)
        ON DELETE CASCADE,  -- If student leaves, remove enrollments
    FOREIGN KEY (course_id) REFERENCES courses(id)
        ON DELETE CASCADE   -- If course canceled, remove enrollments
);

-- Delete a student
DELETE FROM students WHERE id = 1;
-- All their enrollments disappear automatically!

-- Delete a teacher
DELETE FROM teachers WHERE id = 1;
-- Their courses remain but teacher_id becomes NULL