Fire Incident Information System Database Design

Fire Incident Information System Database Design

Introduction

A Fire Incident Information System (FIIS) is a crucial tool for effectively managing and responding to fire emergencies. It provides a centralized platform for tracking incidents, managing fire safety information, and coordinating with fire departments. By streamlining these processes, FIIS enhances emergency response capabilities and improves overall fire safety within a community.

This tutorial is designed for individuals involved in fire safety, emergency management, or database administration. It will guide you through the process of creating a robust and efficient FIIS database, covering essential concepts such as database design, table structures, and relationships.

By the end of this tutorial, you will have a solid understanding of how to build a FIIS database that can effectively support your organization’s fire safety initiatives.

Requirements Analysis

Functional Requirements

1. Track and Record Fire Incidents: The system must allow users to log detailed information about each fire incident, including location, type of fire, response times, and damage estimates. This helps in accurate record-keeping and efficient response management.
2. Store Information on Fire Types: It should categorize and store different types of fires (e.g., structure fires, vehicle fires) with detailed descriptions. This classification aids in analyzing fire patterns and trends.
3. Maintain Records of Fire Safety Tips and Contacts: The system needs to store valuable fire safety tips and contact information for fire departments and organizations. This ensures that users have quick access to essential resources and contacts.
4. Enable User Management and Secure Access: It should support user accounts with various roles and permissions to ensure secure access to the system. Admins should be able to manage user roles and control access based on user needs.
5. Backup and Restore Functionality: The system must provide backup and restore options to safeguard data against loss or corruption. Regular backups ensure data integrity and continuity of operations.

Non-Functional Requirements

1. Data Security: The system must implement robust security measures to protect sensitive information. This includes encrypted passwords, secure access controls, and regular security updates to prevent unauthorized access and data breaches.
2. Scalability: It should be designed to handle growing amounts of data and an increasing number of users without performance degradation. Scalability ensures that the system remains efficient as demands increase.
3. Ease of Use: The user interface should be intuitive and user-friendly, allowing users to navigate and operate the system efficiently without extensive training. An easy-to-use system enhances user satisfaction and productivity.

Database Tables and Schema Design

The database for the Fire Incident Information System is designed to efficiently manage and organize various types of data related to fire incidents, fire safety resources, user information, and system backups. Below is an explanation of each table and its role within the database:

1. tblIncident

• Purpose: This table stores detailed information about each fire incident reported to the system. It is the central table for managing incident records.
• Columns:
  • incident_id (INT PRIMARY KEY): A unique identifier for each incident report.
  • location_id (INT): References the location_id in the tblBarangay table, indicating where the incident occurred.
  • complete_address (VARCHAR(255)): Provides the full address of the incident location.
  • fire_type_id (INT): References the fire_type_id in the tblFireType table, specifying the type of fire.
  • reported_date (DATETIME): The date and time when the incident was reported.
  • arrival_date (DATETIME): The date and time when the fire station arrived at the scene.
  • responded_by (TEXT): A list of firefighters or units that responded to the incident.
  • time_fire_out (DATETIME): The date and time when the fire was fully extinguished.
  • estimated_damage (DECIMAL(10, 2)): The estimated financial damage caused by the fire, expressed in PHP.
  • fatality_casualty (INT): The number of fatalities or casualties resulting from the incident.
  • injured (INT): The number of individuals injured in the incident.
  • number_establishment (INT): The number of establishments affected by the fire.
  • number_family_affected (INT): The number of families affected by the incident.
  • no_liters_consumed (DECIMAL(10, 2)): The amount of water (in liters) consumed in extinguishing the fire.
  • remarks (TEXT): Optional field for additional details or comments about the incident.
  • documentation (VARCHAR(255)): Stores the filename or path of any related documentation (e.g., a PDF report).
  • latitude (DECIMAL(9, 6)): Latitude coordinate of the incident location.
  • longitude (DECIMAL(9, 6)): Longitude coordinate of the incident location.
  • encoded_by (INT): References the user_id in the tblUser table, indicating who recorded the incident.

2. tblBarangay

• Purpose: This table maintains information about barangays (neighborhoods or areas) where incidents occur.
• Columns:
  • location_id (INT PRIMARY KEY): A unique identifier for each barangay.
  • barangay (VARCHAR(255)): The name of the barangay or area.

3. tblFireType

