Register

GO Classes DPP | GATE CS, DA | DBMS | Natural Join

74 views
1 1 vote

Consider $\text{Postings(post, position, user, ptext)}$.

Two aliases of this relation are used:

$\text{P1 = Postings}$

$\text{P2 = Postings}$

Consider the query:

SELECT count(DISTINCT P1.user)
FROM Postings AS P1 NATURAL JOIN Postings AS P2
WHERE P1.position <> P2.position;

What will happen?

  1. It correctly counts users who posted at least twice on the same post.
     
  2. It returns an empty result because the natural join already requires the two $\text{position}$ values to be equal.
     
  3. It computes the Cartesian product of $\text{P1}$ and $\text{P2}$.
     
  4. It joins the two relations only on $\text{user}$.

1 Answer

1 1 vote

$\text{P1}$ and $\text{P2}$ are aliases of the same relation.

Therefore, they have the same attribute names:

$\text{post}$, $\text{position}$, $\text{user}$, and $\text{ptext}$.

A natural join equates all attributes having the same name.

Therefore, among other conditions, the join requires

$\text{P1.position}=\text{P2.position}$

But the $\text{WHERE}$ condition requires

$\text{P1.position}\neq\text{P2.position}$

These conditions cannot both be true.

Therefore, the query returns no matching rows.

Hence, the correct answer is B.

Voting & Accepting an answer helps improve the community
Answer:
← Previous · Go classes Linear Al...Next → · GO Classes DPP | GAT...
← Previous in category · GO Classes DPP | GAT...Next in category → · GO Classes DPP | GAT...
Position:
Show:

Related questions

2 2 votes
2 2 answers
141
141 views
GO Classes asked Sep 14
141 views
GO Classes DPP | GATE CS, DA | DBMS | Decomposition
Consider relation $R(A,B,C,D,E,F)$ with$A\to B$$A\to C$$F\to D$$F\to E$Suppose $R$ is decomposed into $R_1(A,B,C)$ and $R_2(D,E,F)$.Which statement best describes this de...
1 1 vote
1 1 answer
107
107 views
GO Classes asked Sep 14
107 views
GO Classes DPP | GATE CS, DA | DBMS | Decomposition & Redundancy
A redundant relation is decomposed appropriately to improve its logical database design.Which of the following benefits can be relied upon as a purpose of the decompositi...
1 1 vote
1 1 answer
87
87 views
GO Classes asked Sep 14
87 views
GO Classes DPP | GATE CS, DA | DBMS | Insertion - Deletion Anomaly
Consider the single relation containing $\text{Course, Teacher, Room, Hour, StudentID, Grade}$.\[\begin{array}{|c|c|c|c|c|c|}\hline\text{Course} & \text{Teacher} & \text{...
1 1 vote
1 1 answer
72
72 views
GO Classes asked Sep 14
72 views
GO Classes DPP | GATE CS, DA | DBMS | Update Anomaly
A relation contains four tuples for course $\text{CS 186}$, and every tuple stores the teacher as $\text{Hellerstein}$.Lets consider the set of data is as follows :\[\beg...