Step one is to create the tables. You should follow the rules for translating entities and relationship as described in the textbook and shown in class. There is not much room for flexibility here.
In step two, you will populate your tables with some example data, based on the given information. You can make this data up; however, your data will have to comply with your schema and the constraints outlined in this project.
For step three, we provide you with two SQL queries. Once you have finished step two, you should run the provided queries against your database, and get corresponding result.
You are to implement a contact-tracing database (CTDB) based on the following contact tracing E/R 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.
An addition in this E/R schema over the requirements of Project #1 is as follows.
Use this E/R diagram as the specification for your relational schema for your CTDB.
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.,
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.)
Add data to your tables, following these rules.
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.
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.)
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;
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.
The York River Bookstore (YRB) schema script is for a small database that is similar to the example discussed in class. You can reference it as an example for doing this project. Note that the file has both the schema and the data in the same file; you are asked to put these in seperate files for your schema and data, however.
yrb-drop is a simple file of SQL drop commands that clears out the YRB database. You may find you want to make a “drop” file too for your database, as you will likely find doing the project is quite an iterative process.
The project is due by
by electronic submission.
submit the following four or five files.
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.
This will be a sequence of SQL INSERT statements. (Do not use COPY for this project. It is the command for bulk loading.)
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.
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).
% submit 3421X schema schema data check track
Or, if you have additionally a note file,
% submit 3421X schema schema data check track note
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.)
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.
Populating our schema with the data required is so much work! Do I have to do this?
Yes, this is part of the project. This should not be as bad as it might initially look. But do not wait until the last minute!
Should I add constraints that I think should be here but that are not reflected in the E/R diagram above, or just faithfully render this E/R diagram into a relational schema?
Faithfully render this E/R diagram into a relational schema.
I did a lot of my work creating tables and populating data directly within the
psql client shell. Is there an easy way I can “export” from
Postgres the SQL for creating my schema and for populating my data?
Yes, there is a command
pg_dump that will do this. E.g.,
% pg_dump -h db -U godfrey -d godfrey
Replacing “godfrey” for user (
-U) and database (
-d) with your own, of course. And
% ./pg_dump -h db -U godfrey -d godfrey -f ~/myCTDB.sql
to have the output put into a file (e.g,
myCTDB.sql), instead. (Or just use *nix shell redirect, “
>”, of course.)
In the E/R schema given, couldn’t Recon have been modelled as a multi-way relationship set instead of a weak entity set weak on all the things it relates? And, if so, wouldn’t that be a simpler design?
Yes and yes. In this case, the multi-way relationship version and this weak entity set version have the same logic. And we generally do advocate for simplicity. (Note that an equivalent relational schema will be the same in either case!)
This version came about because we were considering whether to have Recon weak on Place when we were modelling this for Project #1 ourselves. If we wanted to change the logic so it is not weak on Place — thus, Recon‘s key becomes the union of the keys from Person, Time Slot, and Method — we could modify this easily. With the multi-way version, we can model this too; but understanding arrows from a multi-way can be tricky, as we have seen.
And if we were looking actually to enforce, “a person cannot be in two places at the same time,” we would need to have Recon just weak on Person and Time Slot, and then related to Place with a many-one, but related to Method with a many-many. The requirements had said there could be more than one way (method) that confirms that a person was at a place at a given time. We could not model this with Recon as a multi-way relationship set.