GATE DA 2025 | Question 17

MCQ · 1 mark · −0.33

Consider the following three relations:

Car (model, year, serial, color)
Make (maker, model)
Own (owner, serial)

A tuple in Car represents a specific car of a given model, made in a given year, with a serial number and a color. A tuple in Make specifies that a maker company makes cars of a certain model. A tuple in Own specifies that an owner owns the car with a given serial number. Keys are underlined; (owner, serial) together form key for Own. (\(\bowtie\) denotes natural join)

\[\pi_{\texttt{owner}}(\texttt{Own} \bowtie (\sigma_{\texttt{color}=\text{“}\mathit{red}\text{”}}(\texttt{Car} \bowtie (\sigma_{\texttt{maker}=\text{“}\mathit{ABC}\text{”}}\texttt{Make}))))\]

Which one of the following options describes what the above expression computes?

  1. Option A: All owners of a red car, a car made by ABC, or a red car made by ABC
  2. Option B: All owners of more than one car, where at least one car is red and made by ABC
  3. Option C: All owners of a red car made by ABC (correct)
  4. Option D: All red cars made by ABC

Answer

(C)

Explanation

Evaluate the expression from the innermost part outwards.

  1. \(\sigma_{\texttt{maker}=\text{“}\mathit{ABC}\text{”}}\texttt{Make}\) keeps the models that ABC makes.
  2. Joining with Car (on model) keeps only cars of those models, and \(\sigma_{\texttt{color}=\text{“}\mathit{red}\text{”}}\) then keeps the red ones. Both conditions hold for the same car.
  3. Joining with Own (on serial) pairs each such car with its owners, and \(\pi_{\texttt{owner}}\) keeps only the owners.

The result is every owner of a red car made by ABC. Option A would need a union of separate selections, B needs a count that the expression never takes, and D describes cars although the projection outputs owners.

Answer: C