(original) (raw)

-- version 0.6.1 CREATE TABLE IF NOT EXISTS Students ( student_id VARCHAR(20) NOT NULL PRIMARY KEY, name VARCHAR(50) NOT NULL, surname VARCHAR(100) NOT NULL, class_id VARCHAR(10) NOT NULL REFERENCES Classes(class_id), birthdate DATE NOT NULL, sex VARCHAR(1) NOT NULL, primary_teacher_id INTEGER NOT NULL REFERENCES Teachers(teacher_id), primary_instrument VARCHAR(50) NOT NULL REFERENCES Instruments(name), secondary_teacher_id INTEGER REFERENCES Teachers(teacher_id), secondary_instrument VARCHAR(50) REFERENCES Instruments(name) ); CREATE TABLE IF NOT EXISTS Classes ( class_id VARCHAR(10) NOT NULL PRIMARY KEY, name VARCHAR(10) NOT NULL, cycle VARCHAR(40) NOT NULL, class_teacher_id INTEGER NOT NULL REFERENCES Teachers(teacher_id) ); CREATE TABLE IF NOT EXISTS Teachers ( teacher_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(50) NOT NULL, surname VARCHAR(100) NOT NULL, degree VARCHAR(20), position VARCHAR(100) NOT NULL ); CREATE TABLE IF NOT EXISTS Instruments ( name VARCHAR(50) NOT NULL PRIMARY KEY, section_id VARCHAR(100) NOT NULL REFERENCES Sections(name) ); CREATE TABLE IF NOT EXISTS Subjects ( name VARCHAR(100) NOT NULL PRIMARY KEY ); CREATE TABLE IF NOT EXISTS Sections ( name VARCHAR(100) NOT NULL PRIMARY KEY, supervisor_id INTEGER NOT NULL REFERENCES Teachers(teacher_id) ); CREATE TABLE IF NOT EXISTS Schedule ( lesson_id INTEGER NOT NULL PRIMARY KEY, teacher_id INTEGER NOT NULL REFERENCES Teachers(teacher_id), day VARCHAR(15) NOT NULL, start TIME NOT NULL, lenght INTEGER NOT NULL, room VARCHAR(20) NOT NULL, type VARCHAR(20) NOT NULL ); CREATE TABLE IF NOT EXISTS PersonalLessons ( lesson_id INTEGER NOT NULL PRIMARY KEY REFERENCES Schedule(lesson_id), student_id VARCHAR(20) NOT NULL REFERENCES Students(student_id), subject_id VARCHAR(100) NOT NULL REFERENCES Subjects(name) ); CREATE TABLE IF NOT EXISTS ClassLessons ( lesson_id INTEGER NOT NULL PRIMARY KEY REFERENCES Schedule(lesson_id), class_id VARCHAR(10) NOT NULL REFERENCES Classes(class_id), subject_id VARCHAR(100) NOT NULL REFERENCES Subjects(name) ); CREATE TABLE IF NOT EXISTS EnsembleLessons ( lesson_id INTEGER NOT NULL PRIMARY KEY REFERENCES Schedule(lesson_id), ensemble_id VARCHAR(100) NOT NULL REFERENCES Ensembles(name) ); CREATE TABLE IF NOT EXISTS Ensembles ( name VARCHAR(100) NOT NULL PRIMARY KEY, teacher_id INTEGER NOT NULL REFERENCES Teachers(teacher_id) ); CREATE TABLE IF NOT EXISTS EnsemblesMembers ( ensemble_id VARCHAR(100) NOT NULL REFERENCES Ensembles(name), student_id INTEGER NOT NULL REFERENCES Students(student_id), PRIMARY KEY(ensemble_id, student_id) ); CREATE VIEW IF NOT EXISTS PersonalSchedule AS SELECT * FROM Schedule NATURAL JOIN PersonalLessons; CREATE VIEW IF NOT EXISTS ClassSchedule AS SELECT * FROM Schedule NATURAL JOIN ClassLessons; CREATE VIEW IF NOT EXISTS EnsembleSchedule AS SELECT * FROM Schedule NATURAL JOIN EnsembleLessons; CREATE TRIGGER IF NOT EXISTS PS_dis INSTEAD OF INSERT ON PersonalSchedule BEGIN INSERT INTO Schedule VALUES( NEW.lesson_id, NEW.teacher_id, NEW.day, NEW.start, NEW.lenght, NEW.room, NEW.type ); INSERT INTO PersonalLessons VALUES( NEW.lesson_id, NEW.student_id, NEW.subject_id ); END; CREATE TRIGGER IF NOT EXISTS CS_dis INSTEAD OF INSERT ON ClassSchedule BEGIN INSERT INTO Schedule VALUES( NEW.lesson_id, NEW.teacher_id, NEW.day, NEW.start, NEW.lenght, NEW.room, NEW.type ); INSERT INTO ClassLessons VALUES( NEW.lesson_id, NEW.class_id, NEW.subject_id ); END; CREATE TRIGGER IF NOT EXISTS ES_dis INSTEAD OF INSERT ON EnsembleSchedule BEGIN INSERT INTO Schedule VALUES( NEW.lesson_id, NEW.teacher_id, NEW.day, NEW.start, NEW.lenght, NEW.room, NEW.type ); INSERT INTO EnsembleLessons VALUES( NEW.lesson_id, NEW.ensemble_id ); END;