$24
HW1:6 points
In this assignment, you need to create a conceptual Entity-Relational (ER) diagram, to model the scenario described below; note that your design is not going to be at an implementation level, ie. you don't have to worry about representing your design using relational tables (including bridges) - you will only need to come up with entities and relationships.
Please submit your work as a single image file in .jpg or .png form that shows the entire diagram, via D2L Dropbox (pics/DropboxSubmission.jpg) (not via Blackboard). ALSO include a README.txt that contains any design choices you want to highlight, and/or assumptions you made [if the ER diagram were 'code', this would be 'comments'].
You can create the ER diagram using any software of your choice, including:
yEd (http://www.yworks.com/products/yed
(http://www.yworks.com/products/yed))
draw.io (browser-based)
Lucidchart (browser-based)
1/7
16/09/2021, 12:49 HW1
Vertabelo (also entirely online)
DIA
Project Mogwai (https://github.com/mirkosertic/MogwaiERDesignerNG (https://github.com/mirkosertic/MogwaiERDesignerNG)) E/R Assistant (Windows only:
https://highered.mheducation.com/sites/0072942207/student_view0/e_r_assistant.html
(https://highered.mheducation.com/sites/0072942207/student_view0/e_r_assistant.html))
Visio
https://erdplus.com/standalone (https://erdplus.com/standalone) - browser-
based :)
...
After constructing the ER diagram, save, or take a screengrab snapshot, submit it [as a .jpg or .png image file].
Note that you can even draw your diagram legibly on paper and take a photo of it and submit that - but having said that, I'd encourage you to use a diagramming tool, that will make your result look professional, and have you follow industry practice.
You need to use Crow's Foot Notation for the ER diagram. For each relationship, indicate the cardinality (minimum and maximum participation), also via Crow's Foot symbols - use this (pics/ERD_Relationship_Symbols_Quick_Reference-
1.png) infographic (from www.vivekmchawla.com/) as a guide [you don't need to denote cardinality as (1,1) etc., instead, you would use the notation shown in the infographic, ie. use symbols such as |O and ||].
2/7
16/09/2021, 12:50 HW1
How much detail should your diagram contain? Use this (pics/Sample_ER_Diagram.jpg) sample as a guide (eg. you do not need to indicate data types for attributes).
You (a database design specialist) are contracted by a private business, to build them a COVID-19 contact tracing system. The business operates in a building with several floors, with multiple office rooms and meeting rooms on each floor (eg. a law firm, investment company, etc). The rationale is this - the company would like to assure its employees that they would be notified as soon as possible, of any exposure they might have had with a sick co-worker, including pre-symptomatic (where testing indicates the presence of n-CoV, but symptoms haven't developed yet) and asymptomatic (testing indicates virus presence, no symptoms ever develop) cases detected through random testing.
Below are descriptions of what the business would like to have happen - if the specifications seem incomplete, you are free (expected, in fact) to make relevant assumptions [be sure to document them] that help create your design.
In RL, your design would get implemented (possibly in the form of a web-connected DB, along with an app that employees will use, in order to get notified and to self-report symptoms and any additional
3/7
16/09/2021, 12:50 HW1
p y p y
status). The system might softly go live, on a small scale, deployed to a limited set of 'beta' users; their usage feedback will guide the evolution of your design, possibly through one or two more versions, after which the process would be rolled out company-wide.
1. Employees would mandatorily need to sign up via a registration system where they need to provide their employee ID, a smartphone number and optionally an email address. They will then be required to install an app that would let them get alerts and do reporting of their health.
2. The company would randomly scan employees as they enter the facility or when they exit, taking their temperature. Any employee that has a fever, would be immediately tested onsite for COVID-19; at random, employees will also be singled out for testing even if they don't display a high temperature (this is to catch presymptomatic or asymptomatic cases).
3. Employees can also self-report (using their app), if they develop COVID-19 symptoms (any of five symptoms that the company would like them to report). Such employees will be required to undergo a test, and report the result (positive or negative)
4/7
16/09/2021, 12:50 HW1
to the company (again, via their app); they can get tested at a location of their choice, or choose to be tested onsite at their company.
4. Employees who test positive would be triggers for initiating contact tracing.
5. Close contact between employees that happens during meetings, is always recorded, via the employees' apps - the location (meeting room number and floor), and the meeting start time.
6 As soon as an employee tests positive, the contact tracing system kicks in - others who were in close proximity (ie in a meeting) with this employee, will be notified via their phone (in the form of a live call from HR), and additionally, if they provided an email address, via email.
7. The notified employees are expected to get tested as soon as they can, and report the result (again, they can get tested at a location they choose, or onsite at their company) - if any of them tests positive, the contact tracing procedure is repeated for others who were in proximity, until all affected employees have been notified and tested. Sick employees (who tested positive) are expected to self-quarantine for two weeks (unless they end up going to a hospital to get admitted), and during
that time, report their status to the company daily
https://bytes
5/7
16/09/2021, 12:50 HW1
• the status would be 'sick', 'hospitalized' or 'well'. An employee who feels well after two weeks is allowed to return to work, and the hospitalized ones come back to work upon recovery (after reporting a 'well' status); there is a chance that a hospitalized employee could become deceased :(
8. Also, employees on the same floor as the sick employee are notified as well, but this time, with a message of concern, suggesting that they consider getting tested (not mandatory) - because such employees might have gotten sick, on account of virus transmission via door handles, restrooms or break rooms/kitchens.
There you have it - a description of how the business intends to use contact tracing and testing, to immediately inform its employees of exposures to the virus.
Note that there isn't a single correct/perfect design, or even a single 'good' design - any design
involves tradeoffs which determine their efficacy/viability. You are free to make intelligent choices about what data (attributes) to store where (entities), and how to connect all the pieces (relations). Note too that some requirements stated above, might not be able to be captured in an ER diagram - this is fine. Be sure to document
https://
6/7
16/09/2021, 12:50 HW1
your design decisions (they would serve to provide rationale for "why you created your design the way you did"). Also, feel free to come up with an EER diagram if you see a need for one.
Please do not plagiarize, 'work together', etc. If we see that your solution resembles anything else, we will need to report you to SJACS, etc. How to avoid this? Simply do your own work. This is a design problem, so you have much latitude in coming up with one that works - enjoy the process!
Submission checklist:
.jpg or .png pic of your ER diagram
README.txt description file
You can post questions (and answer others' :)) on Piazza, under 'hw1'. HAVE FUN!
7/7
