Student and Faculty Clearance System Database Design

Student and Faculty Clearance System Database Design

Introduction

The automation of all tasks and operations increases the outcome’s efficiency and correctness. Almost all sectors and organizations have adapted technology to automate their processes, and the educational sector is no exception. “Student and Faculty Clearance System,” the capstone project, is another unique educational tool that will streamline yet another crucial school process: clearance processing. The said project is designed to make the clearance process more accessible while also ensuring the integrity and accuracy of clearances.

Students and faculty who have received school clearance have met all of the school’s requirements. The clearance is a pass that allows them to go on to other tasks in place of the clearance’s subject. Traditional clearance processing necessitates using pen and paper, which is time-consuming, error-prone, and challenging to check signatures. Academic institutions require a clearance processing procedure that is time-efficient and ensures the clearance’s integrity and consistency.

Database Tables

This article will provide you with an idea about the Student and Faculty Clearance System database design.

tbl_course – this table will store the information of the courses recorded in the system.

• course_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).
• course_code – this is unique code given to the specific course
• course_description – the description of the course

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

CREATE TABLE `tbl_course` (
`course_id` int(11) NOT NULL,
`course_code` varchar(15) NOT NULL,
`course_description` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_department – the department’s information will be stored in this table.

• 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_name – the name of the department

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_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_department_user – this table will store the information of the department user registered in the system.

• department_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).
• department_id – this is a foreign key that points out to the department
• assigned_personnel – the name of the assigned personnel as department user
• username – the desired username of the department user for the account
• password – the desired password of the department user for the account
• account_status – (0)active,(1)inactive
• user_id – this is a foreign key that points out to the user who processed the records

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

CREATE TABLE `tbl_department_user` (
`department_user_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
`assigned_personnel` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_student – this table will store the information of the students registered in the system.

• student_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).
• student_id_number – this is a unique number given to a specific student
• last_name – the last name of the student
• first_name – the first name of the student
• middle_name – the middle name of the student
• course_id – this is a foreign key that points out to the course of the student
• year_level- the student’s year level
• contact – the contact information of the student
• email_address – the email address of the student
• username – the desired username of the username of the student
• password – the desired password of the student combined with the username to login to the system
• account_status – (0)active,(1)inactive
• user_id – this is a foreign key that points out to the user who processed the record

 

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

CREATE TABLE `tbl_student` (
`student_id` int(11) NOT NULL,
`student_id_number` varchar(15) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`course_id` int(11) NOT NULL,
`year_level` int(1) NOT NULL,
`contact` varchar(15) NOT NULL,
`email_address` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_faculty – the records of the faculties are stored in this table.

• 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).
• faculty_id_number – this is a unique number assigned to the faculty
• last_name – the last name of the faculty
• first_name – the first name of the faculty
• middle_name – the middle name of the faculty
• contact – the contact details of the faculty preferably mobile number
• email_address – the email address of the faculty
• username – the preferred username of the faculty, used to login to the system
• password – the preferred password of the faculty combined with the username  to login
• account_status – (0)active,(1)inactive
• user_id – this is a foreign key that points out to the user who processed the record

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,
`faculty_id_number` varchar(15) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`contact` varchar(15) NOT NULL,
`email_address` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`account_status` int(1) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_deliverable – this table will store the information of the deliverable deposited in the system.

• deliverable_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_id – this is a foreign key that points out to the department
• category – (0)student, (1)faculty
• requirement_name – the name of the requirement
• description – description of the deliverable

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

CREATE TABLE `tbl_deliverable` (
`deliverable_id` int(11) NOT NULL,
`department_id` int(11) NOT NULL,
`category` int(1) NOT NULL,
`requirement_name` varchar(30) NOT NULL,
`description` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_list_deliverable – this table will store the information of the list of deliverable in the system.

• list_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).
• deliverable_id – this is a foreign key that points out to the deliverable
• uploaded_file – the file uploaded for the deliverable
• date_uploaded – the date the file was uploaded
• faculty_id – this is a foreign key that points out to the faculty
• student_id – this is a foreign key that points out to the student
• remarks – additional information about the list of deliverable
• status – (0)pending, (1)approved, (2)rejected
• department_user_id – this is a foreign key that points out to the department user to review the requirement

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

CREATE TABLE `tbl_list_deliverable` (
`list_id` int(11) NOT NULL,
`deliverable_id` int(11) NOT NULL,
`uploaded_file` longblob NOT NULL,
`date_uploaded` date NOT NULL,
`faculty_id` int(11) NOT NULL,
`student_id` int(11) NOT NULL,
`remarks` varchar(50) NOT NULL,
`status` int(1) NOT NULL,
`department_user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_user – this table will store 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
• avatar – this will hold the profile photo of the user
• username – the desired username of the user combined with the password to login to the system
• password – the desired password of the user combined with the password to login to the system
• contact – the contact number of the user
• email – the email address of the user
• status – (0)active, (1)inactive

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,
`avatar` longblob NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL,
`status` int(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Video Tutorial

Summary

The Student and Faculty Clearance project aims to make it as simple as possible for students and faculty members to obtain the information necessary to complete their clearance procedure. The project entails the development of a website and a mobile application that provide up-to-date information on clearance requirements and procedures. Additionally, the website and app contain a directory of campus resources that might assist students and instructors.

Please watch the video tutorial on how to prepare and create the different tables of the database of Student and Faculty Clearance System.

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.

Related Topics and Articles:

Student and Faculty Clearance System Free Bootstrap Source code

Clearance Processing System for Students and Faculty

Abstract of Faculty and Student Clearance Application

Barangay Blotter and Clearance System

Electronic Police Clearance System Capstone Project