Purchase Solution

Fire Department Tracks for Reporting on Emergency Incidents

Not what you're looking for?

Ask Custom Question

The following tables represent some of the data which the Fire Department tracks for reporting on emergency incidents.

INC - Incident table (Gives information about an emergency incident.)

INC_IDY (Unique incident number) SIT* (Situation Type) ADATE (Alarm Date) ATIME (Alarm Time) FIRST_DUE_STN# (The station district in which the incident occurred)
9000188 TAT 1/3/2009 14:20:22 ST05
9000770 TA 1/11/2009 17:25:31 ST02
9002128 MED 1/30/2009 8:39:15 ST11
9002322 SFIR 2/1/2009 15:55:25 ST01
9002516 MED 2/3/2009 13:19:16 ST08

* TA = Traffic accident, TAT = Traffic accident, person trapped, MED = Medical emergency, and SFIR = Structure fire.
# ST01 = Fire station 1, ST02 = Fire station 2, etc.

CMP_INC_RSP - Company response table (Gives information as to the units dispatched to an incident.)

INC_IDY (Unique incident number) CMP_IDY$ (Unit dispatched) DSP_TME (Time unit dispatched) RSP_TME (Time unit responded) ARR_TME (Time unit arrived) INS_TME (Time unit is available for another assignment)
9000188 E3 14:20:43 14:22:06 14:23:19 14:28:39
9000188 T1 14:20:43 14:22:38 14:24:54
9000770 E2 17:25:49 17:26:47 17:28:03 18:10:16
9000770 T8 17:25:49 17:26:55 17:30:25 18:08:24
9002128 E11 8:39:38 8:40:35 8:43:07 8:53:08
9002322 E1 15:56:56 15:57:09 15:59:04 16:05:48
9002322 E2 15:56:56 15:57:25 16:00:04 16:06:30
9002322 T1 15:56:56 15:57:15 15:59:49 16:01:21
9002516 E8 13:19:31 13:19:40 13:19:42 13:37:50

$ E1 = Engine at station 1, T1 = Truck at station 1, E2 = Engine at station 2, etc.

Using the table and column data given above as a guide, provide SQL queries that you would execute against the full data set - approximately 50,000 incidents per year - for the following questions:

a) To how many traffic accidents did the Fire Department send two or more companies in 2009?
b) For each incident, what is the first arriving company?
c) What is the delay between the time the alarm was received and the time each unit arrived at the scene?
d) How many times has each unit responded to incidents outside of their primary running district? (The FIRST_DUE_STN in the INC table indicates in which station's primary running district the incident occurred).

Purchase this Solution
Solution Summary

The solution discusses fire department tracks for reporting on emergency incidents.

Solution Preview

(a)
SELECT Count(INC_IDY) AS IncidentsWithMultipleDispatches
FROM [SELECT CMP_INC_RSP.INC_IDY, Count(CMP_INC_RSP.INC_IDY) AS Dispatches
FROM CMP_INC_RSP
GROUP BY CMP_INC_RSP.INC_IDY
HAVING Count(CMP_INC_RSP.INC_IDY)>1]. AS IntermediateTable;

(b)
SELECT INC_IDY, CMP_IDY, ARR_TME
FROM CMP_INC_RSP
WHERE ARR_TME ...

Purchase this Solution
Free BrainMass Quizzes
Basic Networking Questions

This quiz consists of some basic networking questions.

C# variables and classes

This quiz contains questions about C# classes and variables.

Basic UNIX commands

Use this quiz to check your knowledge of a few common UNIX commands. The quiz covers some of the most essential UNIX commands and their basic usage. If you can pass this quiz then you are clearly on your way to becoming an effective UNIX command line user.

Word 2010: Tables

Have you never worked with Tables in Word 2010? Maybe it has been a while since you have used a Table in Word and you need to brush up on your skills. Several keywords and popular options are discussed as you go through this quiz.

Basic Computer Terms

We use many basic terms like bit, pixel in our usual conversations about computers. Are we aware of what these mean? This little quiz is an attempt towards discovering that.

View More Free Quizzes
Related BrainMass Solutions

  • Fire Accidents or Incidents

    In addition the emergency operations center will be the physical and/or virtual location whose function is to coordinate and provide support for the crisis incident team. The fire accidents or incidents are examined.

  • role of the US DOT

    110540 Role of the US Department of Transportation Explain the role of the US DOT in both preventing incidents and responding to those incidents should they occur.

  • Analysing Improvement in Firemen Policy

    the fire department.

  • Emergency Managers' Response to Terrorism

    Emergency management uses an "all hazards" framework based on the premise that most incidents will draw on similar resources and will apply a similar structure in managing the response to an incident, as opposed to identifying a different structural response

  • High Risk Hazards

    : The risk should be classified as low medium or high based on the intensity of fire.

  • Information Security Management - Mitigation Plans for Hotels

    Details of Plan • Activate nearest fire alarm • Call emergency number for fire brigade • Use fire extinguishers to prevent fire from spreading • Assist persons with disabilities to get out of the hotel as quick as possible • Follow evacuation plan

  • Columbine High School

    Fire Administration Major Incidents Investigation Team, (2000). Special Report: Wanton Violence at Columbine High School April 20, 1999. 2000. U.S. Fire Administration and The Federal Emergency Management Agency: Emmitsburg, MD.

  • fire safety / emergency management document

    For additional background, see Occupational Safety and Health Administration's (OSHA) standard on fire prevention, 29 CFR 1910.39. Your emergency management document should mention the key phases of emergency management.

  • Homeland Security and Emergency Management..

    When the Department of Homeland Security was developed after the 9/11 attacks, emergency management and FEMA, the federal organization created for emergency management incidents, was implemented into the DHS.

View More Related Solutions