GATE DA 2025 | Question: 7

Question

Consider the following three relations:

         $\text{Car (model, year, $\underline{\text{serial}}$, color)}$
         $\text{Make (maker, $\underline{\text{model}}$)}$
         $\text{Own ($\underline{\text{owner}},$ $\underline{\text{serial}}$)}$

A tuple in $\text{Car}$ represents a specific car of a given $\text{model},$ made in a given $\text{year},$ with a $\text{serial}$ number and a $\text{color}.$ A tuple in $\text{Make}$ specifies that a $\text{maker}$ company makes cars of a certain $\text{model}.$ A tuple in $\text{Own}$ specifies that an $\text{owner}$ owns the car with a given $\text{serial}$ number. Keys are underlined; ($\underline{\text{owner}},$ $\underline{\text{serial}}$) together form key for $\text{Own}.$ ( $\bowtie$ denotes natural join)
\[
\pi_{\text{owner}} \left( \text{Own} \bowtie \left( \sigma_{\text{color} = \text{"red"}} \left( \text{Car} \bowtie \left( \sigma_{\text{maker} = \text{"ABC"}}  \text{ Make} \right) \right) \right) \right)
\]

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

• A. All owners of a red car, a car made by $\text{ABC},$ or a red car made by $\text{ABC}$
• B. All owners of more than one car, where at least one car is red and made by $\text{ABC}$
• C. All owners of a red car made by $\text{ABC}$
• D. All red cars made by $\text{ABC}$

Comments

The correct answer is Option C) All owners of a red car made by ABC.

Answer 1

OPTION(C) 

From Make table first select the Maker="ABC" then select the Colour ="red" then  Project the Owner...

hence it gives the " ALL owners Of a Red car Made by ABC"

If like Please Upvote!!

Answer 2

Car(serial, model, year, color): Represents cars with their serial numbers and properties.
Make(model, maker): Represents which company (maker) makes a specific model.
Own(owner, serial): Represents ownership of a car based on its serial number.

---

 

\( \sigma_{\text{maker} = 'ABC'} \text{Make} \): Selects those tuples from Make where the maker is 'ABC'.

\( \text{Car} \bowtie (\sigma_{\text{maker} = 'ABC'} \text{Make}) \): Joins the Car relation with the Make relation where the maker is 'ABC'.

\( \sigma_{\text{color} = 'red'} (\text{Car} \bowtie (\sigma_{\text{maker} = 'ABC'} \text{Make})) \): Selects those cars that are red.

\( \text{Own} \bowtie (\sigma_{\text{color} = 'red'} (\text{Car} \bowtie (\sigma_{\text{maker} = 'ABC'} \text{Make}))) \): Joins the Own relation with the red cars made by ABC.

