COMPSCI 186 Midterm: cs186-sp2018-mt1-Shah-soln
CS 186/286 Spring 2018 Midterm 1
•Do not turn this page until instructed to start the exam.
•You should receive 1 single-sided answer sheet and a 18-page exam packet.
•All answers should be written on the answer sheet. The exam packet will be collected but not graded.
•You have 80 minutes to complete the midterm.
•The midterm has 4 questions, each with multiple parts.
•The midterm is worth a total of 75 points.
•For each question, place only your final answer on the answer sheet; do not show work.
•For multiple choice questions, please fill in the bubble or box completely, do not mark the box with
an X or checkmark.
•Use the blank spaces in your exam for scratch paper.
•You are allowed one 8.5” ×11” double-sided page of notes.
•No electronic devices are allowed.
1 SQL/Relational Algebra (28 points)
For the following questions, we consider the following schema:
CREATE TABLE Player(
pid INTEGER PRIMARY KEY,
name TEXT
);
CREATE TABLE Relationship(
pid1 INTEGER REFERENCES Player(pid),
pid2 INTEGER REFERENCES Player(pid),
type TEXT,
time TIMESTAMP,
PRIMARY KEY(pid1, pid2, time)
);
Players may be part of a relationship (with the “type” being either “Friend” or “Enemy”) with other players.
A single row in the Relationship table represents the start of a relationship between two players. The most
current relationship between players A and B is the row in Relationship with A, B, and the latest timestamp.
Consider the following relationships:
At 2018-03-01 11:40:00:
Cat
Dog Pig
Enemy
At 2018-03-01 11:50:00:
Cat
Dog Pig
Enemy
Enemy
At 2018-03-01 12:00:00:
Cat
Dog Pig
Enemy
Friend
The corresponding tables are:
Player
pid name
1 Cat
2Dog
3Pig
Relationship
pid1 pid2 type time
1 2 Enemy 2018-03-01 11:40:00
21 Enemy 2018-03-01 11:40:00
23 Enemy 2018-03-01 11:50:00
32 Enemy 2018-03-01 11:50:00
23 Friend 2018-03-01 12:00:00
32 Friend 2018-03-01 12:00:00
Relationships are always mutual: if there is a row with pid1=3 and pid2=4, there will be a corresponding
row with the same type/time and with pid1=4 and pid2=3.
Assume that a player is never in a relationship with themself (Dog cannot be friends with Dog, and Cat
cannot be enemies with Cat).
Page 2 of 18
1. (4 points) We wish to find players that have started enemy relationships (with the same or with different
players) at least 50 times. This could be between the same pair of players at least 50 times, or between
at least 50 others, etc.
Mark all of the following queries that do this.
A. SELECT Player.*
FROM Player
INNER JOIN Relationship ON pid1 = pid
WHERE type = ‘Enemy’ AND COUNT(*) >= 50
GROUP BY pid, name;
B. SELECT Player.*
FROM Player, Relationship
WHERE pid = pid1 AND type = ‘Enemy’
GROUP BY pid, name
HAVING COUNT(*) >= 50;
C. SELECT Player.*
FROM Player
INNER JOIN Relationship ON pid1 = pid
WHERE COUNT(*) >= 50;
GROUP BY pid, name, type
HAVING type = ‘Enemy’;
D. SELECT Player.*
FROM Player, Relationship
GROUP BY pid, pid1, type, name
HAVING pid = pid1 AND type = ‘Enemy’ AND COUNT(*) >= 50;
Solution: A is incorrect. It is an invalid query, since we are using an aggregate in the WHERE clause.
B is correct.
C is incorrect. It is an invalid query, since we are using an aggregate in the WHERE clause.
D is correct. We group based on pid,pid1, and type, and throw out groups where the player IDs
are unequal, where the type is not “Enemy”, or where the count is too small.
This question was graded as 4 independent true/false questions, each worth 0.5 points. That is, you
got 0.5 points for every correct choice that you selected and 0.5 points for every incorrect choice
that you did not select.
2. (6 points) Now instead, we decide that we want to fetch the number of times each player has started an
enemy relationship (if a player became enemies with the same player twice, count it twice). If a player
has never started an enemy relationship, the count should be 0.
Mark all of the following queries that do this.
A. SELECT Player.*, COUNT(*)
FROM Player
LEFT JOIN Relationship ON pid1 = pid
WHERE type = ‘Enemy’
GROUP BY pid, name;
B. SELECT Player.*, COUNT(pid1)
FROM Player
LEFT JOIN Relationship ON pid1 = pid
GROUP BY pid, name, type
HAVING type = ‘Enemy’;
Page 3 of 18
Document Summary
Cs 186/286 spring 2018 midterm 1: do not turn this page until instructed to start the exam, you should receive 1 single-sided answer sheet and a 18-page exam packet, all answers should be written on the answer sheet. For the following questions, we consider the following schema: Create table player( pid integer primary key, name text. Create table relationship( pid1 integer references player(pid), pid2 integer references player(pid), type text, time timestamp, Players may be part of a relationship (with the type being either friend or enemy ) with other players. A single row in the relationship table represents the start of a relationship between two players. The most current relationship between players a and b is the row in relationship with a, b, and the latest timestamp. Relationships are always mutual: if there is a row with pid1=3 and pid2=4, there will be a corresponding row with the same type/time and with pid1=4 and pid2=3.
