Biometric Based Attendance System Database Model

Biometric Based Attendance System Database Model

Technicality of the Project

The researchers want to develop a monitoring system using RFID. In the development, the researcher used the following software components:
• Phpmyadmin – to perform different tasks such as creating database with tables, fields, or rows.
• Hypertext Markup Language (HTML) and Cascading Style Sheet (CSS) – to design the graphical features of the system.
• PHP – for server-side scripting language that provides functions in what it is defined on web page.
• Web browser– can access by localhost using PHPmyadmin software tool that capable to store and handle large amount of data with structured, easy and secured process.
• JavaScript – to control the web browser and adjust the document content that is displayed.
• Notepad ++ – the codes were being typed.
• UWAMP – to create a server for creating an application that you can access to test the web applications locally by being offline.

List of Database Table of Biometric Based Attendance System

• tblattendance
• tblcourse
• tblevent
• tbleventpicture
• tblfingerprinttemplate
• tblpenalty
• tblstudent
• tbluser
• tblusercategory

Database Schema/Design/Model

tblattendance (attendance_id, student_id, course_id, activity_id, time_in, time_out, late, under_time, date_log)

Table Description: attendance of students in the different activities will be stored in the tblattendance table. The table has 9 columns; (1) attendance_id is the primary key of the table, (2) student_id is a foreign key that connects to the tblstudent table, (3) course_id is another foreign key that links to the tblcourse table, it refers to the course of the student, (4) activity_id refers to the activity or event, it is also a foreign key that links to the tblactivity table, (5) time_in is the column that records the time wherein students enters the school or the time the student is registered in the biometric attendance, (6) time_out is the time the student exits the event, (7) late refers to the time when students arrive at the event after the expected time (8) under_time this column records the time when the student leaves early in an event, (9) date_log is the date the student logged in the biometric attendance.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblattendance` (
`attendance_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`activity_id` int(11) NOT NULL,
`time_in` time NOT NULL,
`time_out` time NOT NULL,
`late` time NOT NULL,
`under_time` time NOT NULL,
`date_log` date NOT NULL,
PRIMARY KEY (`attendance_id`),
KEY `student_id` (`student_id`,`course_id`,`activity_id`),
KEY `course_id` (`course_id`),
KEY `activity_id` (`activity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcourse (course_id, course_name, course_description)

Table Description: the list of the courses or programs offered by the school are stored. The table has 3 columns; (1) course_id is the primary key of the table, (2) course_name is the name of the course and (3) course_description is the detailed information of the course.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcourse` (
`course_id` int(11) NOT NULL AUTO_INCREMENT,
`course_name` varchar(15) NOT NULL,
`course_description` varchar(100) NOT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblevent (event_id, code, activity_name, date, start_time, end_time, course_id)

Table Description: tblevent table will store the different events of the school for the whole academic year. The table has 7 fields/columns; (1) event_id is the primary key of the table, (2) code refers to the auto-generated code by the system, (3) activity_name is the name of the activity or event, (4) date refers to the scheduled date of the activity, (5) start_time is the time the event will start, (6) end_time refers to the time the event will end, (7) course_id is a foreign key that links to the tblcourse, this refers to the that is specific for a department/course/program.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblevent` (
`event_id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(15) NOT NULL,
`activity_name` varchar(50) NOT NULL,
`date` date NOT NULL,
`start_time` time NOT NULL,
`end_time` time NOT NULL,
`course_id` int(11) NOT NULL,
PRIMARY KEY (`event_id`),
KEY `course_id` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbleventpicture (picture_id, event_id, picture)

Table Description: the system can accept or will allow the users to upload multiple images or pictures in an event, these images will be displayed in attendance module where students will register/sign-in. The table has 3 fields; (1) picture_id is the primary key, (2) event_id is the foreign key that links to the tblevent table, (3) picture is the image that will be used to display in the attendance module.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tbleventpicture` (
`picture_id` int(11) NOT NULL AUTO_INCREMENT,
`event_id` int(11) NOT NULL,
`picture` blob NOT NULL,
PRIMARY KEY (`picture_id`),
KEY `event_id` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblpenalty (penalty_id, penalty_amount)

Table Description: students that will not attend a certain event will be penalized with a certain amount, this is in accordance to the guidelines and is also stipulated in the student handbook of the institution, though not all schools implements this type of rule.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblpenalty` (
`penalty_id` int(11) NOT NULL AUTO_INCREMENT,
` penalty_amount` float NOT NULL,
PRIMARY KEY (`penalty_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblstudent (student_id, student_id_no, student_name, gender, contact_no, course_id, year_level, class_section, user_id)

Table Description: personal information of the students will be stored in tblstudent table.  The information includes; (1) student_id is the unique key provided by the database, (2) student_id_no is the id number provided by the school, (3) student_name stores the complete name of the student, (4) gender of the student, (4) contact_no or the contact information of the student, (5) course_id is a foreign key of the table that links to the tblcourse, this is the current course of the student, (6) year_level of the student, (7) class_section of the student, (8) user_id represents the user who encodes the information of the student.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblstudent` (
`student_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id_no` varchar(15) NOT NULL,
`student_name` varchar(100) NOT NULL,
`gender` int(1) NOT NULL,
`contact_no` varchar(15) NOT NULL,
`course_id` int(11) NOT NULL,
`year_level` int(1) NOT NULL,
`class_section` varchar(10) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`student_id`),
KEY `course_id` (`course_id`,`user_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblfingerprinttemplate (template_id, student_id, template, mask)

Table Description: fingerprint information of the student will be stored in a template. The system will compare the template stored in the database to the image or template captured during the attendance.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblfingerprinttemplate` (
`template_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`template` longblob NOT NULL,
`mask` int(11) NOT NULL,
PRIMARY KEY (`template_id`),
KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (user_id, account_name, username, password, user_category_id)

Table Description: the system will be accessed by the student council of the school, which means that more than one user will use the back-end part of the project. User information will be stored in the tbluser table and it has 5 columns; (1) user_id is the primary key of the table, (2) account_name is the full name of the user, (3) username is an identifier used to access a system, (4) password refers to the secret word used together with the username to gain access to the system, (5) user_category_id refers to the type or category of the user, this is a foreign key that links to the tblusercategory.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`account_name` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`user_category_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`),
KEY `user_category_id` (`user_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblusercategory (user_category_id, category_name, can_add, can_update, can_delete, can_print)

Table Description: the system can have multiple types of users depending on their privileges. The types of users are stored in the tblusercategory and it has 6 columns; (1) user_category_id is the primary key of the table, (2) category_name is the name of the group, (3) can_add is a column which allows the user to add a record in the system, (4) can_update is a privilege that allows the user to update a record, (5) can_delete is a privilege that allows the user to delete a record and (6) can_print is a privilege that allows the user to print a record.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblusercategory` (
`user_category_id` int(11) NOT NULL AUTO_INCREMENT,
`category_name` varchar(15) NOT NULL,
`can_add` int(1) NOT NULL,
`can_update` int(1) NOT NULL,
`can_delete` int(1) NOT NULL,
`can_print` int(1) NOT NULL,
PRIMARY KEY (`user_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables

—
— Constraints for table `tblattendance`
—
ALTER TABLE `tblattendance`
ADD CONSTRAINT `tblattendance_ibfk_3` FOREIGN KEY (`activity_id`) REFERENCES `tblevent` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblattendance_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `tblcourse` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblattendance_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `tblstudent` (`student_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblevent`
—
ALTER TABLE `tblevent`
ADD CONSTRAINT `tblevent_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `tblcourse` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbleventpicture`
—
ALTER TABLE `tbleventpicture`
ADD CONSTRAINT `tbleventpicture_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `tblevent` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblfingerprinttemplate`
—
ALTER TABLE `tblfingerprinttemplate`
ADD CONSTRAINT `tblfingerprinttemplate_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `tblstudent` (`student_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblstudent`
—
ALTER TABLE `tblstudent`
ADD CONSTRAINT `tblstudent_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `tblcourse` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblstudent_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbluser`
—
ALTER TABLE `tbluser`
ADD CONSTRAINT `tbluser_ibfk_1` FOREIGN KEY (`user_category_id`) REFERENCES `tblusercategory` (`user_category_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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.