DB Schema - JUCSE49-Mavericks/Smart-Class-Routine-Management-System GitHub Wiki
Database Schema Overview
This document provides an overview of the database schema used in the project. The schema consists of several tables that represent entities like departments, teachers, students, and courses, along with their relationships through foreign keys.
1. Department Table
The Department table stores information about various departments in the institution. It is referenced by other tables such as Teacher, Staff, Room, Session, and DepartmentChairman.
CREATE TABLE Department (
dept_id INT AUTO_INCREMENT PRIMARY KEY,
Dept_Name VARCHAR(255) NOT NULL,
Descript TEXT,
Phone VARCHAR(15),
Fax VARCHAR(15),
Email VARCHAR(255)
);
2. Teacher Table
The Teacher table stores information about teachers. It references the Department table and is referenced by the DepartmentChairman, FinalSchedule, and ExamCommittee tables.
CREATE TABLE Teacher (
teacher_id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Designation ENUM('Professor', 'Associate Professor', 'Assistant Professor', 'Lecturer') NOT NULL,
profile_image VARCHAR(255),
dept_id INT,
Abvr VARCHAR(10),
Email VARCHAR(255),
Password VARCHAR(255),
Phone VARCHAR(15),
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
3. Staff Table
The Staff table stores information about the staff members. It references the Department table.
CREATE TABLE Staff (
staff_id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Role ENUM('Admin', 'Support') NOT NULL,
dept_id INT,
Email VARCHAR(255),
Password VARCHAR(255),
Phone VARCHAR(15),
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
4. Room Table
The Room table contains information about rooms used for classes, labs, and offices. It is referenced by the FinalSchedule table and links to the Department table.
CREATE TABLE Room (
room_id INT AUTO_INCREMENT PRIMARY KEY,
Room_no VARCHAR(10) NOT NULL,
Room_type ENUM('Classroom', 'Laboratory', 'Office') NOT NULL,
Capacity INT,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
5. Session Table
The Session table stores information about academic sessions and is linked to the Department table. It is referenced by the ExamYear and Student tables.
CREATE TABLE Session (
session_id INT AUTO_INCREMENT PRIMARY KEY,
dept_id INT,
Session_name VARCHAR(255) NOT NULL,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
6. ExamYear Table
The ExamYear table holds information about the exam years, which are linked to academic sessions and used in various tables like Course, ExamCommittee, and FinalSchedule.
CREATE TABLE ExamYear (
exam_year_id INT AUTO_INCREMENT PRIMARY KEY,
session_id INT,
Education_level ENUM('Undergraduate', 'Postgraduate') NOT NULL,
Exam_year VARCHAR(4),
Year INT,
Semester ENUM('Spring', 'Summer', 'Fall') NOT NULL,
Start_date DATE,
End_date DATE,
FOREIGN KEY (session_id) REFERENCES Session(session_id)
);
7. Student Table
The Student table holds student-related information. It references the Session table.
CREATE TABLE Student (
student_id INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Gender ENUM('Male', 'Female', 'Other') NOT NULL,
session_id INT,
Class_roll VARCHAR(10),
Exam_roll VARCHAR(10),
Registration_no VARCHAR(20),
Email VARCHAR(255),
Password VARCHAR(255),
Phone VARCHAR(15),
FOREIGN KEY (session_id) REFERENCES Session(session_id)
);
8. Course Table
The Course table stores course-related information and references the ExamYear table.
CREATE TABLE Course (
course_id INT AUTO_INCREMENT PRIMARY KEY,
exam_year_id INT,
course_code VARCHAR(10) NOT NULL,
course_credit INT,
course_title VARCHAR(255) NOT NULL,
course_type ENUM('Theory', 'Lab') NOT NULL,
contact_hour INT,
rationale TEXT,
FOREIGN KEY (exam_year_id) REFERENCES ExamYear(exam_year_id)
);
9. PrerequisiteCourse Table
The PrerequisiteCourse table defines prerequisite courses for a given course. It references the Course table.
CREATE TABLE PrerequisiteCourse (
prc_id INT AUTO_INCREMENT PRIMARY KEY,
course_id INT,
prerequisite VARCHAR(255),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
10. CourseChapter Table
The CourseChapter table contains information about the chapters in a course. It references the Course table.
CREATE TABLE CourseChapter (
chapter_id INT AUTO_INCREMENT PRIMARY KEY,
course_id INT,
chapter VARCHAR(255),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
11. CourseObjective Table
The CourseObjective table defines the objectives of a course. It references the Course table.
CREATE TABLE CourseObjective (
co_id INT AUTO_INCREMENT PRIMARY KEY,
course_id INT,
objective TEXT,
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
12. StudentLearningOutcome Table
The StudentLearningOutcome table defines the outcomes expected for students from a course. It references the Course table.
CREATE TABLE StudentLearningOutcome (
slo_id INT AUTO_INCREMENT PRIMARY KEY,
course_id INT,
outcome TEXT,
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
13. RecommendedBook Table
The RecommendedBook table stores information about the books recommended for a course. It references the Course table.
CREATE TABLE RecommendedBook (
book_id INT AUTO_INCREMENT PRIMARY KEY,
course_id INT,
book_title VARCHAR(255),
writer VARCHAR(255),
edition INT,
publisher VARCHAR(255),
publish_year VARCHAR(255),
FOREIGN KEY (course_id) REFERENCES Course(course_id)
);
14. Holidays Table
The Holidays table tracks holiday events and does not reference other tables.
CREATE TABLE Holidays (
holiday_id INT AUTO_INCREMENT PRIMARY KEY,
event_name VARCHAR(255) NOT NULL,
start_date DATE,
end_date DATE,
num_days INT
);
15. Schedule Table
The Schedule table manages class schedules by referencing the Teacher, Course, Room, and Department tables.
CREATE TABLE Schedule (
id INT AUTO_INCREMENT PRIMARY KEY,
day VARCHAR(50) NOT NULL,
year VARCHAR(10) NOT NULL,
time_slot VARCHAR(50) NOT NULL,
teacher_id INT,
course_id INT,
room_id INT,
dept_id INT,
FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id),
FOREIGN KEY (course_id) REFERENCES Course(course_id),
FOREIGN KEY (room_id) REFERENCES Room(room_id),
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);
16. FinalSchedule Table
The FinalSchedule table is used for managing exam schedules and links to the Room, Teacher, and ExamYear tables.
CREATE TABLE FinalSchedule (
final_schedule_id INT AUTO_INCREMENT PRIMARY KEY,
date DATE NOT NULL,
time_slot VARCHAR(50) NOT NULL,
room_id INT,
teacher_id INT,
exam_year_id INT,
Status ENUM('Scheduled', 'Conducted', 'Cancelled', 'Rescheduled') NOT NULL,
FOREIGN KEY (room_id) REFERENCES Room(room_id),
FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id),
FOREIGN KEY (exam_year_id) REFERENCES ExamYear(exam_year_id)
);
17. ExamCommittee Table
The ExamCommittee table represents the exam committee and links to the ExamYear and Teacher tables.
CREATE TABLE ExamCommittee (
exam_committee_id INT AUTO_INCREMENT PRIMARY KEY,
exam_year_id INT,
teacher_id INT,
FOREIGN KEY (exam_year_id) REFERENCES ExamYear(exam_year_id),
FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id)
);