edited by
15,083 views
47 votes
47 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
edited by

5 Answers

Best answer
24 votes
24 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
36 votes
36 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.

15 votes
15 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
5 votes
5 votes
query having division with {x | x ∊ Enrolment ^ x.school-id = t}| * 100 > 35}.
school with enrollment % is 35 or above ..
edited by
Answer:

Related questions

52 votes
52 votes
5 answers
1
Ishrat Jahan asked Oct 29, 2014
18,039 views
Consider the following relational schema:$\text{Student} (\underline{\text{school-id}, \text{sch-roll-no}}, \text{sname}, \text{saddress})$$\text{School} (\underline{\tex...