-- Create the database
CREATE DATABASE kyambogo_university_db;
USE kyambogo_university_db;
-- 1. Core Tables
CREATE TABLE person (
person_id INT AUTO_INCREMENT PRIMARY KEY,
national_id VARCHAR(20) UNIQUE,
passport_no VARCHAR(20) UNIQUE,
first_name VARCHAR(100) NOT NULL,
middle_name VARCHAR(100),
last_name VARCHAR(100) NOT NULL,
sex ENUM('M', 'F', 'O'),
dob DATE,
phone VARCHAR(15),
email VARCHAR(255) UNIQUE NOT NULL,
address_line1 VARCHAR(255),
address_line2 VARCHAR(255),
city VARCHAR(100),
country VARCHAR(100) DEFAULT 'Uganda'
);
CREATE TABLE programme (
programme_id INT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(20) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
level ENUM('Certificate', 'Diploma', 'UG', 'PG'),
duration_years INT
);
CREATE TABLE term (
term_id INT AUTO_INCREMENT PRIMARY KEY,
acad_year VARCHAR(9) NOT NULL, -- e.g., "2025/2026"
term_code ENUM('Sem1', 'Sem2', 'Trim1', 'Trim2', 'Trim3') NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
UNIQUE KEY unique_term (acad_year, term_code)
);
CREATE TABLE course (
course_id INT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(20) UNIQUE NOT NULL,
title VARCHAR(255) NOT NULL,
credit_units INT NOT NULL,
level INT,
is_core BOOLEAN DEFAULT FALSE
);
CREATE TABLE grade_scale (
grade_scale_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
letter VARCHAR(2) NOT NULL,
min_mark DECIMAL(5,2) NOT NULL,
max_mark DECIMAL(5,2) NOT NULL,
grade_point DECIMAL(3,2) NOT NULL,
UNIQUE KEY unique_grade_scale (name, letter)
);
-- 2. Programme Versioning
CREATE TABLE programme_version (
programme_version_id INT AUTO_INCREMENT PRIMARY KEY,
programme_id INT NOT NULL,
version_code VARCHAR(10) NOT NULL, -- e.g., "2024"
effective_from_term_id INT NOT NULL,
effective_to_term_id INT NULL,
UNIQUE KEY unique_programme_version (programme_id, version_code),
FOREIGN KEY (programme_id) REFERENCES programme(programme_id),
FOREIGN KEY (effective_from_term_id) REFERENCES term(term_id),
FOREIGN KEY (effective_to_term_id) REFERENCES term(term_id)
);
-- 3. Course Prerequisites
CREATE TABLE course_prerequisite (
course_id INT NOT NULL,
prereq_course_id INT NOT NULL,
min_grade VARCHAR(2),
PRIMARY KEY (course_id, prereq_course_id),
FOREIGN KEY (course_id) REFERENCES course(course_id),
FOREIGN KEY (prereq_course_id) REFERENCES course(course_id)
);
-- 4. Admissions Module
CREATE TABLE application (
application_id INT AUTO_INCREMENT PRIMARY KEY,
person_id INT NOT NULL,
programme_version_id INT NOT NULL,
entry_intake_term_id INT NOT NULL,
status ENUM('submitted', 'under_review', 'eligible', 'offered', 'rejected', 'accepted', 'withdrawn'),
submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (person_id) REFERENCES person(person_id),
FOREIGN KEY (programme_version_id) REFERENCES programme_version(programme_version_id),
FOREIGN KEY (entry_intake_term_id) REFERENCES term(term_id)
);
CREATE TABLE application_document (
app_doc_id INT AUTO_INCREMENT PRIMARY KEY,
application_id INT NOT NULL,
doc_type VARCHAR(50) NOT NULL,
file_url TEXT NOT NULL,
received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
verified BOOLEAN DEFAULT FALSE,
verified_at TIMESTAMP NULL,
verified_by VARCHAR(100),
FOREIGN KEY (application_id) REFERENCES application(application_id)
);
CREATE TABLE eligibility_check (
elig_check_id INT AUTO_INCREMENT PRIMARY KEY,
application_id INT NOT NULL,
rule_code VARCHAR(50) NOT NULL,
passed BOOLEAN NOT NULL,
details TEXT,
checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
checked_by VARCHAR(100),
UNIQUE KEY unique_eligibility_check (application_id, rule_code),
FOREIGN KEY (application_id) REFERENCES application(application_id)
);
CREATE TABLE offer (
offer_id INT AUTO_INCREMENT PRIMARY KEY,
application_id INT UNIQUE NOT NULL,
offer_term_id INT NOT NULL,
offer_status ENUM('pending', 'sent', 'accepted', 'declined', 'expired'),
issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
responded_at TIMESTAMP NULL,
conditions_text TEXT,
deadline DATE,
FOREIGN KEY (application_id) REFERENCES application(application_id),
FOREIGN KEY (offer_term_id) REFERENCES term(term_id)
);
CREATE TABLE admission_event (
admission_event_id INT AUTO_INCREMENT PRIMARY KEY,
application_id INT NOT NULL,
event_type VARCHAR(50) NOT NULL,
event_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
actor VARCHAR(100),
payload JSON,
FOREIGN KEY (application_id) REFERENCES application(application_id)
);
-- 5. Student Records
CREATE TABLE student (
student_id INT AUTO_INCREMENT PRIMARY KEY,
person_id INT UNIQUE NOT NULL,
student_number VARCHAR(20) UNIQUE NOT NULL,
admit_term_id INT NOT NULL,
status ENUM('active', 'suspended', 'graduated', 'withdrawn'),
FOREIGN KEY (person_id) REFERENCES person(person_id),
FOREIGN KEY (admit_term_id) REFERENCES term(term_id)
);
CREATE TABLE applicant_promotion (
promotion_id INT AUTO_INCREMENT PRIMARY KEY,
application_id INT NOT NULL,
student_id INT NOT NULL,
promoted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
promoted_by VARCHAR(100),
FOREIGN KEY (application_id) REFERENCES application(application_id),
FOREIGN KEY (student_id) REFERENCES student(student_id)
);
-- 6. Registration Module
CREATE TABLE student_programme (
stud_prog_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
programme_version_id INT NOT NULL,
start_term_id INT NOT NULL,
end_term_id INT NULL,
current_year_of_study INT,
status ENUM('active', 'completed', 'transferred'),
CHECK (current_year_of_study > 0),
UNIQUE KEY unique_student_programme (student_id, programme_version_id, start_term_id),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (programme_version_id) REFERENCES programme_version(programme_version_id),
FOREIGN KEY (start_term_id) REFERENCES term(term_id),
FOREIGN KEY (end_term_id) REFERENCES term(term_id)
);
CREATE TABLE student_contact (
stud_contact_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
type ENUM('next_of_kin', 'sponsor', 'emergency'),
name VARCHAR(255) NOT NULL,
phone VARCHAR(15),
email VARCHAR(255),
address TEXT,
FOREIGN KEY (student_id) REFERENCES student(student_id)
);
CREATE TABLE course_offering (
offering_id INT AUTO_INCREMENT PRIMARY KEY,
course_id INT NOT NULL,
term_id INT NOT NULL,
programme_version_id INT NULL,
section VARCHAR(5),
capacity INT,
UNIQUE KEY unique_course_offering (course_id, term_id, section),
FOREIGN KEY (course_id) REFERENCES course(course_id),
FOREIGN KEY (term_id) REFERENCES term(term_id),
FOREIGN KEY (programme_version_id) REFERENCES programme_version(programme_version_id)
);
CREATE TABLE registration (
registration_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
term_id INT NOT NULL,
registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status ENUM('initiated', 'provisional', 'confirmed', 'cancelled'),
UNIQUE KEY unique_registration (student_id, term_id),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (term_id) REFERENCES term(term_id)
);
CREATE TABLE enrollment (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
registration_id INT NOT NULL,
offering_id INT NOT NULL,
enroll_status ENUM('enrolled', 'waitlisted', 'dropped'),
enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_enrollment (registration_id, offering_id),
FOREIGN KEY (registration_id) REFERENCES registration(registration_id),
FOREIGN KEY (offering_id) REFERENCES course_offering(offering_id)
);
CREATE TABLE prereq_waiver (
waiver_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
term_id INT NOT NULL,
approved_by VARCHAR(100),
approved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
reason TEXT,
UNIQUE KEY unique_prereq_waiver (student_id, course_id, term_id),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (course_id) REFERENCES course(course_id),
FOREIGN KEY (term_id) REFERENCES term(term_id)
);
CREATE TABLE capacity_reservation (
reservation_id INT AUTO_INCREMENT PRIMARY KEY,
offering_id INT NOT NULL,
student_id INT NOT NULL,
reserved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP NOT NULL,
UNIQUE KEY unique_capacity_reservation (offering_id, student_id),
FOREIGN KEY (offering_id) REFERENCES course_offering(offering_id),
FOREIGN KEY (student_id) REFERENCES student(student_id)
);
-- 7. Assessments Module
CREATE TABLE assessment_component (
component_id INT AUTO_INCREMENT PRIMARY KEY,
offering_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
weight_pct DECIMAL(5,2),
max_mark DECIMAL(5,2) DEFAULT 100,
is_makeup BOOLEAN DEFAULT FALSE,
CHECK (weight_pct BETWEEN 0 AND 100),
UNIQUE KEY unique_assessment_component (offering_id, name),
FOREIGN KEY (offering_id) REFERENCES course_offering(offering_id)
);
CREATE TABLE mark_entry (
mark_entry_id INT AUTO_INCREMENT PRIMARY KEY,
component_id INT NOT NULL,
enrollment_id INT NOT NULL,
raw_mark DECIMAL(5,2),
entered_by VARCHAR(100),
entered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
locked BOOLEAN DEFAULT FALSE,
CHECK (raw_mark >= 0),
UNIQUE KEY unique_mark_entry (component_id, enrollment_id),
FOREIGN KEY (component_id) REFERENCES assessment_component(component_id),
FOREIGN KEY (enrollment_id) REFERENCES enrollment(enrollment_id)
);
CREATE TABLE moderation_action (
moderation_id INT AUTO_INCREMENT PRIMARY KEY,
component_id INT NOT NULL,
action ENUM('scale_up', 'scale_down', 'cap', 'add_constant', 'remark'),
parameters JSON,
applied_by VARCHAR(100),
applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (component_id) REFERENCES assessment_component(component_id)
);
CREATE TABLE course_result (
course_result_id INT AUTO_INCREMENT PRIMARY KEY,
enrollment_id INT UNIQUE NOT NULL,
total_mark DECIMAL(5,2),
letter_grade VARCHAR(2),
grade_point DECIMAL(3,2),
result_status ENUM('provisional', 'board_approved', 'incomplete', 'supplementary_required',
'retake_required'),
approved_at TIMESTAMP NULL,
FOREIGN KEY (enrollment_id) REFERENCES enrollment(enrollment_id)
);
CREATE TABLE exam_board_decision (
decision_id INT AUTO_INCREMENT PRIMARY KEY,
course_result_id INT NOT NULL,
decision ENUM('approve', 'withhold', 'amend', 'require_retake', 'convert_incomplete'),
reason TEXT,
decided_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
board_ref VARCHAR(100),
FOREIGN KEY (course_result_id) REFERENCES course_result(course_result_id)
);
CREATE TABLE incomplete_case (
incomplete_id INT AUTO_INCREMENT PRIMARY KEY,
course_result_id INT NOT NULL,
cause VARCHAR(50),
deadline_term_id INT,
resolved BOOLEAN DEFAULT FALSE,
resolved_at TIMESTAMP NULL,
FOREIGN KEY (course_result_id) REFERENCES course_result(course_result_id),
FOREIGN KEY (deadline_term_id) REFERENCES term(term_id)
);
CREATE TABLE retake_instance (
retake_id INT AUTO_INCREMENT PRIMARY KEY,
enrollment_id INT NOT NULL,
retake_offering_id INT NOT NULL,
reason ENUM('failed', 'supplementary'),
scheduled_at TIMESTAMP NULL,
completed_at TIMESTAMP NULL,
outcome_mark DECIMAL(5,2) NULL,
outcome_grade VARCHAR(2) NULL,
FOREIGN KEY (enrollment_id) REFERENCES enrollment(enrollment_id),
FOREIGN KEY (retake_offering_id) REFERENCES course_offering(offering_id)
);
-- 8. Graduation Module
CREATE TABLE fee_account (
fee_account_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT UNIQUE NOT NULL,
currency VARCHAR(3) DEFAULT 'UGX',
balance DECIMAL(12,2) DEFAULT 0,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES student(student_id)
);
CREATE TABLE fee_transaction (
fee_txn_id INT AUTO_INCREMENT PRIMARY KEY,
fee_account_id INT NOT NULL,
term_id INT NULL,
txn_type ENUM('charge', 'payment', 'adjustment'),
amount DECIMAL(12,2) NOT NULL,
posted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
reference VARCHAR(100),
FOREIGN KEY (fee_account_id) REFERENCES fee_account(fee_account_id),
FOREIGN KEY (term_id) REFERENCES term(term_id)
);
CREATE TABLE graduation_rule_set (
rule_set_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
programme_level VARCHAR(20),
min_credits_required INT,
min_cgpa DECIMAL(3,2),
allow_trails BOOLEAN DEFAULT FALSE,
effective_from_term_id INT NOT NULL,
FOREIGN KEY (effective_from_term_id) REFERENCES term(term_id)
);
CREATE TABLE graduation_audit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
stud_prog_id INT NOT NULL,
rule_set_id INT NOT NULL,
credits_completed INT DEFAULT 0,
cgpa DECIMAL(4,2),
retakes_outstanding INT DEFAULT 0,
missing_marks INT DEFAULT 0,
fees_clear BOOLEAN DEFAULT FALSE,
eligible BOOLEAN DEFAULT FALSE,
evaluated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY unique_graduation_audit (stud_prog_id, rule_set_id),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (stud_prog_id) REFERENCES student_programme(stud_prog_id),
FOREIGN KEY (rule_set_id) REFERENCES graduation_rule_set(rule_set_id)
);
-- Create indexes for better performance
CREATE INDEX idx_person_email ON person(email);
CREATE INDEX idx_student_number ON student(student_number);
CREATE INDEX idx_course_code ON course(code);
CREATE INDEX idx_application_status ON application(status);
CREATE INDEX idx_enrollment_status ON enrollment(enroll_status);
CREATE INDEX idx_course_result_status ON course_result(result_status);
CREATE INDEX idx_fee_account_balance ON fee_account(balance);