Database Schema Design
The database consists of 6 main tables with appropriate relationships and constraints:
-- Student table with validation constraints
CREATE TABLE student (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(45) NOT NULL,
surname VARCHAR(45) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL CHECK (email LIKE "%@%.%"),
phone VARCHAR(45) NOT NULL,
city VARCHAR(50) NOT NULL,
registration_date DATE NOT NULL
);
Stored Procedures & Functions
Implemented comprehensive business logic through stored procedures and functions:
-- Procedure to validate and insert exam records
DELIMITER //
CREATE PROCEDURE sp_Insert_Exam_With_Validation(
IN p_student_id INT,
IN p_module_id INT,
IN p_exam_date DATE,
IN p_total_mark DECIMAL(5,2)
)
BEGIN
-- Transaction with REPEATABLE READ isolation
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- Check enrollment before insertion
IF NOT EXISTS (SELECT 1 FROM student_module
WHERE student_id = p_student_id AND module_id = p_module_id) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Student not enrolled in module';
ELSE
INSERT INTO exam (student_id, module_id, exam_date, total_mark)
VALUES (p_student_id, p_module_id, p_exam_date, p_total_mark);
COMMIT;
END IF;
END //
DELIMITER ;
Triggers for Automation
Implemented triggers to automate audit trails and performance tracking:
-- Trigger to log mark changes
DELIMITER //
CREATE TRIGGER trg_exam_before_update
BEFORE UPDATE ON exam
FOR EACH ROW
BEGIN
INSERT INTO exam_audit (exam_id, old_total_mark, new_total_mark, changed_by)
VALUES (OLD.exam_id, OLD.total_mark, NEW.total_mark, CURRENT_USER());
END //
DELIMITER ;
Views for Data Access
Created updatable views to simplify complex data access patterns:
-- View showing student status (Pass, Fail, Distinction)
CREATE VIEW vw_status AS
SELECT
e.student_id,
e.module_id,
m.module_name,
s.first_name,
s.surname,
e.total_mark,
CASE
WHEN e.total_mark >= 80 THEN 'Distinction'
WHEN e.total_mark >= 50 THEN 'Pass'
ELSE 'Fail'
END AS status
FROM exam e
JOIN student s ON s.student_id = e.student_id
JOIN module m ON m.module_id = e.module_id;
