-- TINYINT 
-- Signed -128 to 126 
-- Unsigned 0 to 255 

-- SMALLINT 
-- Signed -32,768 to 32,767 
-- Unsigned 0 to 65,535 

-- MEDIUMINT 
-- Signed -8,388,608 to 8,388,607 
-- Unsigned 0 to 16,777,215 

-- BIGINT 
-- Signed  -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
-- Unsigned 0 to 18,446,744,073,709,551,615

-- These ranges are based on the number of bytes each type uses: 
-- SMALLINT 2 Bytes 
-- MEDIUMINT 3 Bytes 
-- BIGINT 8 Bytes



-- Student

-- ID – Integer ranging from 0 to 65,535, Primary Key
-- Name – Variable-length string up to 150 characters
-- Age – Integer
-- Program_ID – Integer, Foreign Key referencing Program(ID), indicating which program the student belongs to

-- Course

-- Course_ID – Positive integer up to 255, Primary Key
-- Course_Name – Variable-length string up to 150 characters
-- Program_ID – Integer, Foreign Key referencing Program(ID), indicating which program it belongs to
-- Instructor_ID – Integer, Foreign Key referencing Instructor(InstructorID), indicating who teaches the course

-- Create the Table:

-- Registration

-- Registration_ID – Primary Key, Auto Increment, Integer ranging from 0 to 16,777,215.
-- Student_ID – Foreign Key referencing Student(ID), If a ID in the Student Table is deleted the associated registrations should also be deleted.
-- Course_ID – Foreign Key referencing Course(Course_ID). If a ID in the Course Table is deleted the associated registrations should also be deleted.
-- Status – Variable-length string with max 15 characters, must be 'Enrolled', 'Completed', or 'Dropped'. Cannot be null.
-- Registration_Date – Date, must be ≥ Jan 1, 2020


CREATE TABLE Student( 
    ID SMALLINT PRIMARY KEY, 
    Name VARCHAR(150), 
    Age INT,
    Program_ID INT, 
    FOREIGN KEY(Program_ID) REFERENCES Program(ID)
    );

CREATE TABLE Course ( 
    Course_ID TINYINT UNSIGNED PRIMARY KEY, 
    Course_Name VARCHAR(150), 
    Program_ID INT, 
    Instructor_ID INT, 
    FOREIGN KEY (Program_ID) REFERENCES Program(ID), 
    FOREIGN KEY (Instructor_ID) REFERENCES Instructor(Instructor_ID)
    );

CREATE TABLE Registration( 
    Registration_ID MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
    Student_ID SMALLINT UNSIGNED, 
    Course_ID TINYINT UNSIGNED, 
    Status VARCHAR(15) NOT NULL CHECK(Status IN('Enrolled', 'Completed', 'Dropped')), 
    Registration_Date DATE CHECK( Registration_Date >= '2020-01-01'), 
    FOREIGN KEY (Student_ID) REFERENCES Student(ID) ON DELETE CASCADE, 
    FOREIGN KEY (Course_ID) REFERENCES Course(Course_ID) ON DELETE CASCADE 
    );