Tripos Worked Solutions: 2021 Paper 3 Question 2 | Databases

Question: Databases (Dr Timothy Griffin) --- 20 marks

The question uses the relational movie database with tables: movies(movie_id, title, year), people(person_id, name), has_position(person_id, movie_id, position), plays_role(person_id, movie_id, role), genres(genre_id, genre), has_genre(movie_id, genre_id).

(a) Interpreting an SQL count [6 marks]

Query:

SELECT COUNT(*) FROM has_position WHERE position = 'director';

Result: 1422. Conclusion: “Our database contains information on 1422 directors.”

Is the conclusion justified?

Answer:

No, the conclusion is not justified. The query counts the number of entries (rows) in has_position where position = 'director', not the number of distinct directors. A person who directs multiple movies will appear in has_position multiple times, each counting separately. For example, if Christopher Nolan has directed 10 movies, he contributes 10 to the count.

To obtain the number of distinct directors, the correct query is:

SELECT COUNT(DISTINCT person_id)
FROM has_position
WHERE position = 'director';

A further subtlety: the original query does not verify that the person_id values actually exist in the people table. If referential integrity is not enforced, has_position could contain person_id values with no matching row in people. A robust query would join to people:

SELECT COUNT(DISTINCT hp.person_id)
FROM has_position hp
JOIN people p ON p.person_id = hp.person_id
WHERE hp.position = 'director';

However, the primary flaw is the failure to use DISTINCT. The count of entries is an upper bound on the count of directors; it is correct only if every director directed exactly one film.

(b) Missing information from filtered query [6 marks]

Query:

SELECT person_id, name, position, COUNT(*) AS total
FROM has_position AS hp
JOIN people AS p ON p.person_id = hp.person_id
WHERE position <> 'actor' AND name = 'Stan Lee'
GROUP BY person_id, name, position;

Result: (Stan Lee, writer, 15). Conclusion: “Stan Lee did not produce any movies.”

Is the conclusion justified?

Answer:

No, the conclusion is not justified. The WHERE clause explicitly excludes rows where position = 'actor', but the query only returns rows grouped by position. The presence of (Stan Lee, writer, 15) tells us Stan Lee has 15 entries as a writer. It says nothing about whether he also appears as a producer, because the GROUP BY groups by position, and the query would return a separate row for each position Stan Lee holds (provided that position is not ‘actor’).

To check whether Stan Lee was a producer, one should query for all positions held:

SELECT DISTINCT position
FROM has_position hp
JOIN people p ON p.person_id = hp.person_id
WHERE p.name = 'Stan Lee';

If this returns both 'writer' and 'producer', then Stan Lee did produce movies. The original query’s GROUP BY person_id, name, position means that if Stan Lee had both writer and producer roles, the result would have shown two rows (one for each position). The fact that only one row appears does suggest he may only hold the writer role in this database, but the conclusion that he “did not produce any movies” cannot be drawn with certainty without examining the complete data. The query omits ‘actor’ roles, and the absence of a producer row could also be due to data entry errors or the fact that the specific position name might be stored differently (e.g., 'producer' vs 'executive producer').

The correct approach: query without the position <> 'actor' filter, or specifically check for the producer role:

SELECT COUNT(*)
FROM has_position hp
JOIN people p ON p.person_id = hp.person_id
WHERE p.name = 'Stan Lee' AND hp.position = 'producer';

If this returns 0, only then can we conclude Stan Lee did not produce any movies (according to this database).

(c) Cross join misinterpretation [8 marks]

Query:

SELECT r1.role, m1.year, COUNT(*) AS total
FROM plays_role AS r1, plays_role AS r2, movies AS m1, movies AS m2, people AS p
WHERE p.person_id = r1.person_id
  AND r1.person_id = r2.person_id
  AND r1.role = r2.role
  AND r1.movie_id = m1.movie_id
  AND r2.movie_id = m2.movie_id
  AND p.name = 'Jennifer Lawrence';
GROUP BY r1.role, m1.year;

Result includes: (Katniss Everdeen, 2012, 4). Conclusion: “Jennifer Lawrence played Katniss Everdeen in 4 movies released in 2012.”

Is the conclusion justified?

Answer:

No, the conclusion is not justified. The query uses a cross join pattern (comma-separated table list in the FROM clause creates a Cartesian product) that pairs every role with every other role where the same person plays the same character. The COUNT(*) counts the number of (r1, r2) pairs, not the number of distinct (role, year, movie) triples.

To see why the result is 4, suppose Jennifer Lawrence played Katniss Everdeen in exactly 2 movies released in 2012. The cross join self-pairing produces:

r1 = movie A, r2 = movie A (1 pair)
r1 = movie A, r2 = movie B (1 pair)
r1 = movie B, r2 = movie A (1 pair)
r1 = movie B, r2 = movie B (1 pair)

Total: 4 pairs. But there are only 2 distinct movies. The query counts combinations, not distinct instances.

Worse, the GROUP BY r1.role, m1.year groups by the role name and the year of m1 (the movie joined to r1). This means all four pairs above fall into the same group (same role, same year), giving total = 4. The year from m2 is not visible in the result.

The correct query to count distinct movies where Jennifer Lawrence played a given role in a given year:

SELECT role, year, COUNT(DISTINCT r.movie_id) AS total
FROM plays_role AS r
JOIN movies AS m ON m.movie_id = r.movie_id
JOIN people AS p ON p.person_id = r.person_id
WHERE p.name = 'Jennifer Lawrence'
GROUP BY role, year;

This counts each movie exactly once per (role, year) group. The COUNT(DISTINCT r.movie_id) ensures duplicates from the join are eliminated.

A more general lesson: when a query contains a self-join or cross join, always ask whether the count is counting the thing you think it is counting, or counting pairs/combinations. The COUNT(*) form is particularly prone to this misinterpretation.