ECE 356 Fall 2013, Midterm Examination
INSTRUCTOR: Prof. Wojciech Golab CLOSED BOOK, NO AIDS
DATE: October 24th, 2013 TIME ALLOWED: 80 Minutes
(1) Print your name and student ID at the top of all pages and on the cover page below.
(2) The examination paper is 7 pages long (including this cover page). Check that you have all of
them and let the proctors know immediately if you are missing any pages.
(3) Answer the questions in any order. Read an entire question before you begin to answer it.
(4) Write your answer in the space provided. If you need more space, you may use backs of
pages. Write legibly and show all your work.
(5) Please do not separate the pages of this exam.
(6) If required for a given question, or if in doubt, clearly state any reasonable assumptions
and then proceed to complete the question.
(7) The number in square brackets [...] after each problem statement denotes the weight assigned
to that question.
(8) Grading will take into account the correctness and clarity of your solutions, as well as the
soundness of any assumptions you make. Course staff will not comment on the correctness,
clarity, or soundness of your solutions during the exam.
(9) Questions vary in difficulty and weight. Do not get stuck on one question. Good luck!
Student name (please print): ________________________________
Student ID: _____________________________________________
Student signature: _______________________________________
Q1 Q2 Q3 Q4 Total Percent
10 10 5 10 Student name: Student ID number:
Question 1: Entity-relationship modeling [10 points]
A restaurant would like to construct a database of their food and beverage menu. The following
requirements have been identified:
The restaurant serves dishes and beverages.
Each dish has a name, price, weight, and category (e.g., appetizer, entrée, dessert).
Each beverage has a name, price, weight, and temperature (e.g., hot or cold).
Some dish-beverage pairs are compatible with each other, while other pairs are not.
A dish may be compatible with more than one beverage, and a beverage may be
compatible with more than one dish.
Each dish must be compatible with at least one beverage.
A beverage need not be compatible with any dish.
Part (a) [6 points]: Use an entity-relationship diagram to depict the above scenario. Your
diagram should follow the notational conventions described in the lecture notes or the course
textbook. Include all relevant entities and relationships, cardinality constraints, and participation
constraints. Indicate primary keys of entities by underlining them.
Part (b) [4 points]: Translate your E-R diagram into tables. For each table, give the schema,
underline the primary key, and state any referential integrity (i.e., foreign key) constraints.
Food Item Tables:
FoodItem(name, price, weight)
name Dish(name, category)
Compatible.d_name references Dish.name
Compatible.b_name references Beverage.name
Page 2 of 7 Student name: Student ID number:
Question 2: Query languages [10 points]
Consider the following relational schemas used to describe the data maintained by a pet hospital.
Pet (pid, name, age, species, breed)
Owner (oid, first_name, last_name, age, address, phone, email)
Owns (pid, oid)
Owns.pid is a foreign key that references Pet.pid
Owns.oid is a foreign key that references Owner.oid
Answer the following queries using the indicated query languages.
SQL - Structured Query Language
RA - Relational Algebra
For answers using SQL, you may use any SQL 92 syntax, including any syntax covered in the
lecture notes or the course textbook (6 edition), as well as any syntax that is accepted by the
version of MySQL used in the lab environment. Each query should return only the information
being asked for, and nothing else.
Part (a) [2 points]:
Using RA, find addresses of owners who own cats named "Fluffy".
Owner.addressspecies='Cat' and name='Fluffy'wns) ⋈ Owns.oid=Owner.oider
Note: Use theta join instead of natural join to ensure that Pet.age is not matched with Owner.age.
Part (b) [2 points]: Using SQL, find the distinct emails of owners who own more than one pet.
SELECT DISTINCT email
FROM Owner, Owns as A, Owns as B
WHERE Owner.oid = A.oid AND Owner.oid = B.oid AND A.pid <> B.pid
Alternate solution using aggregation, works in MySQL:
SELECT DISTINCT email
FROM Owner NATURAL JOIN Owns