1) Revise the conceptual database design for the CFA case study based on the feedback you received from
Assignment 1. Also, you must consider the following constraints in your database design:
a) A person cannot work in overlapping incidents (at the same time)
b) A vehicle cannot be used overlapping incident (at the same time)
2) Create the logical design for your database, by following the below steps:
Step 2.1 Derive relations for logical data model
Step 2.2 Validate relations using normalisation
Step 2.3 Validate relations against user transactions
Step 2.4 Check integrity constraints
Step 2.5 Review logical data model against user requirements expressed in the case study
Step 2.6 Merge logical data models into a global model (see Figure 17.9 in the textbook as an example)
Step 2.7 Check for future growth
Step 2.8 Use MySQL Workbench to create the ERR database model (diagram). Include all details of
database (tables, relationships, PK, FK, indexes) based on your logical database design.
3) Use MYSQL workbench to create the [physical] database in MySQL server.
4) Enter hypothetical/sample data in your tables (minimum 30 records in each table)
5) Write SQL queries:
Query1: List top 3 most frequent used vehicles in the incidents each year, sorted based on year then based
on frequency (High to Low), between 2000 and 2015.
Query2: How much is the total water capacity of all tankers (in litres) belonging to each station? (total water
capacity per station)
Query3: How many of each equipment (compressor, printer, …) is available in the vehicles of each station?
Station 1 Station 2 …
Equipment 1 ### ###
Equipment 2 ### ###
…
Query4: What is the total number of hours each volunteer member worked for CFA, in each year?
Query5: List the members involved in an incident (of your choice), classified by their employment status
(staff/volunteer)?
Query6: List the stations that have at least a HazMat Van and at least 2 tankers.
Query7: List the CFA vehicles that have not been used in any incident, by Station.
Query8: Create a query that replaces empty (null) phone numbers of members with “###”
Query9: List incidents that involve more than 3 CFA members.
Query10: Create a query that lists the CFA members who have ‘ar’ in their surname (make sure there are
few people names with this characteristic in your sample data).
EER diagram for the CFA database exported from SQL Workbench as image. The diagram must be created
using MySQL workbench.
Describe any assumptions you made in your database design.