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: