Try-out Exam
Jelle Hellings
3DB3: Databases – Fall 2021
Department of Computing and Software
McMaster University
Cheating and plagiarism. This exam is an individual exam: do not submit work of others. All parts
of your submission must be your own work and be based on your own ideas and conclusions. If you
submit work, then you are certifying that you have completed the work for that assignment by yourself. By
submitting work, you agree to automated and manual plagiarism checking of the submitted work.
Cheating and plagiarism are serious academic oenses. All cases of academic dishonesty will
be handled in accordance with the Academic Integrity Policy via the Oce of Academic Integrity.
Instructions
I This exam is open-book: you may use the textbook, copies of the lecture slides, material from the
tutorials, the assignments and their solutions, the exercises and their solutions, and/or other printed
resources.
I Before you start wporking on the Exam Questions, answer the Regulatory Questions on Avenue to
Learn.
I All ve Exam Questions (Q1–Q5) must be made on paper (handwritten).
I Make each of the ve Exam Questions (Q1—Q5) on individual pages. No two questions on one piece
of paper!
I Submit, for each of the ve Exam Questions (Q1—Q5), a high-quality scan or photo to Avenue to Learn
before the end of the exam.
I Each of the ve Exam Questions (Q1—Q5) has their own submit entry on Avenue to Learn. Submit
each answer at the right place!
I After nishing the Exam Questions, answer the “Concluding Declarations” on Avenue to Learn.
Q1 Regulatory Questions
Do you agree with the following statements?
Q1.1. I (the student) understand the above Instructions (“False” is wrong.)
Q1.2. I (the student) am aware that academic integrity requires me to only submit my own anwsers. I will
not seek help for exam questions during the exam, and will only submit answers that are my own
work (“False” is wrong.)
1
Q1.3. I (the student) am aware that during the exam (December 22, 2021 from 9:00AM to 11:30AM) and the
hour after the exam (12:30PM), communication about the exam or related topics with other people
except the COMPSCI 3DB3 team constitutes academic dishonesty. I commit to not communicate with
other people until 1 hour after the exam (“False” is wrong.)
Q1.4. I (the student) have switched o all communication tools (mobile phones, Facebook, WhatsApp,
KakaoTalk, WeChat, Microsoft Teams, Skype, Discord, etc.) and I intend to keep these tools o for the
remainder of the exam (“False” is wrong).
If these questions are not answered positively, your exam will not be graded.
Exam Questions
Submissions that do not follow the Instructions will not be graded.
Q2 Theory questions
Q2.1. Explain the default policy used by SQL-based DBMSs to maintains foreign key constraints under
modications of tables.
Q2.2. Provide an example of the dierence between 4NF and BCNF by illustrating which redundancies 4NF
can prevent and BCNF cannot prevent.
Q3 The Entity-Relationship Model and the Relational Data Model
Consider the following ER-Diagram that models statues made by one-or-more artists and placed in a city.
Some statues can be of special types. E.g., memorial statues commemorate a specic event, while person
statues commemorate a person. No statue commemorates both an event and a person, however.
Q3.1. Translate the ER-diagram to a set of tables. Motivate and explain each of the choices you made during
the translation.
Q3.2. For each table in your solution, mention the primary keys, the foreign keys, the types of each attribute,
and any other constraints that apply to your solution.
city
name
population
placed_inStatue
title year
Artist
name
makes
ISA
Memorial
event_date
event_title
Person
whom
birthdate
2
Q4 SQL Queries
Use the relational schema from the appendix to answer these questions.
Express the following four queries in SQL. Your queries must use the constructs presented on the slides
or in the book (we do not allow any system-specic constructs that are not standard SQL). Your queries
should not use superuous statements (e.g., DISTINCT when the query cannot produce duplicates).
Q4.1. Return the names of restaurants that have an appetizer that cost more than one of their main courses.
Q4.2. Return the names of users that have never written a review for restaurants that oer “expensive”
dishes (one or more dishes with a price of at-least $100).
Q4.3. Return the names of restaurants that only oer dishes of a single type.
Q4.4. Return, per restaurant, the restaurant name, their average score, and the cheapest dish (name and
type). Assume that each restaurant has reviews and oers dishes and assume that each dish has a
distinct price.
Q5 The Relational Algebra
Use the relational schema from the appendix to answer these questions.
Express the following three queries in Relational Algebra. Your queries must use the basic relational alge-
bra (with set semantics). Hence, only use relation name atoms, selections, projections, unions, intersections,
dierences, renames, cross products, joins, and natural joins.
Q5.1. Return the places that have exactly one restaurant.
Q5.2. Return the names of restaurants that oer the cheapest type of a specic dish (hence, no other
restaurant oers a dish with the same name, but possibly a dierent type, at a lower price).
Q5.3. Return the names of users that have reviewed every restaurant.
Q6 Dependency Theory, Decomposition, and Normal Forms
Consider the relational schema r(퐴, 퐵,퐶, 퐷, 퐸) and the following functional dependencies:
픖 = {퐴 −→ 퐵,퐶퐷 −→ 퐸, 퐵퐸 −→ 퐶}.
Q6.1. Does the functional dependency 퐴 −→ 퐶 follow from픖 (does픖 |= 퐴 −→ 퐶 hold)? Explain why.
Q6.2. Provide a minimal cover and a key for the provided relational schema with respect to the functional
dependencies픖.
Q6.3. Is this relational schema in BCNF?
If the relational schema is in BCNF, then explain why.
If the relational schema is not in BCNF, then apply theDecompose-BCNF algorithm to put the relational
schema in BCNF. Explain each decomposition step you make, e.g., the functional dependency used to
determine a BCNF violation. Provide keys for each of the resulting relational schemas.
Grading
Submissions that do not follow the Instructions will not be graded.
Each of the ve parts count toward 20% of the nal exam grade.
3
Appendix
A review website for food establishments holds information on restaurants, customers, and ratings. The
relational schema for this review website consists of the following relations (SQL tables):
I Restaurant(name, address, place)
Each restaurant has a unique name, an address, and a place.
I Dish(name, type, description)
Each dish has a name, a type (e.g., appetizer, main course, desert, beverage), and a description. As
some restaurants serve several types of the same dish (e.g., smaller portion as appetizer, larger portion
as main course), we use the name and the type to uniquely identify each variant of a dish (primary
key).
I RestaurantDish(rname, dname, dtype, price)
The relationshipRestaurantDish relates each restaurant to the dishes they oer for sale. Furthermore,
this relationship stores the price of each oering. The attribute rname is a foreign key referencing
Restaurant and the pair of attributes (dname, dtype) is a foreign key referencing Dish.
I Review(user, rname, score)
Finally, users can write reviews of restaurants. The attribute rname is a foreign key referencing
Restaurant