Question

Requirements Specification (this is a fictional scenario)

A large business, with several medical practices, contacted your company to create a database design. You received the task to investigate if there is a need for a hierarchy based on the specifications below, and to create the hierarchy analysis if needed. You must not create the entire analysis but only the aspects related to the hierarchy as described in the instructions, class lectures and sample examples. This is the relevant fragment from the requirements specification related to your task.

In the practice we have medical doctors, nurses, staff and other employees (e.g. cleaners for which we just keep contact information: name, SSN, phone).

For the doctors we keep the name, SSN, phone, email, main specialty, medical school. A doctor may have appointments and write prescripions. For nurses we keep the name, SSN, phone, and nurse program they completed. A nurse will prepare the patient during the visit and may collect various data. For staff we keep the name, SSN, phone and their role (e.g. receptionist, scheduler, accountant). Based on the practice policies, a nurse will not be allowed to perform staff duties.

The company wants to keep a log with who was working daily, between what hours and in which role. The company also wants to fast identify the role of a person in the organization (e.g. doctor, nurse, ...), based on the last name or id.

• Which is the supertype?
• SUPERTYPE_ENTITY_NAME (it might be in the current model or it might be a new entity)
• Which are the subtypes?
• SUBTYPE_ENTITY_NAME, SUBTYPE_ENTITY_NAME, SUBTYPE_ENTITY_NAME... (they might be in the current model or some might be new entities) Do not include subtypes that are not needed. You must have a justification why you need a subtype to be in the list. Including a subtype that does not store anything specific (attribute or relationship) will be considered a mistake.
• List the common attributes for the supertype and its PK:
• SUPERTYPE (PRIMARY_KEY, ATTRIBUTE, ATTRIBUTE, ....)
• [Composite/Simple/Surrogate] Primary Key: PRIMARY_KEY
• List the specific attributes for the subtypes and their PKs/FKs:
• For each subtype provide:
• SUPERTYPE (PRIMARY_KEY, ATTRIBUTE, ATTRIBUTE, ....)
• Primary Key: PRIMARY_KEY
• Foreign Key: FOREIGN_KEY references primary key ... in ...
• List the hierarchy relationships:
• ENTITY relation ENTITY (repeat as needed)
• Analyze the completeness constraint:
• The hierarchy has complete/partial subtypes, because ...
• Analyze the disjoint constraint:
• The hierarchy has disjoint/overlapping subtypes, because ...
• Do you need a subtype discriminator? Explain.
• The hierarchy needs (does not need) a subtype discriminator because ...
• Describe the subtype discriminator (if needed):
• The subtype discriminator is: .... with the values ...
• Entity Relationship Diagram
• After you finished the hierarchy analysis you must draw the ERD in MySQL Workbench and include in your report a signed screenshot. (to sign a screenshot you write your name in a text object visible in the screenshot; crop the image to show only the ERD and the text object)
• Draw the hierarchy using 1:1 relationships, add the correct constraints symbol and the subtype discriminator if needed.

Answer 1

a) Here we can take the employees as a supertype because all the staff including doctors, nurses, staffs and other employees share a some common type of informations. So the super type is SUPERTYPE employees ;

b) the subtypes are doctors, staffs, nurses. because these peoples are also working under the same place but have different duties . So we can consider them as subtypes.

SUBTYPE are doctors,nurses, staffs, other_employees ;

c)

• employees(employee_ID, name, phone, working_period, role)PRIMARY KEY :employee_ID.

d)

• doctors(doctor_ID, employ_ID,email, main specialty, medical school)PK:doctor_ID, FK:employ_ID(links to employees table with Employee_ID. and this doctors entity is a supertype of the following subtypes because a doctor can have appointments and prescriptions. The subtype appointment have the attributes like appointmentID(pk), doc_ID(fk),appointment_date,appointment_time,venue. The subtype prescription have the following attributes - prescription_ID(pk), doc_ID(fk), prescriptions,patient_ID(fk links to any patient details. FK:employ_ID used to link this table to the employees table with employee_ID.
• nurses(nurse_ID, employ_ID, nurse_program). Primary key:nurse_ID, Foreign key:employ_ID links to employees table with employee_ID.The nurse entity is a supertype of a subtype patient. The patient entity will holds the following attributes-patientID(pk) , name, phone, address, status.
• staffs(staffID, employID, role), PK:staffID, FK:employID (links this table to the employees table with employee_ID).

Here we do not include other staff as a subtype because we don't want to store any special information about those type of employees.