Question

Visiting Doctor scenario

1. a) Draw an ERD for a scenario in which patients visit doctors. It is possible that a patient is visited by more than one doctor. Create DOCTOR and PATIENT entities and add at least four attributes to them including a primary key. Create an implementation ready ERD with all appropriate connectivities and cardinalities. Justify your choice of cardinalities with real world examples. In case you need to add any new entities, add at least two attributes apart from their primary keys to describe the entity.

2. b) Extend your ERD by adding a test entity. During each visit, doctors may ask patients to take a test in the doctor’s office. Write all appropriate connectivities and cardinalities in the ERD.

Answer 1

ERD:

Explanation:

represents ENTITY

represents Attribute

represents primary attribute

represents relationship

represents cardinality

• here the relationship is many-to-many
• A doctor can visit many patients or may not vist at all, we represent it as zero-or-many
• A patient can be visited by one doctor or many doctors. we represent it as one-or-many
• Since the relationship is many-to-many, visits relationship also acts as another entity with Doctor entity primary key(doctor license number) and Patient entity primary key (Patient name) as foreign keys
  with additional attributes (prescription and visit time)