Bahria University Database Management System
Department of Computer Science Semester 4B (2014)
HOME ASSIGNMENT # 2
Assignment Description
Consider the following business rules for designing a database for a medical clinic:
1. A patient can make many appointments with one or more doctors in the clinic, and a doctor
can accept appointments with many patients. However, each appointment is made with only
one doctor and one patient.
2. Emergency cases do not require an appointment. However, for appointment management
purposes, an emergency is entered in the appointment book as unscheduled.
3. If kept, an appointment yields a visit with the doctor specified in the appointment. The visit
yields a diagnosis and, when appropriate, treatment.
4. Each patient visit creates a bill. Each patient visit is billed by one doctor, and each doctor can
bill many patients.
5. Each bill must be paid. However, a bill may be paid in many installments, and a payment may
cover more than one bill.
Perform the following steps:
1) List all the possible Entities.
Patient
Doctor
Appointment
Visit
2) List all the types of Relationships between these entities.
Patient
can have one or more Appointments
Doctor
can have one or more Appointments
Appointment
must have one and only one Patient
must have one and only one Doctor
can have zero or one Visits
Visit
must have one and only one Appointment
3) List possible Constraints.
4) For each Entity, list possible Attributes (in the form of schema)
Patient
o PateintId
o Name
o Address
o PhoneNo
Doctor
o DoctorId
o Name
o PhoneNo
Appointment
o AppointmentId
o DoctorId (FK)
o PatientId (FK)
Visit
o VisitId (PK)
o AppointmentId
o ArivalTime
Bill
o BillId (PK)
o VisitId (FK)
5) For each entity specify the Primary Keys (in the same schema)
Patient
o PateintId (PK)
Doctor
o DoctorId (PK)
Appointment
o AppointmentId (PK)
Visit
o VisitId (PK)
Bill
o BillId (PK)
6) For each entity specify the Foreign Keys (list after the schema)
Patient
o No Foreign Key
Doctor
o No Foreign Key
Appointment
o DoctorId (FK)
o PatientId (FK)
Visit
o AppointmentId (FK)
Bill
o VisitId (FK)
7) Using the Crows Foot notation, draw an ER diagram that is ready for implementation using
components identified in above steps.
8) In your ERD show the connectivity and the cardinality for all relationships.
1. For each table, identify the primary key and the foreign key(s). If a table does not have a foreign
key, write None.
Employee
o EMP_CODE(PK)
o STORE_COE(FK)
Store
o STORE_CODE(PK)
o EMP_CODE(FK)
o REGION_CODE(FK)
Region
o REGION_CODE(PK)
2. For each table, do the tables exhibit entity integrity? Answer yes, no or not applicable and then
explain why.
Yes
3. Do the tables exhibit referential integrity? Answer yes, no or not applicable and then explain why.
Yes
4. Describe the type(s) of relationship(s) between STORE and REGION.
Each Region can have many stores , But each store exist in only one Region , so relationship is one
to many .
5. Create the ERD to show the relationship between STORE and REGION
6. Describe the type(s) of relationship(s) between EMPLOYEE and STORE.
(Hint: Each store employs many employees, one of whom manages the store.)
7. Create the ERD to show the relationships among EMPLOYEE, STORE, and REGION.
