Entity relationship

Table of contents

• Introduction
• Diagram
• Explanation

Introduction

This time we have to create a entity relationship diagram which contains the following requirements:

• Entities
• Attributes, Primary Key
• Roles in recursive relationships.
• (optional) Ternary relationships
• (optional) Complex attributes. Composite attributes. Multivalued attributes. Derived attributes.
• All possible cardinalities: one-to-one, one-to-many, many-to-many.
• Total and partial participation.
• Weak entities.
• Generalization-specialization. Overlapping/disjoint. Total/partial.

Diagram

The image below represents the relationships between entities,entities sets and their attributes:

Explanation

The diagram starts from the top down and works as follows

• Hospital is an entity that has attributes like name which can be a composite attribute like address which contains composite attributes like country, state, city and street address. The primary key is id_hospital which is underlined like all the primary keys we will find, its relationship is one-to-many a Hospital has several surgeons and the surgeons work for a Hospital.
• On the right side in red is the generalization-specialization in this case it is a total-disjoint because the entity is public or private there are no other alternatives.
• Surgeons is a weak entity that depends on Hospital, it is a many-to-one relationship because several surgeons can have the same speciality and normally a surgeon is usually specialised in a certain type of surgery and works in this or related positions, e.g. a surgeon specialised in heart surgery.
• Speciality is a complementary entity to Surgeons. The primary key has roles depending on the speciality we need a prerequisite.e.g. A surgeon needs a university degree.
• Patient is an entity with a relationship many-to-many a surgeon has several patients and vice versa e.g. it is not the same surgeon for a transplant and a rhinoplasty, the participation is total because a patient must have a surgeon assigned and there is also an attribute multivalued which is phone_number e.g. a patient can have different numbers associated to it.A derived values like age
• Medical history is a weak entity and depends of the entity patient, a set of relationships called register with an attribute which is date this data stores the date of the patient's last surgery. The relationship is one-to-one a patient only has one medical history and the medical history belongs to one patient.