Instructions concerning ERDs Create a logical data model documented with an ERD for each of the problems on the following pages using the crowsfoot format discussed in the text and in the presentations for this class. Be sure each entity has the entity name at the top of the box, the primary key attribute or attributes in the middle box of the entity, and the non-key attributes in the bottom of the box. Lines should separate each part of the entity box.

Follow these instructions about each ERD:

The ERD must not have any many-to-many (m:n) relationships.

All attributes must be placed within an entity.

Each entity must have a primary key defined. A primary key may consist of one or more attributes.

Each relationship must have a foreign key. Denote the foreign key(s) with the notation (FK) on the ERD.

Each relationship must include both a maximum and minimum cardinality for both sides of the relationship.

Each relationship should have a verb or verb phrase to describe it.

You do not have to differentiate between an identifying or non-identifying relationship.

Question #1 Application Scenario. Design a database for a single hospital (there is only one hospital it is not part of a group of hospitals). The hospital wants to keep track of data about transactions regarding patients. The hospital wants to keep track of admitting a patient to the hospital, as well as treating a patient. Both admitting and treating a patient are performed by a health care professional (HCPROF). Sample data for this scenario is provided in the Excel workbook called HospitalTransactionData.xlsx. On the worksheet, each row represents a contact between a patient and a HCPROF.

Assume the hospital has a large number of HCPROFs, but they store the same data about HCPROFs. Attributes of HCPROF include HCPROFID (identifier), type, and specialty. The hospital wants to keep track of the manager for a HealthCareProfessional. Each HCPROF can have only one manager; however, a manager might manage multiple HCPROFs. All managers are also health care professionals.

When a patient is admitted to the hospital, the hospital wants to keep track of the date and time the patient was admitted and also the admitting HCPROF. It is possible a patient may be admitted to the hospital more than once. The hospital wants to keep the patients on file with the assumption the patient may be admitted more than once and the hospital needs to know each different date and time a patient was admitted. Attributes of PATIENT include patientID (identifier) and patient name first and last names. Any individual patient who is admitted at a given date and time must have exactly one admitting HCPROF, but an HCPROF may admit no patients or many patients.

Once admitted, a given patient may be treated by no HCPROFs, but could be treated by multiple HCPROFs. The particular HCPROF who admits a given patient may or may not treat that same patient. A particular HCPROF may treat any number of patients or may not treat any patients.

Whenever a patient is treated, the hospital records the details of the treatment (TREATMENTDETAIL). Attributes of TREATMENTDETAIL include date/time, and outcome. It is possible more than one HCPROF participates in the same TREATMENTDETAIL for a patient. For example, it is possible that during a single TREATMENTDETAIL like a consultation about a patients pain, a neurologist, a psychologist and a nurse practitioner (all are considered HCPROFs) may participate. The hospital wants to keep track of all HCPROFs who participate in a treatment. For each HCPROF who participates in a TREATMENTDETAIL, the hospital wants to record the notes from the HCPROF and the amount of time that the HCPROF spent on the treatment. For example, if three HCPROFs participate in a single TREATMENTDETAIL for a single patient, the hospital wants to keep track of the notes and time spent individually for each of the three HCPROFs.

On the next page, I provide a few of the entities required by the application. The strong entities of patient and HealthCareProfessional (HCPROF) are included. I also included a weak entity for the TreatmentDetail transaction. TreatmentDetail will definitely need a primary key.

Expand this data model to include the rest of the data attributes, keys and all the relationships required to create a database for this scenario.