Fire Safety Inspection Certificate System Database Model

Fire Safety Inspection Certificate System Database Model

Fire Safety Inspection Certificate System is a web based system designed for the applicants who wants to apply for a fire safety certificate online. The implementation of this project would also be a great help to the administration of bureau of fire.

Significance of the study

The results of the study will be beneficial to the following:

Chief Operation/Collecting Agent – It helps for the chief operational collecting agents for it allows easy and convenient inputting, viewing, checking and keeping records. Also it allows easy viewing of unpaid FSIC, and managing financial planning.

Chief Administrative – It helps for the chief administrative for it allows easy viewing, checking and preparing data records.

Fire Marshall – It helps for the fire Marshall for it helps in terms of decision making.

Establishment – It helps for the Establishment to easy searching for their records and accounts.

Future Researchers – It helps for the Future Researcher for it can be their basis for their future system to be developed soon.

Applicants – It helps for the Resident for easy application of permit.

The Fire Safety Inspection Certificate System has 8 tables:

• tblapplicant
• tblapplication
• tblfee
• tblincome
• tblinspectionmodule
• tblinspectionstatus
• tbllog
• tbluser

Database Design/Model/Schema

tblapplicant (applicant_id, applicant_name, contact_no, address, username, password, account_status)

Table Description: the applicant will need first to register its personal information to be able to apply for fire safety compliance certificate. The table that will store that information is the tblapplicant table and it has 7 columns; (1) applicant_id is the primary key of the table, (2) applicant_name is the complete name of the applicant, (3) contact_no is the contact information or number of the applicant, (4) address is the complete address of the applicant, (5) username and (6) password is the credential that will be used to login in the system, (7) account_status refers to the activation of the user account.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblapplicant` (
`applicant_id` int(11) NOT NULL AUTO_INCREMENT,
`applicant_name` varchar(50) NOT NULL,
`contact_no` varchar(15) NOT NULL,
`address` varchar(100) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
`account_status` int(1) NOT NULL,
PRIMARY KEY (`applicant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblapplication (application_id, applicant_id, application_category, owner_name, project_title, occupancy_classification, construction_location, date_received, date_release, no_of_storey, total_floor_area, lot_area, plan_evaluator, picture, status, remarks, date_approved, expiry_date)

Table Description: once the account of the applicant has been approved, they are now allowed to access the system and apply for a fire safety certificate. The information  will be stored in the tblapplication table and it has 18 fields or columns; (1) application_id is the primary key and the unique identifier of the record, (2) applicant_id is a foreign key that refers to the applicant information, (3) application_category is the type of application, (4) owner_name refers to the owner of the building or business, (5) project_title is the name of the building or the name of the business, (6) occupancy_classification this is to classify if the business or building is owned/rented by the owner, (7) construction_location this refers to the address or location of the building/business, (8) date_received refers to the date the application was submitted, (9) date_release the expected released date of the application, (10) no_of_storey refers to the number of floors of the building, (11) total_floor_area the area of the building, (12) lot_area the area of the entire lot measured in square meters, (13) plan_evaluator is the officer that will inspect and incharge of the application, (14) picture the image of the location or the building for documentation purposes, (15) status refers to if the application was approved or disapproved, (16) remarks is the brief explanation or observation about the application, (17) date_approved is the date where the application will be granted a certificate, (18) expiry_date the date that the certificate will expire and needs to be renewed before that date.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblapplication` (
`application_id` int(11) NOT NULL AUTO_INCREMENT,
`applicant_id` int(11) NOT NULL,
`application_category` int(1) NOT NULL,
`owner_name` varchar(50) NOT NULL,
`project_title` varchar(100) NOT NULL,
`occupancy_classification` varchar(25) NOT NULL,
`construction_location` varchar(100) NOT NULL,
`date_received` date NOT NULL,
`date_release` date NOT NULL,
`no_of_storey` int(3) NOT NULL,
`total_floor_area` float NOT NULL,
`lot_area` int(11) NOT NULL,
`plan_evaluator` varchar(50) NOT NULL,
`picture` longblob NOT NULL,
`status` int(1) NOT NULL,
`remarks` varchar(50) NOT NULL,
`date_approved` date NOT NULL,
`expiry_date` date NOT NULL,
PRIMARY KEY (`application_id`),
KEY `applicant_id` (`applicant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblfee (fee_id, fee_name, amount)

