Relational Algebra
Q11.
Suppose R1(A, B) and R2(C, D) are two relation schemas. Let r1 and r2 be the corresponding relation instances. B is a foreign key that refers to C in R2. If data in r1 and r2 satisfy referential integrity constraints, which of the following is ALWAYS TRUE?Q12.
Consider the following relations A, B and C: How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A\cupB is the same as that of A. (A\cup B)\bowtie _{A.Id \gt 40 \vee C.Id \lt 15}CQ13.
Consider the relational schema given below, where eId of the relation dependentis a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation. The above query evaluates to the set of empIds of employees whose age is greater than that ofQ14.
Let R and S be two relations with the following schema R (\underline{P},\underline{Q},R1,R2,R3) S (\underline{P},\underline{Q},S1,S2) Where {P, Q} is the key for both schemas. Which of the following queries are equivalent? I. \Pi _{P}(R\Join S) II. \Pi _{p}(R)\Join \Pi _{P}(S) III. \Pi _{P}(\Pi _{P,Q}(R)\cap \Pi _{P,Q}(S)) IV. \Pi _{P}(\Pi _{P,Q}(R)-(\Pi _{P,Q}(R)-\Pi _{P,Q}(S)))Q15.
Consider a relational table r with sufficient number of records, having attributes A_{1}, A_{2},..., A_{n} and let 1\leq p\leq n . Two queries Q1 and Q2 are given below. Q1:\pi _{A_{1}....A_{n}}(\sigma _{A_{p}=c}(r)) where c is a constant Q2:\pi _{A1....A_{n}}(\sigma _{c_{1}\leq A_{p}\leq c_{2}}(r)) where c_{1} and c_{2} are constants The database can be configured to do ordered indexing on A_{p} or hashing on A_{p}. Which of the following statements is TRUE?Q17.
Consider the following relational schema:Student(school-id,sch-roll-no,sname,saddress) School(school-id,sch-name,sch-address,sch-phone) Enrolment(school-id,sch-roll-no,erollno,examname) ExamResult(erollno,examname,marks) Consider the following tuple relational calculus query. \begin{array}{l} \{t \mid \exists E \in \text { Enrolment } t=E \text { .school-id } \\ \wedge \mid\{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)\}|\div|\{x \mid x \in \text { Enrolment } \wedge x . \text { school-id }=t\} \mid \\ * 100>35\} \end{array}If a student needs to score more than 35 marks to pass an exam, what does the query return?Q18.
The following functional dependencies hold for relations R(A, B, C) and S(B, D, E) B \rightarrowA, A \rightarrowC The relation R contains 200tuples and the relation S contains 100tuples. What is the maximum number of tuples possible in the natural join R\Join S?Q19.
Consider the following relation schemas : b-Schema = (b-name, b-city, assets) a-Schema = (a-num, b-name, bal) d-Schema = (c-name, a-number) Let branch, account and depositor be respectively instances of the above schemas. Assume that account and depositor relations are much bigger than the branch relation. Consider the following query: \Pi _{c-name}(\sigma _{b-city="Agra" \wedge bal \lt 0} (branch \Join (account \Join depositor))) Which one of the following queries is the most efficient version of the above query ?Q20.
Information about a collection of students is given by the relation studinfo(\underline{studId}, name, sex). The relation enroll(\underline{studId}, courseId) gives which student has enrolled for (or taken) what course(s). Assume that every course is taken by at least one male and at least one female student. What does the following relational algebra expression represent?