edited by
29,076 views
70 votes
70 votes

Consider the following relations $A, B$ and $C:$

$$\overset{\text{A}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Name} & \text {Age}  \\\hline\text{12}& \text{Arun} & \text{60} \\\hline\text{15}& \text{Shreya} & \text{24} \\\hline\text{99}& \text{Rohit} & \text{11}  \\\hline \end{array}} \qquad
\overset{\text{B}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Name} & \text {Age} \\\hline  \text{15}& \text{Shreya} & \text{24} \\\hline\text{25}& \text{Hari} & \text{40} \\\hline\text{98}& \text{Rohit} & \text{20} \\\hline\text{99}& \text{Rohit} & \text{11}  \\\hline \end{array}}\qquad
\overset{\text{C}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Phone} & \text {Area} \\\hline  \text{10}& \text{2200} & \text{02} \\\hline\text{99}& \text{2100} & \text{01} \\\hline \end{array}}$$

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of $A\cup B$ is the same as that of $A$.

$$(A\cup B)\bowtie _{A.Id > 40 \vee C.Id < 15} C$$

  1. $7$
  2. $4$
  3. $5$
  4. $9$
edited by

6 Answers

Best answer
22 votes
22 votes
Given the relations $A, B$ and $C:$ $$\overset{\text{A}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Name} & \text {Age}  \\\hline\text{12}& \text{Arun} & \text{60} \\\hline\text{15}& \text{Shreya} & \text{24} \\\hline\text{99}& \text{Rohit} & \text{11}  \\\hline \end{array}} \qquad
\overset{\text{B}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Name} & \text {Age} \\\hline  \text{15}& \text{Shreya} & \text{24} \\\hline\text{25}& \text{Hari} & \text{40} \\\hline\text{98}& \text{Rohit} & \text{20} \\\hline\text{99}& \text{Rohit} & \text{11}  \\\hline \end{array}}\qquad
\overset{\text{C}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Phone} & \text {Area} \\\hline  \text{10}& \text{2200} & \text{02} \\\hline\text{99}& \text{2100} & \text{01} \\\hline \end{array}}$$ This is an example of theta join and we know: $R⋈_{\theta}S = \sigma_{\theta}(R\times S)$

$\therefore (A \cup B)⋈_{A.Id>40 \vee C.Id<15}C =  (_{A.Id>40}((A\cup B) \times C)) \cup (_{C.Id<15}((A\cup B) \times C)) $

To make the query more efficient we can perform the select operation before the cross product.

$\therefore (A\cup B)⋈_{A.Id>40 \vee C.Id<15}C =  (_{A.Id>40}(A \cup B) \times C) \cup ((A \cup B) \times _{C.Id<15}C)$

Now calculate $A\cup B:$ $$\begin{array}{c c c } \hline \textbf{ID} & \textbf{Name} & \textbf{Age} \\\hline 12 & \text{Arun} & 60 \\\hline 15 & \text{Shreya} & 24 \\\hline 25 & \text{Hari} & 40 \\\hline 98 & \text{Rohit} & 20 \\\hline 99 & \text{Rohit} & 11 \end{array}$$ Please note that union is a set operation and duplicates will not be included by default.

First perform cross-product $(_{A.Id>40}(A\cup B) \times C)$, i.e., Multiply each row of $_{A.Id>40}(A\cup B)$ with each row of $C:$ $$\begin{array}{c c c } \hline \textbf{ID} & \textbf{Name} & \textbf{Age} & \textbf{C.ID} & \textbf{Phone} & \textbf{Area} \\\hline 98 & \text{Rohit} & 20 & 10 & 2200 & 02 \\\hline 98 & \text{Rohit} & 20 & 99 & 2100 & 01 \\\hline 99 & \text{Rohit} & 11 & 10 & 2200 & 02 \\\hline 99 & \text{Rohit} & 11 & 99 & 2100 & 01  \end{array}$$ Now perform cross-product $((A \cup B) \times _{C.Id<15}C) $, i.e., Multiply each row of $(A∪B)$ with each row of $_{C.Id<15}C:$ $$\begin{array}{c c c } \hline \textbf{ID} & \textbf{Name} & \textbf{Age} & \textbf{C.ID} & \textbf{Phone} & \textbf{Area} \\\hline 12 & \text{Arun} & 60 & 10 & 2200 & 02 \\\hline 15 & \text{Shreya} & 24 & 10 & 2200 & 02 \\\hline 25 & \text{Hari} & 40 & 10 & 2200 & 02  \\\hline 98 & \text{Rohit} & 20 & 10 & 2200 & 02\\\hline  99 & \text{Rohit} & 11 & 10 & 2200 & 02  \end{array}$$ Now take the union: $(_{A.Id>40}(A\cup B) \times C) \cup ((A\cup B) \times _{C.Id<15}C) $

