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

## What is the Weightage of Relational Model, Relational Algebra, Tuple Calculus in GATE Exam?

Total 21 Questions have been asked from Relational Model, Relational Algebra, Tuple Calculus topic of Databases subject in previous GATE papers. Average marks 1.76.

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

 P X Y Z X1 Y1 Z1 X1 Y1 Z2 X2 Y2 Z2 X2 Y4 Z4

 Q X Y T X2 Y1 2 X1 Y2 5 X1 Y1 6 X3 Y3 1

 R Y V Y1 V1 Y3 V2 Y2 V3 Y2 V2

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

Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query
$\style{font-family:'Times New Roman'}{\mathrm Q:\;r\;\bowtie\left(\sigma_{B<5}\left(S\right)\right)}$
Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.

Which one of the following queries is NOT equivalent to Q?

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?

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

 CR StudentName CourseName SA CA SA CB SA CC SB CB SB CC SC CA SC CB SC CC SD CA SD CB SD CC SD CD SE CD SE CA SE CB SF CA SF CB SF CC

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

SELECT operation in SQL is equivalent to

Consider two relations R1(A,B) with the tuples(1, 5), (3, 7) and R2(A, C) = (1, 7), (4, 9). Assume that R(A,B,C) is the full natural outer join of R1 and R2. Consider the following tuples of the form (A, B, C): a =(1, 5, null), =(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?

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

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?

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

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) {<SN> | ∃SR ∃RP ( <SR, SN>$\in$ Students ∧ <SR, 107, RP>$\in$ Registration ∧ RP>90)}

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?

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?

Consider a relational table r with sufficient number of records, having attributes A1, A2,……An and

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

Q1: where c is a constant

Q2: where c1 and c2 are constants

The database can be configured to do ordered indexing on Ap or hashing on Ap. Which of the following statements is TRUE?

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)×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?

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?

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?

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

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

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))

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)×{\prod }_{\mathrm{courseId}}\left(\mathrm{enroll}\right)\right)-\mathrm{enroll}\right)$

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