GATE Questions & Answers of Relational Model, Relational Algebra, Tuple Calculus

Consider the following relations P(X,Y,Z), Q(X,Y,T) and R(Y,V).

How many tuples will be returned by the following relational algebra query?

$ \prod\nolimits_X(\sigma_{\left(P.Y=R.Y\wedge R.V=V2\right)}(P\times R))-\prod\nolimits_X(\sigma_{\left(Q.Y=R.Y\wedge Q.T>2\right)}(Q\times R)) $

•  (A) $\style{font-family:'Times New Roman'}{\sigma_{B<5}\left(r\;\bowtie S\right)}$
•  (B) $\style{font-family:'Times New Roman'}{\sigma_{B<5}\left(r\;LOJ\;s\right)}$
•  (C) $\style{font-family:'Times New Roman'}{r\;LOJ\left(\;\sigma_{B<5}\left(s\right)\right)}$
•  (D) $\style{font-family:'Times New Roman'}{\sigma_{B<5}\left(r\right)\;LOJ\;s}$

Consider  a database that has the relation schems EMP (Empld, EmpName, DeptId), and DEPT (DeptName, DeptId). Note that the DeptId can be permited to be NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus.

$\style{font-family:'Times New Roman'}{\begin{array}{l}(\mathrm I)\;\{\mathrm t\vert\exists\mathrm u\in\mathrm{EMP}(\mathrm t\lbrack\mathrm{EmpName}\rbrack=\mathrm u\lbrack\mathrm{EmpName}\rbrack\wedge\forall\mathrm v\in\mathrm{DEPT}\;(\mathrm t\lbrack\mathrm{DeptId}\rbrack\neq\mathrm v\lbrack\mathrm{DeptId}\rbrack))\}\\(\mathrm{II})\;\{\mathrm t\vert\exists\mathrm u\in\mathrm{EMP}(\mathrm t\lbrack\mathrm{EmpName}\rbrack=\mathrm u\lbrack\mathrm{EmpName}\rbrack\wedge\exists\mathrm v\in\mathrm{DEPT}\;(\mathrm t\lbrack\mathrm{DeptId}\rbrack\neq\mathrm v\lbrack\mathrm{DeptId}\rbrack))\}\\(\mathrm{III})\;\{\mathrm t\vert\exists\mathrm u\in\mathrm{EMP}(\mathrm t\lbrack\mathrm{EmpName}\rbrack=\mathrm u\lbrack\mathrm{EmpName}\rbrack\wedge\exists\mathrm v\in\mathrm{DEPT}\;(\mathrm t\lbrack\mathrm{DeptId}\rbrack=\mathrm v\lbrack\mathrm{DeptId}\rbrack))\}\end{array}}$ 

Which of the above queries are safe?

•  (A)  (I) and (II) only
•  (B)  (I) and (III) only
•  (C)  (II) and (III) only
•  (D)  (I) ,(II) and (II)

Consider a database that has the relation schema CR (StudentName, CourseName).An instance of the schema CR is as given below.

The following query is made on the database.

$\style{font-family:'Times New Roman'}{\begin{array}{l}T1\leftarrow{\mathrm\pi}_{CourseName}\;({\mathrm\sigma}_{StudentName\mathit=\mathit'SA\mathit'}(\mathrm{CR}))\\T2\leftarrow CR\;\div T1\end{array}}$

The number of rows in T2 is__________

Which of the following is NOT a superkey in a relational schema with attributes $V,\;W,\;X,\;Y,\;Z$ and primary key $ V\;Y $?

•  (A)  $VXYZ$
•  (B)  $VWXZ$
•  (C)  $VWXY$
•  (D)  $VWXYZ$

SELECT operation in SQL is equivalent to

•  (A)  the selection operation in relational algebra
•  (B)  the selection operation in relational algebra, except that SELECT in SQL retains duplicates
•  (C)  the projection operation in relational algebra
•  (D)  the projection operation in relational algebra, except that SELECT in SQL retains duplicates

Consider two relations R₁(A,B) with the tuples(1, 5), (3, 7) and R₂(A, C) = (1, 7), (4, 9). Assume that R(A,B,C) is the full natural outer join of R₁ and R₂. Consider the following tuples of the form (A, B, C): a =(1, 5, null), b =(1, null, 7), c = (3, null, 9), d = (4, 7, null), e = (1, 5, 7), f = (3, 7, null), g = (4, null, 9). Which one of the following statements is correct?

•  (A)  R contains a, b, e, f, g but not c, d.
•  (B)  R contains all of a, b, c, d, e, f, g.
•  (C)  R contains e, f, g but not a, b.
•  (D)  R contains e but not f,g.

Consider a join (relation algebra) between relations r(R) and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size (r(R))<size(s(S)), the join will have fewer number of disk block accesses if