Table Description: fire inspection has a lot of requirements and it entails a lot of payment. The list of fees will be stored in the tblfee table and it has 3 attributes; (1) fee_id is the primary key of the table, (2) fee_name is the name of the fee, and (3) amount is the amount to be paid for a specific fee.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblfee` (
`fee_id` int(11) NOT NULL AUTO_INCREMENT,
`fee_name` varchar(30) NOT NULL,
`amount` float NOT NULL,
PRIMARY KEY (`fee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblincome (income_id, application_id, total_payment, date_received, user_id)

Table Description: this table will store the payment of the applicants and it has 5 attributes; (1) income_id is the primary key of the table, (2) application_id links to the application details from the tblapplication, (3) total_payment is the total amount that the applicant has paid, (4) date_received is the date of payment, and (5) user_id refers to the user who processed and receives the payment.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblincome` (
`income_id` int(11) NOT NULL AUTO_INCREMENT,
`application_id` int(11) NOT NULL,
`total_payment` float NOT NULL,
`date_received` date NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`income_id`),
KEY `application_id` (`application_id`,`user_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblinspectionmodule (module_id, module_description)

Table Description: the list of requirements that needs to be complied the business owner or applicant is stored in the tblinspectionmodule. The table has only 2 columns; (1) module_id is the primary key of the table and the (2) module_description is the name of the requirement.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblinspectionmodule` (
`module_id` int(11) NOT NULL AUTO_INCREMENT,
`module_description` varchar(100) NOT NULL,
PRIMARY KEY (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblinspectionstatus (status_id, application_id, module_id, status, date_accomplished, remarks, fee_id)

Table Description: tblinspectionstatus is the table where the inspector will evaluate the requirements one by one if the business complies with the proper protocol. The table has 7 fields; (1) status_id is the primary key of the table, (2) application_id links to the application table, (3) module_id is the foreign key that refers to the specific requirement that needs to be comply, (4) status refers to if the requirement has met its standard or not, (5) date_accomplished the date the requirement has been approved, (6) remarks refers to the brief narrative of the requirement, (7) fee_id this corresponds to the amount to be paid for this requirement.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tblinspectionstatus` (
`status_id` int(11) NOT NULL AUTO_INCREMENT,
`application_id` int(11) NOT NULL,
`module_id` int(11) NOT NULL,
`status` int(1) NOT NULL,
`date_accomplished` date NOT NULL,
`remarks` varchar(100) NOT NULL,
`fee_id` int(11) NOT NULL,
PRIMARY KEY (`status_id`),
KEY `application_id` (`application_id`,`module_id`),
KEY `module_id` (`module_id`),
KEY `fee_id` (`fee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbllog (log_id, user_id, action, date_recorded)

Table Description: tbllog records the activity of the user once logged-in in the system. It includes the following attributes; (1) log_id is the primary key of the table, (2) user_id links to the user from the tbluser table, (3) action refers to the activity the user has executed, (4) date_recorded the date when the activity or action has beed executed.

CREATE SQL Statement:

CREATE TABLE IF NOT EXISTS `tbllog` (
`log_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`action` varchar(30) NOT NULL,
`date_recorded` date NOT NULL,
PRIMARY KEY (`log_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (user_id, username, password, fullname, contact, designation)

Table Description: the application login credentials will only allow them to apply and to monitor the status of their application, whereas the user of the system has all the control over the information system. The table that will store the user information is the tbluser and it has 6 attributes; (1) user_id is the primary key of the table, (2) username and (3) password are used to access the system, (4) fullname is the complete name of the user, (5) contact stores the contact number of the user/employee, (6) designation refers to the users/employees rank.

CREATE SQL Statement:

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

Constraints for dumped tables
—
— Constraints for table `tblapplication`
—
ALTER TABLE `tblapplication`
ADD CONSTRAINT `tblapplication_ibfk_1` FOREIGN KEY (`applicant_id`) REFERENCES `tblapplicant` (`applicant_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblincome`
—
ALTER TABLE `tblincome`
ADD CONSTRAINT `tblincome_ibfk_2` FOREIGN KEY (`application_id`) REFERENCES `tblapplication` (`application_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblincome_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblinspectionstatus`
—
ALTER TABLE `tblinspectionstatus`
ADD CONSTRAINT `tblinspectionstatus_ibfk_3` FOREIGN KEY (`fee_id`) REFERENCES `tblfee` (`fee_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblinspectionstatus_ibfk_1` FOREIGN KEY (`module_id`) REFERENCES `tblinspectionmodule` (`module_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblinspectionstatus_ibfk_2` FOREIGN KEY (`application_id`) REFERENCES `tblapplication` (`application_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tbllog`
—
ALTER TABLE `tbllog`
ADD CONSTRAINT `tbllog_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_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.