Relational Database Design An Example: The Cambridge Fire Department The Scenario Imagine that you have recently been hired by the City of Cambridge's Management Information Systems Department to work on their GIS and citywide databases. As your first task, you have been asked to use database techniques to organize the data for the city's Fire Department. Your goal is to design and populate a database that records the current state of Cambridge's fire houses, fire companies, and firefighting apparatus. Your database design should include at least the following information: • The location of the fire houses • The neighborhoods they serve • The year they were built • The companies (e.g., Engine 5, Rescue 2) that they house • A description of the apparatus including: o Model year o Manufacturer o Model or type o Ladder length in feet o Water capacity in gallons (gal) o Water pumping rate in gallons per minute (gpm) All the vital information you need for this task is located on Cambridge's Fire Houses and Apparatus web page. You may find some extra details by looking at the individual web pages for each neighborhood's firehouse; links to these can be found at the top of the main firehouse page. Some Issues to Consider Queries The database design should be capable of answering the following kinds of questions through reasonably simple queries (although tables may need to be joined first): • Which fire houses were built after 1899? • Which companies are using apparatus from a model year earlier than 1990?• How many pieces of apparatus were built by Pierce? • Which pieces of equipment have a capacity of less than 500 gallons? • Others? Updates In addition to likely queries, you should also consider how your database will be affected when you need to update it. Below are some updates your database should be able to handle only by adding, removing, or updating rows. You should not have to resort to changing the structure of the database (adding or removing tables or columns). • Engine 2 replaces its 1989 Pierce Lance with a new Pierce model • The fire house serving the South Side is closed, and its company and equipment are transferred to the Lafayette Square station • Engine 5 retires its wagon without replacing it, becoming a "single unit" (pump only) • The disbanded Engine 7 is reinstated in its old location at 352 Main Street, adding new hose and pump units. (This will never happen, since the firehouse is being converted into a hotel!) •