Draw an Enhanced Entity-Relationship (EER) diagram that describes the following business environment.
The City of San Francisco wants to design a system to keep track of its emergency services personnel. Each emergency services employee has a unique employee number, a name, and a cell phone number. Some of these employees are supervisors, supervising other employees. An employee can have (or may not have any) dependents (spouse, children). A dependent has a first name and date of birth. There are three kinds of emergency services personnel: firefighters, police officers, and emergency medical technicians (EMT). An emergency services employee can have only one of these three jobs.
A firefighter is assigned to one fire station. Obviously, several firefighters are assigned to a fire station. Each fire station is identified by a unique station number, and has an address, and a telephone number. A firefighter can be one or more of the following: an airport specialist, a marine specialist, a high-rise building specialist, or can have one or more other specialties. For marine specialists, the city wants to know how many years the firefighter has had this specialty. For high-rise specialists, the city wants to know their proficiency rating on a 1-5 scale. For every firefighter, the city wants to know their rank and the year they joined the department.
[Note: In this paragraph be careful about the difference between the term “police officer” and “patrol officer”.] A police officer can be a patrol officer, a detective, or can be in some other police specialty, but can be in only one of these. The city wants to store each police officer’s rank and date of hire. Each patrol officer is assigned to one patrol car, which belongs exclusively to that patrol officer. Each car has a unique license plate number, a model, and the year it was manufactured. The city stores data about active police cases, a unique case number, the date the case was opened, and the type of case (robbery, burglary, etc.). A detective may be assigned to several open cases or to none. A case must have at least one detective assigned to it. The city wants to keep track of how long a particular detective has been assigned to a particular case.
An emergency medical technician (EMT) is assigned to just one, particular ambulance. Each ambulance has two EMTs in it at any one time and several EMTs are associated with a particular ambulance because of different shifts. The city wants to store each EMT’s salary and age. It also wants to store the name of the manufacturer of each ambulance and the year it was manufactured.