Database Implementation

Preview

Tables

-- Create table: faculties
CREATE TABLE faculties (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50),
    Courses INT,
    Attendees INT,
    -- Note: Faculty information
    CONSTRAINT uc_faculty_name UNIQUE (Name)
);

-- Create table: courses
CREATE TABLE courses (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50),
    Faculty VARCHAR(50),
    Duration INT,
    Attendees INT,
    -- Note: Course information
    CONSTRAINT fk_courses_faculty FOREIGN KEY (Faculty) REFERENCES faculties(Name)
);

-- Create table: students
CREATE TABLE students (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50),
    Surname VARCHAR(50),
    Age INT,
    Gender VARCHAR(6),
    Course VARCHAR(50),
    Started VARCHAR(25),
    Graduation VARCHAR(25),
    Semester INT,
    -- Note: Student information
    CONSTRAINT fk_students_course FOREIGN KEY (Course) REFERENCES courses(Name)
);

-- Create table: department
CREATE TABLE department (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50),
    FacultyID INT,
    Budget INT,
    -- Note: Department within a faculty
    CONSTRAINT fk_department_faculty FOREIGN KEY (FacultyID) REFERENCES faculties(ID)
);

-- Create table: exam
CREATE TABLE exam (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    CourseID INT,
    Date DATE,
    ExamType VARCHAR(50),
    Duration INT,
    -- Note: Information about the exams
    CONSTRAINT fk_exam_course FOREIGN KEY (CourseID) REFERENCES courses(ID)
);

-- Create table: grade
CREATE TABLE grade (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    ExamID INT,
    Grade VARCHAR(2),
    -- Note: Student grades for exams
    CONSTRAINT fk_grade_student FOREIGN KEY (StudentID) REFERENCES students(ID),
    CONSTRAINT fk_grade_exam FOREIGN KEY (ExamID) REFERENCES exam(ID)
);

-- Create table: class
CREATE TABLE class (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    CourseID INT,
    RoomNumber VARCHAR(10),
    Schedule VARCHAR(100),
    -- Note: Class information with schedule and room number
    CONSTRAINT fk_class_course FOREIGN KEY (CourseID) REFERENCES courses(ID)
);

Triggers

Update faculties.Courses when a new course is added

CREATE TRIGGER trg_after_insert_course
AFTER INSERT ON courses
FOR EACH ROW
BEGIN
    UPDATE faculties
    SET Courses = Courses + 1
    WHERE Name = NEW.Faculty;
END;

Update faculties.Attendees when a new student is added

CREATE TRIGGER trg_after_insert_student
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    UPDATE faculties
    SET Attendees = Attendees + 1
    WHERE Name = (
        SELECT Faculty FROM courses WHERE Name = NEW.Course
    );
END;

Prevent inserting a grade not in the allowed format (A–F, including +/−)

CREATE TRIGGER trg_validate_grade
BEFORE INSERT ON grade
FOR EACH ROW
BEGIN
    IF NEW.Grade NOT REGEXP '^[ABCDF][+-]?$' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid grade format. Use A, B, C, D, F optionally followed by + or -.';
    END IF;
END;

Ensure exam date is not in the past

CREATE TRIGGER trg_exam_date_check
BEFORE INSERT ON exam
FOR EACH ROW
BEGIN
    IF NEW.Date < CURDATE() THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Exam date cannot be in the past.';
    END IF;
END;

Log student deletions into a separate audit table

-- First, create the audit log table
CREATE TABLE student_deletion_log (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    Name VARCHAR(50),
    Surname VARCHAR(50),
    DeletedAt DATETIME
);

-- Then, the trigger
CREATE TRIGGER trg_log_student_delete
BEFORE DELETE ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_deletion_log (StudentID, Name, Surname, DeletedAt)
    VALUES (OLD.ID, OLD.Name, OLD.Surname, NOW());
END;