Question

Assume that you are working with a hospital and this hospital needs a software system to track its patients’ information. Your role in this software development is to design the database. There are many aspects of such a hospital software system to develop. However, in this assignment, you will only address interactions between doctors and patients. Your first step will be to create the relations necessary for this system and identify and describe the constraints that would be appropriate for the relations. Currently, hospital representatives describe three entities: Doctor, Patient, and Appointment.

“Doctor” will need information such as a unique identifier for each doctor, doctor name, doctor phone, specialty number, and specialty.

“Patient” will need information such as a unique identifier for each patient, patient name, patient phone, patient e-mail, patient address, date added to the system, allergies (list of any known allergies), and doctor identifier (in our case each patient is admitted by one doctor).

Each patient is treated through appointments. Hence, there should be an “Appointment” relation. The “Appointment” will need information such as a unique identifier, appointment date, doctor and patient identifiers. During the appointment, treatment information such as blood pressure, pulse, treatment notes of the doctor, and medicines should also be included in the “Appointment” relation.

Be sure to identify what are the candidate, primary keys, and the degree of each relation. It would be recommended that you document your relations in the manner used in the Library Management System case study in chapter 3.3 of Sharma et al. (2010, 77).

After determining the relations, identify and describe the constraints that would be appropriate for the relations that we have within our hospital software system.

Constraints that you should consider may include:

• Entity integrity constraint
• Referential integrity constraint
• Semantic integrity constraint
• Domain constraint
• Null constraint
• Unique constraint

The constraints in Bold type should all be present in your assignment.

Assignment Instructions

1. Assignment should include the relations required for the hospital software system as follows:

• Doctor
• Patient
• Appointment

2. Each relation must have identifiers and its data types

3. The degree of each relation must be described

4. Each relation must include appropriate information other than identifiers such as attributes and the data type of attributes

5. The “Doctor” relation should include at least a unique identifier for each doctor, doctor name, doctor phone, specialty number, and specialty

6. The “Patient” relation should include at least a unique identifier, patient name, patient phone, patient e-mail, patient address, patient added date, allergies, and doctor identifier.

7. The “Appointment” relation should include at least a unique identifier, appointment date, doctor and patient identifiers, blood pressure, pulse, treatment notes of doctor and medicines.

8. Identify and describe the constraints for each attribute

Reference

Sharma, N., Perniu, L., Chong, R. F., Iyer, A., Nandan, C., Mitea, A. C., Nonvinkere, M. & Danubianu, M. (2010). Database fundamentals. IBM Canada.

Teacher's note:

for the first assignment you need to create the three relations (Doctor, Patient, and Appointment): for example, I would create something like this:

Doctor Relation:

DoctorID	What Type (Number, Text, etc.)	Describe the constraint

Primary Key:

Candidate Keys:

Degree of relations:

Answer 1

The required relation is described in the figure belowHere Doctor_ID,Patient_ID,Appointment_ID will be the primary keys for their respective tables as it is enough to uniquely identify each tuple in the table.

Since each doctor can treat multiple patients and each patient can be treated by multiple doctors the relation between patient and doctor will be many to many.

A doctor can give multiple appointments and a patient can also ask for multiple appointments hence relation between doctor and appointment is one to many

Relation between patient and appointment is also one to many

Here Varchar datatype represents alphanumeric characters.