GATE CS 2018 Set 1 — Question 51
Go beyond PYQs with Success TrackerAI-powered personalised practice and doubt support. Unlimited practice on eligible plans; AI usage limits apply.MCQ+2 / -0.67HardJoinsRelational ModelDatabasesSelection & Projection
Databases → Relational Model → Joins
Last updated
Question
Consider the relations and , where is a primary key and is a foreign key referencing . Consider the queryLet denote the natural left outer-join operation. Assume that and contain no null values.Which one of the following queries is NOT equivalent to ?
Correct answer
(C) r LOJ (σ_(B < 5)(s))
Solution
In the given scenario, is a foreign key referencing , and is a primary key. This implies that for every tuple in , there exists a corresponding tuple in with the same value. Also, there are no null values in the base relations.
1.Query : returns tuples from that have a matching tuple in where . Effectively, it filters for and joins it with the corresponding attributes from .
2.Option (A): . Since every tuple in has a match in , is essentially extended with attributes from . Filtering on this result is equivalent to .
3.Option (B): . Because every tuple in is guaranteed to have a match in (due to the foreign key constraint and no nulls), the natural left outer join is identical to the natural join . Thus, this is equivalent to .
4.Option (C): . This query performs a left outer join. It will include all tuples from . For tuples in where , there will be no match in the filtered relation . Consequently, these tuples will appear in the result set padded with NULL values for the attributes of . Since would have excluded these tuples entirely, (C) is NOT equivalent to .
5.Option (D): . This filters first for . Since every such tuple has a match in , the LOJ behaves like a natural join. This is equivalent to .
Continue learning with Success Tracker
A step still unclear? Work through it with support
Use Success Tracker to ask about the reasoning, then try another GATE CS question to check your understanding.
AI-powered practice· Unlimited practice on eligible plans
- PYQs with solutions
- Attempt available previous-year questions, then compare your reasoning with the worked solution. Coverage varies by stream.
- Practice that adapts
- Choose a topic, work on weaker areas and bookmark questions to revisit. Your attempts feed your progress tracking.
- AI doubt support
- Ask follow-up questions about a step or concept while practising, instead of stopping at the final answer.
Unlimited practice is available on eligible plans. Free practice and AI usage have limits; check the current plan allowances before choosing.
This page stays readable without an account. AI responses can be wrong; check them against the solution and source material.
More questions on Relational Model
2026 Set 2 Q15In the context of DBMS, consider the two sets T and S given below. | T | S | |---|---| | I:…2026 Set 2 Q20Consider concurrent execution of two transactions and in a DBMS, both of which access a…2026 Set 1 Q30Let and be the attributes of a relation in a relational schema. Let…2026 Set 1 Q31In the context of relational database normalization, which of the following statements is/are true?2026 Set 2 Q42In the context of schema normalization in relational DBMS, consider a set F of functional…