Designing the database schema and a partial working prototype of an Access database for an ambulance service. The database should capture the following data:
There are three stations (North, South, and West) from which EMTs are based. The database needs to track the name, address, and phone number of each station. Each EMT is assigned to only one station. The database should also track the EMTs name, home address, and phone number.
Each station also has vehicles assigned to it. The vehicle can be assigned to only one station. Each vehicle needs to have a name assigned to it in addition to make, model, and year.
When emergency calls come in, the dispatcher records the following information: call date, call time, caller name, emergency address, emergency zip code, and type of emergency.
For this project, use the following emergencies and codes (this should also be two separate fields, one for the code and one for the description): C=Childbirth, A=Auto accident, D=Drowning, H=Heart attack, DV=Domestic violence.
For this project, each call can only have one emergency type.
Additionally, the dispatcher records which EMTs respond to each emergency. More than one EMT can respond to a given emergency.
Steps:
1. Complete an ER diagram to show the tables and their relationships (in Visio)
• List the primary keys, foreign keys, and any additional attributes
• Decompose N-N relationships with join tables
• Normalize the tables to 3rd normal form
2. Create tables and relationships in Access using the ER diagram as the blueprint
3. Add sample data to the following tables:
• Station information - 3 records needed here - North, South, and West
• Call types - 5 types
4. Create forms for entering the following transactions:
• Add new EMT to database
• Add new vehicle
• Record new emergency call
5. Using the forms, enter sample data into the database - at least 5 EMTs, 5 vehicles, and 5
emergency calls