Create a entity relationship (ER) diagram for each of the following scenarios using the standard Chens Notation for ER diagrams as discussed in class and in our textbook. You must use Chens notation as we have discussed in class. Use of any other type of modeling notation (e.g. UML, Crows foot) will result in a score reduction.
You may complete scenarios A and B using drawing software such as draw.io or another drawing program that may have templates for ER diagraming. You may, instead, complete them as hand-drawn diagrams. A hand drawn diagram must be complete and legible. It may be scanned and uploaded or it can be an easily legible photograph from a cell phone camera. If we cannot read it, we will not be able to grade it.
Scenario A Fitness rooms in a gym
a) Create an ER diagram using standard Chen notation for the following scenario:
A friend who works for Penn State Campus Recreation told you they would like to set up a database to keep track of the group fitness rooms and the classes in the IM Building. This database will only serve the IM building. Each ROOM has a unique number (e.g., 001, 002, 003, 116, etc.). They also want to keep track of the maximum capacity of each room.
MEMBERS can sign up for group fitness sessions (but it is possible they wont sign up for any sessions). Sessions are held in rooms. For each group SESSION, the date and start time, and room number
, and minimum capacity (below which the class will not run) will be recorded.
Each group session is officially led by one TRAINER. A trainer is identified by his/her certification number. When a trainer is employed, they must be assigned to a minimum of one class.
b) An ER diagram cannot easily support description/enforcement of temporal constraints (these are things having to do with time). Can you think of an example of a temporal constraint that could arise in this scenario that cannot be captured with this diagramming method?
Scenario B Medical Solutions Hospital
A hospital has a large number of registered physicians. Attributes of PHYSICIAN include Physician ID (the identifier) and specialty. Patients are admitted to the hospital by physicians. Attributes of PATIENT include patient ID(identifier) and patient name. Any patient who is admitted must have exactly one admitting physician. A physician may optionally admit any number of patients. Once admitted, a given patient must be treated by at least one physician. A particular physician may treat any number of patients, or none at all. Whenever a patient is treated by a physician, the hospital wishes to record the details of the treatment (TREATMENT DETAIL). Attributes of Treatment Detail include date, time, and results.
What to submit: Upload pdf, png, or jpeg file(s) containing your handwritten or digitally designed diagrams.