University Management System -
Database Design
Entity-Relationship Diagram (ERD)
SQL Code for Table Creation
CREATE TABLE Faculty (
faculty_id INT PRIMARY KEY,
faculty_name VARCHAR(100),
dean_name VARCHAR(100)
);
CREATE TABLE Department (
department_id INT PRIMARY KEY,
department_name VARCHAR(100),
faculty_id INT,
FOREIGN KEY (faculty_id) REFERENCES Faculty(faculty_id)
);
CREATE TABLE Program (
program_id INT PRIMARY KEY,
program_name VARCHAR(100),
duration_years INT,
department_id INT,
FOREIGN KEY (department_id) REFERENCES Department(department_id)
);
CREATE TABLE Student (
student_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(10),
date_of_birth DATE,
email VARCHAR(100),
phone_number VARCHAR(20),
address VARCHAR(200),
program_id INT,
FOREIGN KEY (program_id) REFERENCES Program(program_id)
);
CREATE TABLE Course (
course_id INT PRIMARY KEY,
course_name VARCHAR(100),
course_code VARCHAR(20),
credit_hours INT,
department_id INT,
FOREIGN KEY (department_id) REFERENCES Department(department_id)
);
CREATE TABLE Lecturer (
lecturer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone_number VARCHAR(20),
hire_date DATE,
department_id INT,
FOREIGN KEY (department_id) REFERENCES Department(department_id)
);
CREATE TABLE Classroom (
classroom_id INT PRIMARY KEY,
building_name VARCHAR(100),
room_number VARCHAR(20),
capacity INT
);
CREATE TABLE Enrollment (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
semester VARCHAR(20),
session VARCHAR(20),
enrollment_date DATE,
FOREIGN KEY (student_id) REFERENCES Student(student_id),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
CREATE TABLE Result (
result_id INT PRIMARY KEY,
enrollment_id INT,
grade VARCHAR(2),
remarks VARCHAR(200),
FOREIGN KEY (enrollment_id) REFERENCES Enrollment(enrollment_id)
);
CREATE TABLE Course_Schedule (
schedule_id INT PRIMARY KEY,
course_id INT,
lecturer_id INT,
classroom_id INT,
day_of_week VARCHAR(15),
start_time TIME,
end_time TIME,
FOREIGN KEY (course_id) REFERENCES Course(course_id),
FOREIGN KEY (lecturer_id) REFERENCES Lecturer(lecturer_id),
FOREIGN KEY (classroom_id) REFERENCES Classroom(classroom_id)
);
