Consider the relations R(A, B) and S(B, C) and the following four relational algebra queries over R and S : I. ΠA, B (R ⋈ S) II. R ⋈ ΠB(S) III. R ∩ (ΠA(R) × ΠB(S)) IV. ΠA,R.B (R × S) where R⋅B refers to the column B in table R. One can determine that :
I, II and III are the same query.
R(A, B) and S(B, C) share only attribute B, so the natural join R ⋈ S matches tuples on B. Each expression is reduced to see which tuples of R it returns.
Step 1 — Query I: \(\Pi_{A,B}(R \bowtie S)\) keeps the (A, B) part of every R tuple that has a matching B value in S. This is the semi-join of R with S: the R tuples whose B appears in \(\Pi_B(S)\).
Step 2 — Query II: \(R \bowtie \Pi_B(S)\) joins R with the set of B values of S on B. The result has schema (A, B) and contains exactly the R tuples whose B appears in S, the same set as Query I.
Step 3 — Query III: \(\Pi_A(R) \times \Pi_B(S)\) holds every pair (a, b) with a an A value of R and b a B value of S. Every R tuple already has its A value in \(\Pi_A(R)\), so intersecting with R keeps exactly the R tuples whose B appears in S, again the same set.
Step 4 — Query IV: \(\Pi_{A,R.B}(R \times S)\) pairs every R tuple with every S tuple without any condition, so whenever S is non-empty it returns all of R, including R tuples whose B value never occurs in S. In general it differs from the other three.
Thus Queries I, II and III always give the same result, while IV does not; every combination that includes IV is wrong.
I, II and III are the same query. option is correct.
Similar Questions
Consider the following statements :
I. Re-construction operation used in mixed fragmentation satisfies commutative rule.
II. Re-construction operation used in vertical fragmentation satisfies commutative rule
Which of the following is correct ?
Which of the following is true ?
I. Implementation of self-join is possible in SQL with table alias.
II. Outer-join operation is basic operation in relational algebra.
III. Natural join and outer join operations are equivalent.
Division operation is ideally suited to handle queries of the type :
Consider the following three tables R, S and T. In this question, all the join operations are natural joins (\(\bowtie\)). (\(\pi\)) is the projection operation of a relation :
R S T A B B C A C 1 2 6 2 7 1 3 2 2 4 1 2 5 6 8 1 9 3 7 8 8 3 5 4 9 8 2 5 3 5 Possible answer tables for this question are also given as below :
(a)
A B C 1 2 4 1 2 5 3 2 4 3 2 5 5 6 2 7 8 1 7 8 3 9 8 1 9 8 3 (b)
A B C 1 2 2 3 2 5 5 6 4 7 8 1 9 8 3 (c)
A B C 1 6 2 3 2 5 5 2 4 7 8 1 9 8 3 (d)
A B C 3 2 5 7 8 1 9 8 3 What is the resulting table of \(\pi_{A,B}(R \bowtie T) \bowtie \pi_{B,C}(S \bowtie T)\) ?
A ________ operation combines the rows of the two relations and outputs a new relation that has both input relations’ rows in it.
Given below are the two statements:
Statement I : Any retrieval request that is specified in the basic relational algebra can also be specified in relational calculus.
Statement II : Any retrieval request that is specified in the relational calculus can also be specified in basic relational algebra.
In the light of the above statements, choose the correct answer from the options given below:
Important Questions from Relational Algebra
Consider the given relations $X$, $Y$ and $Z$. The relation $X$ has three columns $P$, $Q$ and $R$. The relation $Y$ has three columns $P$, $Q$ and $S$. The relation $Z$ has two columns $P$ and $T$.
Table X
P Q R P1 Q1 R1 P2 Q2 R2 P3 Q3 R2 Table Y
P Q S P1 Q1 2 P1 Q2 5 P2 Q1 6 P3 Q3 1 Table Z
P T P1 T1 P3 T2 P4 T3 P4 NULL Consider the relational algebra expression $$ \Pi_{P, R, S} \left[ \left( \sigma_{(Q = Q3 \lor R = R2)} [X \bowtie Y] \right) \bowtie \left( \sigma_{(S > 1)} [Y \bowtie Z] \right) \right] $$ where $\bowtie$ denotes natural join operation.
Which of the following options is the correct output for the given expression?
Consider a database that includes the following relations:
Defender($name, rating, side, goals$)
Forward($name, rating, assists, goals$)
Team($name, club, price$)
Which ONE of the following relational algebra expressions checks that every name occurring in Team appears in either Defender or Forward, where $\phi$ denotes the empty set?
Consider the following three relations:
Car (model, year, serial, color)
Make (maker, model)
Own (owner, serial)
A tuple in Car represents a specific car of a given model, made in a given year, with a serial number and a color. A tuple in Make specifies that a maker company makes cars of a certain model. A tuple in Own specifies that an owner owns the car with a given serial number. Keys are underlined; (owner, serial) together form key for Own. ($\bowtie$ denotes natural join)
$ \pi_{\text{owner}} (\text{Own} \bowtie (\sigma_{\text{color}=\text{"red"}} (\text{Car} \bowtie (\sigma_{\text{maker}=\text{“ABC”}} \text{Make})))) $Consider the following tables, Loan and Borrower of a bank.
Loan Borrower loan_num branch_name amount customer_name loan_num L11 Banjara Hills 90000 Anand L11 L14 Kondapur 50000 Karteek L11 L15 SR Nagar 40000 Ankita L15 L22 SR Nagar 25000 Gopal L19 L23 Balanagar 80000 Karteek L22 L25 Kondapur 70000 Karteek L23 L19 SR Nagar 65000 Sunil L23 Sunil L25
Query: $\pi_{\text{branch\_name}, \text{customer\_name}}(\mathbf{Loan} \bowtie \mathbf{Borrower}) \div \pi_{\text{branch\_name}}(\mathbf{Loan})$
where $\bowtie$ denotes natural join.
The number of tuples returned by the above relational algebra query is _______
(Answer in integer)- Consider a relational database schema with two relations $R(P, Q)$ and $S(X, Y)$.
Let $E = \{\langle u \rangle \mid \exists v\ \exists w\ \langle u, v \rangle \in R\ \land\ \langle v, w \rangle \in S\}$ be a tuple relational calculus expression.
Which one of the following relational algebraic expressions is equivalent to $E$ ?
