in Databases edited by
14,915 views
52 votes
52 votes

Consider the following relational schema.

  • Students(rollno: integer, sname: string)
  • Courses(courseno: integer, cname: string)
  • Registration(rollno: integer, courseno: integer, percent: real)

Which of the following queries are equivalent to this query in English?

“Find the distinct names of all students who score more than $90\%$ in the course numbered 107”

  1. SELECT DISTINCT S.sname FROM Students as S, Registration
        as R WHERE 
        R.rollno=S.rollno AND R.courseno=107 AND R.percent >90
    
  2. $∏_{sname}(σ_{courseno=107 ∧ percent > 90} (Registration ⋈ Students))$
  3. $\{T \mid ∃S \in Students, ∃R \in Registration ( S.rollno=R.rollno$
    $∧ R.courseno=107 ∧ R.percent>90 ∧T.sname=S.sname) \}$
  4. $\left\{ \langle S_N\rangle \mid ∃S_R∃R_P (\langle S_R,S_N\rangle ∈Students ∧ \\ \langle S_R,107,R_P\rangle ∈Registration ∧ R_P>90) \right\}$
  1. I, II, III and IV
  2. I, II and III only
  3. I, II and IV only
  4. II, III and IV only
in Databases edited by
by
14.9k views

4 Comments

Why not T.sname in 3rd option? It is causing hell a lot of confusion. In every other TRC question the attribute to be printed is given in LHS of |.

2
2
Please someone explain the IV query.
1
1
TRC & DRC removes duplicate result same as the DISTINCT keyword in SQL.
5
5

2 Answers

41 votes
41 votes
Best answer
Answer: A

Four queries given in SQL, RA, TRC and DRC in four statements respectively retrieve the required information.
selected by

20 Comments

In the third option, No range relation for T is specified ?

e.g. A simple tuple relational calculus expression is :

{ t | Relation_Name(t) ^ Condition(t) } where

"Relation_Name" is the range of the relation and "Condition" is any logical expression.

Please, let me know where I am wrong? Thanks ^_^
3
3

but how is the tuple caculus query correct 
it prints the whole tupple but it is asked to print only student name to
it should be 

  {T.sname∣∃S∈Students,∃R∈Registration(S.rollno=R.rollno∧R.courseno=107∧R.percent>90∧T.sname=S.sname)}

7
7

@hkara That's not how tuple relation calculus defines tuple selection. You can see here:

https://en.wikipedia.org/wiki/Tuple_relational_calculus

1
1
how is T in 3rd query gives only T.sname ? why is it not printing the entire row?
3
3
because t is a tupple variable with only one attribute sname
3
3
@Arjun sir,

Here there is nothing like there exist T that belongs to student, so how in first place is T.sname= S.name comparison occuring????
0
0

sname(σcourseno=107∧percent>90(RegistrationStudents)) 

Here there is no joining condition like Registeration.rollno=Students.rollno is given

0
0
"(Registration⋈Students) ", It is NATURAL JOIN, an implicit equijoin condition is specified for each pair of attributes with same name in both relations so there is no need to apply separate joining condition
1
1
How is the 3rd query  correct?.. T is a tuple variable, but it is not defined that on which table it ranges...?

And also there are no brackets between the and conditions so how will it decide precedence?  

Please do throw some light.. :)
0
0
@Arjun sir. TRC DRC results are distinct always?
3
3
how drc is working explain please
0
0

@rahul sharma 5

yes , TRC and DRC returns distinct results . https://www.geeksforgeeks.org/gate-gate-cs-2013-question-35/

If you find it is wrong please reply , i also had same doubt

2
2

@mehul vaidya

Do TRC and DRC return distinct results (here in case two students with same name & course 107 & 90 % and different roll nos )...above link is not so useful..

 

1
1

I think this will answer all the above questions -

1. How is T in 3rd query gives only T.sname ? why is it not printing the entire row?
2. Why range relation is not specified ?

Reference 1 - https://edux.fit.cvut.cz/archive/B121/BI-DBS/_media/en/lectures/chapt04.pdf
Reference 2 - here

. TRC DRC results are distinct always?

Yes.
The result of a TRC query is a relation. A relation is defined as a "set" means relations don't contain duplicate elements.

23
23
Thanks man! It helped a lot.
0
0
tuple calculus query must print the whole row,it must be incorrect
1
1
For people having problem that why option 3 is corect as it is printing whole tuple... actually it is not printing whole tuple  ,T.sname=S.sname will print the student name from student table  ....(T is a free variable )
1
1
Thanks a lot for this !
0
0
T is a free variable but to print only student names we should have T.Sname on the left hand side of the | instead of T.

If T itself returns student names then T.sname dose not make any sense. It is like writing sname.sname.
1
1
Got it!
0
0
16 votes
16 votes
Option A:

This is a SQL query expression. It first perform a cross product of Students 
and Registration, then WHERE clause only keeps those rows in the cross product 
set where the student is registered for course no 107, and percentage is > 90. 
Then select distinct statement gives the distinct names of those students as the 
result set.

Option B:

This is a relational algebra expression. It first perform a NATURAL JOIN 
of Students and Registration (NATURAL JOIN implicitly joins on the basis 
of common attribute, which here is rollno ), then the select operation( sigma) 
keeps only those rows where the student is registered for courseno 107,
and percentage is > 90. And then the projection operation (pi) projects only 
distinct student names from the set.

Note: Projection operation (pi) always gives the distinct result.
Option C:

This is a Tuple Relational Calculus (TRC) language expression,
It is not a procedural language (i.e. it only tells “what to do”, 
not “how to do”). It just represents a declarative mathematical 
expression.

Here T is a Tuple variable.

From left to right, it can be read like this, “It is a set of
tuples T, where, there exists a tuple S in Relation Students, and 
there exist a tuple R in relation Registration, such that 
S.rollno = R.rollno AND R.couseno = 107 AND R.percent > 90 AND 
T.sname = S.sname”. And the schema of this result is (sname), i.e. each 
tuple T will contain only student name, because only T.sname has been defined 
in the expression.

As TRC is a mathematical expression, hence it is expected to give only distinct result set.
Option D:

This is a Domain Relational Calculus (DRC) language expression. 
This is also not procedural. Here SN is a Domain Variable. It can be read 
from left to right like this “The set of domain variable SN, where, 
there exist a domain variable SR , and a domain variable Rp, such that, 
SN and SR domain variables is in relation Students and SR,107,RP is a domain
variables set in relation Registration, AND RP > 90 “

Above, SN represents sname domain attribute in Students relation, SR 
represents rollno domain attribute in Students relation, and RP represents 
percentage domain attribute in Registration relation.
The schema for the result set is (SN), i.e. only student name.

As DRC is a mathematical expression, hence it is expected to
give only distinct result set.

 

So clearly all options are correct.

1 comment

Geeks for geeks 😂😂
1
1
Answer:

Related questions