3.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
edited | 3.5k views
+2

beautiful qestion to apply it all

+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?
+2

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 \}$

+1

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?
+1

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

0
Please someone explain the IV query.

Four queries given in SQL, RA, TRC and DRC in four statements respectively retrieve the required information.
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 ^_^
+5

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

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

+1

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

+3

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.

1