Question

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 σ_{(A>10)∨(B=18)}(r) is ______

Answer 1

Answer: 819 to 820 OR 205 to 205

Explanation:

σ(A>10)∨(B=18)(r) 

case 1: (A >10) 

Allowed A values are (11, 12, 13, 14, 15, 16, 17, 18, 19, 20) total 10

This can map to all values of B (1, 2, 3, ......, 20) total 20 

Hence number of (A,B)  pairs = 10× 20 = 200 (Remember Here 10 pairs are also counted where B = 18 )

case 2:(B=18)

Allowed B values are (18) 

This can map to all values of  A (6, 9, 10, 11,...,20) total 15 

Hence number of (A,B)  pairs = 15 × 1 = 15 

Since OR operation is used in condition part 

Total pairs = 215 But we have to subtract the tuples which are being counted twice 

= 215 - 10 => 205

Note:

This Question can be done in a probabilistic way since the estimated number of the tuple is asked

P(A>10) = 10/15 = 2/3

P(B=18) = 1/20

P[(A>10) ∧  (B=18)] = (2/3)× (1/20) = 1/30

P[(A>10) ∨ (B=18)] =  P(A>10) + P(B=18) - P[(A>10) ∧  (B=18)]

 P[(A>10) ∨ (B=18)]  = (2/3) + (1/20) - (1/30) = 41/60

Estimated Number of tuples = (41/60) × 1200 = 820