Register

GATE IT 2008 | Question: 75

• edited by
26,399 views
70 70 votes

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?

  1. The empty set
  2. schools with more than $35\%$ of its students enrolled in some exam or the other
  3. schools with a pass percentage above $35\%$ over all exams taken together
  4. schools with a pass percentage above $35\%$ over each exam

10 Answers

Best answer
36 36 votes

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

• selected by
48 48 votes

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.

16 16 votes

{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

• edited by
1 flag:
✌ Edit necessary (js__)
8 8 votes

 

The outer expression is:

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

  • This indicates that the query returns a set of tuples $t$ containing a single attribute: school-id.

  • Thus, the query returns schools that satisfy a certain condition.

 

The main filtering condition on each school-id $t$ is:

$$|A| \div |B| * 100 > 35$$

Where $A$ and $B$ are two distinct sets of tuples derived from the Enrolment table for that specific school $t$. Let's analyze what sets $A$ and $B$ represent.

 

Set $B$ (The Denominator)

$$B = \{x \mid x \in \text{Enrolment} \wedge x.\text{school-id} = t\}$$

  • This set contains all the enrolment records belonging to school $t$.

  • Meaning of $|B|$: The total number of exams taken (or enrolled in) by all students belonging to school $t$.

 

Set $A$ (The Numerator)

$$A = \{x \mid x \in \text{Enrolment} \wedge x.\text{school-id} = t \wedge (\exists B \in \text{ExamResult} \quad B.\text{erollno} = x.\text{erollno} \wedge B.\text{examname} = x.\text{examname} \wedge B.\text{marks} > 35)\}$$

  • This set contains all the enrolment records belonging to school $t$ where the student actually scored more than 35 marks in that specific exam.

  • Meaning of $|A|$: The total number of exams passed (scoring $> 35$) by students from school $t$.

 

When we plug the meanings of $|A|$ and $|B|$ back into the main condition:

$$\frac{\text{Total exams passed by students of school } t}{\text{Total exams taken/enrolled by students of school } t} \times 100 > 35$$

  • This calculates the overall pass percentage for school $t$ across all exams taken by its students combined.

  • The query checks if this overall pass percentage is strictly greater than 35%.

 

Correct Ans: C

C. Schools with a pass percentage above 35% over all exams taken together This matches our calculated condition exactly.

 

5 5 votes
query having division with {x | x ∊ Enrolment ^ x.school-id = t}| * 100 > 35}.
school with enrollment % is 35 or above ..
• edited by
2 2 votes
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.
Page:
Voting & Accepting an answer helps improve the community
Answer:
← Previous · GATE IT 2008 | Quest...Next → · GATE IT 2008 | Quest...
← Previous in category · GATE IT 2008 | Quest...Next in category → · GATE IT 2007 | Quest...
Position:
Show:

Related questions

68 68 votes
7 answers 7 answers
29.4k
29.4k views
Ishrat Jahan asked Oct 29, 2014
29,355 views
GATE IT 2008 | Question: 74
Consider the following relational schema:$\text{Student} (\underline{\text{school-id}, \text{sch-roll-no}}, \text{sname}, \text{saddress})$$\text{School} (\underline{\tex...
65 65 votes
10 answers 10 answers
27.5k
27.5k views
Ishrat Jahan asked Oct 28, 2014
27,546 views
GATE IT 2008 | Question: 63
Consider the following three schedules of transactions T1, T2 and T3. [Notation: In the following NYO represents the action Y (R for read, W for write) performed by trans...
41 41 votes
7 answers 7 answers
15.3k
15.3k views
Ishrat Jahan asked Oct 28, 2014
15,346 views
GATE IT 2008 | Question: 62
Let $R (A, B, C, D, E, P, G)$ be a relational schema in which the following functional depen­dencies are known to hold: $AB \to CD, DE \to P, C \to E, P \to C$ and $B \to...
140 140 votes
5 answers 5 answers
51.2k
51.2k views
Ishrat Jahan asked Oct 28, 2014
51,224 views
GATE IT 2008 | Question: 61
Let $R (A, B, C, D)$ be a relational schema with the following functional dependencies :$A → B$, $B → C$, $C → D$ and $D → B$. The decomposition of $R$ into $(A, B), (B, ...