Please help create a Crow's Foot Model ERD diagram using the narrative and information below

- Be sure to label all keys (Primary and Foreign), relationships, and cardinalities using Crow's Foot Notation

Narrative

As the Assistant Chief of the city police department, my job is to manage our police officers and make sure they are all assigned to their designated police station where they will work from as their assigned work location. I need a police assignment database that will allow me to manage this responsibility. Here is the narrative that explains what I do, and what are some of the important information that I need the database to help keep track of.

We hire a lot of police officers, so for each police officer we assign a unique Badge Number to him/her. Along with the badge number, we also have on file the Officers First Name, Officers Last Name, Officer Address, Zip Code, Email, and Phone Number.

Because of the size of our city, we have multiple Police Stations in operation. For each Police Station we have a unique Location ID assigned to it, along with Location Name, Location Address, Zip Code, Phone Number, Fax Number, and Number of Desk Space in the station.

Since most of our officers work revolves around doing patrols, we also keep a fleet of Police Cars in each of the police stations. We maintain the following information on all our patrol cars. Car VIN Number (unique), Car Make, Car Model, Car Year, and Last Service Date.

Here are some of the key decisions that I make, which creates complex relationships between Police Officers, Police Stations, and Police Cars.

• Which station(s) the Officers will report to:
  • Each of our Officer can be assigned to at least one or up to many Station(s) when they are doing their patrol
  • Each of our Station will have at least one or up to many Officer(s) assign to it at any giving time so there are desks for the Officers to do their paperwork
• Which car in the fleet is to be assigned to which station as its parking location:
  • Each of our Car is assigned to only one Police Station as its main parking location
  • Each of our Station will have at least one or up to many Car(s) assign to it

I am having difficulty in trying to organize and plan each quarters assignments since all the information specified above are all done on paper or excel sheets. So, when it comes time to make changes, it is very inefficient and time-consuming.

We hope you can help us design a database where we can store the data into a database using best practices of database design concepts. More importantly, we would like for this database design to account for the ability to track which Officer(s) is assigned to which Station(s) after we assign them so we can report out all the Assignments. Also, we like to track which Car is parked at which Station so we know where our fleet is at.

You are to create an Entity Relationships Diagram (ERD) using the entity and attributes information provided in this assignment.

You are to create the below entities and attributes in Access, and establish the necessary relationships in the database.

• OfficerTable Database table capturing and storing hired officers information
  • BadgeNo Primary Key (Number)
  • OfficerFirstName - (Required Field - Text)
  • OfficerLastName - (Required Field - Text)
  • OfficerAddress (Required Field - Text)
  • OfficerZipCode (Required Field Text with input mask)
  • OfficerEmailAdd - (Required Field Text)
  • OfficerPhoneNo - (Required Field Text with input mask)
• StationTable Database table capturing and storing police stations information
  • LocationID Primary Key (Number)
  • LocationName (Required Field - Text)
  • LocationAddress (Required Field - Text)
  • LocationZipCode (Required Field Text with input mask)
  • LocationPhoneNo - (Required Field Text with input mask)
  • LocationFaxNo - (Required Field Text with input mask)
  • LocationDeskCount - (Required Field Number)
• PatrolCarTable Database table capturing and storing patrol car fleet information
  • CarVinNo Primary Key (Number)
  • CarMake - (Required Field - Text)
  • CarModel - (Required Field - Text)
  • CarYear - (Required Field - Number)
  • CarLastService (Required Field Date/Time)
  • AssignedStation - Foreign Key (When creating a Form, this field will be a Combo Box on the Form - Displaying LocationID, LocationName in the dropdown list (combo box) for users to select)
• OfficerAssignTable Database table capturing and storing assignments of officers (Note: each officer can be assigned to at least one or many stations, and each station can have multiple officers assigned to it)
  • AssignmentNo Primary Key (Number)
  • BadgeNo - Foreign Key
  • LocationID - Foreign Key (When creating a Form using this table, this field will be a Combo Box on the Form - Displaying LocationID, LocationName in the dropdown box for users to select)
  • AssignStartDate (Required Field Date/Time)
  • AssignEndDate - (Required Field Date/Time

• Application User Interface Forms and Feature Requirements:

• AssignmentForm User interface form to assign Officers in the database to Stations (Note: each officer can be assigned to at least one or many stations, and each station can have multiple officers assigned to it)
  • Hint: The AssignmentForm is based on the OfficerTable meaning the form shows each Officer at a time, then allow administrator of the form to select Stations to assign the officer to as many station as needed
  • Hint: A subform will need to be created on this form to resolve the many-to-many relationship in the assignment form design view you will need to use 2 tables to complete this form
  • Note: The subform on the AssignmentForm needs to allow users to pick a station from a dropdown list of Police Stations in the database for every assignment
• OfficerForm User interface form to add/update Officers in the database (Note: include input masks on the form)
• StationForm User interface form to add/update Stations in the database (Note: include input masks on the form)
• PatrolCarForm User interface form to add/update Patrol Cars in the database (Note: include input masks on the form)
  • Note: The form needs to include a dropdown list (combo box) for selecting Location where the car is assigned to.

• Reporting Requirements:

• Officer Assignment Report The Chief is asking for a report that shows all the officers assignments in the database. The report will show the following:
  • Listing of all the Officers in the database
    • BadgeNo, OfficerFirstName, OfficerLastName
  • Under each Officer, the report shows list of the assignments the officer is assigned with. For each assignment listed, show the following:
    • LocationID, LocationName, AssignStartDate, AssignEndDate
• Fleet Assignment Report The Chief is asking for a report that shows all the cars assignments in the database. The report will show the following:
  • Listing of all the Stations in the database
    • LocationID, LocationName
  • Under each Station, the report shows list of the cars assigned to the location. For each car listed, show the following:
    • CarVinNo, CarYear, CarLastService

• Hint: Use report wizard grouping to create your reports.

• Only need the ERD diagram is needed