Question

1. Given the business rule below. Draw the Data directories for each entities & ER Diagram.

A DOCTOR can be scheduled for many APPOINTMENTS, but may not have any scheduled at all. Doctor has the attributes such as DoctorID, Firstname, & Lastname. Appointment has attributes such as AppointmentID, Date & Reason. A PATIENT can schedule one or more appointments. Patient entity has attributes such as PatientID, PatientName & Address. One appointment is scheduled with exactly one patient. An appointment must generate exactly one BILL. Bill entity has attributes such as BillID, Amount & Status. One PAYMENT is applied to exactly one bill. Payment entity has attributes such as PaymentID & Amount. One patient can make many payments, but a single payment is made by only one patient.

1. Identify all the entities, attributes & data types, & constraints in data dictionaries. [10 marks]
2. By drawing an ER diagram, identify the cardinality relationship of the entities. [20 marks]

Answer 1

Entities, attributes & data types, & constraints in data dictionaries

DOCTOR
Attribute name	Data type	NULL	Key
DoctorID	VARCHAR(10)	NOT NULL	Primary key
FirstName	VARCHAR(50)	NOT NULL
LastName	VARCHAR(50)	NOT NULL

PATIENT
Attribute name	Data type	NULL	Key
PatientID	VARCHAR(10)	NOT NULL	Primary key
PatienttName	VARCHAR(50)	NOT NULL
PatientAddress	VARCHAR(50)	NOT NULL

APPOINTMENT
Attribute name	Data type	NULL	Key
AppointmentID	VARCHAR(10)	NOT NULL	Primary key
PatientID	VARCHAR(10)	NOT NULL	Foreign key
DoctorID	VARCHAR(10)	NOT NULL	Foreign key
Date	DATE	NOT NULL
Reason	VARCHAR(100)	NOT NULL

BILL
Attribute name	Data type	NULL	Key
BillID	INT	NOT NULL	Primary key
AppointmentID	VARCHAR(10)	NOT NULL	Foreign key
Amount	FLOAT	NOT NULL
Status	VARCHAR(10)	NOT NULL

PAYMENT
Attribute name	Data type	NULL	Key
PaymentID	INT	NOT NULL	Primary key
BillID	INT	NOT NULL	Foreign key
Amount	FLOAT	NOT NULL
PatientID	VARCHAR(10)	NOT NULL	Foreign key

ER diagram

Relationship and cardinalities are shown in crow's foot notation

• A DOCTOR can be scheduled for many APPOINTMENTS, but may not have any scheduled at all. And an APPOINTMENT is made for exactly one DOCTOR. It is a one mandatory to many optional relationship.
• A PATIENT can schedule one or more APPOINTMENT . And one APPOINTMENT is scheduled with exactly one PATIENT. It is a one mandatory to many mandatory relationship.
• An APPOINTMENT must generate exactly one BILL. And a BILL belongs to exactly one APPOINTMENT. It is a one to one relationship.
• A BILL may have one or more PAYMENT. And one PAYMENT is applied to exactly one BILL. It is a one mandatory to many mandatory relationship.
• One PATIENT can make many PAYMENT, but a single PAYMENT is made by only one PATIENT. It is a one mandatory to many mandatory relationship.