Categories
MySQL

3.8 LAB – Create LessonSchedule table with FK constraints Two tables are created

Swamped with your writing assignments? Take the weight off your shoulder!

Submit your assignment instructions

3.8 LAB – Create LessonSchedule table with FK constraints
Two tables are created:
Horse with columns:ID – integer, primary key
RegisteredName – variable-length string
Student with columns:ID – integer, primary key
FirstName – variable-length string
LastName – variable-length string
Create the LessonSchedule table with columns:
HorseID – integer with range 0 to 65 thousand, not NULL, partial primary key, foreign key references Horse(ID)
StudentID – integer with range 0 to 65 thousand, foreign key references Student(ID)
LessonDateTime – date/time, not NULL, partial primary key
If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically.
If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.
CREATE TABLE horse (
ID smallint unsigned auto_increment,
RegisteredName Varchar(15),
Primary key (ID)
);
Create table student(
ID Smallint unsigned auto_increment,
Firstname Varchard(20),
Lastname Varchar(30),
Primary Key (ID)
);
–your sql statements go here
create table lessonschedule (
horseID smallint unsigned not null,
studentid smallint unsigned,
lessondatetime datetime not null,
primary key (horseid) references horse(ID)
on delete cascade
foreign key (studentid) refences student(ID)
on delete set null
);

Swamped with your writing assignments? Take the weight off your shoulder!

Submit your assignment instructions