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

Query is:

{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)} / | {x | x ∊ Enrolment ∧∧ x.school-id = t}| * 100 > 35}.

Too long. Let's break it:

Let A = {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)}

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

So, query becomes: A / B * 100 > 35. Now, should I assume this like: $\frac{A}{(B*100)}>35$ or $\frac{A}{(B*100)>35}$. It is not clear in question. So, lets assume:  $\frac{A}{(B*100)}>35$.

Now, A will give tuple for students with marks>35. Now, marks greater than 35 could be 35 or 3355 or more.

Dividing by B will bring A in limits of t such that x should not range outside t.

now denominator has 100 too. This will work as a percentage. Now this is done overall (for every exam)

>35 will ensure that the output number in every tuple is greater than 35.

Hence, Option C is the most accurate one.