Question

Suppose relation R(ABC) has the following tuples:

How many tuples are resulted by the given RA expression?

πAB(R) ⋈R.B < S.B ρS(A, B)(πBC(R))

• (a) 6
• (b) 2
• (c) 3
• (d) 4

Solution

The correct answer is: (b) 2.

The relational algebra expression breaks down as follows:

1. Projection πAB(R) results in the following relation:
   

   A	B
   1	2
   3	2

2. Projection πBC(R) followed by renaming produces:
   

   A	B
   2	3
   2	1

3. The join ⋈R.B < S.B is performed, yielding:
   

   A	B	A’	B’
   1	2	2	3
   3	2	2	3

Question

Consider the following schema:

• Student (Sid, Sname, Sage)
• Course (Cid, CourseName, Credit)
• Enrolled (Sid, Cid)

The relational algebra query is:

Q = πSid(Student) - πSid(σCredit < 5(Course) ⋈ Enrolled)

Which of the following is correct about the relational algebra query Q?

• (a) Finds Sid of students who enrolled in at least one course with credit greater than 5.
• (b) Finds Sid of students who enrolled in all courses with credit ≥ 5.
• (c) Finds Sid of students who enrolled in all courses with credit less than 5.
• (d) None of these.

Solution

The correct answer is: (b) Finds Sid of students who enrolled in all courses with credit ≥ 5.

Explanation:

1. First, compute:

   S = πSid(σCredit < 5(Course) ⋈ Enrolled)

   This computes the Sid of students who are enrolled in at least one course with credit less than 5.

2. Next, compute:

   Q = πSid(Student) - S

   This computes the Sid of students who are enrolled in every course with credit greater than or equal to 5.

Questions

Q6

Consider the selection of the form SA ≤ 200(r), where r is a relation with 1000 tuples. Assume attribute values of A among the tuples are uniformly distributed in the interval [1, 500]. Find the number of tuples returned by the given query:

• (a) 100
• (b) 200
• (c) 300
• (d) 400

Q7

Consider the relation P(A, B, C), Q(C, D, E), and R(E, F) having tuples 200, 300, and 100 respectively. Estimate the number of tuples in relation P ⋈ Q ⋈ R.

• (a) 100
• (b) 200
• (c) 300
• (d) 400

Q8

Consider the following statements:

1. If R(A, B) and S(A, B) are any two relations, then πA(R ∪ S) = πA(R) ∪ πA(S).
2. If P(A, B) and Q(A, B) are two relations, then πA(R ∩ S) = πA(P) ∩ πA(Q).

Which of the above statements is/are correct?

• (a) Only (i)
• (b) Only (ii)
• (c) Both (i) and (ii)
• (d) None of these

Solutions

Q6

Answer: (d) 400

Explanation:

A is uniformly distributed among [1, 500]. The number of tuples in [1, 100], [101, 200], [201, 300], [301, 400], and [401, 500] is 200 each. Therefore, the number of tuples in [1, 200] is 400.

Q7

Answer: (a) 100

Explanation:

• P ⋈ Q: The common attribute is C, which is a key for both P and Q. |P ⋈ Q| = min(200, 300) = 200 tuples.
• R1 ⋈ R: The common attribute is E, which is a key for both R1 and R. |R1 ⋈ R| = min(200, 100) = 100 tuples.
• So, |P ⋈ Q ⋈ R| = 100 tuples.

Q8

Answer: (a) Only (i)

Explanation:

Statement (ii) is false. For example, consider:

P(A, B) = {(0, 1)}
Q(A, B) = {(0, 2)}
πA(P ∩ Q) = ∅ but πA(P) ∩ πA(Q) = {0}

Therefore, (i) is correct and (ii) is false.

Question

Consider the relation R(A, B, C, D). Which relational algebra expression returns the lowest value of A?

• (a) πR1.A(R1 ⋈R1.A < R2.A R2)
• (b) πA(R) − πR1.A(R1 ⋈R1.A > R2.A R2)
• (c) πA(R) − πR1.A(R1 ⋈R1.A < R2.A R2)
• (d) None of these (R1 and R2 are just renames of R)

Solution

Answer: (b)

Explanation:

1. Compute:

   S = πR1.A(R1 ⋈R1.A > R2.A R2)

   This finds all values of A except the lowest.

2. Subtract S from πA(R) to find the lowest value of A:

   πA(R) − S

Question

Consider the following two tables:

Table: R₁

Table: R₂

The number of rows where null entries are present in the table R1 ⟗ R2 (R₁ natural full outer join R₂) is _______.

Solution

Answer: 7

Explanation:

In a natural full outer join, all rows from both tables are included. Rows with no matching values in the other table result in null entries. After performing the join:

• Rows from R1 that do not have a match in R2 result in null values for columns D.
• Rows from R2 that do not have a match in R1 result in null values for columns C.

In this example, there are 7 such rows with null entries after the join.