Face Recognition Application Database Design

Table of Contents

Face Recognition Application Database Design
About the System
Database Tables
Summary
Video Tutorial

Technology has grown so fast; it changes the way we do our daily tasks. Technology has made our daily lives easier. The capstone project, entitled “Face Recognition Attendance System” is designed to automate checking and recording of students’ attendance during school events using face recognition technology. The system will work by storing the student’s information along with their photographs in a server and the system will detect the faces of the students during school events and match it and verify to record the presence or absence of the student.

About the System

In schools, events or culmination activities are not new, it is conducted to enhance students’ participation in schools activities and their extracurricular activities. During school events, students’ attendance is strictly checked to monitor if they are present during the activity and are participating. Currently, the process of monitoring the attendance of every student is done manually, whereas inactivity cards, listing attendance on a sheet of paper are only used in monitoring the attendance of every student. Having this work done all over again seems to be tedious and time-consuming. With these methods, errors, redundancy, and discrepancies are inevitable. Another problem that the student body organization or officer in charge is facing now is the consolidation of the report of the attendance of the students. During the signing of the clearance, many students complain that they are marked absent although they are present during the activity and vice versa. Due to the unreliable reports of attendance monitoring and unreliable monitoring of student attendance the schools need an effective system that will accurately and efficiently store records of students’ attendance and consolidated attendance report.

Database Tables

Let’s start by making the tables and columns. For a thorough tutorial, please watch the video.

Face Recognition Application Database Design - List of Tables — Face Recognition Application Database Design – List of Tables

tbl_department – this table will hold the information of the college departments 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 – a unique code given to a specific department.
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_code` varchar(10) NOT NULL,
`department_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_event – the details of the events culminated in school are stored in this table.

event_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).
event_name – the name of the event.
description – additional information about the event.
date – the date when the event will be culminated.
upload_banner- this will allow uploading banner for the event.

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

CREATE TABLE `tbl_event` (
`event_id` int(11) NOT NULL,
`event_name` varchar(50) NOT NULL,
`description` varchar(100) NOT NULL,
`date` date NOT NULL,
`upload_banner` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_employee – this table will store the information of the employees in the system.

employee_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 – the unique id number of the employees.
last_name – the last name of the employee
first_name – the first name of the employee
middle_name- the middle name of the employee
gender- the employee’s gender
deparment_id- this is a foreign key that points out to the department where the employee belong.
contact_number- the contact details of the employee, preferably mobile number.
email_address- the email address of the employee.
username – the preferred username of the employee for his/her account.
password – the desired password of the employee used to login to his/her account combined with the username
account_status – either the account is active or inactive

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

CREATE TABLE `tbl_employee` (
`employee_id` int(11) NOT NULL,
`id_number` varchar(15) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`gender` int(1) NOT NULL,
`department_id` int(11) NOT NULL,
`contact_number` 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
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Face Recognition Attendance System Application Free Download Bootstrap and PHP Script - Face Enrollment — Face Recognition Attendance System Application Free Download Bootstrap and PHP Script – Face Enrollment

tbl_student – the student’s information are stored in this table 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).
id_number – unique id number of the students
last_name – last name of the student
first_name- the student’s first name
middle_name – the student’s middle name
gender – the student’s gender
deparment_id – this is a foreign key that points out to the department where the student belong
year_level – the year level of the student
contact_number – contact number of the student, can be mobile number.
email_address – the email address of the student
username – the desired username of the student, this is use to login to his/her account
password – the desired password of the student combined with the username to login to the system.
account_status – either the account is active or inactive

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,
`id_number` varchar(15) NOT NULL,
`last_name` varchar(30) NOT NULL,
`first_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`gender` int(1) NOT NULL,
`department_id` int(11) NOT NULL,
`year_level` int(1) NOT NULL,
`contact_number` 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
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_face_recognition_enrollment – this table will store the information about the face recognition enrollment.

enrolment_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- the id number of the student
face_image – the captured face image of the student to be enrolled
type (student, employee) -the type of the user, either a student or employee
admin_id – this is foreign key that point out to the admin

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

CREATE TABLE `tbl_face_recognition_enrollment` (
`enrolment_id` int(11) NOT NULL,
`id_number` varchar(15) NOT NULL,
`face_image` longblob NOT NULL,
`type` int(1) NOT NULL,
`admin_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_attendance – this table will store the information about the recorded attendance during events.

attendance_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).
event_id – this is a foreign key that points out to the event
enrolment_id – this is a foreign key that points out to the face recognition enrollment
date – the date the attendance was committed
time – the time when the attendance was made

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

CREATE TABLE `tbl_attendance` (
`attendance_id` int(11) NOT NULL,
`event_id` int(11) NOT NULL,
`enrolment_id` int(11) NOT NULL,
`date` date NOT NULL,
`time` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbl_admin_account – this table store the information of the administrator’s account in the system.

admin_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).
username – the username of the admin’s account combine with the password for logging in purposes.
password – the password of the admin’s account combine with the password for logging in purposes.
name – the name of the admin
contact – the contact details of the admin, can be a mobile or telephone number
email – email address of the admin

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

CREATE TABLE `tbl_admin_account` (
`admin_id` int(11) NOT NULL,
`username` varchar(30) NOT NULL,
`password` text NOT NULL,
`name` varchar(100) NOT NULL,
`contact` varchar(15) NOT NULL,
`email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Summary

A face recognition program would be able to recognize and identify a person in an image or a video recording. It would match the features of a person’s face in a photograph or video with the features of other people’s faces in a database, using algorithms to make the comparison. It would be possible for the program to recognize a person if their image is stored in the database. Among other things, this application could be used for security purposes, such as identifying individuals who are not supposed to be at a given location.

Database design planning has a number of advantages over other approaches. In the first instance, it can aid in the development of a database that is both efficient and simple to use. Additionally, it can aid in the prevention of problems that can arise when data is entered into a database, which is beneficial. Moreover, planning contributes to ensuring that the database fits the requirements of its users. In the end, it can assist in ensuring that the database is dependable and will continue to satisfy the demands of the company for many years to come.

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

Video Tutorial

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.