GATE IT 2008 | Question: 75

Question

Consider the following relational schema:

• $\text{Student} (\underline{\text{school-id}, \text{sch-roll-no}}, \text{sname}, \text{saddress})$
• $\text{School} (\underline{\text{school-id}}, \text{sch-name}, \text{sch-address}, \text{sch-phone})$
• $\text{Enrolment}(\underline{\text{school-id}, \text{sch-roll-no}}, \text{erollno}, \text{examname})$
• $\text{ExamResult}(\underline{\text{erollno}, \text{examname}}, \text{marks})$

Consider the following tuple relational calculus query.

$\left\{t \mid \exists E \in \text{Enrolment }\;\; t = E.\text{school-id} \wedge \left | \{x \mid x \in \text{Enrolment} \wedge x.\text{school-id} = t \wedge (\exists B \in \text{ExamResult} \;\;B.\text{erollno} = x.\text{erollno} \wedge  B.\text{examname} = x.\text{examname} \wedge  B.\text{marks} > 35)\}\right | \div \left | \{x \mid x \in \text{Enrolment} \wedge x.\text{school-id} = t\}\right | * 100 > 35\right\}$

If a student needs to score more than 35 marks to pass an exam, what does the query return?

• A. The empty set
• B. schools with more than $35\%$ of its students enrolled in some exam or the other
• C. schools with a pass percentage above $35\%$ over all exams taken together
• D. schools with a pass percentage above $35\%$ over each exam

Comments

Take it as an assignment. Practice it and share your method, I will tell you will it satisfy option d or not.

---

Before looking at the answer, please note that:

1. $\div$ operator is simple mathematical division, not to be confused with “Division operator in Relational algebra”
2. $| \{query\} |$ indicates the number of tuples returned while executing that query. 

 

---

@madhes23 you are right

Answer 1

$t \mid \exists E \in \text{Enrolment}\; t = E.\text{school-id}$

Returns school-ids from Enrolment table SUCH THAT

• $| \{x \mid x \in \text{Enrolment} \wedge x.\text{school-id} = t \wedge (\exists B \in \text{ExamResult}\;\; B.\text{erollno} = x.\text{erollno} \wedge  B.\text{examname} = x.\text{examname} ∧  B.\text{marks} > 35)\}| \div$
• the number of student enrolments from the school for exams with marks > 35 divides
  • $|\{x \mid x \in \text{Enrolment} \wedge x.\text{school-id} = t\}|$ 
  • total number of student enrolments from the school
    • $* 100 > 35$
  • percentage of student enrolments with mark > 35 is > 35

Since to pass an exam $>35$ mark is needed, this means selecting the school-ids where the pass percentage of students across all the exams taken together is $> 35.$

Correct Answer: C.

Comments

In the explaination in place of student number it will be school no.

---

Kind of confusing here . What if a student scores passing mark in one exam and does not pass in another exam. So will that be considered as passed or not? Means a student taking 2 exams and he is passing in 1 and that is getting divided against unique school ids to find the pass percentage at the end . Wouldn't it be divided by the total no of exams taken by all the students (not school ids)

---

the query seems to be more in line with "schools with percentage of students passing atleast one exam > 35"
correct me if im wrong

Answer 2

t | ∃ E ∈ Enrollment t = E.school-id

select a school-id from Enrollment table

 

Let | {x | x ∈ Enrollment _^ x.school-id = t ∧ (∃ B ∊ ExamResult B.erollno = x.erollno ∧∧  B.examname = x.examname ∧∧  B.marks > 35)}| be A

gives count of enrollments for the selected school-id with exam result having marks > 35 (pass criteria)

 

 Let | {x | x ∊ Enrollment ∧ x.school-id = t}|  be B

gives count of enrollments for the selected school-id

 

So, query selects those school-id with (A/B)*100 > 35. Which in plain english means school-id with pass percentage in all exams taken together > 35%.

So ans should be C.

Answer 3

{t | ∃ E ∊ Enrolment t = E.school-id _^
        | {x | x ∊ Enrolment _^ x.school-id = t _^                      

(∃ B ∊ ExamResult B.erollno = x.erollno _^  B.examname = x.examname _^ B.marks > 35)}

In this first query  it is picking up tuple of the student who Enroll in some exam and who got >35 marks in that exam

{x | x ∊ Enrolment _^ x.school-id = t}| * $100 > 35$}

in second part of the query t is tuples of first query and x is their enrollment and enrollment $>35$%

So, The query returns the tuples where $35$% students enrolled  and all of them got $>35$ marks in some exam

B) division will pick $35$% enrollment of the student in some exams

Comments

As per the question, we are asked to assume that students need more than 35 marks to pass in an examination.

With this in mind, isn't option(c) more appropriate?

---

what is the answer please elaborate correctly

---

What if hypothetically, all the schools in which 35% students enrolled and all of them got less than 35 marks? From option B, those schools should be printed in output, but should they actually be printed?

Answer 4

query having division with {x | x ∊ Enrolment ^ x.school-id = t}| * 100 > 35}.
school with enrollment % is 35 or above ..

Comments

what is x in the given question . plz. make it clear

---

How this " * 100 > 35" is giving 35% . I'm not getting it.Plz explain.