Visitor Log Monitoring System Database Model

Visitor Log Monitoring System Database Model

Database schema

tblinmate (id, inmateidno, lastname, firstname, middlename, age, gender, remarks, image, encodedby)

Description: tblinmate is the table that stores information of the inmates or person confined in the jail, the information includes the name, age, gender and image.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblinmate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`inmateidno` varchar(11) NOT NULL,
`lastname` varchar(30) NOT NULL,
`firstname` varchar(30) NOT NULL,
`middlename` varchar(30) NOT NULL,
`age` int(3) NOT NULL,
`gender` varchar(6) NOT NULL,
`remarks` varchar(100) NOT NULL,
`image` blob NOT NULL,
`encodedby` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `encodedby` (`encodedby`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblvisitor (id, lastname, firstname, middlename, age, gender, contact, address, remarks, relationshiptoinmate, encodedby)

Description: tblvisitor is the table where the information of visitors are stored and encoded.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblvisitor` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lastname` varchar(30) NOT NULL,
`firstname` varchar(30) NOT NULL,
`middlename` varchar(30) NOT NULL,
`age` int(3) NOT NULL,
`gender` varchar(6) NOT NULL,
`contact` varchar(11) NOT NULL,
`address` varchar(100) NOT NULL,
`remarks` varchar(100) NOT NULL,
`relationshiptoinmate` varchar(20) NOT NULL,
`encodedby` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `encodedby` (`encodedby`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblvisitlog (id, inmateid, visitorid, dateofvisit, timein, timeout, month, year, purposeofrvisit, jailofficerid)

Description: the system records every time a visitor enters the jail; it is stored in the tblvisitlog. It is also the source of the different reports that are produced by the system.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tblvisitlog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`inmateid` int(11) NOT NULL,
`visitorid` int(11) NOT NULL,
`dateofvisit` date NOT NULL,
`timein` time NOT NULL,
`timeout` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`month` varchar(15) NOT NULL,
`year` int(4) NOT NULL,
`purposeofvisit` varchar(100) NOT NULL,
`jailofficerid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `inmateid` (`inmateid`,`visitorid`,`jailofficerid`),
KEY `jailofficerid` (`jailofficerid`),
KEY `visitorid` (`visitorid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (id, username, password, fullname, contact)

Description: tbluser is where the information of users that can access the system are stored and modified.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluser` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`fullname` varchar(50) NOT NULL,
`contact` varchar(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbljailofficer (id, jailofficeridno, officername, position, contact)

Description: information of the jail officer on duty is also part of the system, it is stored in the tbljailofficer table.

Create Table SQL Statement:

CREATE TABLE IF NOT EXISTS `tbljailofficer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`jailofficeridno` varchar(11) NOT NULL,
`officername` varchar(50) NOT NULL,
`position` varchar(15) NOT NULL,
`contact` varchar(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Constraints for dumped tables

— Constraints for table `tblinmate`

ALTER TABLE `tblinmate`
ADD CONSTRAINT `tblinmate_ibfk_1` FOREIGN KEY (`encodedby`) REFERENCES `tbluser` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

— Constraints for table `tblvisitlog`

ALTER TABLE `tblvisitlog`
ADD CONSTRAINT `tblvisitlog_ibfk_3` FOREIGN KEY (`inmateid`) REFERENCES `tblinmate` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
ADD CONSTRAINT `tblvisitlog_ibfk_1` FOREIGN KEY (`jailofficerid`) REFERENCES `tbljailofficer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `tblvisitlog_ibfk_2` FOREIGN KEY (`visitorid`) REFERENCES `tblvisitor` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

— Constraints for table `tblvisitor`

ALTER TABLE `tblvisitor`
ADD CONSTRAINT `tblvisitor_ibfk_1` FOREIGN KEY (`encodedby`) REFERENCES `tbluser` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

Literature

RFID Technology Based Attendance Management System

According to Sumita Nainan and et.al (2013), the primary aim of the research is to uniquely identify individual students based on their unique tag identifiers. The research should shower light on how scalable and efficient the system is. A systematic and serialised approach is required to solve this conundrum. The key characteristics of the application include; Perform automated attendance, Generate report of attendees for a particular course, Error free tag identifier detection, Easy scalability to incorporate more records, Integrity and security in data storage.

This paper concentrates on the principal purpose to overcome the human errors while recording student attendance and the creation of a data centric student attendance database system with an improved overall efficiency.  (ijcsi.org)

Technicality of the Project

In the development, the researcher used the following software components:

PHPmyadmin. This software is a free and open source tool written in PHP intended to handle the administration of MySQL with the use of a web browser. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows (www.phpadmin.net).

Hypertext Markup Language (HTML). Markup tells the Web browser how to display a WEB page’s words and images for the user (searchsoa.techtarget.com).

Cascading Style Sheet (CSS). They are the technical specifications for a layout, whether print or online. A style sheet for a Web page serves the same purpose, but with the added functionality of also telling the viewing engine (the Web browser) how to render the document being viewed. CSS is used to style web page to define how it will look (webdesign.about.com).

Web Browser. Technically, a Web Browser is a client program that uses HTTP (Hypertext Transfer Protocol) to make request of Web servers throughout the internet on behalf of the browser user. Most browsers support e-mail and the File Transfer Protocol (FTP) but a Web Browser is not required for those Internet protocols and more specialized client programs are more popular.

(searchwindevelopment.techtarget.com)

PHP. PHP is a programming language that can do all sorts of things; evaluate form data sent from a Web Browser, build custom web content to serve the browser, talk to a database, and even send and receive cookies (little packets of data your browser uses to remember things, like if you’re logged in to Codecademy). (www.codecademy.com)

MySQL. Is the world’s most popular open source database, enabling the cost-effective delivery or reliable, high performance and scalable Web-based and embedded database applications, including all five of the top five websites. (www.oracle.com)

UWAMP. Use to create a server for creating an application and it is a Windows-based server aims to give you access to test your web applications locally being offline (softstrive.com).

JavaScript. Stephen Chapman (2014) JavaScript is a programming language used to make web pages interactive. It runs on your visitor’s computer and doesn’t require constant downloads from your website. JavaScript is often used to create polls and quizzes. JavaScript is a multipurpose programming language. Generally speaking, it is used to allow Website developers to create web pages that have some intelligence built in to them. JavaScript can allow web pages adapt to what you do on the page, load new information, make decisions and respond to events (www.whatismybrowser.com).

Notepad ++. Is a free source code editor and notepad replacement that supports several languages. Running in the MS Windows environment, its use is governed by GPL License (notepad-plus-plus.org).

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.