To create the Horse, Student, and LessonSchedule tables with the specified constraints, you can use the following SQL statements
Create the Horse table
CREATE TABLE Horse (
ID INT UNSIGNED AUTO_INCREMENT,
RegisteredName VARCHAR(15) NOT NULL,
Breed VARCHAR(20) CHECK (Breed IN (‘Egyptian Arab’, ‘Holsteiner’, ‘Quarter Horse’, ‘Paint’, ‘Saddlebred’)),
Height DECIMAL(4, 1) CHECK (Height >= 10.0 AND Height <= 20.0),
BirthDate DATE CHECK (BirthDate >= ‘2015-01-01’),
PRIMARY KEY (ID)
);
[order_button_a]
Create the Student table
CREATE TABLE Student (
ID INT UNSIGNED AUTO_INCREMENT,
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 UNSIGNED CHECK (Zip >= 0 AND Zip <= 16000000) NOT NULL,
Phone CHAR(10) NOT NULL,
Email VARCHAR(30) UNIQUE,
PRIMARY KEY (ID)
);
[order_button_b]
Create the LessonSchedule table with Foreign Key (FK) constraints
CREATE TABLE LessonSchedule (
HorseID INT UNSIGNED NOT NULL,
StudentID INT UNSIGNED,
LessonDateTime DATETIME NOT NULL,
PRIMARY KEY (HorseID, LessonDateTime),
FOREIGN KEY (HorseID) REFERENCES Horse(ID) ON DELETE CASCADE,
FOREIGN KEY (StudentID) REFERENCES Student(ID) ON DELETE SET NULL
);
[order_button_c]
In the LessonSchedule table, HorseID is part of the primary key and a foreign key referencing the ID column in the Horse table. When a row is deleted from the Horse table, the corresponding rows in the LessonSchedule table with the same HorseID will be automatically deleted due to the ON DELETE CASCADE constraint.
Similarly, StudentID is a foreign key referencing the ID column in the Student table. When a row is deleted from the Student table, the corresponding StudentID values in the LessonSchedule table will be automatically set to NULL due to the ON DELETE SET NULL constraint.