Question

Step 3: Relationships

While creating your entities you should have been thinking about what relationships the entities would have with each other. Create the relationships between your entities including the relationship’s optionality and cardinality.

1. Write out the ERDish for each of the relationships.

2. Some relationships will be transferrable and some non-transferrable – be sure to illustrate this point on the ERD. For example once a prescription is written for a patient it cannot be transferred to another patient.

3. The possible relationship types are : 1-to-1, 1-to-many and many-to-many.

4. Any many-to-many relationships will need to be resolved. For example each doctor may be affiliated with many hospitals and each hospital may have many doctors affiliated with it. We need to make sure this many-to-many relationship is resolved so that we can track which doctors are affiliated with which hospitals. Modify the ERD to include the relationships.

Step 4: Normalization Ensure that each entity has been normalized to third normal form – this means:

1. 1st normal form states that all attributes have a single value - no multivalued attributes. For example : each patient can only have one primary doctor, each doctor can only have one specialty etc.

2. 2nd normal form says that all attributes must be dependent on the entire key of the entity. For example we need to know each drug’s name, purpose and side effects but if we include this in the Prescription entity it will be dependent only on what drug is prescribed not who it’s for or what doctor prescribed it – so it does not belong in the same entity as the prescription information itself.

3. 3rd normal form states that no non-UID attribute can be dependent on another non-UID attribute. For example : A patient’s insurance ID number will determine what insurance company they are insured with. The ID number determines the insurance company’s name. Modify the ERD to incorporate all 3 stages of normalization.

Step 5: Arcs

Each prescription issued by a doctor must be refillable or non-refillable. It can’t be both.

Modify the ERD to make this distinction using an arc. Re-fillable prescriptions will have information about the number and size of refills. All prescriptions will need information about the date, dosage and duration of the RX.

Answer 1

The student is tasked with creating an Entity-Relationship Diagram for a healthcare system, which involves identifying relationships, enforcing normalization rules, and using arcs to indicate prescription types.

Step-by-step explanation:

The question is regarding the creation of an Entity-Relationship Diagram (ERD) for a health system database, considering relationships, cardinality, optionality, normalization, and use of arcs to demonstrate specific constraints. In Step 3, you should write out the ERDish (Entity-Relationship Diagram language) for each of the relationships, while taking into account whether they are 1-to-1, 1-to-many, or many-to-many. Many-to-many relationships need to be resolved using an associative (linking) entity. Mention whether relationships are transferable or non-transferable, as in the case of prescriptions that cannot be transferred to another patient. In Step 4, you need to ensure each entity is normalized up to the third normal form. This involves that every attribute must have a single value (1st normal form), be fully functionally dependent on the primary key (2nd normal form), and there should be no transitive dependencies (3rd normal form). Finally, in Step 5, introduce an arc in the ERD to distinguish between refillable and non-refillable prescriptions, each with its own set of attributes.