Based on the 8 descriptions provided in the assignment, I will introduce my Extend Entity Relational (EER) diagram.
(1) "EMPLOYEE" entity has attributes “EMP_ID”, “EMP_PHONENUM”, “EMP_EMAIL” for signing up. As the description mentions, EMP_EMAIL is optional. Also, I add “EMP_LNAME” and “EMP_FNAME”, which represents the last name and first name of the employee. The alert is represented in “CONTACT NOTIFICATION” entity, and the health report is represented in “HEALTH REPORT” entity.
(2) EMPLOYEE entity has attributes “EMP_TEMPERATURE” and “EMP_TEMPERATURE_TIME” which represent the randomly scanning information of company. Since it’s random, these 2 attributes are optional. If the temperature indicates that the employee is fever, then this employee would be tested onsite, which is represented in “HEALTH_REPORT” entity. This entity uses “EMP_ID” and “REPORT_ID” as primary keys. “TEST_RESULT”, “TEST_LOCATION” and “TEST_TIME” represent the test information. “SYMPTOMS” is optional because of pre-symptomatic and asymptomatic. Also, employee without high temperature can be singled out for randomly health test. Since one employee can have 0 or many health reports, there is a 1:M relationship between EMPLOYEE and HEALTH REPORT.
(3) The self-report is also recorded in HEALTH REPORT entity, where they can report their symptoms and test result. The TEST_LOCATION attribute represents the test location of their choice.
(4) If the TEST_RESULT indicates positive, then it would trigger “CONTACT TRACING”. “CONTACT TRACING” entity is used for contact tracing, it uses “TRACE_ID” and “REPORT_ID” as primary key. It has “TEST_POSITIVE_EMP_ID” and “CLOSE_CONTACT_EMP_ID” represent the test positive employee and one close contact employee. “CONTACT_TYPE” can be “meeting” or “same floor”. Since one employee may have 0 or many close contact employees, there is a 1:M relationship between HEALTH REPORT and CONTACT TRACING.
(5) Each employee may attend 0 or many meetings, so there is a 1:M relationship between “EMPLOYEE” and “MEETING”. “MEETING” entity records the meeting location and time, which is useful for contact tracing. “EMP_ID” and “MEETING_ID” are primary key. “ROOM_ID” is a foreign key represents meeting room number, and “START_TIME” indicates meeting start time. Since each room may contain 0 or many meetings, there is a 1:M relationship between “MEETING” and “ROOM”. “ROOM” entity uses “ROOM_ID” as primary key. “ROOM_TYPE” represents type of the room (office room or meeting room), and “FLOOR” indicates the floor number. By leveraging these records, we can do contact tracing immediately (for contact during meetings).
(6) As mentioned by (4), employees who test positive would be triggers for “CONTACT TRACING”. According to (5), we can do contact tracing immediately. Since one contact tracing can create one contact notifications (send to the close contact employee), there is a 1:1 relationship between “CONTACT TRACING” and “CONTACT NOTIFICATION”. One employee can receive multiple notifications, so there is a 1:M relationship between “EMPLOYEE” and “CONTACT NOTIFICATION”. CONTACT NOTIFICATION entity uses “TRACE_ID” and “EMP_ID” as primary keys, where EMP_ID indicates the employee to be notified. “NOTIFICATION_TYPE” can be “phone” or “phone & e-mail”. “NOTIFICATION_CONTENT” and “NOTIFICATION_DATE” is the content and date of notification, where the content of notification may be influenced by “CONTACT_TYPE” in “CONTACT_TRACING” (for instance, employees on the same floor as the sick employee may be notified with a message of concern, suggesting that they consider getting tested).
(7) The notified employees can get test immediately and record in HEALTH REPORT, and the contact tracing procedure can be repeated if them test positive, as mentioned before, until all affected employees have been notified and tested. Here we use EER: “STATUS” attribute in “EMPLOYEE” can be “sick”, “hospitalized” or “well”. We make disjoint subtypes with partial completeness. For “sick”, we make “SELF-QUARANTINE” subtype, which represents self-quarantine employees. “QUARANTINE_ADDRESS” and “QUARANTINE_DATE” means the address and start date of self-quarantine. “QUARANTINE_STATUS” records sick or feels well during quarantine and corresponding date. An employee who feels well after two weeks is allowed to return to work, and his STATUS would be changed to “well”. For “hospitalized”, we make “HOSPITALIZED” subtype, which represents employees who go to hospital. “HOSPITAL_ADDRESS” and “HOSPITAL_DATE” means the address and start date of hospital. “PATIENT_STATUS” can be sick, recovery or deceased. For “well”, it means healthy employees and they don’t have subtype.
(8) Finally, “EMP_OFFICE_FLOOR” attribute in “EMPLOYEE” is used for tracing contact in the same floor. It can be added in “CONTACT TRACING” as mentioned before. “CONTACT_TYPE” will then be “same floor”, and creating corresponding “NOTIFICATION_CONTENT”, as mentioned before.
Assumption: TEST_TIME in HEALTH REPORT, EMP_TEMPERATURE_TIME in EMPLOYEE and START_TIME in MEETING can be useful for contact tracing. For instance, we only trace back to employees who had close contact within the past 48 hours.
Note: all of the above relationships are strong relationships.