•  (A) relation r(R) is in the outer loop.
•  (B) relation s(S) is in the outer loop.
•  (C) join selection factor between r(R) and s(S) is more than 0.5
•  (D) join selection factor between r(R) and s(S) is less than 0.5.

What is the optimized version of the relation algebra expression ${\pi }_{A1}\left({\mathrm{\pi }}_{A2}\left({\mathrm{\sigma }}_{F1}\left({\mathrm{\sigma }}_{F2}\left(r\right)\right)\right)\right),$ where A1, A2 are sets of attributes in r with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?

•  (A) ${\pi }_{A1}\left({\mathrm{\sigma }}_{\left(F1\Lambda F2\right)}\left(r\right)\right)$
•  (B) ${\pi }_{A1}\left({\mathrm{\sigma }}_{\left(F1\vee F2\right)}\left(r\right)\right)$
•  (C) ${\pi }_{A2}\left({\mathrm{\sigma }}_{\left(F1\wedge F2\right)}\left(r\right)\right)$
•  (D) ${\pi }_{A2}\left({\mathrm{\sigma }}_{\left(F1\vee F2\right)}\left(r\right)\right)$

Consider the relational schema given below, where eId of the relation dependent is a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.

employee (empId, empName, empAge)
dependent(depId, eId, depName, depAge)
Consider the following relational algebra query:

$\style{font-family:'Courier New'}{{\mathrm\pi}_\mathrm{empId}\left(\mathrm{employee}\right)-{\mathrm\pi}_\mathrm{empId}\left(\mathrm{employee}\bowtie_{\left(\mathrm{empId}=\mathrm{eID}\right)\wedge\left(\mathrm{empAge}\leq\mathrm{depAge}\right)}\mathrm{Dependent}\right)}$

The above query evaluates to the set of empIds of employees whose age is greater than that of

•  (A) some dependent.
•  (B) all dependents.
•  (C) some of his/her dependents.
•  (D) all of his/her dependents.

Consider the following relational schema.
Students(rollno: integer, sname: string)
Courses(courseno: integer, cname: string)
Registration(rollno: integer, courseno: integer, percent: real)
Which of the following queries are equivalent to this query in English?
“Find the distinct names of all students who score more than 90% in the course numbered 107”

(I) SELECT DISTINCT S.sname
FROM Students as S, Registration as R
WHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent >90

(II) $\prod_\mathrm{sname}({\mathrm\sigma}_{\mathrm{courseno}=107\;\wedge\;\mathrm{percent}>90}(\mathrm{Registration}\;\bowtie\;\mathrm{Students})$

(III) {T | ∃S$\in$ Students, ∃ R$\in$ Registration ( S.rollno=R.rollno ∧ R.courseno=107 ∧ R.percent>90 ∧T.sname=S.sname)}

(IV) {<S_N> | ∃S_R ∃R_P ( <S_R, S_N>$\in$ Students ∧ <S_R, 107, R_P>$\in$ Registration ∧ R_P>90)}

•  (A) I, II, III and IV
•  (B) I, II and III only
•  (C) I, II and IV only
•  (D) II, III and IV only

Suppose R₁(A, B) and R₂(C, D) are two relation schemas. Let r₁ and r₂ be the corresponding relation instances. B is a foreign key that refers to C in R₂. If data in r₁ and r₂ satisfy referential integrity constraints, which of the following is ALWAYS TRUE?

•  (A) ${\Pi }_B\left(r_1\right)-{\Pi }_C\left(r_2\right)=\varnothing$
•  (B) ${\Pi }_C\left(r_2\right)-{\Pi }_B\left(r_1\right)=\varnothing$
•  (C) ${\Pi }_B\left(r_1\right)={\Pi }_C\left(r_2\right)$
•  (D) ${\Pi }_B\left(r_1\right)-{\Pi }_C\left(r_2\right)\ne \varnothing$

Consider a relational table with a single record for each registered student with the following attributes.

1. Registration_Num: Unique registration number of each registered student

2. UID: Unique identity number, unique at the national level for each citizen

3. BdnkAccount_Num: Unique account number at the bank. A student can have multiple accounts or joint accounts. This attribute stores the primary account number.

4. Name: Name of the student

 5. Hostel_Room: Room number of the hostel

Which of the following options is INCORRECT?

•  (A) BankAccount_Num is a candidate key
•  (B) Registration_Num can be a primary key
•  (C) UID is a candidate key if all students are from the same country
•  (D) If S is a superkey such that $S\cap UID$ is NULL then $S\cup UID$ is also a superkey

Consider a relational table r with sufficient number of records, having attributes A₁, A₂,……A_n and

let 1 ≤ p ≤ n. Two queries Q1 and Q2 are given below.

Q1: ${\pi }_{ A_{1......}{A}_p}\left({\sigma }_{A_{p=c}}\left(r\right)\right)$where c is a constant

Q2: ${\pi }_{ A_{1......}{A}_p}\left({\sigma }_{ c_1\le A_p\le c_2}\left(r\right)\right)$ where c₁ and c₂ 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?

•  (A) Ordered indexing will always outperform hashing for both queries
•  (B) Hashing will always outperform ordered indexing for both queries
•  (C) Hashing will outperform ordered indexing on Q1, but not on Q2
•  (D) Hashing will outperform ordered indexing on Q2, but not on Q1

Let R and S be relational schemes such that R={a,b,c} and S={c}. Now consider the following queries on the database:

I.  ${\pi }_{R-S}\left(r\right)-{\pi }_{R-S}\left({\pi }_{R-S}\left(r\right)\times S-{\pi }_{R-S,S}\left(r\right)\right)$
II. $\left\{t|t\in {\pi }_{R-S}\left(r\right)\wedge \forall u\in s\left(\exists v\in r\left(u=v\left[s\right]\wedge t=v\left[R-S\right]\right)\right)\right\}$
III. $\left\{t|t\in {\pi }_{R-S}\left(r\right)\wedge \forall v\in r\left(\exists u\in s\left(u=v\left[s\right]\wedge t=v\left[R-S\right]\right)\right)\right\}$
IV. Select R.a, R.b
     from R, S
     where R.c = S.c

Which of the above queries are equivalent?

•  (A) I and II
•  (B) I and III
•  (C) II and IV
•  (D) III and IV

Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)

Consider the following relational query on the above database:

SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN (SELECT C.sid
                                FROM Catalog C
                                WHERE C.pid NOT (SELECT P.pid
                                                            FROM Parts P
                                                            WHERE P.color<> 'blue'))

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?

•  (A) Find the names of all suppliers who have supplied a non-blue part.
•  (B) Find the names of all suppliers who have not supplied a non-blue part.
•  (C) Find the names of all suppliers who have supplied only blue parts.
•  (D) Find the names of all suppliers who have not supplied only blue parts.

Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)

Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?

•  (A) The schema is in BCNF.
•  (B) The schema is in 3NF but not in BCNF.
•  (C) The schema is in 2NF but not in 3NF.
•  (D) The schema is not in 2NF.

Which of the following tuple relational calculus expression(s) is/are equivalent to $\forall t\in r\left(P\left(t\right)\right)$?

I. $\neg \exists t\in r\left(P\left(t\right)\right)$
II.$\exists t\notin r\left(P\left(t\right)\right)$
III. $\neg \exists t\in r\left(\neg P\left(t\right)\right)$
IV. $\exists t\notin r\left(\neg P\left(t\right)\right)$

•  (A)  I only
•  (B) II only
•  (C) III only
•  (D)  III and IV only

Let R and S be two relations with the following schema
R (P,Q.R1,R2,R3)
S (P,Q,S1,S2)
Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?

I. ${\prod }_{}$_p (R $\bowtie$ S)
II. ${\prod }_{}$_p (R) $\bowtie$ ${\prod }_{}$_p (S)
III. ${\prod }_{}$_p (${\prod }_{}$_{p, Q} (R) $\cap$ ${\prod }_{}$ _{p, Q} (S))
IV. ${\prod }_{}$_p (${\prod }_{}$_p, _Q (R) - (${\prod }_{}$_{p, Q} (R) - ${\prod }_{}$ _{p, Q} (S))

•  (A) Only I and II
•  (B) Only I and III
•  (C) Only I , II and III
•  (D) Only I , III and IV

Information about a collection of students is given by the relation studInfo(studId, name, sex). The relation enroll(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?

${\prod }_{\mathrm{courseId}}\left(\left({\prod }_{\mathrm{studId}}\left({\mathrm{\sigma }}_{\mathrm{sex}="\mathrm{female}"}\left(\mathrm{studInfo}\right)\right)\times {\prod }_{\mathrm{courseId}}\left(\mathrm{enroll}\right)\right)-\mathrm{enroll}\right)$

•  (A)   Courses in which all the female students are enrolled.
•  (B)   Courses in which a proper subset of female students are enrolled.
•  (C)   Courses in which only male students are enrolled.
•  (D)   None of the above.

Consider the relation employee(name, sex, supervisorName) with name as the key. supervisorName gives the name of the supervisor of the employee under consideration. What does the following Tuple Relational Calculus query produce?

{e.name | employee(e) $\wedge$
                ($\forall$x) [$\neg$employee(x) $\vee$ x.supervisorName ≠ e.name $\vee$ x.sex = "male" ] }

•  (A) Names of employees with a male supervisor.
•  (B) Names of employees with no immediate male subordinates.
•  (C) Names of employees with no immediate female subordinates.
•  (D) Names of employees with a female supervisor.