Discussion
Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query
Q: \(r \bowtie (\sigma_{B \lt 5}(s))\)
Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.
Which one of the following queries is NOT equivalent to Q?
Answer: C
The query that is NOT equivalent is \(r\ LOJ\ (\sigma_{B \lt 5}(s))\).
Q keeps only the r tuples whose B value is below 5, joined with their s tuple. Because r.B is a foreign key into s.B and there are no nulls, every r tuple has exactly one matching s tuple.
The left outer join of r with the filtered s keeps every r tuple: those with B below 5 join normally, but those with B of 5 or more find no partner and are kept with C padded with null. Those extra rows are not in Q.
The others match Q. Selecting \(B \lt 5\) after the join gives the same rows. With the foreign key, r LOJ s equals the plain join, so selecting after it is the same again. And \(\sigma_{B \lt 5}(r)\ LOJ\ s\) joins only the small r tuples, each of which has its partner in s.
Post your comment
Please log in or register to post a comment.
Examination Categories
- SSC Exams
- Banking Exams
- Teaching Exams
- Civil Services Exams
- Railways Exams
- Engineering Recruitment Exams
- Defence Exams
- State Government Exams
- Police Exams
- Insurance Exams
- Nursing Exams
- Other Government Exams
- NRA CET
- PG Entrance Exams
- Campus Placement
- MBA Entrance Exams
- CUET and UG Entrance
- Judiciary Exams
- Regulatory Body Exams
- Software Development