• Purpose: This table categorizes different types of fires, helping to classify incidents for better analysis and reporting.
• Columns:
  • fire_type_id (INT PRIMARY KEY): A unique identifier for each fire type.
  • type (VARCHAR(255)): A brief description of the fire type (e.g., Structure Fire, Vehicle Fire).
  • description (TEXT): A detailed description of the fire type.

4. tblUser

• Purpose: This table manages user information, including authentication details and roles.
• Columns:
  • user_id (INT PRIMARY KEY): A unique identifier for each user.
  • username (VARCHAR(255)): The username used for logging into the system.
  • password (VARCHAR(255)): A hashed password for secure authentication.
  • user_type (VARCHAR(50)): Indicates the user’s role, such as admin or user.
  • complete_name (VARCHAR(255)): The full name of the user.

5. tblFireSafetyInfo

• Purpose: This table stores educational resources and safety tips related to fire safety.
• Columns:
  • resource_id (INT PRIMARY KEY): A unique identifier for each safety resource.
  • title (VARCHAR(255)): The title of the fire safety tip or resource.
  • content (TEXT): The content of the resource, which could be text or a link to an external resource.

6. tblFireContact

• Purpose: This table provides contact information for fire departments and organizations.
• Columns:
  • contact_id (INT PRIMARY KEY): A unique identifier for each contact entry.
  • name (VARCHAR(255)): The name of the fire department or organization.
  • phone_number (VARCHAR(255)): The contact phone number for the organization.
  • facebook_page (VARCHAR(255)): Optional field for the Facebook page URL of the organization.

7. tblBackup

• Purpose: This table handles backup records, ensuring data can be restored if needed.
• Columns:
  • id (INT PRIMARY KEY): A unique identifier for each backup record.
  • user_id (INT): References the user_id in the tblUser table, indicating who performed the backup.
  • backup_file (VARCHAR(255)): The filename or path of the backup file.
  • backup_date (DATETIME): The date and time when the backup was created.

Each table is designed to handle specific aspects of the Fire Incident Information System, facilitating efficient data management and retrieval. The relationships between these tables support comprehensive tracking, reporting, and analysis of fire incidents and related information.

Relationships and Constraints

Defining Relationships

Defining relationships between tables in a database is crucial for ensuring data consistency and enabling meaningful queries. Relationships are typically established using foreign keys, which link records in one table to records in another. Here’s how to define the relationships in our Fire Incident Information System:

1. tblIncident and tblBarangay:
   • Relationship: Many-to-One
   • Foreign Key: location_id in tblIncident references location_id in tblBarangay.
   • Purpose: This relationship connects each fire incident to a specific barangay or location where the incident occurred.
2. tblIncident and tblFireType:
   • Relationship: Many-to-One
   • Foreign Key: fire_type_id in tblIncident references fire_type_id in tblFireType.
   • Purpose: This links each fire incident to a specific type of fire, facilitating classification and analysis.
3. tblIncident and tblUser:
   • Relationship: Many-to-One
   • Foreign Key: encoded_by in tblIncident references user_id in tblUser.
   • Purpose: This identifies the user who recorded or encoded the incident, linking incidents to their creators.
4. tblUser and tblBackup:
   • Relationship: One-to-Many
   • Foreign Key: user_id in tblBackup references user_id in tblUser.
   • Purpose: This links backup records to the users who performed them, providing accountability and traceability for backups.

Boreas – 618 A Firefighting Robot

Enforcing Data Integrity

Data integrity is vital for ensuring that the database remains accurate and reliable. Constraints are rules applied to data fields to enforce validity and consistency. Here’s how to enforce data integrity in the Fire Incident Information System:

1. Not Null Constraints:
   • Purpose: Ensure that critical fields cannot be left empty, which is essential for maintaining the completeness of the data.
   • Example: In tblIncident, fields like incident_id, location_id, and reported_date should be defined with NOT NULL constraints to ensure every incident record has these essential details.
2. Unique Constraints:
   • Purpose: Guarantee that each value in a column is unique across the table, preventing duplicate entries.
   • Example: In tblUser, the username column should have a UNIQUE constraint to ensure that no two users have the same username, which is crucial for secure login and user identification.
3. Primary Key Constraints:
   • Purpose: Define a unique identifier for each record in a table. This ensures that every record can be uniquely identified.
   • Example: The incident_id in tblIncident and user_id in tblUser are primary keys that uniquely identify each record in their respective tables.
