The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+24 votes
2.5k views

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
asked in Databases by Veteran (355k points)
edited by | 2.5k views
+2

beautiful qestion to apply it all yes

+1
@Arjun Sir please help. There is no attribute name (T.attrname) given in left side of '|' in choice 3. Then how is it correct?
0
Hello Arjun Sir, Can u please help me to understand , we have not included joining condition in Option 2 (Relational Algebra) , so it can be correct?
+1

TRC and DRC -> https://www.w3schools.in/dbms/relational-calculus/

I think in third part. $T∈Students$ is missing. Ideally it should be -

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

Refer - https://gateoverflow.in/1331/gate2009-45

0

it is not necessary according to this image

0
In DRC, for all the attributes in a table the variables shld b mentioned right

ex: student(a,b,c,d)

but in 4th option only required var is mentioned!

in this case how the system identifies a var is assigned to a particular attribute in a table?
0

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 |.

1 Answer

+23 votes
Best answer
Answer: A

Four queries given in SQL, RA, TRC and DRC in four statements respectively retrieve the required information.
answered by Boss (34k points)
selected by
+1
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 ^_^
+2

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)}

0

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

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

+1
how is T in 3rd query gives only T.sname ? why is it not printing the entire row?
0
because t is a tupple variable with only one attribute sname
0
@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

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

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

+1
"(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
0
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
@Arjun sir. TRC DRC results are distinct always?
0
how drc is working explain please
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

Answer:

Related questions



Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

37,939 questions
45,453 answers
131,190 comments
48,203 users