Break Things Challenge: Data Constraints Save the Day! 🛡️

-- The worst database design ever - NO constraints!
CREATE TABLE bad_students (
    id INTEGER,
    name VARCHAR(100),
    email VARCHAR(100),
    age INTEGER
);

CREATE TABLE bad_grades (
    student_id INTEGER,
    grade INTEGER
);

-- Task 1: Duplicate IDs
INSERT INTO bad_students VALUES (1, 'Alice', 'alice@school.com', 15);
INSERT INTO bad_students VALUES (1, 'Bob', 'bob@school.com', 16);
-- What happens? Both get inserted! Chaos!

-- Task 2: Impossible ages
INSERT INTO bad_students VALUES (2, 'Baby', 'baby@school.com', -5);
INSERT INTO bad_students VALUES (3, 'Ancient', 'old@school.com', 999);
-- What happens? All accepted! More chaos!

-- Task 3: Grades gone wild
INSERT INTO bad_grades VALUES (1, 150);  -- 150%?
INSERT INTO bad_grades VALUES (999, 85);  -- Student doesn't exist!
INSERT INTO bad_grades VALUES (NULL, 90);  -- No student?
-- What happens? Total anarchy!

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

-- Setup: Insert a valid student
INSERT INTO students VALUES (1, 'Alice', 'alice@school.com', 15);

-- Attack 1: Duplicate primary key
INSERT INTO students VALUES (1, 'Evil Twin', 'twin@school.com', 15);
-- Result: ERROR! PRIMARY KEY constraint failed

-- Attack 2: NULL primary key
INSERT INTO students VALUES (NULL, 'Ghost', 'ghost@school.com', 20);
-- Result: ERROR! NOT NULL constraint failed

-- Attack 3: String as ID
INSERT INTO students VALUES ('ABC', 'Hacker', 'hack@school.com', 18);
-- Result: ERROR! datatype mismatch

CREATE TABLE protected_students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 5 AND age <= 25),
    gpa DECIMAL(3,2) CHECK (gpa >= 0.0 AND gpa <= 4.0)
);

-- Attack 1: Age violations
INSERT INTO protected_students VALUES (1, 'Baby', 'baby@school.com', 2, 3.5);
-- Result: CHECK constraint failed: age >= 5 AND age <= 25

-- Attack 2: GPA madness
INSERT INTO protected_students VALUES (2, 'Genius', 'genius@school.com', 18, 5.0);
-- Result: CHECK constraint failed: gpa >= 0.0 AND gpa <= 4.0

-- Attack 3: Duplicate emails
INSERT INTO protected_students VALUES (3, 'Student1', 'same@school.com', 16, 3.0);
INSERT INTO protected_students VALUES (4, 'Student2', 'same@school.com', 17, 3.2);
-- Result: UNIQUE constraint failed

-- Teachers table
CREATE TABLE teachers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subject VARCHAR(50) NOT NULL
);

-- Courses with foreign key to teachers
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)
);

-- Enrollments with composite key
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 protected_students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- Setup: Insert valid data
INSERT INTO teachers VALUES (1, 'Ms. Smith', 'Math');
INSERT INTO courses VALUES (1, 'Algebra', 1, 25);
INSERT INTO protected_students VALUES (10, 'John', 'john@school.com', 16, 3.5);

-- Attack 1: Non-existent teacher
INSERT INTO courses VALUES (2, 'Physics', 999, 30);
-- Result: FOREIGN KEY constraint failed

-- Attack 2: Non-existent student enrollment
INSERT INTO enrollments VALUES (999, 1, 85);
-- Result: FOREIGN KEY constraint failed

-- Attack 3: Delete teacher with courses
DELETE FROM teachers WHERE id = 1;
-- Result: FOREIGN KEY constraint failed

-- Attack 4: Duplicate enrollment
INSERT INTO enrollments VALUES (10, 1, 90);
INSERT INTO enrollments VALUES (10, 1, 95);  -- Same student, same course!
-- Result: PRIMARY KEY constraint failed

-- Your mission: Create the tables with proper constraints
CREATE TABLE books (
    -- Your constraints here!
);

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

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

-- Create tables with CASCADE rules
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    dept_id INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
        ON DELETE CASCADE
);

-- Insert test data
INSERT INTO departments VALUES (1, 'Engineering');
INSERT INTO employees VALUES (1, 'Alice', 1);
INSERT INTO employees VALUES (2, 'Bob', 1);

-- The cascade test
DELETE FROM departments WHERE id = 1;
-- What happens to Alice and Bob?