\( \pi_{\text{owner}} (\text{Own} \bowtie (\sigma_{\text{color} = 'red'} (\text{Car} \bowtie (\sigma_{\text{maker} = 'ABC'} \text{Make}))) \): Projects the owner attribute.

---

 

All owners of a red car made by ABC.

Answer 3

We are given three relations:

• $\texttt{Car}(\texttt{model}, \texttt{year}, \texttt{serial}, \texttt{color})$, with key $\texttt{serial}$
• $\texttt{Make}(\texttt{maker}, \texttt{model})$, with key $(\texttt{maker}, \texttt{model})$
• $\texttt{Own}(\texttt{owner}, \texttt{serial})$, with key $(\texttt{owner}, \texttt{serial})$

The relational algebra expression is:

$$
\pi_{\texttt{owner}} \left( \texttt{Own} \bowtie \left( \sigma_{\texttt{color = "red"}} \left( \texttt{Car} \bowtie \left( \sigma_{\texttt{maker = "ABC"}} \texttt{Make} \right) \right) \right) \right)
$$

We determine its meaning using a concrete instance.

Instance

$$
\texttt{Make:}
\quad
\begin{array}{|c|c|}
\hline
\texttt{maker} & \texttt{model} \\
\hline
\texttt{ABC} & M1 \\
\texttt{ABC} & M2 \\
\texttt{XYZ} & M3 \\
\hline
\end{array}
$$

$$
\texttt{Car:}
\quad
\begin{array}{|c|c|c|c|}
\hline
\texttt{model} & \texttt{year} & \texttt{serial} & \texttt{color} \\
\hline
M1 & 2020 & S1 & \texttt{red} \\
M1 & 2021 & S2 & \texttt{blue} \\
M2 & 2022 & S3 & \texttt{red} \\
M3 & 2023 & S4 & \texttt{red} \\
\hline
\end{array}
$$

$$
\texttt{Own:}
\quad
\begin{array}{|c|c|}
\hline
\texttt{owner} & \texttt{serial} \\
\hline
O1 & S1 \\
O2 & S2 \\
O3 & S3 \\
O4 & S4 \\
\hline
\end{array}
$$

Step-by-Step Evaluation

1. $\sigma_{\texttt{maker = "ABC"}}(\texttt{Make})$  

   Returns models made by ABC: $\{( \texttt{ABC}, M1 ), ( \texttt{ABC}, M2 )\}$.

2. $\texttt{Car} \bowtie$ (above)  
   Natural join on $\texttt{model}$ yields cars of models $M1$ or $M2$:  
   $$
   \begin{array}{|c|c|c|c|}
   \hline
   \texttt{model} & \texttt{year} & \texttt{serial} & \texttt{color} \\
   \hline
   M1 & 2020 & S1 & \texttt{red} \\
   M1 & 2021 & S2 & \texttt{blue} \\
   M2 & 2022 & S3 & \texttt{red} \\
   \hline
   \end{array}
   $$

3. $\sigma_{\texttt{color = "red"}}$  
   Keeps only red cars: serials $S1$ and $S3$.

4. $\texttt{Own} \bowtie$ (above)  
   Join on $\texttt{serial}$ gives:  
   $$
   \begin{array}{|c|c|}
   \hline
   \texttt{owner} & \texttt{serial} \\
   \hline
   O1 & S1 \\
   O3 & S3 \\
   \hline
   \end{array}
   $$

5. $\pi_{\texttt{owner}}$  
   Projects to $\{O1, O3\}$.

These are precisely the owners who own a red car made by ABC.

Note:

• $O2$ owns a blue ABC car → excluded.
• $O4$ owns a red car, but made by XYZ → excluded.

Option Analysis

• A. All owners of a red car, a car made by ABC, or a red car made by ABC  
  • Incorrect: includes $O2$ and $O4$, who do not satisfy both conditions.
• B. All owners of more than one car, where at least one is red and made by ABC  
  •   Incorrect: no such requirement on number of cars; $O1$ owns only one car.
• C. All owners of a red car made by ABC  
  •   Correct: matches $\{O1, O3\}$.
• D. All red cars made by ABC  
  • Incorrect: the result is a set of owners, not cars.

Conclusion

The expression returns all owners who own at least one red car manufactured by ABC. The correct choice is:

$$
\boxed{\text{C. All owners of a red car made by ABC}}
$$

Answer 4

Make

maker	model
ABC	ModelX
XYZ	ModelY

Car

model	year	serial	color
ModelX	2023	S1	red
ModelX	2023	S2	blue
ModelY	2024	S3	red

Own

owner	serial
Alice	S1
Bob	S2
Eve	S3

---

Option A: All owners of a red car, a car made by ABC, or a red car made by ABC

Relational Algebra Expression:

π owner​(Own⋈σcolor=”red”​(Car))∪π owner​(Own⋈(Car⋈σmaker=”ABC”​(Make)))∪π owner​(Own⋈σcolor=”red”​(Car⋈σmaker=”ABC”​(Make)))

---

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

Relational Algebra Expression:

πowner(σowner=owner2∧serial≠serial2(ρowner2,serial2(Own)×Own)) ∩ πowner(Own⋈σcolor="red"(Car⋈σmaker="ABC"Make))

---

Option C: All owners of a red car made by ABC

Relational Algebra Expression (Original Query):

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

1. σ_maker="ABC" Make → Models by ABC: (ABC, ModelX).

2. Join with Car → ABC cars: (S1: red, S2: blue).

3. σ_color="red" → Red ABC cars: (S1).

4. Join with Own → Owner of S1: Alice.

Output: {Alice}
Why? Alice owns the only red ABC car (S1).

---

Option D: All red cars made by ABC

Relational Algebra Expression:

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