This is the second step of assignment 1. In this one, you will do the following:

• create the relational schema (the tables) based upon an E/R design which we are providing. (Note that this is a slightly different style of ER design where attributes are inside the entity box. The meaning is precisely the same as in the case where the attributes are inside the ovals.)
• populate the tables with some example data, and then
• execute an SQL query that demonstrates the viability of your schema.

Translate the E/R diagram into an “equivalent” relational schema in SQL (the data definition language, DDL). 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 — integer, date, varchar(…), etc. — as is appropriate. Key attributes in the E/R diagram should be declared as not nullable. For attribute and table names, carry over those from the E/R diagram where you can, and make sensible choices where you cannot.

Fill your tables with data. You are free to choose any data you like. However, you must make sure that the query you run on your database (see below) returns at least one tuple.

List all courses taken by John Malkovich in 2017/18 together with their instructors, times, and locations. Since you make up your own data your answer can be completely arbitrary.

Due: June 13 at 9:30PM (no extensions!)

Hand in a hardcopy of your project.

This should include the following.

• All your table definitions in SQL (i.e. create table statements). They should all be in one file as shown here: yrb-create (just the schema definition, not the data).
• Output of the following query:

  select * from T

  for each table T in your schema. This is to show the contents of your tables.
• The SQL query about John Malkovich and its result.

Your project must be typeset. Have a cover page for submitting your work, filling out your student number, etc., something as follows.

Student Number
Sur (Family) Name
Given Name

Drop off for the hardcopy of your assignment at the EECS-3421 drop-off box in the Lassonde Building (adjacent to Lassonde #1012).