EECS-3421 (A & B) Project #2 Relational Schema: Contact Tracing solution

The Conceptual Design

This conceptual design is an E/R-schema solution to Project #1 for the Contact Tracing domain, having made some sensible design assumptions, and with some minor additions.

• Recon is weak on Person, Place, Time Slot, and Method. It is important to include method as part of the key of Recon; there could be more than one recon telling us a person was at a place at a given time, each recon supported by different evidence (method).
• We may have a person observed at different places during the same time (slot) and recorded with the same method, due to the way we are handling time: Each time value represents a fifteen-minute period.
• A Test Centre is a Place. A Test is administered at a Test Centre, not at any arbitrary place. We need to have Test Centre as a subtype of Place then, with Test Centre having the relationship with Test.

An addition in this E/R schema over the requirements of Project #1 is as follows.

• Non-key attributes in the E/R diagram that are in italics are to be interpreted as non-nullable. (This is information beyond what was in the requirements in Project #1. Note that you need not to have accommodated this in your solution to Project #1.)

Use this E/R diagram as the specification for your relational schema for your CTDB.

Your Relational Schema

Translate the E/R diagram into an “equivalent” relational schema in SQL’s DDL (data definition language). Do not create any tables that are not needed. Declare primary keys and foreign keys per table appropriately to capture the logic of the E/R diagram correctly.

Choose appropriate domain types — e.g., numeric(), timestamp, and varchar(…) — as is appropriate, or, in some cases, as dictated in the requirements below. Refer to PostgreSQL’s Documentation for all the built-in general-purpose data types.

Key attributes and ones in italics in the E/R diagram should be declared as not nullable.

For attribute and table names, carry over those names from the E/R diagram where you can, and make sensible choices that are compatible with the queries provided. (You are not permitted to change the queries provided. So your schema should be compatible with the queries.)

Your Data

Add data to your tables, following these rules.

1. Add to your database at least:
   • 5 people(persons)
   • 20 recons
   • 6 tests
   • 3 test centres
   • 3 actions
   • 3 test types
2. The Social Insurance Number (sin) for a person is a nine-digit number.
3. Each recon is supported by evidence, a method. Consider the following to be the values for method: “contact-tracing phone app”, “surveillance camera”, ”registry sign in”, and “registry sign out”.
4. A person should have recon‘s of a “registry sign in” upon entering and a “registry sign out” upon exiting a test centre, if the person has a test administered to them there. (The data should be that any sign-in is “followed by” a sign-out. There should not be cases where a person signed in but never signed out, or signed out without ever having signed in.)
5. A test that is of a given test type may only be administered in a test center that offers that test type.
6. A time value identifies the beginning of a fifteen-minute time slot; e.g., “3:00pm 25 September 2020” stands for 3:00pm up to 3:15pm on 25 September 2020.

Note that there is repetition of values in a relation database! Columns, and the values of those columns, are duplicated between tables to support the foreign keys. This is called controlled redundancy, though; the primary keys, additional keys via unique, and foreign keys control these repeated values. So this does not violate our single-source-of-truth principle. (The relational model uses the data themselves to relate tuples between tables, not pointers.)

We strongly recommend that you use a decent text editor, so that you can cut and paste values efficiently and can modify rows of text together. Build your schema script and data script in files from the beginning, loading these files into DB (the PostgreSQL server in PRISM) via psql as needed. This will greatly reduce the amount to grunt work you would have to do otherwise for this project.

The Queries

We provide two queries as follow to run against your database. Ensure that your schema is compatible with the queries’s syntax.

Once you have populated your database — added data as specified above (§Your Data ) — you should check that these queries run against your database successfully. (Fix your schema and data until they do.)

1. check

For each test, list all its attributes and whether the type of the test as recorded — “yes” or “no” — is, in fact, offered by the test centre where the test was administered.

select  *,
        (case when t.testtype in 
                  (select testtype 
                   from   Offer as o 
                   where  o.testcentre = t.testcentre)
         then 'yes' else 'no' end) as offered
from  Test as t;

2. track

For each test, list the person’s sin and name, the time (slot) when s/he took the test, and the times that the person entered and exited the test centre around when the test was administered.

with Visit (sin, placename, enterTime, exitTime) as (
        select  Entry.sin, 
                Entry.placename,
                Entry.time,
                min(Exit.time)
        from  Recon as Entry,
              Recon as Exit
        where Entry.sin = Exit.sin
          and Entry.placename = Exit.placename
          and Entry.method    = 'registry sign in'
          and Exit.method     = 'registry sign out'
          and Entry.time     <= Exit.time
        group by Entry.sin, Entry.placename, Entry.time
)
select  P.sin, P.name, T.time as testTime, V.enterTime, V.exitTime
from    Person as P, Test as T, Visit as V
where   P.sin        = T.sin
  and   T.sin        = V.sin
  and   T.testcentre = V.placename
  and   T.time      >= V.enterTime
  and   T.time      <= V.exitTime;

If you have t tuples in your table Test, each query above should result in t rows.

For query check, the offered value should be ‘yes’ for each row returned.

Note that the SQL queries are really testing that your relational schema is correct, and that your data follows the rules we provided. If you are finding there are errors to execute the queries, check that your schema is making sense. The problem could be with the schema. If you are finding the answer tables to the queries are not as described above, check that your data is populated as required.

Materials

You should submit the following four or five files.

• schema:
  The SQL creation script that you wrote and used to create your database under PostgreSQL.

  This will be a sequence of SQL CREATE statements, each separated by a semicolon (the statement terminator).

  For full credit, you must create all appropriate primary key and foreign key constraints, enforce participation constraints where possible, and give appropriate domain types to the attributes.

• data: An SQL script that adds the requested data to your database under PostgreSQL.

  This will be a sequence of SQL INSERT statements. (Do not use COPY for this project. It is the command for bulk loading.)

• check:
  An ASCII file containing the answer-table output from running the SQL Query #1, Check, against your database.
• track:
  An ASCII file containing the answer-table output from running the SQL Query #2, Track, against your database.
• note (optional):
  An ASCII text file with any clarifications of the above, if you feel it necessary.

All your files being submitted online must be ASCII text files (e.g., “.txt”). Files in any other format — MS Word, PDF, etc. — will be discarded.

Submission

You are to submit your project in electronic form via the submit script on PRISM, the EECS network machines (e.g., red.eecs.yorku.ca).

To submit:

% submit 3421X schema schema data check track

Or, if you have additionally a note file,

% submit 3421X schema schema data check track note

Replace “X” above in the “3421X” with “A” if you are in Section A (with Parke Godfrey) or with “B” if you are in Section B (with Wenxiao Fu).

Note that the first occurrence of the word “schema” in the submit command above is the project name (which tells submit to where to submit your files). The second occurrence of the word “schema” is the name of your schema creation file. (As data, check, and track are your files too that you are submitting.)

EECS’s web submit utility is simply a web-gui frontend for submit. You are welcome to use it instead of the command-line submit, if you prefer.