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. (‌⋈ denotes natural join)

πowner(Own⋈(σcolor="red"(Car⋈(σmaker="ABC"Make))))

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

A.

All owners of a red car, a car made by ABC, or a red car made by ABC

B.

All owners of more than one car, where at least one car is red and made by ABC

C.

All owners of a red car made by ABC

D.

All red cars made by ABC

Solution:

We are given a relational algebra expression:

πowner(Own⋈(σcolor="red"(Car⋈(σmaker="ABC"Make))))

Let's break it down step by step:

 

Step-by-step Breakdown of the Expression:

  1. σmaker="ABC"Make\sigma_{\text{maker} = "ABC"} \text{Make}

    • Selects rows from Make where maker = "ABC"

  2. Car⋈(σmaker="ABC"Make)\text{Car} \bowtie (\sigma_{\text{maker} = "ABC"} \text{Make})

    • Natural join on model, so now we get cars made by ABC

  3. σcolor="red"(…)\sigma_{\text{color} = "red"} (\ldots)

    • Filter above result to keep only red cars made by ABC

  4. Own⋈(…)\text{Own} \bowtie (\ldots)

    • Join with Own on serial, so now we get owners of red cars made by ABC

  5. πowner(…)\pi_{\text{owner}}(\ldots)

    • Project just the owner column: final result is the owners of red cars made by ABC

✅ Correct Answer:

(C) All owners of a red car made by ABC