Class Scheduling Database Project

Class Scheduling Database Project

About the Project

The study was conducted to assess the methods and processes used by the academic institution in preparing the class schedule, the problems encountered by the students and instructors with regards to speed, accuracy and efficiency in retrieving the class schedule, the level of acceptability on the current class scheduling, and the level of need for developing a Class Scheduling System. The Class Scheduler will assist the institution in making the schedule of classes in an efficient and easy way, as well as, aid the students and instructors in checking their class schedules in a quick manner.

An automated class scheduling system could be used in a school or university to streamline the process of scheduling classes. The system would be able to take into account the preferences of both students and faculty, as well as the availability of classrooms and other resources. The benefits of such a system include the ability to schedule classes more efficiently and with less conflict. The system could also help to ensure that all classes are filled, which could improve student retention rates.

Database Tables

tbl_room_type – this table stores the information of the room types in the system.

• type_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• type_name – classroom, laboratory, science lab, etc
• capacity – the capacity or the number of students the room can accommodate.

Create SQL Statement – the statement below is used to create the tbl_room_type, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_room_type` (
`type_id` int(11) NOT NULL,
`type_name` varchar(30) NOT NULL,
`capacity` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_room – the information of the rooms are stored in this table.

• room_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• room_name – the name of the room

Create SQL Statement – the statement below is used to create the tbl_room, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_room` (
`room_id` int(11) NOT NULL,
`room_name` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_faculty – this table stores the information of the faculty in the system.

• faculty_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• id_number – id number of the faculties
• lastname – the last name of the faculty
• firstname – the first name of the faculty
• middlename – the middle name of the faculty
• email_address – the email address of the faculty member
• contact – the contact information of the faculty member
• category – regular, part-time
• username – the preferred username of the faculty
• password -the preferred password of the faculty

Create SQL Statement – the statement below is used to create the tbl_faculty, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_faculty` (
`faculty_id` int(11) NOT NULL,
`id_number` varchar(15) NOT NULL,
`lastname` varchar(30) NOT NULL,
`firstname` varchar(30) NOT NULL,
`middlename` varchar(30) NOT NULL,
`email_address` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`category` int(1) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_college – this table stores the information of the colleges in the system.

• college_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• college_code – unique code given to a particular college
• college_name -the name of the college

Create SQL Statement – the statement below is used to create the tbl_college, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_college` (
`college_id` int(11) NOT NULL,
`college_code` varchar(15) NOT NULL,
`college_name` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_department – this table stores the information of the department in the system.

• department_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• department_code – unique code given to a particular department
• department_name – the name of the department
• college_id – this is a foreign key that points out to the college

Create SQL Statement – the statement below is used to create the tbl_department, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_department` (
`department_id` int(11) NOT NULL,
`department_code` varchar(15) NOT NULL,
`department_name` varchar(50) NOT NULL,
`college_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_subject – this table stores the information of the subjects in the system.

• subject_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• subject_code – this is a unique code given to a specific subject
• subject_name – the name of the subject

Create SQL Statement – the statement below is used to create the tbl_subject, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_subject` (
`subject_id` int(11) NOT NULL,
`subject_code` varchar(15) NOT NULL,
`subject_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_school_year – the details of the school years will be stored in this table.

• sy_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• year – the particular year
• semester – 1^st, 2^nd, 3^rd, summer

Create SQL Statement – the statement below is used to create the tbl_school_year, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_school_year` (
`sy_id` int(11) NOT NULL,
`year` varchar(4) NOT NULL,
`semester` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_class – this table stores the information of the classes registered in the system.

• class_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• class_name – the name of the class

Create SQL Statement – the statement below is used to create the tbl_class, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_class` (
`class_id` int(11) NOT NULL,
`class_name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_schedule – this table stores the schedule information in the system.

• schedule_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• sy_id – this is a foreign key that points to the school year
• class_id – this is a foreign key that points out to the class
• room_id – this is a foreign key that points out to the room
• days – MWF, TTH
• start_time – the time when the class will start
• end_time -the time when the class will end
• faculty_id – this is a foreign key that points out to the faculty
• subject_id – this is a foreign key that points out to the subject
• department_id – this is a foreign key that points out to the department
• encoded_by (user_id) – this is a foreign key that points out to the user who encoded the schedule

Create SQL Statement – the statement below is used to create the tbl_schedule, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_schedule` (
`schedule_id` int(11) NOT NULL,
`sy_id` int(11) NOT NULL,
`class_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`days` varchar(10) NOT NULL,
`start_time` time NOT NULL,
`end_time` time NOT NULL,
`faculty_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
`encoded_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – this table stores the information of the users of the system.

• user_id – primary key of the table. It is set usually to auto_increment (the database will automatically give this column a value starting from 1).
• complete_name – the complete name of the user
• username – the preferred username of the user used to login to the system
• password – the preferred password of the user
• email – the email address of the user
• contact – the contact number of the user preferably mobile number
• department_id – this is a foreign key that points out to the department

Create SQL Statement – the statement below is used to create the tbl_user, copy the sql statement and paste it in the SQL manager/tab of your phpmyadmin.

CREATE TABLE `tbl_user` (
`user_id` int(11) NOT NULL,
`complete_name` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`email` varchar(50) NOT NULL,
`contact` varchar(15) NOT NULL,
`department_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

FREE DOWNLOAD SQL FILE

Summary

A database is a collection of data that is organized in a specific way. A database design is a plan for how the data in a database will be organized. Normalization is a process of organizing data in a database so that it is consistent and easy to use.

Database design and normalization can help reduce data redundancy and improve data consistency. Redundant data is data that is duplicated in a database. This can lead to confusion and frustration when trying to use the data, as it is difficult to determine which information is relevant. Consistent data, on the other hand, is data that is organized in a way that makes it easy to find what you are looking for. This makes it easier for users to understand and use the data.

The time required to access the data can be decreased with the use of database design and normalization. Finding and using data that is consistently arranged is simple. As a result, consumers won’t have to waste time seeking for the information they need. Furthermore, information that is presented in an accessible manner will be used more frequently. The database users will benefit by saving time and resources in this way.

Overall, database design and normalization can provide many benefits for the users of the database. These include reduced redundancy and consistency, which makes it easier for users to access and use the data. Additionally, the time that is needed to access and use the data will be reduced. This makes it easier for users to get their work done quickly.

Please watch the video tutorial that will be posted on our YouTube Channel.

Readers are also interested in:

Class Scheduling Use Case Diagram

Calendar and Schedule App in Django Free Source code

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.

Hire our team to do the project.