7 Pages
Unlock Document

University of Waterloo
Electrical and Computer Engineering
ECE 356
Wojciech Golab

ECE 356 Fall 2013, Midterm Examination INSTRUCTOR: Prof. Wojciech Golab CLOSED BOOK, NO AIDS DATE: October 24th, 2013 TIME ALLOWED: 80 Minutes INSTRUCTIONS: (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) Beverage(name, temperature) price Compatible(d_name, b_name) weight Compatible.d_name references Compatible.b_name references Beverage Dish category temperature compatible 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) is a foreign key that references 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 <> Alternate solution using aggregation, works in MySQL: SELECT DISTINCT email FROM Owner NATURAL JOIN Owns
More Less

Related notes for ECE 356

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.