My query
Ciugiu
sql
a year ago
2.4 kB
7
Indexable
CREATE TABLE Programs ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE Students ( id INT PRIMARY KEY, name VARCHAR(100), contact_info VARCHAR(100), status VARCHAR(10) CHECK (status IN ('candidate', 'recruited', 'student')), payment_status VARCHAR(8) CHECK (payment_status IN ('not paid', 'paid')) ); CREATE TABLE Courses ( id INT PRIMARY KEY, name VARCHAR(100), duration INT ); CREATE TABLE Teachers ( id INT PRIMARY KEY, name VARCHAR(100), contact_info VARCHAR(100), bank_account VARCHAR(100), email VARCHAR(100) UNIQUE ); CREATE TABLE Sessions ( id INT PRIMARY KEY, course_id INT, teacher_id INT, room VARCHAR(50), start_datetime TIMESTAMP, end_datetime TIMESTAMP, type VARCHAR(14) CHECK (type IN ('on-campus', 'online', 'practical work', 'exam')), FOREIGN KEY (course_id) REFERENCES Courses(id), FOREIGN KEY (teacher_id) REFERENCES Teachers(id) ); CREATE TABLE Admins ( id INT PRIMARY KEY, username VARCHAR(50) UNIQUE, password VARCHAR(100) ); CREATE TABLE ProgramCourses ( program_id INT, course_id INT, PRIMARY KEY (program_id, course_id), FOREIGN KEY (program_id) REFERENCES Programs(id), FOREIGN KEY (course_id) REFERENCES Courses(id) ); CREATE TABLE Intakes ( id INT PRIMARY KEY, year INT, number INT ); CREATE TABLE StudentIntakes ( student_id INT, intake_id INT, PRIMARY KEY (student_id, intake_id), FOREIGN KEY (student_id) REFERENCES Students(id), FOREIGN KEY (intake_id) REFERENCES Intakes(id) ); CREATE TABLE Attendance ( id INT PRIMARY KEY, session_id INT, student_id INT, status VARCHAR(7) CHECK (status IN ('present', 'absent')), FOREIGN KEY (session_id) REFERENCES Sessions(id), FOREIGN KEY (student_id) REFERENCES Students(id) ); CREATE TABLE Exams ( id INT PRIMARY KEY, course_id INT, type VARCHAR(16) CHECK (type IN ('quiz', 'practical work', 'project', 'oral presentation')), weight DECIMAL(5,2), FOREIGN KEY (course_id) REFERENCES Courses(id) ); CREATE TABLE ExamResults ( id INT PRIMARY KEY, exam_id INT, student_id INT, grade DECIMAL(5,2), FOREIGN KEY (exam_id) REFERENCES Exams(id), FOREIGN KEY (student_id) REFERENCES Students(id) );
Editor is loading...
Leave a Comment