My query

 avatar
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