in Databases retagged ago by
6,222 views
14 votes
14 votes
A relation $r(A, B)$ in a relational database has $1200$ tuples. The attribute $A$ has integer values ranging from $6$ to $20$, and the attribute $B$ has integer values ranging from $1$ to $20$. Assume that the attributes $A$ and $B$ are independently distributed.

The estimated number of tuples in the output of $\sigma _{(A>10)\vee(B=18)}(r)$ is ____________.
in Databases retagged ago by
by
6.2k views

2 Answers

25 votes
25 votes
Best answer
  • $P(A>10) = \frac{10}{15} = \frac{2}{3}$
  • $P(B=18) = \frac{1}{20}$
  • $P(A>10 \land B=18) = \frac{2}{3}\times\frac{1}{20} = \frac{1}{30}$

$P(A>10 \lor B=18) = P(A>10) + P(B=18) – P(A>10 \land B=18)$

$\qquad = \frac{2}{3} + \frac{1}{20} – \frac{1}{30} = \frac{40 + 3 – 2}{60} = \frac{41}{60}$

$\text{Estimated number of tuples} = \frac{41}{60}\times1200 = 820$

The above answer is TRUE for SQL SELECT but not for Relational Algebra as by theory relational algebra operates on a set which means all the elements must be distinct. Since we have $15$ distinct possible values for $A$ and $20$ distinct possible values for $B,$ in strict relational algebra we’ll get 

$\text{Estimated number of tuples} = \frac{41}{60}\times (15 \times 20) = 205.$

Official Answer: $205$ OR $820.$

selected by

4 Comments

But since Relational Database is just a Relation, which itself is a Set, then how can we have duplicates?
0
0
Anybody going to challenge this question ?
1
1
null values are also possible in A or  B
0
0
7 votes
7 votes

To all those answering 820

Remember relational algebra operator select is also distinct

By principal of mutual inclusion exclusion

For each A>10 (11-20)

there are 20 distinct B(1-20)

So current total = 200 (20*10)

For each B = 18 

There are 15 distinct A

current total = 200 + 15 = 215

Now excluding the tuples which we have counted twice i.e B=18 and A>10

There are 10 such tuples

So current total = 200 +15 -10

Final Answer should be 205 according to me

Sadly I made a silly mistake and entered 210 :)

 

4 Comments

I think pi projection eliminates duplicates but not the sigma.

The sirs as well have said the same thing. But I could please provide me with a screenshot of the textbook from where you have read this? (I just wanted to verify, that’s all)

0
0
What is the final answer, 820 or 205?
0
0
marks were awarded for both of these answers.
1
1
Answer:

Related questions