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

Question No. 41

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?

Question No. 46

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 CA SC CB SC CC SC CA SD CB SD CC SD CD SD CD SE CA SE CB SE 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__________

Question No. 31

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

Question No. 31

SELECT operation in SQL is equivalent to

Question No. 140

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?

Question No. 140

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

Question No. 231

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?

Question No. 240

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

Question No. 35

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

Question No. 43

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?

Question No. 12

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?

Question No. 39

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?

Question No. 45

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?

Question No. 55

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?

Question No. 56

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?

Question No. 15

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

Question No. 68

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

Question No. 59

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" ] }