Project #1 E/R: Contact Tracing solved

E/R Dialects

The textbook — and our examples in the lectures — uses the “Stanford” style of E/R modelling. Note that there are many dialects of E/R, however. For consistency, for this project, you must adhere to the Stanford (the textbook’s) dialect.

A different textbook was used previously in this course at York a while back which used a different dialect of E/R (the “Wisconsin” dialect). So there is “3421” material around with E/R examples written in the Wisconsin dialect. The two dialects are not significantly different, but there are notable syntactic differences. Feel free to use these for studying and for reference, of course. But be aware of the differences.

There are many additional dialects. Some studentsa may have experience with them. However, for purposes of this class, we insist that you do your work in the Stanford dialect.

The Contact Tracing Domain

To be able to trace with whom a person has been in contact, we need to know where that person has been and when. We have to know this about everyone else too. Then we could figure out the potential contacts by seeing who was in places at the same time as the person. During an epidemic with a highly infectious disease, by tracing the recent contacts of a person who has become ill, these people can be warned to take appropriate action. This can greatly help to stem the epidemic.

Thus Person is fundamental in our database. We are tracing people, after all. Information we should keep for people is a name, address, and phone#. We can keep sin, a person’s social insurance number to identify the person. (Assume that the government will issue anyone in Canada who does not have a sin a temporary sin for this very purpose. Also note that, in building such a database for real, in truth, using sin for this would likely not be a good choice. But, for the project, let us assume it is.)

We should also record Places. This will include public building and other indoor places where people can meet, and thus come into contact. (We assume for now that the disease does not transmit outdoors, so we are not keeping track of all possible locations.) We can use a place’s name to identify it, and we want to keep about a place its gps coordinates, address, and a description.

Central to the whole database’s operation is to collect “observations” about which places a person has been, and when they were there. Thus, we are designing a mass-surveillance database! But we are assuming that this is for a good cause, and that the database, once up and running, will not be abused for other purposes.

Let us call an observation that such a person was in such a place at such a time a Recon — a shortened form of the military term reconnaissance — to have an easier way to organize our thoughts. A Recon names a Person (who) as being at a Place at a given time (when). Let us employ the notion of Time Slot for handling times. There will be effectively an entry in Time Slot for every fifteen-minute period; e.g., 3:00pm 25 September 2020, 3:15pm 25 September 2020, 3:30pm 25 September 2020, and so forth. For any given time slot in which a person was observed somehow to be at a place, we would have a Recon entry. Thus each Recon is associated with a Time Slot, telling us when the person was there. (A better way to handle time in such a scenario is to work with time intervals. This is significantly more involved design-wise, however. Therefore, for our first cut of a schema for our contact-tracing database — that is, this project — let us employ Time Slot to handle the “when” aspect.) In fact, we can use when to identify any given Time Slot. (Say we assume the when value identifies the beginning of the time slot; e.g., 3:00pm stands for 3:00pm up to 3:15pm.)

A Recon then identifies — and therefore, is identified, at least in part, by — the person, the place, and the time slot (when), which is “saying” that Person (who) was at Place during this Time-slot time (when). For any Recon, we also must record the Method, the way, that we know that the person was at the place at that time. Examples of methods might be, for example, contact-tracing phone app, surveillance camera with facial recognition, and a registry entry (the person had to sign a registry book on entering and exiting the building). Note that there could be more than one recons telling us a person was at a place at a given time, each recon supported by different evidence (Method).

In our database, we also need to track when a person is tested, a Test, for the disease. Such a diagnosis Test is administered to a given Person at (“upon”) a given time (Time Slot). This information identifies any given Test. A Test is administered in a Test Centre — which is a Place — and is of a given Test Type. (There are different types of test for the disease, which may differ in efficacy and cost.) Thus, we want to record where the Test was administered, and what type of test it was.

A Test may result in an Action. Let Action be identified by an action name. (An action might be taken given the result of the test; for instance, if it came back positive for the disease, the person might be placed in quarantine.) Assume that a given Test results in at most one Action. Of course, a Test might not result in any action. (Say, the test came back negative and no action was necessary.) We want to track actions resulting from tests.

Not all Test Centres are equiped to administer all types of test (Test Types). Thus, we want to record which Test Types are offered at which Test Centres. A Test Centre may offer different types of test; and a type of test may be available at a number of test centres.

Lastly, the Government is to require that each Person identifies their bubble; that is family and friends — other people (Persons) — with whom they are regularly in contact. We need to record this “bubble” information in the database.

Go forth and design! And keep us all safe.

Considerations

This design exercise is meant to simulate a real life system-analysis situation; hence, the domain description above is unstructured, and sometimes fuzzy. It is your responsibility as an analyst / designer to translate what you read into a proper E/R diagram that models the domain correctly.

Keep the following things in mind for your design.

1. In real life, many more data elements than described in the “requirements” above would be needed to build a useful database. We do not, however, want to turn this project into something huge. So keep in mind that this is a highly simplified case.
2. As a general approach, if a particular constraint is not explicitly given, then assume the least restricted situation. For example, “many” in the E/R Model is less restricted than “at most one”.
3. You are not required to specify the domains of the attributes here in your diagram (step A).
4. Make certain that you clearly indicate any constraints that should be considered as belonging to your design but that are additional to those captured in the logic of your E/R diagram.

   That is, state any constraints that are indicated by the requirements, but that you know are not enforced by your design in brief documentation attached with your design. For each such case, explain whether the unenforced constraint

   • is beyond the scope of E/R,
   • seems to be beyond the scope of E/R (but you do not know for certain),
   • would greatly complicate the design beyond value, or
   • is simply not accommodated in your design.

Work Strategy

We suggest working on your design iteratively, little by little. E/R is a refinement process. Get a rough sketch of your design first. Then improve on it to fix issues as you find them.

Practice

The best way to learn E/R modelling well is lots of practice! Look through the examples in the textbook and the slides.

Look through the examples in the exercises linked here below, and previous E/R assignments in this course.

• E/R project from winter 2020:
  • E/R Project: Songs
  • a solution (PDF)
• E/R project from winter 2019:
  • E/R Project: TapTap
  • a solution (PDF)
• E/R project from winter 2018:
  • E/R Project: Episodes
  • A solution appears on the description of that class’s schema project, Relational Schema: Episodes.
• E/R project from fall 2016:
  • E/R Project: Loot!
  • a solution (PDF)
  • an extreme solution (PDF)

Warning: The E/R below are in the Wisconsin dialect, not the Stanford dialect.

• E/R project from fall 2015:
  • E/R Project: The Howling
  • a solution (PDF)
• E/R project from winter 2014:
  • E/R Project: The Canadian Quiddich Association Match DB
  • a solution (PNG)
• E/R project from winter 2013:
  • E/R Project: A Prescription DB
  • a solution (PDF)
• Assorted exercises covering the relational model, E/R design, relational design, relational algebra & calculus, and normalization:
  • exercises (PDF), &
  • exercises w/ answers (PDF).