v33bEnqi

1CREATE TABLE IF NOT EXISTS student(studentId varchar(255) NOT NULL, name varchar(255), degreeScheme varchar(255), PRIMARY KEY (studentId));
2CREATE TABLE IF NOT EXISTS staff(staffId varchar(255) NOT NULL, staffName varchar(255), staffGrade varchar(255), PRIMARY KEY (staffId));
3CREATE TABLE IF NOT EXISTS programme(programmeId varchar(255) NOT NULL, programmeName varchar(255), credits varchar(255), PRIMARY KEY(programmeId));
4CREATE TABLE IF NOT EXISTS registered(studentId varchar(255) NOT NULL, programmeId varchar(255) NOT NULL, PRIMARY KEY(studentId, programmeId));
5CREATE TABLE IF NOT EXISTS teaches(staffId varchar(255) NOT NULL, programmeId varchar(255) NOT NULL, PRIMARY KEY(staffId, programmeId));
6INSERT INTO student (studentId, name, degreeScheme) VALUES ('S10345', 'John Smith', 'BSc Computer Science'), ('S10346', 'Sian Evans', 'BSc Computer Science'), ('S10347', 'Sean Crossan', 'BSc Elec Engineering'), ('S10348', 'Siobhan McChristy', 'BSc Mathematics');
7INSERT INTO staff (staffId, staffName, staffGrade) VALUES ('E10010', 'Jim O’Donovan', 'Senior Lecturer'), ('E10011', 'Neil Batchelor', 'Reader'), ('E10012', 'Steve Marriott', 'Lecturer');
8INSERT INTO programme (programmeId, programmeName, credits) VALUES ('CS101', 'Introduction to Computing', '10'), ('CS203', 'Data Structures and Algorithms', '10'), ('CS204', 'Computer Architecture', '10'), ('M101', 'Mathematics I', '20');
9INSERT INTO registered (studentId, programmeId) VALUES ('S10345', 'CS101'), ('S10346', 'CS203'), ('S10346','CS204'), ('S10347','CS204'), ('S10348', 'M101'), ('S10348','C101');
10INSERT INTO teaches (staffId, programmeId) VALUES ('E10010', 'CS101'), ('E10011', 'CS203'), ('E10012','CS204'), ('E10010','CS204'), ('E10011','M101'), ('E10011','C101');
11ALTER TABLE student ADD FOREIGN KEY (StudentId) REFERENCES registered(studentId) ON DELETE CASCADE ON UPDATE CASCADE ;
12ALTER TABLE staff ADD FOREIGN KEY (staffId) REFERENCES teaches(staffId) ON DELETE CASCADE ON UPDATE CASCADE ;
13ALTER TABLE programme ADD FOREIGN KEY (programmeId) REFERENCES registered(programmeId) ON DELETE CASCADE ON UPDATE CASCADE ;
14ALTER TABLE registered ADD FOREIGN KEY (studentId) REFERENCES student(studentId) ON DELETE CASCADE ON UPDATE CASCADE ;
15ALTER TABLE registered ADD FOREIGN KEY (programmeId) REFERENCES programme(programmeId) ON DELETE CASCADE ON UPDATE CASCADE ;
16ALTER TABLE teaches ADD FOREIGN KEY (staffId) REFERENCES staff(staffId) ON DELETE CASCADE ON UPDATE CASCADE ;
17ALTER TABLE teaches ADD FOREIGN KEY (programmeId) REFERENCES programme(programmeId) ON DELETE CASCADE ON UPDATE CASCADE ;