We will get: $$\begin{array}{c c c } \hline \textbf{ID} & \textbf{Name} & \textbf{Age} & \textbf{C.ID} & \textbf{Phone} & \textbf{Area} \\\hline 12 & \text{Arun} & 60 & 10 & 2200 & 02 \\\hline 15 & \text{Shreya} & 24 & 10 & 2200 & 02 \\\hline 25 & \text{Hari} & 40 & 10 & 2200 & 02 \\\hline 98 & \text{Rohit} & 20 & 10 & 2200 & 02 \\\hline  98 & \text{Rohit} & 20 & 99 & 2100 & 01 \\\hline  99 & \text{Rohit} & 11 & 10 & 2200 & 02 \\\hline  99 & \text{Rohit} & 11 & 99 & 2100 & 01  \end{array}$$ which has $7$ Tuples, hence answer is $A.$
edited by
55 votes
55 votes
50. For C.ID = 10, all tuples from $A \cup B$ satisfies the join condition, hence 5 tuples (union of A and B has only 5 tuples are 2 of them are repeating for Shreya and Rohit)  will be returned. Now, for C.ID = 99,  A.ID = 99 and A.ID = 98 (for A.ID = 98, we need to assume A ∪ B, has the same schema s A as told in the question) satisfies the condition A.ID>40, and hence two tuples are returned. So, number of tuples = 5 + 2 = 7.

The output will be:
$$\begin{array}{llllll}\\\hline \textbf{Id}  &  \textbf{Name} & \textbf{Age}  &  \textbf{Id} &  \textbf{Phone} & \text{Area} \\\hline \text{12} & \text{Arun}& \text{60} & \text{10}& \text{2200} & \text{02}\\ \text{15} & \text{Shreya}& \text{24} & \text{10}& \text{2200} & \text{02}\\ \text{99} & \text{Rohit}& \text{11} & \text{10}& \text{2200} & \text{02}\\ \text{25} & \text{Hari}& \text{40} & \text{10}& \text{2200} & \text{02}\\ \text{98} & \text{Rohit}& \text{20} & \text{10}& \text{2200} & \text{02}\\ \text{99} & \text{Rohit}& \text{11} & \text{99}& \text{2100} & \text{01} \\ \text{98} & \text{Rohit}& \text{20} & \text{99}& \text{2100} & \text{01}\\\hline  \end{array}$$

Correct Answer: $A$
edited by
5 votes
5 votes

Hope you find this helpful...

edited by
4 votes
4 votes
SIMPLY WE TRRAT JOIN OPERATOR AS CROSS JOIN on (AUB) cross join C then we have 10 tuples now if we apply cpnstrain on cross join A.id <40 and C.id>45 then we hav three combination

12, 99

15 ,99

25 ,99

if we subtract 3 combination from 10 it will give 7 tuples

 

now for 2nd ques inner sql give 0 and A.age will be greater tan 0

so, 3 tuples will be selectd
Answer:

Related questions

48 votes
48 votes
4 answers
1
go_editor asked Apr 21, 2016
13,587 views
Consider the following relations $A, B$ and $C:$ $$\overset{\textbf{A}}{\begin{array}{|c|c|c|}\hline\\\textbf{Id}& \textbf{Name}& \textbf{Age} \\\hline12& \text{A...
70 votes
70 votes
7 answers
3