CREATE TABLE Horse ( 
    ID TINYINT AUTO_INCREMENT PRIMARY KEY, 
    RegisteredName VARCHAR(100), 
    Breed CHAR(50), 
    Height DECIMAL (4,1) CHECK (Height BETWEEN 10.0 AND 20.0), -- Height number has 3 significant digits and 1 decimal place must be >= 10.0 and <= 20.0
    BirthDate DATE CHECK (BirthDate >= '2015-01-01') -- BirthDate date must be >= Jan 1, 2015
    ); 

CREATE TABLE Student (
    ID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- Integer with range 0 to 65 thousand
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    Street VARCHAR(50) NOT NULL,
    City VARCHAR(20) NOT NULL,
    State CHAR(2) NOT NULL DEFAULT 'TX',
    Zip INT NOT NULL,
    CHECK (Zip BETWEEN 0 AND 16000000),
    Phone CHAR(10) NOT NULL,
    Email VARCHAR(30) NOT NULL UNIQUE -- Must Be UNIQUE
);

CREATE TABLE LessonSchedule( 
    HorseID TINYINT UNSIGNED, 
    StudentID SMALLINT UNSIGNED, 
    LessonDateTime DATETIME NOT NULL, -- Date/time
    PRIMARY KEY(HorseID, LessonDateTime), 
    FOREIGN KEY (HorseID) REFERENCES Horse(ID) ON DELETE CASCADE, -- If a row is deleted from horse, the rows with the same horse id should be deleted from lessonschedule automatically
    FOREIGN KEY(StudentID) REFERENCES Student(ID) ON DELETE SET NULL -- If a row is deleted from student, the same student ID should be set to null lessonSchedule automatically
    );