Description

Section (315/415/615):
MIDTERM EXAM – 601.315/415/615 – Databases
The total number of points in this exam is 75 for both 601.315 and 601.415/615 students (although the questions are mostly different). If you work at approximately 1 minute per point, you should finish on time.
Question 1 – Relational Algebra (5 points)
The tables that are used in this (and following) questions are found on your supplementary handout.
You can use this view WINNER for all other relational algebra questions on this exam.
Question 2 – Relational Algebra (5 points)
Express the following query in the relational algebra:
Question 3 – Relational Algebra (5 points)
Question 4 – Relational Algebra (5 points)
Express the following query in the relational algebra.
(a) (5 points) 601.315 only: List the name and birthdate for players who have played for at least one team which has never won a match.
(b) (5 points) 601.415/615 only: List the name and birthdate for players who have played for at least one team which has never won a match, but has refereed for the same game that they have also played in.
Question 5 – Tuple Relational Calculus (6 points)
Express the following query in Tuple Relational Calculus:
Question 6 – SQL (25 points)
Express the following queries in SQL:
(a) 601.315 only: List the names and birth country of players who were played for a
(aa) 601.415/615 only: List the names and birth country of players who have never played for a team whose host country is the country where they (the player) were born.
(b) 601.315 only: List the names and birthdate of all players born after 1/1/84 who have played for more than two teams.
(bb) 601.415/615 only: List the names and birthdate of all players born after 1/1/84 who have played for more than two teams, and also include the total number of teams that the player has ever played for.
(c) (315 and 415/615: For each team in the league, list the total number of points scored by Colette Cretin when playing against that team.
(d) 601.315 only: Name the female player with the highest total points scored in 2017, and include that total.
(e) 601.315 only: List the names and birthdates of all players who have never played for more than one team.
(ee) 601.415/615 only: How many players in the database have played for more than two teams in the same host country in their entire careers.
Question 7 – QBE (15 points)

Express the following queries in QBE. To simplify your work, table shells have been provided. Just fill in the appropriate cells with variables/values.
(a) (5 points) List the name and birthdate of all players who have played in a game against someone who played in a game against Wally Whinger, but were not born in the same country as Wally Winger.
PLAYER PName BDate Sex CityOfBirth CountryofBirth

TEAM HostCity TName HostCountry

PLAYS-FOR PName TName Season

P Name G amelD Position PointsScored

Conditions

PLAYER PName BDate Sex CountryofBirth

TEAM HostCity TName HostCountry

PLAYS-FOR PName TName Season

PLAYED-IN-GAME PName GamelD Position PointsScored

Conditions

(c) List the name, birthdate and birth city of all players who have played for a team with a host city and country that is the same as the city/country where the player was born.
PLAYER PName BDate Sex CityOfBirth CountryofBirth

TEAM HostCity TName HostCountry

PLAYS-FOR PName TNa me Season

PLAYED-IN_GAME PName GamelD Position PointsScored

Conditions

Question 8 – Functional Dependencies (10 points)
(a) Consider the relation r(A,B,C,D,E) with functional dependencies:
BC D, D E, CE A
Circle One If yes, briefly show your derivation
Does D -+ A? Yes/No
Does CD -+ E? Yes/No
Does CDE A? Yes/No
Does BCD A? Yes/No
(b) List at least one candidate key for r (informally show your work):
CERTIFICATION PAGE
By signing below, I promise that my answers on this exam are entirely my own work. I have not looked at the answers written by others and I have not allowed others to look at my answers. I also have not consulted any books or notes while taking this exam besides the 4 single sided pages allowed.
My Signature: