I was tasked with creating an ER diagram for a theoretical Covid-19 risk dashboard. This is the diagram I created:

And here is a spreadsheet with tuples (a tuple is one row/record/entry):

Entities

The entities I chose are patient, occupation, behavior, location, hospital, and risk.

Patient

Patient describes the user and has the primary key of User ID. Primary keys are unique fields that identify each tuple of an entity. Patient also contains foreign keys for occupation and location to create a relationship between the entities. A foreign key is when an entity contains the primary key of another entity.

Occupation

Occupation describes the patient's job and contains attributes that are relevant to the risk of contracting Covid-19. The primary key is a combination of company name and job title because the same job within the same company should be similar enough to not need individual entries.

Behavior

Behavior describes how a patient behaves in their everyday life. Its primary key is also User ID because behavior will be unique to each user. It also contains a foreign key for occupation to reflect the relationship between occupation and behavior. Its relationship with patient is maintained through the shared primary key.

Risk

Risk describes a patient's risk in general and from Covid-19. It also uses the primary key of User ID because it is unique to each user and is based on all of the other information. The User ID field links Risk to Patient.

Location

Location describes the characteristics of a Patient's physical environment as well as relevant government policies. Its primary key is City/Town because any policies within the same city or town should be same.

Hospital

Hospital describes the characteristics of a hospital within a location. The foreign key for Location is used to form this link. Hospital has the primary key of name because hospital names tend to be very location specific.

Why This Layout?

I chose this layout in an attempt to maximize user convenience. The entities that use User ID as a primary key will be unique to each user, so the user will have to enter information manually for the patient and behavior entities(Risk would be generated by the dashboard). The other entities, however, could be shared by multiple users(patients), so if there is already an entry for an occupation, location, or hospital then the user will not have to enter in all of the information. Hospital's link to location also means that a list of known hospitals in that location can be generated after the user enters their location.

A Video Discussing the Same Topic: