1. Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values.

F = {CH -> G, A -> BC, B -> CFH, E -> A, F -> EG} is a set of functional
dependencies (FDs) so that F+ is exactly the set of FDs that hold for R. How many
candidate keys does the relation R have?
a) 3
b) 4
c) 5
d) 6

2. Which of the following statements are TRUE about an SQL query? P : An SQL query can
contain a HAVING clause even if it does not have a GROUP BY clause Q : An SQL query can
contain a HAVING clause only if it has a GROUP BY clause R : All attributes used in the
GROUP BY clause must appear in the SELECT clause S : Not all attributes used in the GROUP
BY clause need to appear in the SELECT clause
a) P and R
b) P and S
c) Q and R
d) Q and S
3.

Table A

Id Name Age

----------------

12 Arun 60

15 Shreya 24

99 Rohit 11

Table B

Id Name Age

----------------

15 Shreya 24

25 Hari 40

98 Rohit 20
99 Rohit 11

Table C

Id Phone Area

-----------------

10 2200 02

99 2100 01

Consider the above tables A, B and C. How many tuples does the result of the following SQL
query contains?

SELECT A.id

FROM A

WHERE A.age > ALL (SELECT B.age

FROM B

WHERE B. name = "arun")

a) 4
b) 3
c) 0
d) 1

4. Database table by name Loan_Records is given below.

Borrower Bank_Manager Loan_Amount

Ramesh Sunderajan 10000.00

Suresh Ramgopal 5000.00

Mahesh Sunderajan 7000.00

What is the output of the following SQL query?

SELECT Count(*)

FROM ( ( SELECT Borrower, Bank_Manager

FROM Loan_Records) AS S

NATURAL JOIN ( SELECT Bank_Manager, Loan_Amount

 FROM Loan_Records) AS T );

a) 3
b) 9
c) 5
d) 6

5. 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 IN (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.
6. Given the following statements:

S1: A foreign key declaration can always

be replaced by an equivalent check

assertion in SQL.

S2: Given the table R(a,b,c) where a and

b together form the primary key, the

 following is a valid table definition.

CREATE TABLE S (

a INTEGER,

d INTEGER,

e INTEGER,

PRIMARY KEY (d),

FOREIGN KEY (a) references R)

Which one of the following statements is CORRECT?

a) S1 is TRUE and S2 is FALSE.

b) Both S1 and S2 are TRUE.
c) S1 is FALSE and S2 is TRUE.
d) Both S1 and S2 are FALSE.

7. Consider the following relational schema:

employee(empId, empName, empDept)

customer(custId, custName, salesRepId, rating)

salesRepId is a foreign key referring to empId of the employee relation. Assume that each
employee makes a sale to at least one customer. What does the following query return?

SELECT empName

FROM employee E

WHERE NOT EXISTS ( SELECT custId

FROM customer C

WHERE C.salesRepId = E.empId

AND C.rating <> `GOOD`);

a) Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
b) Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
c) Names of all the employees with none of their customers having a ‘GOOD’ rating.
d) Names of all the employees with all their customers having a ‘GOOD’ rating.

8. The statement that is executed automatically by the system as a side effect of the
modification of the database is
a) backup
b) assertion
 c) recovery
d) trigger

9. Consider the relation "enrolled(student, course)" in which (student, course) is the primary
key, and the relation "paid(student, amount)" where student is the primary key. Assume no
null values and no foreign keys or integrity constraints. Given the following four queries:

Query1: select student from enrolled where

student in (select student from paid)

Query2: select student from paid where

student in (select student from enrolled)

Query3: select E.student from enrolled E, paid P

where E.student = P.student

Query4: select student from paid where exists

(select * from enrolled where enrolled.student

= paid.student)

Which one of the following statements is correct?

a) All queries return identical row sets for any database

b) Query2 and Query4 return identical row sets for all databases but there exist databases
for which Query1 and Query2 return different row sets.
c) There exist databases for which Query3 returns strictly fewer rows than Query2
d) There exist databases for which Query4 will encounter an integrity violation at runtime.

10. Consider the following relation schema pertaining to a students database:

Student (rollno, name, address)

Enroll (rollno, courseno, coursename)

where the primary keys are shown underlined. The number of tuples in the Student and Enroll
tables are 120 and 8 respectively. What are the maximum and minimum number of tuples that
can be present in (Student * Enroll), where '*' denotes natural join ?

a) 8, 8
b) 120, 8
c) 960, 8
d) 960, 120
11. The employee information in a company is stored in the relation

Employee (name, sex, salary, deptName)

Consider the following SQL query

select deptName

from Employee

where sex = 'M'

group by deptName

having avg (salary) > (select avg (salary) from Employee)

It returns the names of the department in which

a) the average salary is more than the average salary in the company
b) the average salary of male employees is more than the average salary of all male
employees in the company
c) the average salary of male employees is more than the average salary of employees in
the same department
d) the average salary of male employees is more than the average salary in the company

12. 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

13. Database applications were built directly on top of file system to overcome the following
drawbacks of using file-systems: (i) Data redundancy and inconsistency (ii) Difficulty in
accessing Data (iii) Data isolation (iv) Integrity problems
a) (i)
b) (i) and (iv)
c) (i), (ii) and (iii)
d) (i), (ii), (iii) and (iv)

14. Which of the following statements is FALSE about weak entity set?
a) Weak entities can be deleted automatically when their strong entity is deleted.
b) Weak entity set avoids the data duplication and consequent possible inconsistencies
caused by duplicating the key of the strong entity.
c) A weak entity set has no primary keys unless attributes of the strong entity set on which
it depends are included
d) Tuples in a weak entity set are not partitioned according to their relationship with tuples
in a strong entity set.
15. The Relation Vendor Order (V_no, V_ord_no, V_name, Qty_sup, unit_price) is in 2NF
because:
a) Non_key attribute V_name is dependent on V_no which is part of composite key
b) Non_key attribute V_name is dependent on Qty_sup
c) Key attribute Qty_sup is dependent on primary_key unit price
d) Key attribute V_ord_no is dependent on primary_key unit price