4. Foreign Key Constraints:
   • Purpose: Maintain referential integrity by ensuring that a record in one table must correspond to a valid record in another table.
   • Example: The foreign key location_id in tblIncident ensures that each incident must be linked to a valid location_id in tblBarangay, preventing incidents from being associated with non-existent locations.

By defining these relationships and enforcing constraints, the Fire Incident Information System maintains a robust and reliable database structure, which supports accurate data management, reduces the risk of errors, and ensures consistency across the system.

SQL Statement

-- Create tblBarangay
CREATE TABLE tblBarangay (
location_id INT PRIMARY KEY AUTO_INCREMENT,
barangay VARCHAR(255) NOT NULL
);

-- Create tblFireType
CREATE TABLE tblFireType (
fire_type_id INT PRIMARY KEY AUTO_INCREMENT,
type VARCHAR(255) NOT NULL,
description TEXT
);

-- Create tblUser
CREATE TABLE tblUser (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
user_type ENUM('admin', 'user') NOT NULL,
complete_name VARCHAR(255) NOT NULL
);

-- Create tblIncident
CREATE TABLE tblIncident (
incident_id INT PRIMARY KEY AUTO_INCREMENT,
location_id INT,
complete_address VARCHAR(255) NOT NULL,
fire_type_id INT,
reported_date DATETIME NOT NULL,
arrival_date DATETIME,
responded_by TEXT,
time_fire_out DATETIME,
estimated_damage DECIMAL(10, 2),
fatality_casualty INT,
injured INT,
number_establishment INT,
number_family_affected INT,
no_liters_consumed DECIMAL(10, 2),
remarks TEXT,
documentation VARCHAR(255),
latitude DECIMAL(9, 6),
longitude DECIMAL(9, 6),
encoded_by INT,
FOREIGN KEY (location_id) REFERENCES tblBarangay(location_id),
FOREIGN KEY (fire_type_id) REFERENCES tblFireType(fire_type_id),
FOREIGN KEY (encoded_by) REFERENCES tblUser(user_id)
);

-- Create tblFireSafetyInfo
CREATE TABLE tblFireSafetyInfo (
resource_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL
);

-- Create tblFireContact
CREATE TABLE tblFireContact (
contact_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
phone_number VARCHAR(255) NOT NULL,
facebook_page VARCHAR(255)
);

-- Create tblBackup
CREATE TABLE tblBackup (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT,
backup_file VARCHAR(255) NOT NULL,
backup_date DATETIME NOT NULL,
FOREIGN KEY (user_id) REFERENCES tblUser(user_id)
);

FREE DOWNLOAD SQL

Conclusion

In this tutorial, we explored the essential elements of designing a Fire Incident Information System database. We covered the following key aspects:

• Database Design: We outlined the purpose and structure of the database, including the tables tblIncident, tblBarangay, tblFireType, tblUser, tblFireSafetyInfo, tblFireContact, and tblBackup. Each table was designed to store specific types of information related to fire incidents, locations, fire types, users, safety tips, contacts, and backups.
• Table Structures: We provided SQL CREATE TABLE statements to define the structure of each table, including primary keys, foreign keys, and constraints to enforce data integrity.
• Relationships and Constraints: We explained how to define relationships between tables using foreign keys and how to enforce data integrity with constraints such as NOT NULL, UNIQUE, and PRIMARY KEY.

Further Enhancements

To enhance the Fire Incident Information System further, consider the following:

• Integration with GIS: Implement Geographic Information Systems (GIS) to provide advanced location tracking and visualization of incidents. This could include mapping incidents in real-time, analyzing geographical patterns, and integrating with other spatial data.
• Frontend Development: Develop a user-friendly frontend interface to interact with the database. This could include dashboards for viewing incident reports, maps for location tracking, and forms for entering and updating data.
• Advanced Reporting: Implement advanced reporting and analytics features to generate insights from the data, such as incident trends, response times, and damage assessments.
• Notifications and Alerts: Add features for automated notifications and alerts based on incident data, such as sending alerts to fire departments or emergency response teams when a new incident is reported.

We encourage you to implement and test the database design to ensure it meets your specific needs. Start by setting up the database schema using the provided SQL statements, then begin populating it with sample data to test its functionality. As you explore the system, consider additional features or enhancements that could improve its effectiveness and usability.

Feel free to adapt and expand upon this design based on your requirements, and continue to refine the system to better serve the needs of fire departments, emergency response teams, and municipal offices.

Readers are also interested in:

Fire Safety Inspection Certificate System ER Diagram

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.