Advanced DBMS Sheet With Sloution_2022

GATE Computer Science & IT
ADVANCED
DATABASE
MANAGEMENT
SYSTEM
Practice Questions
Booklet
ANALYSIS OF DATABASE MANAGEMENT SYSTEM IN GATE PAPER
Years Marks
2015 6
2016 5
2017 8
2018 6
2019 4
2020 7
2021Set-1 10
2021Set-2 7
DATABASE MANAGEMENT SYSTEM GATE SYLLABUS

 Overview: Introduction of Relational Database Management System concepts.
 SQL: Overview of the structured query language, Basic Structure of SQL Queries, Set
Operations, Null values, Aggregate Functions, Nested Sub Queries, Views, Join
Expressions, Insert ,Delete, Update Statements in SQL etc.
 Other Relational Languages: Tuple Relational Calculus, Domain Relational Calculus etc.
 Normalization: Functional Dependencies, Multivalued Dependencies Decomposition
Using Functional Dependencies, Normal Form, 1NF 2NF, 3NF, BCNF, 4NF, etc.
 Hashing and Indexing: Basic Concepts, Ordered Indices, B, B+Tree, Static Hashing,
Dynamic Hashing etc.
 Transaction and Serializability: Transaction Concept, Transaction State, ACID Property,
Recoverability, Concurrency Control etc.
 Entity Relationship Model: Entity Relationship Diagrams, Constraints, Entity Types,
Entity Sets, Attributes and keys, Relational Database Design Using ER-to-Relational
Mapping etc.
DBMS REFERENCE BOOKS

 Fundamentals of Database Systems by Shamkant B. Navathe.
 Database System Concepts by Henry F. Korth.
ADVANCED DATABASE MANAGEMENT SYSTEM
Data for the next six questions.
Consider the following definition of tables and snapshot of the tables:
Definition of tables:
CREATE TABLE MOVIES (MOVIE_ID INT, MOVIE_NAME VARCHAR2(20), DIRECTORID
INT, SCORE INT, YEAR INT, PRIMARY KEY (MOVIE_ID), FOREIGN KEY (DIRECTORID)
REFERENCES DIRECTORS (DIRECTORID));
CREATE TABLE DIRECTORS (DIRECTORID INT, NAMEVARCHAR2(20),
BIRTH_YEARINT, PRIMARY KEY ("DIRECTORID"));
Snapshot of the tables:
Q1. Consider the following relational query on the above database:

SELECT Name FROM DIRECTORS D, MOVIES M
WHERE D.DirectorID = M.DirectorID AND
EXISTS (SELECT * FROM MOVIES M1 WHERE M.Score > M1.Score);
Which one of the following is the correct output of the above query?
ADVANCED DATABASE MANAGEMENT SYSTEM Page 1

Answer: (a)
Solution:
Select Name from directors D, Movie M where D. Director ID= M. Director ID and
EXISTS(Select * from movies M1 where M. score >M1.score);
This query will find all the score which are greater than minimum score in movies
table.
hence output will displays all those names whose sores are greater than minimum
score.
Name
Mehboob Khan
Satyajit Roy
K Asif
Vijyanand
R.Mukhrjee
Answer:-a
NOT EXISTS (SELECT * FROM MOVIES M1 WHERE M.Score > M1.Score);
Answer: (C)
Solution:

It will display the name of where M. director id is equal to Director. dirtcore id and
the score of movie is minimum .
Only Guru datt will be printed
Answer:C

NOT EXISTS (SELECT * FROM MOVIES M1 WHERE M.Score < M1.Score);
Answer: (B)
Solution:
It will print the name which has maximum score(i.e there not exist a score which is less
than the selected score)
Only Mehhboob Khan is printed.
Name
Mehboob Khan
Answer:B

NOT EXISTS (SELECT * FROM MOVIES M1 WHERE M.Score > M1.Score AND
EXISTS (SELECT * FROM MOVIES M2 WHERE M.Score < M2.Score));

Answer: (D)
Solution:
Select Name from directors D, Movies M where D. director ID= M. director 2D and
Not exists(Select * from movies M1 where M.score>M1.score and exists (select* from
movies M2 where M.score<M2.score));
First we match D. director ID= M.directorID
So we first check for score 10 here M.score =10 So check inner query of correlated
quury for score 10.
i.e select * from movies M1 where M.score>M1.score and exists (Selcet *from movies
M2 where M.score<M2.score
10>10 false
m1.score check each row of score
10>9 True
as it is true check another condition select * from movies M2 where M.score<m2.score
10<10 – False
10<9 – False
10<8 – False
hence as both condition is false not exist print name corresponding to ID 101 i.e.
Mehboob Khan.
Similarly Guru Datt is also printed.
Answer:D

EXISTS (SELECT * FROM MOVIES M1 WHERE M.Score > M1.Score AND
NOT EXISTS (SELECT * FROM MOVIES M2 WHERE M.Score < M2.Score));

Answer: (B)
Solution:
Query work similarly as the above query of Q 9.
M.score>M1.score
10>10 – False
10>9 – True
10>9 – False. , 10<9 – False.  Not exist will become true at 5.

Answer:B

EXISTS (SELECT * FROM MOVIES M1 WHERE M.Score > M1.Score AND EXISTS
(SELECT * FROM MOVIES M2 WHERE M1.Score < M2.Score));
The number of rows return by the above SQL query is_____________
Answer: (5)
Solution:
This query will result in the output.
Mehboob Khan
Satyajit Roy
R Asif
Vijayanand
R Mukharjee
Answer: 5
Data for the next three questions.

Consider the following definition of tables and snapshot of the tables:

CREATE TABLE SHOES (SHOE_ID INT, TYPE VARCHAR2(20), COLOR VARCHAR2(20),
PRIMARY KEY (SHOE_ID));
CREATE TABLE SHOE_BRANDS (BRAND_ID INT, BRAND_NAME VARCHAR2(20),
COUNTRY VARCHAR2(20), PRIMARY KEY (BRAND_ID)) ;
CREATE TABLE CATALOG(BRAND_ID INT, SHOE_ID INT, PRICE INT, PRIMARY KEY
(BRAND_ID, SHOE_ID), FOREIGN KEY (BRAND_ID) REFERENCES SHOE_BRANDS
(BRAND_ID), FOREIGN KEY (SHOE_ID) REFERENCES SHOES(SHOE_ID));
Snapshot of the tables:

SELECT Brand_name, COUNT(Brand_name) as Count
FROM Shoe_Brands SB, Shoes S, Catalog C

WHERE SB.Brand_ID = C.Brand_ID AND C.Shoe_ID = S.Shoe_ID AND Color
='White' GROUP BY Brand_name;
The numbers of rows return by the above SQL query is___________
Answer: (5)
Solution:
Select Brand_name, count (Brand_name) as Count from Shoe_Brands SB, Shoes S,
catlog C where SB. Brand _ID =C. Brand _ID and C.Brand_IDandC.Shoe_ID=S.shoeID
and color =‟white‟. Group by Brand_Name.
The output will be like . It will return all the name of brand and count it where the
color of shoes is white.
Brand Count
Name
Adidas 2
Nike 1
Calvin 1
Kevin
Munich 1
Superga 1
 5 rows are there.

Answer:5

SELECT Brand_name, COUNT(Brand_name) AS counts FROM Shoe_Brands b1,
Catalog c1, Shoes s1 WHERE b1.brand_id = c1.Brand_id AND
c1.shoe_id = s1.shoe_id AND color ='White' GROUP BY Brand_name
MINUS
SELECT Brand_name, COUNT(Brand_name) AS counts FROM Shoe_Brands b2,
Catalog c2, Shoes s2 WHERE b2.brand_id = c2.Brand_id AND
c2.shoe_id = s2.shoe_id AND color <> 'White' GROUP BY Brand_name;
The numbers of rows return by the above SQL query is__________
Answer: (4)
Solution:
See the explanation from Q7 above.
1st query output will be (Brand name and Count with color=white)
Brand name Cou

nt
Adidas 2
Nike 1
calvinKlien 1
Munich 1
Superga 1
minus
2nd query return. (Brand name and Count with shoes color ≠ white)
Brand Count
name
Puma 2
Rebook 1
Munich 1
Gusci 1
output will be
Brand name Count

Adidas 2
Nike 1
calvinklein 1
Superga 1
Output has 4 rows.

Answer:4

SELECT Brand_name FROM Shoe_Brands b1, Catalog c1, Shoes s1
WHERE b1.brand_id = c1.Brand_id AND c1.shoe_id = s1.shoe_id AND Type =
'Tennis' AND b1.brand_id NOT IN (SELECT b2.brand_id
FROM Shoe_Brands b2, Catalog c2, Shoes s2
WHERE b2.brand_id = c2.Brand_id AND c2.shoe_id = s2.shoe_id AND type
='Basketball');
The numbers of rows that will be returned by the above SQL query is_______________.
Answer: (1)

Solution:
Outer query select Brand name where Type = tennis and shoe id is same as in catlog
and shoes.
Brand name
Adidas
calvinklein
Inner query‟s output where type is basket ball .

Brand name
Adidas
Rebook
Puma
Munich
not in select 1 row only from table (1.2)

Answer:1
Data for the next five question. Consider the following definition of tables and snapshot of
the tables:
CREATE TABLE EMPLOYEE (EMP_ID NUMBER, EMP_NAME VARCHAR2(20),
JOBVARCHAR2(20), MANG_ID NUMBER, SALARY NUMBER, DEPT_ID VARCHAR2(20),
PRIMARY KEY (EMP_ID), FOREIGN KEY MANG_ID) REFERENCES EMPLOYEE
(EMP_ID));
CREATE TABLE DEPARTMENT (DEPT_NO NUMBER, DEPT_NAME VARCHAR2(20),

DEPT_CITY VARCHAR2(20), PRIMARY KEY (DEPT_NO));
Snapshot of tables:

Q10. [MSQ]
Which of following query display name of the employees who does not work in the
departments where there exists the manager with employee_id within the range 7600
and 7700?
(a) SELECT Emp_Name FROM Employee WHERE Emp_ID NOT IN (Select Emp_ID
from Employee where Job = 'MANAGER' and Emp_ID Between 7600 and 7700);
(b) SELECT Emp_Name FROM Employee WHERE Dept_ID NOT IN (Select Dept_ID
from Employee where Job = 'MANAGER' and Emp_ID Between 7600 and 7700);
(c) SELECT Emp_Name, Dept_ID FROM Employee e1 WHERE NOT EXISTS (Select *
from Employee e2 where e2.Job = 'MANAGER' and e2.Emp_ID Between 7600 and
7700 and e2.Dept_ID = e1.Dept_ID);
(d) None of the above
Answer: (b) and (c)

Q11. [MSQ]
Which of following query display name of the employees with second highest salary in
the organization?
(a) SELECT Emp_Name FROM Employee
WHERE Salary = (SELECT MAX(Salary) FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee));

(b) SELECT Emp_Name FROM Employee
WHERE Salary = (SELECT DISTINCT(Salary) FROM Employee e
WHERE 2 = (SELECT COUNT(DISTINCT Salary) FROM Employee p
WHERE e.Salary <= p.Salary));
(c) SELECT Emp_Name FROM Employee
WHERE Salary = (SELECT MIN(Salary)
FROM (SELECT DISTINCT salary FROM Employee ORDER BY Salary DESC)
WHERE ROWNUM <=2);
Answer: (a), (b) and (c)
Solution:
query (a) the inner query will find the salary which is smaller then max salary but
not smaller then remaining i.e second highest salary, and outer query will return
emp_name where salary is equal to salary return by inner query.
query(b) Count (distinct salary) will give count (sal) which is second highest and
outer query will return name according to it.
query (c) Row number is a operator used to calculate number of rows selected . So
this inner query will select 2 rows i.e. second highest salary and outer will return
name.
Answer: (a), (b) and (c)
Q12. [MSQ]
Which of following query display name of the employees with second highest salary in
their department?
(a) SELECT Emp_Name FROM Employee
WHERE (dept_id, Salary) = Any (SELECT e1.dept_id, MAX(e1.salary) FROM
employee e1 WHERE salary < (SELECT MAX(salary) FROM employee e2 WHERE
e2.dept_id = e1.dept_id) GROUP BY e1.dept_Id);
(b) SELECT Emp_Name FROM Employee
WHERE (dept_id, Salary) IN (SELECT dept_id, max(salary) FROM employee
WHERE salary NOT IN (SELECT max(salary) FROM employee GROUP BY
dept_id) GROUP BY dept_id);
(c) SELECT salary, dept_id FROM employee e1

WHERE 2 = (SELECT COUNT(DISTINCT(salary)) FROM employee e2 WHERE
e1.salary <= e2.salary GROUP BY dept_id);
Answer: (a)and (b)
Solution:
query (a)- inner query will return salary of the employee whose salary is second
highest and outer query will display name using dept id and salary. query (b) same as
that of (a)
query (c) will display salary and dept id not name.
Answer: (a) and (b)
Q13. [MSQ]
Write a query to display the employee number, name and salary for all employees
who earn more than the average salary and who work in a department with any
employee with a 'J' in their name.
(a) SELECT Emp_id, EMp_Name , salary FROM employee WHERE salary > (SELECT
AVG (salary) FROM employee) OR dept_id IN (SELECT dept_ID FROM employee
WHERE Emp_Name LIKE '%J%');
(b) SELECT Emp_id, EMp_Name , salary FROM employee WHERE salary > (SELECT
AVG (salary) FROM employee) AND dept_id IN (SELECT dept_ID FROM
employee WHERE Emp_Name LIKE '%J%');
(c) SELECT Emp_id, EMp_Name , salary FROM employee WHERE salary > AVG
(salary) AND dept_id IN (SELECT dept_ID FROM employee WHERE Emp_Name
LIKE '%J%');
(d) SELECT Emp_id, EMp_Name , salary FROM employee WHERE salary > AVG
(salary) AND Emp_Name LIKE '%J%';
Answer: (b)
Solution:
query(a) return information of employee whose salary is greater than average salary or
information where employee work in a dept with any employee with „J‟.
query(b)will return info of employee whose salary is greater as well as having deptno
with any employee with „J‟ in their name.

query(c)wrong use of aggregate function avg.
query(d) wrong query.
Answer:B
Q14. Which of following query display the name and job of all employees whose salary is
smaller than any salary of employees whose job is Manager.
(a) SELECT Emp_name, Job FROM employee WHERE Salary < Any (SELECT salary
FROM employee WHERE job = 'MANAGER');
(b) SELECT Emp_name, Job FROM employee e1 WHERE Exists (SELECT * FROM
employee e2 WHERE e2.job = 'MANAGER' and e1.salary < e2.salary);
(c) SELECT Emp_name, Job FROM employee WHERE Salary < Any (SELECT salary
FROM employee WHERE job = 'MANAGER') and Job <> 'MANAGER';
Answer: (a), (b)
Solution:
query (a) inner query will select salary of employee where job= manager and outer
query return name, job from employee where salary is less than return by inner query.
Query
(b) inner query will return information where job is manager and compare salary
of outer query with inner e2 at every row if there exist a single salary e2 which is
smaller than e1 exist will become true and print that salary‟s corresponding
information.
(c) Select salary of job manager but print those employee who are not manger, salary
<any of salary return of inner query.
Answer: (a) and (b)
Data for the next five questions. Consider the following relations and their snapshot:
Student (snum:integer, sname:string, major:string, level:string, age:integer)
Snum Sname Major Level Age
051135593, Maria White, English, SR, 21
060839453, Charles Harris, Architecture, SR, 22
099354543, Susan Martin, Law, JR, 20
112348546, Joseph Thompson, Computer Science, SO, 19
115987938, Christopher Garcia, Computer Science, JR, 20
132977562, Angela Martinez, History, SR, 20

269734834, Thomas Robinson, Psychology, SO, 18
280158572, Margaret Clark, Animal Science, FR, 18
301221823, Juan Rodriguez, Psychology, JR, 20
318548912, Dorthy Lewis, Finance, FR, 18
320874981, Daniel Lee, Elect. Engineering, FR, 17
322654189, Lisa Walker, Computer Science, SO, 17
348121549, Paul Hall, Computer Science, JR, 18
351565322, Nancy Allen, Accounting, JR, 19
451519864, Mark Young, Finance, FR, 18
455798411, Luis Hernandez, Elect. Engineering, FR, 17
462156489, Donald King, Mech. Engineering, SO, 19
550156548, George Wright, Education, SR, 21
552455318, Ana Lopez, Computer Engineering, SR, 19
556784565, Kenneth Hill, Civil Engineering, SR, 21
567354612, Karen Scott, Computer Engineering, FR, 18
573284895, Steven Green, Kinesiology, SO, 19
574489456, Betty Adams, Economics, JR, 20
578875478, Edward Baker, Veterinary Medicine, SR, 21
Class(cname: string, meets_at: string, room: string, fid: integer)
Cname Meets_at Room Fid
Data Structures, MWF 10, R128, 489456522
Database Systems, MWF 12:30-1:45, 1320 DCL, 142519864
Operating System Design, TuTh 12-1:20, 20 AVW,
489456522
Computer Organization, TuTh 10:30-11:45, R15, 011564812
Computer Organization, TuTh 2-3:15, 20 AVW, 619023588
Computer Organization, Tu 6:30-8:40, R15, 159542516
Data Structures, MTuWTh 3, Q3, 489221823
Data Structures, F 1-2:50, R128, 090873519
Computer Networks, MWF 11, 20 AVW,
489221823
Computer Networks, MW 9:30-10:45, 20 AVW,
141582651
Computer Networks, TuTh 12:30-1:45, R15, 254099823
Enrolled(snum: integer, cname: string) Faculty(fid: integer, fname: string, deptid:
Snum Cname integer)
112348546, Database Systems
Fid Fname Deptid
142519864, Ivana Teach, 20
242518965, James Smith, 68
141582651, Mary Johnson, 20
455798411, Operating System Design
011564812, John Williams, 68
254099823, Patricia Jones, 68
356187925, Robert Brown, 12
489456522, Linda Davis, 20
567354612, Data Structures
287321212, Michael Miller, 12
552455318, Computer Networks

455798411, Computer Networks 248965255, Barbara Wilson, 12
301221823, Data Structures 159542516, William Moore, 33
301221823, Computer Organization 090873519, Elizabeth Taylor, 11
301221823, Computer Organization 486512566, David Anderson, 20
556784565, Computer Organization 619023588, Jennifer Thomas, 11
099354543, Data Structures 489221823, Richard Jackson, 33
574489456, Computer Networks 548977562, Ulysses Teach, 20
The meaning of these relations is straightforward; for example, Enrolled has one record per
student-class pair such that the student is enrolled in the class.
Q15. Consider the following SQL query on the above database:
SELECT DISTINCT S.sname
FROM Student S
WHERE S.snum IN (SELECT E1.snum
FROM Enrolled E1, Enrolled E2, Class C1, Class C2
WHERE E1.snum = E2.snum AND E1.cname <> E2.cname
AND E1.cname = C1.name
AND E2.cname = C2.name
AND C1.meets_at = C2.meets_at)
The number of rows return by the above SQL query is_______________
Answer: (0)
Solution:

SELECT DISTINCT F.fname
FROM Faculty F

WHERE NOT EXISTS ((SELECT room
FROM Class C)
EXCEPT
(SELECT C1.room
FROM Class C1
WHERE C1.fid = F.fid))
Answer: (0)
Solution:

SELECT MAX(S.age)
FROM Student S
WHERE (S.major = „Computer Science‟)
OR S.snum IN (SELECT E.snum
FROM Class C, Enrolled E, Faculty F
WHERE E.cname = C.name AND C.fid = F.fid
AND F.fname = „Ivana Teach‟)
Answer: (1)

Solution:

SELECT DISTINCT S.sname
FROM Student S
WHERE S.snum IN (SELECT E.snum

FROM Enrolled E
GROUP BY E.snum
HAVING COUNT (*) >= ALL (SELECT COUNT (*)
FROM Enrolled E2
GROUP BY E2.snum))
Answer: (1)
Solution:

SELECT S.age, S.level
FROM Student S
GROUP BY S.age, S.level,
HAVING S.level IN (SELECT S1.level
FROM Student S1
WHERE S1.age = S.age
GROUP BY S1.level, S1.age
HAVING COUNT (*) >= ALL (SELECT COUNT (*)
FROM Student S2
WHERE s1.age = S2.age
GROUP BY S2.level, S2.age))
Answer: 5
Solution:
Hint: The above query finds For each age value that appears in Students, find the level
value that appears most often. For example, if there are more FR level students aged 18
than SR, JR, or SO students aged 18, you should print the pair (18, FR).
Age Level Count
18 FR 4
18 SO 1
18 JR 1
19 SO 3
19 JR 1
19 SR 1
20 JR 4
20 SR 1
21 SR 1
22 SR 1
The output of the above query is highlighted row:
S.age S.level
21 SR
22 SR
20 JR
19 SO
18 FR
Data for the next five questions. Consider the following relations and their snapshot:
Flights(flno:integer, from:string, to:string, distance:integer)
Flno From To Distance
99, Los Angeles, Washington D.C., 2308,
13, Los Angeles, Chicago, 1749,
346, Los Angeles, Dallas, 1251,
387, Los Angeles, Boston, 2606,
7, Los Angeles, Sydney, 7487,
2, Los Angeles, Tokyo, 5478,
33, Los Angeles, Honolulu, 2551,
34, Los Angeles, Honolulu, 2551,
76, Chicago, Los Angeles, 1749,
68, Chicago, New York, 802,
7789, Madison, Detroit, 319,
701, Detroit, New York, 470,
702, Madison, New York, 789,
4884, Madison, Chicago, 84,
2223, Madison, Pittsburgh, 517,
5694, Madison, Minneapolis, 247,
304, Minneapolis, New York, 991,
149, Pittsburgh, New York, 303,
Aircraft(aid: integer, aname: string, Certified(eid: integer, aid: integer)
cruisingrange: integer) Eid Aid
141582651, 1
Aid Aname
567354612, 2
Cruisingrange
567354612, 10
1, Boeing 747-400, 8430
242518965, 9
2, Boeing 737-800, 3383
011564812, 7
3, Airbus A340-300, 7120
567354612, 6
4, British Aerospace Jet, 1502
242518965, 7
5, Embraer ERJ-145, 1530
567354612, 9
6, SAAB 340, 2128
141582651, 3
7, Piper Archer III, 520
567354612, 4
8, Tupolev 154, 4103

9, Lockheed L1011, 6900 141582651, 5
10, Boeing 757-300, 4010 552455318, 2
552455318, 10
550156548, 1
550156548, 8
390487451, 3
390487451, 5
390487451, 4
274878974, 3
Employees(eid: integer, ename: string, salary: integer)
Eid Ename Salary
242518965, James Smith, 120433
141582651, Mary Johnson, 178345
011564812, John Williams, 153972
567354612, Lisa Walker, 256481
552455318, Larry West, 101745
550156548, Karen Scott, 205187
390487451, Lawrence Sperry, 212156
274878974, Michael Miller, 99890
SELECT DISTINCT A.aname
FROM Aircraft A
WHERE A.Aid IN (SELECT C.aid
FROM Certified C, Employees E
WHERE C.eid = E.eid AND
NOT EXISTS (SELECT *
FROM Employees E1
WHERE E1.eid = E.eid
AND E1.salary < 80000))
Answer: 0
Solution

SELECT Temp.name, Temp.AvgSalary
FROM (SELECT A.aid, A.aname AS name,
AVG (E.salary) AS AvgSalary
FROM Aircraft A, Certified C, Employees E
WHERE A.aid = C.aid AND
C.eid = E.eid AND A.cruisingrange > 1000
GROUP BY A.aid, A.aname) AS Temp
Answer: 9
Solution

SELECT DISTINCT F.from, F.to
FROM Flights F
WHERE NOT EXISTS (SELECT *
FROM Employees E
WHERE E.salary > 100000
AND
NOT EXISTS (SELECT *
FROM Aircraft A, Certified C
WHERE A.cruisingrange > F.distance
AND E.eid = C.eid
AND A.aid = C.aid))
Solution:
Hint: The above query finds the routes that can be piloted by every pilot who makes
more than $100,000.

SELECT E.ename
FROM Employees E, Certified C, Aircraft A
WHERE C.aid = A.aid AND E.eid = C.eid
GROUP BY E.eid, E.ename
HAVING EVERY (A.cruisingrange > 1000) AND COUNT (*) > 1
Answer: 5
Solution
Larry west is also printed so updated answer is 5.

SELECT E.ename, E.salary
FROM Employees E
WHERE E.eid NOT IN (SELECT DISTINCT C.eid
FROM Certified C)
AND E.salary > (SELECT AVG (E1.salary)
FROM Employees E1
WHERE E1.eid IN (SELECT DISTINCT C1.eid
FROM Certified C1))

Answer: 0
Solution
Hint: The above query prints the name and salary of every non pilot whose salary is
more than the averages salary for pilots. As there is no such employee in employee
table how is not register for any aircraft. All employees are register for at least one
aircraft.
Data for the next five questions. Consider the following relational schema and their
snapshots. An employee can work in more than one department; the pct_time filed of the
Works relation shows the percentage of time that a given employee works in a given
department.
Emp(eid: integer, ename: string, age: integer, salary: real)
Eid Ename Age Salary
548977562, Donald King, 43, 92048
578875478, Edward Baker, 50, 101071
142519864, Susan Martin, 39, 56990
242518965, James Smith, 68, 27099
141582651, Mary Johnson, 44, 94011
011564812, John Williams, 35, 74098
254099823, Patricia Jones, 28, 42783
356187925, Robert Brown, 28, 35431
489456522, Linda Davis, 26, 25971
287321212, Michael Miller, 62, 131072
141582657, Stanley Browne, 23, 14093
619023588, Jennifer Thomas, 24, 34654
Dept(did: integer, dname: string, budget: real, managerid: integer)

Did Dname Budget
Managerid
1, Hardware, 1048572.12, 141582651
2, Operations, 12099101.00, 287321212
3, Legal, 222988.13, 248965255
4, Marketing, 538099.54, 548977562
5, Software, 400011.12, 141582651
6, Production, 12099101.00, 578875478
7, Shipping, 500000.00, 489456522
Works(eid: integer, did: integer, pct_time: integer)

Eid Did Pct_time
548977562, 4, 100

619023588, 2, 25
578875478, 6, 100
142519864, 5, 100
242518965, 1, 100
141582651, 1, 50
141582657, 1, 25
141582651, 5, 50
011564812, 3, 100
619023588, 4, 25
254099823, 3, 100
141582657, 5, 75
356187925, 5, 100
489456522, 7, 100
287321212, 2, 100
619023588, 1, 50
Q25. Which of the following query print the names and ages of each employee who works
in both the hardware department and the Software department?
(a) SELECT E.ename, E.age
FROM Emp E, Works W1, Dept D1
WHERE E.eid = W1.eid AND W1.did = D1.did
AND (D1.dname = „Hardware‟ OR D1.dname= „Software‟)
(b) SELECT E.ename, E.age
FROM Emp E, Works W1, Works W2, Dept D1, Dept D2
WHERE E.eid = W1.eid AND W1.did = D1.did AND D1.dname = „Hardware‟ AND
E.eid = W2.eid AND W2.did = D2.did AND D2.dname = „Software‟
(c) SELECT E.ename, E.age
WHERE E.eid = W1.eid AND W1.did = D1.did AND D1.dname = „Hardware‟
OR E.eid = W2.eid AND W2.did = D2.did AND D2.dname = „Software‟
(d) SELECT E.ename, E.age
WHERE E.eid = W1.eid AND D1.dname = „Hardware‟
AND E.eid = W2.eid AND D2.dname = „Software‟
Answer: B
Solution

Q26. Which of the following query find department with more than 20 full-time-equivalent
employees (i.e., where the part-time and full-time employees add up to at least that
many full-time employees), print the did together with the number of employees that
work in that department?
(a) SELECT W.did, COUNT (W.eid)
FROM Works W, Works W1
GROUP BY W.did
HAVING 2000 < SUM(W1.pct_time)
(b) SELECT W.did, COUNT (W.eid)
FROM Works W, Works W1
WHERE W1.did=W.did
GROUP BY W.did
HAVING 2000 < SUM(W1.pct_time)
(c) SELECT W.did, COUNT (W.eid)
FROM Works W
GROUP BY W.did
HAVING 2000 < (SELECT SUM(W1.pct_time)
FROM Works W1 WHERE W1.did = W.did)
Answer: C
Solution

SELECT E.ename
FROM Emp E
WHERE E.salary > ALL (SELECT D.budget
FROM Dept D, Works W
WHERE E.eid = W.eid AND D.did = W.did)
Answer: 0
Solution

Q28. Which of the following queries find the enames of managers who manage the
departments with the largest budgets?
(a) SELECT E.ename
FROM Emp E, Dept D
WHERE E.eid = D.managerid
AND D.budget = (SELECT MAX(D1.budget) FROM Dept D1)

(b) SELECT E.ename
FROM Emp E
WHERE E.eid IN(SELECT D.managerid FROM Dept D
WHERE D.budget = (SELECT MAX(D2.budget)
FROM Dept D2))
(c) SELECT E.ename
FROM Emp E
WHERE E.eid IN (SELECT D.managerid FROM Dept D
WHERE NOT EXISTS (SELECT * FROM Dept D1
WHERE D1.budget > D.budget))
Answer: (b and c)
Q29. Which of the following query find the distinct managerids of managers who control the
largest amounts?
(a) SELECT DISTINCT D.managerid FROM Dept D
WHERE D.budget = (SELECT MAX(SUM(D1.budget))
FROM Dept D1 WHERE D1.managerid = D.managerid)
(b) SELECT DISTINCT tempD.managerid

FROM (SELECT DISTINCT D. managerid, SUM(D.budget) AS tempBudget
FROM Dept D
GROUP BY D.managerid) AS temp D
WHERE tempD.tempBudget = (SELECT MAX(tempD.tempBudget)
FORM tempD)
(c) SELECT DISTINCT D.managerid FROM Dept D
WHERE D.budget = (SELECT MAX(SUM(D1.budget))
FROM Dept D1 GROUP BY D1.managerid)
Answer: B
Solution

Data for the next five questions. Consider the following relational schemas:
 Book(ISBN, Book_Title, Book_Author, Num_of_Pages, Book_Year, C_ID, P_ID)
 Reader (Reader_ID, First_Name, Last_Name, City, DOB)
 Publisher (P_ID, P_Name, P_City)
 Ratings (R_ID, ISBN, Rating, R_Date)
 Category (C_ID, C_Name)
 Reading (Reader_ID, ISBN)
 Reviewer (R_ID, R_Name)
(SELECT Book.ISBN, Book.Book_Title, rating
FROM Book, ratings
WHERE ratings.ISBN = book.ISBN)
MINUS
(SELECT Book.ISBN, Book.Book_Title, rating
FROM Book, (SELECT R1.ISBN, R1.rating
FROM Ratings R1, Ratings R2 WHERE R1.ISBN = R2.ISBN

AND R1.rating < R2.rating) Stars
WHERE Book.ISBN= Stars.ISBN.)
the above query returns the
(a) average number of stars received by each book.
(b) lowest number of stars received by each book.
(c) total number of stars received by each book.
(d) highest number of stars received by each book.
Answer: D
Solution
Q31. [MSQ]
Consider the following SQL query on the above schema:
SELECT b.Book_Title, b.Book_Author
FROM Book b, (SELECT Book_Author, count(ISBN) AS num
FROM Book GROUP BY Book_Author) Book_Count
WHERE b.Book_Author = Book_Count.Book_Author AND num ≥ 2
ORDER BY b.Book_Author, b.Book_Title;
Which of the following queri(es) is/are similar to the above query?
(a) SELECT B1.Book_Title, B1.Book_Author
FROM Book B1, Book B2
WHERE B1.Book_Author <> B2.Book_Author
AND B1.Book_Title <> B2.Book_Title
ORDER BY B1.Book_Author, B1.Book_Title;
(b) SELECT B1.Book_Title, B1.Book_Author
WHERE B1.Book_Author <> B2.Book_Author
AND B1.Book_Title = B2.Book_Title

(c) SELECT B1.Book_Title, B1.Book_Author
WHERE B1.Book_Author = B2.Book_Author
AND B1.Book_Title <> B2.Book_Title
Answer: C
Solution
Q32. Which of the following query will display the name of publisher which publishes
highest number of books with rating 5?
(a) SELECT p.P_name, COUNT(P_Name)
FROM Publisher p, Ratings r, Book b
WHERE r.rating = 5 AND p.P_ID = b.P_ID AND b.ISBN = r.ISBN GROUP BY

p.P_Name
HAVING COUNT(P_Name) = (SELECT MAX(COUNT(P_Name)) FROM Book
GROUP BY P_Name);
(b) SELECT P_name

WHERE rating = 5 AND p. P_ID = b.P_ID AND b.ISBN = r.ISBN
GROUP BY P_Name HAVING COUNT(P_ID) = (SELECT MAX(COUNT(ISBN)
FROM Ratings GROUP BY ISBN);
(c) SELECT P_Name
WHERE r.rating = 5 AND p.P_ID = b.P_ID AND b.ISBN = r.ISBN GROUP BY
p.P_Name
HAVING COUNT(P_Name)=(SELECT MAX(COUNT(P_Name))
WHERE r.rating = 5 AND p.P_ID = b.P_ID
AND b.ISBN = r.ISBN
GROUP BY p.P_Name)
Answer: C
Solution

SELECT First_Name || Last_Name AS Full_Name FROM Reader
WHERE Reader_ID IN (SELECT Reader.Reader_ID FROM Reader, Reading
WHERE Reader.Reader_ID = Reading.Reader_ID
GROUP BY Reader.Reader_ID
HAVING COUNT(Reader.Reader_ID) = (SELECT
MAX(Count(Reader.Reader_ID))
FROM Reader, Reading
WHERE Reader.Reader_ID = Reading.Reader_ID
GROUP BY Reader.Reader_ID));
the above query returns
(a) the full name of reader who has read the books.
(b) the full name of reader who has read the maximum number of books.
(c) the full name of reader who has read the minimum number of books.
Answer: B
Solution
Q34. [MSQ]
Which of the following(s) query will display the name of books which is reading by
the reader who is born on the date 09 June 1963
(a) SELECT Book_Title FROM Book b, Reading r1, Reader r2
WHERE b.ISBN = r1.ISBN AND r2.Reader_ID = r1.Reader_ID
AND r2.DOB = '09-JUN-1963';

(b) SELECT DISTINCT Book_Title
FROM Book NATURAL JOIN Reading NATURAL JOIN Reader
WHERE DOB = '09-JUN-1963';
(c) SELECT Book_Title
FROM Book NATURAL JOIN Reader WHERE DOB = '09-JUN 1963';
(d) None of the above.
Answer: B
Solution
Data for the next five questions. Consider the following relations containing airline flight
information:
Flights(flno: integer, from: string, to: string, distance: integer)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)
the primary keys are underline and note that the Employees relation describes pilots and other
kinds of employees as well; every pilot is certified for some aircraft (otherwise, he or she
would not qualify as a pilot), and only pilots are certified to fly. You can use the database
snapshot given in Q21 to Q25 for answer to next five questions.
Q35. Consider the following RA query on the above database:
The number of rows return by the above RA query is_______________

Answer: 3
Solution

Q36. Consider the following RA query on the above database:

(a) the eids of employees who make the highest salary.
(b) the eids of employees who make the lowest salary.
(c) the eids of employees who make the second highest salary.
(d) the eids of employees who make the second lowest salary.
Answer: A
Solution

Q37. Consider the following TRC query on the above database:

(a) the eids of employees who make the highest salary.
(b) the eids of employees who make the lowest salary.
(c) the eids of employees who make the second highest salary.
(d) the eids of employees who make the second lowest salary.
Answer: C
Solution

Q38. Consider the following DRC query on the above database:

(a) the names of pilots certified for some Boeing aircraft.
(b) the names of pilots certified for all Boeing aircraft.
(c) the names of pilots not certified for any Boeing aircraft.
Answer: A
Solution
The number of rows return by the above RA query is_______________
Answer: 18
Identify the flights that can be piloted by every pilot whose salary is more than
$100,000.
Data for the next three questions. Consider the following relations schema containing ebay
system information:
Sellers(sellerID: integer, rating: char, email: string)
Items(itemID: integer, description: string, startBid: real, sellerID: integer, qty: integer)
Purchases(purchaseNumber: integer, itemID: integer, custID: integer, count: integer,
soldFor: real)

Customers(custID: integer,address: string)
Q40. Which of the following TRC query find the ID‟s of sellers of items with starting bid ≥
$1000?
(a) {R|∃I ∈ Items(I.startBid ≥ 1000 ∧ R.sellerID = I.sellerID)}
(b) {R|∀I ∈ Items(I.startBid ≥ 1000 ∧ R.sellerID = I.sellerID)}
(c) {I|∃R ∈ Seller(I.startBid ≥ 1000 ∧ R.sellerID = I.sellerID)}
(d) {I|∀R ∈ Seller (I.startBid ≥ 1000 ∧ R.sellerID = I.sellerID)}
Answer: A
Solution
Q41. Which of the following TRC query find the ID‟s of customers who bought ≥ 2 of the
same item or bought an item that a seller had with quantity 1?
(a) {R|∃P ∈ Purchases(P.count ≥ 2 ∧ (∃I ∈ Items(P.itemID = I.itemID ∧ I.qty = 1)))}
(b) {R|∃P ∈ Purchases(P.count ≥ 2 ∨ (∃I ∈ Items(P.itemID = I.itemID ∧ I.qty = 1)))}
(c) {R|∃P ∈ Purchases((P.count ≥ 2 ∧ (∃I ∈ Items(P.itemID = I.itemID ∧ I.qty = 1)) ∧
(R.custID = P.custID))}
(d) {R|∃P ∈ Purchases((P.count ≥ 2 ∨ (∃I ∈ Items(P.itemID = I.itemID ∧ I.qty = 1)) ∧
(R.custID = P.custID))}

Answer: D
Solution
Q42. Which of the following TRC query find the ID‟s of items which are stocked by ≥ 2
sellers?
(a) {R|∃I1, I2 ∈ Items(I1.itemID = I2.itemID
∧ I1.sellerID = I2.sellerID ∧ R.itemID = I1.itemID}
(b) {R|∃I1, I2 ∈ Items(I1.itemID = I2.itemID
∨ I1.sellerID = I2.sellerID ∨ R.itemID = I1.itemID}
(c) {R|∃I1, I2 ∈ Items(I1.itemID = I2.itemID
∧ I1.sellerID ≠ I2.sellerID ∧ R.itemID = I1.itemID}
(d) {R|∃I1, I2 ∈ Items(I1.itemID = I2.itemID
∨ I1.sellerID ≠ I2.sellerID ∨ R.itemID = I1.itemID}
Answer: C
Solution

{R|∃I ∈ Items((∀S ∈ Sellers(S.rating = 'A' ⇒ (I.sellerID = S.sellerID))) ∧ (R.itemID =
I.itemID))}
(a) the ID‟s of items stocked by some seller with rating A
(b) the ID‟s of items stocked by every seller with rating A
(c) the ID‟s of items stocked by every seller with not rating A
(d) the ID‟s of items stocked by some seller with not rating A
Answer: B
Solution

{R|¬(∃P ∈ Purchases(P.soldFor > 1000) ∧ (P.itemID = R.itemID))}

(a) the ID‟s of items that are only sold for > $1000, by some seller.
(b) the ID‟s of items that are only sold for ≤ $1000, by some seller.
(c) the ID‟s of items that are only sold for > $1000, by any seller.
(d) the ID‟s of items that are only sold for ≤ $1000, by any seller.
Answer: D
Solution
Data for the next two questions. Consider the relations HOSPITAL (HOSPITALID, NAME,
LOCATION) and DOCTORS (DOTORNAME, HOSPITALID).
Q45. What will be the tuple relation calculus query equivalent to the following statement?
"Names of all doctors available in Peerless"
Answer: D
Solution

Q46. What will be the relational algebra query equivalent to the following statement?
"Names of all doctors available in Peerless"
Answer: C
Solution

Q47. Consider the relations STUDENT(ID, RANK, MARKS, SCHOOL). What will be the
relational algebra query equivalent to the following statement?
"Ranks of all students of ABS International school, whose marks is greater than 80"
Answer: A
Solution
Q48. Consider the relation PRODUCT (NAME, COUNT, SHOPID) and QUANTITY
(COUNT, SHOPID) as follows:

What product(s) name will be display by the operation PRODUCT ÷ QUANTITY?
(a) MOBILE
(b) LAPTOP
(c) SPEAKER
Answer: B
Solution
Q49. Consider the database with three table:

Drivers(did, dname, rating, age)
Cars(cid, cname, color)
Reserves(did, cid, rdate)
The snapshot of the database is show below:

Which of the following query(s) is/are find the cids of cars that are reserved by a
driver with a rating of at least 5 as well as a driver named Abby?
(a) cid((σdname='Abby'(Drivers)) ⨝ Reserves) ∩ cid((σrating ≥ 5(Drivers)) ⨝ Reserves)
(b) cid((σdname='Abby'(Drivers)) ⨝ Reserves) ∪ cid((σrating ≥ 5(Drivers)) ⨝ Reserves)
(c) cid((σrating ≥ 5 AND dname ='Abby'(Drivers)) × Reserves)
(d) cid((σrating ≥ 5 AND dname ='Abby'(Drivers)) ⨝ Reserves)
Answer: D
Solution

Q50. [MSQ]
Consider the below instance of the CLASS relation.
Which of the following statement(s) is/are true?

(a) If class (C5, DM) is deleted then the information about student S7 would also be
removed because of deletion anomaly.
(b) Inserting CLASS (C6, Calculus, 3) is an example of insertion anomaly.
(c) Updating CLASS (C5, DB, 3) is an example of updation anomaly.
Answer: A,B,C
Solution
Q51. [MSQ]
Which of the following conclusions for functional dependencies is/are correct?
(a) If A → B and BC → D, then AC → D
(b) If AB → C then A → C
(c) If A → B1,…,Bn and C1,…,Cm → D and {C1,...,Cm} is a subset of {B1,…,Bn}, then
A→D
(d) If A → C and B → C and ABC → D, then A → D
Answer: A,C
Solution

Q52. Consider a relation R (A, B, C, D, E) with functional dependencies AB → E and D → C.
How many super keys does R have? ____________
Answer:(4)

Q53. Consider a relation R with 2d attributes (d is an integer ≥ 2) that are named A1, A2,...,
A2d. There is a set F of 2d FDs on R: Ai → A1+(i+1) mod 2d, for i = 1 . . . 2d. For example,
suppose d = 2, the attributes are A1, A2, A3, A4 and F = {A1 → A3, A3 → A1, A2 → A4, A4
→ A2}. Suppose d = 4, What is the number of keys in R?___________
Answer: 16
Solution:
All “odd” attributes determine each other. All “even” attributes determine each other.
No odd attribute determines an even attribute and vice versa. Thus, to get a key, we
need 1 odd and 1 even attribute (d choices each). Thus, the number of keys is dC1 × dC1
= d2.So, if d = 4 then the number of keys = 42 = 16
Q54. [MSQ]
Which of the following statement(s) is/are correct?
(a) Every relation with only two attributes is in BCNF.
(b) If X and Y are super-keys then X ∪ Y is also a super-key.
(c) If X and Y are super-keys then X ∩ Y is also a super-key.
(d) If X → A and Y → A, then (X ∩ Y) → A.
Answer: A,B
Solution

Q55. [MSQ]
Which of the following statement(s) regarding the Boyce-Codd normal form (BCNF)
is(are) correct?
(a) A relation in BCNF does not have anomalies of any kind.
(b) A relation in BCNF does not have anomalies caused by functional dependencies.
(c) For each functional dependency, the determinant must be a super key.
(d) A relation in BCNF does not have anomalies caused by transitive dependencies.
Answer: A, B, C and D
Solution

Since every candidate key is super key therefore (c) is also true. (d) is also true because
in 3NF we remove transitive dependency as well and every BCNF is in 3NF.
Q56. If a table R has only one candidate key, then which of the following is always true?
(a) If R is in 2NF, it is also in 3NF
(b) If R is in 3NF, it is also in BCNF
(c) If R is in 2NF, but is not in 3NF
(d) None of these
Answer: B
Solution
Q57. A key is simple if it consists of only one attribute. If every key of table R is simple key,
then which of the following is always true?
(a) If R is in 2NF, it is also in 3NF
(b) If R is in 3NF, it is also in BCNF
(c) R is in 2NF, but is not in 3NF

(d) None of these.
Answer: (b)
Solution:
Since every key is simple, then we know that for any FD that satisfies
X → A, where A is part of some key implies that A is a key. By the definition of an
FD, if X is known, then A is known. This means that if X is known, we know a key
for the relation, so X must be a superkey. This satisfies all of the properties of
BCNF.
Q58. [MSQ]
Let F be a set of functional dependencies on a table R, and let X+ denote the closure of a
set of attributes X. Which of the statements below is/are true?
(a) For any two sets X, Y: (X ∪ Y)+ = X+ ∪ Y+
(b) For any two sets X, Y: if X  Y then X+  Y+
(c) For any two sets X, Y: if X+ = X and Y+ = Y then (X ∩ Y)+ = X ∩ Y
Answer: A,B
Solution
Data for the next two questions. Let FD1 and FD2 are two FD sets for a relation R. Consider
the following four cases:
1. If all FDs of FD1 can be derived from FDs present in FD2, we can say that FD1 ⊂ FD1.
2. If all FDs of FD2 can be derived from FDs present in FD1, we can say that FD2 ⊂ FD1.
3. If 1 and 2 both are true, FD1 = FD2.

4. If 1, 2, and 3 are not true, FD1 ≠ FD2
All these four cases can be shown using Venn diagram as:
Q59. Consider a relation R (A, B, C, D, E, H) having two FD sets:

FD1 = {A → C, AC → D, E → AD, E → H}
FD2 = {A → CD, E → AH}
Which of the following case is apply to the given FD sets?
(a) 1 (b) 2 (c) 3 (d) 4
Answer: C
Solution
FD1= {AC, ACD, EAD, EH}
AC then in ACD, C is extra.
AC, AD, EA, EH (as EA, AD) is minimal cover.
FD2= {ACD, EAH}
AC, AD, EA, EH
Here FD1 is = FD2
Answer:C
Q60. Consider a relation R (A, B, C, D, E, H) having two FD sets:

FD1 = {A → B, AB → C, D → ACE}
FD2 = {A → BC and D → AE}
Which of the following case is apply to the given FD sets?
(a) 1 (b) 2 (c) 3 (d) 4
Answer: C
Solution
FD1= AB
ABC, here B is extra as AB is there.
 AC
DA
DCextra as AC is there
AE
Minimal cover =
AB
AC
DA DE
FD2= {ABC and DAE}
AB DA
AC DE
Answer:C
Q61. [MSQ]
Consider the following set of FDs on R (P, Q, S, T, U, V)
PQ → S PS → Q PT → U Q→T QS → P U→V
Which of the following statements is/are true?
(a) Given FD set is a minimum cover
(b) The decomposition {PQ, QS, PQTU, UV} lossless
(c) The decomposition {PQ, QS, PQTU, UV} is not dependency preserving.
(d) The decomposition {PQS, PSTU, PTV} is dependency-preserving
Answer: A,C
Solution

Q62. [MSQ]
Consider the following set of FDs on R (P, Q, S, T, U, V)
Q → ST P→T PS → T QU → V
(a) The attribute closure {P}+ is {P,S,T}.

(b) The attribute closure {PQ}+ is {P,T,Q,S}.
(c) The dependency Q → S can be deduced from FD's.
(d) The candidate keys of R are P and Q.
Answer: C
Solution
Q63. Consider the relation:

Student (Reg_No, Name, Address, Phone, Class_ID)
and the set of following functional dependencies:
• Reg_No → Name,Address
• Address, Phone → Class_ID
• Name → Phone
• Class_ID → Reg_No
Which of the following are possible sets of candidate key of Student?
(a) Reg_No, Phone
(b) Reg_No, Class_ID, {Address, Phone}

(c) Reg_No, Phone, {Address, Phone}
(d) Reg_No, Phone, Name
Answer: B
Solution
Q64. Consider the following relational table PET_STORE:
If (Pet_Name, Breed) is a key for this instance, what may be the value of X?
(a) Beagle.
(b) Pug.
(c) Labrador.
(d) Either Beagle or Labrador.
Answer: B
Solution

Q65. [MSQ]
Consider the relation:
Student (Stu_ID, Dept, Cls_Roll)
The following functional dependencies are given:
FD1: Stu_ID → Dept
FD2: Dept → Stu_ID
FD3: Stu_ID → Cls_Roll
FD4: Cls_Roll → Stu_ID
FD5: Dept → Cls_Roll
Which functional dependencies should be removed to obtain the canonical cover of the
set?
(a) FD2 and FD3 (b) FD1 and FD5
(c) FD5 (d) FD1
Answer: A,C
Solution

Q66. The relation R (A, B, C, D, E, F, G) with the functional dependencies:
 A→B
 A→C
 {A, E} → D
 {A, E, F} → G
is normalized into BCNF. Which of the following decomposition is obtained as a result
of this normalization?
(a) R1(C, F), R2(E, D), R3(A, G)
(b) R1(A, B, C), R2(A, E, D), R3(A, E, F, G)
(c) R1(A, D, C), R2(A, B, D), R3(A, D, F, G)
(d) R1(C, F), R2(E, D)
Answer: B
Solution
Q67. Consider a relation STUDENT (Name, Subject, Location, Marks).

STUDENT is decomposed into the following
1. STUSUB1 (Name, Subject, Location) and STUSUB2(Name, Location, Marks)
2. STUSUB1 (Name, Location) and STUSUB2 (Subject, Marks) Which of the following is
TRUE?
(a) 1 is lossy but 2 is lossless. (b) 1 is lossless but 2 is lossy.
(c) Both 1 and 2 are lossless. (d) Both 1 and 2 are lossy.
Answer: D
Solution
a decomposition is lossy if after joining relation contains extra tuple. divide table
according to decomposed relation and the perform joining operation again if the extra
tuple ocur then decomposition is lossy.
Q68. In a hospital, there are various departments. Each doctor is associated with one
department. A patient gets registered to a department under one doctor in that
department. It may be required, that he/she may be treated by other doctors of same
/different departments also. Doctors suggest various tests for the patients, those are
noted along with their reports. If the primary key for the Department table is Dept_No,
Doctor table is Doc_Id and Patient table is Pat_Id, what is the minimal choice for the
primary key of Test table?
(a) Test_date, Doc_Id and Pat_Id
(b) Test_date, Dept_No, and Pat_Id
(c) Test_date, Doc_Id, Pat_Id and Dept_No
(d) Test_date, Dept_No and Doc_Id
Answer: C
Solution

Q69. Consider the relation T with attributes A, B, C, D, E, F, and G and with the following
functional dependencies (FDs):
A→B BD → F AC → E → D
Unfortunately, we do not know what '' is. It could be any nonempty subset of T's
attributes. (In particular, '' might even contain D itself, which would make →D a
trivial dependency.)
Which of the following must be true, regardless of what is inside ''?
(a) A and G must be in any key.
(b) C and D must be in all keys.
(c) A and E must be in all key.
(d) A can never be in a key with G.
Answer: A
Solution

Q70. A functional dependency (Y → X), between X and Y specifies a constraint on the
possible tuples that can form a relation instance r of R. The constraint states that for
any two tuples t1 and t2 in r such that
(a) t1[X] = t2[Y], then t1[Y] = t2[X]
(b) t1[Y] = t2[Y], then t1[X] = t2[X]
(c) t1[X] = t2[Y], then t1[X] = t2[Y]
(d) t1[X] = t2[X], then t1[Y] = t2[Y]
Answer: B
Solution
Q71. Consider the relational schema R = {P, Q, R, S, T, U, V } and the set of functional
dependencies FD:
P→Q Q → R PS → TRV QT → UR S→V
Which of the following is a minimum cover of the FD?
(a) The given FD is a minimum cover.
(b) {P → Q, Q → R, PS → T, QT → UR, S → V}
(c) {P → Q, Q → R, P → T, Q → U, S → V}

(d) {P → Q, Q → R, PS → T, QT → U, S → V}
Answer: D
Solution
PQ PSR(X) SV
QR QTR
QT R (X)
PSV(X) PST
here S is extra as PQ and QR PR
by default PSR (redundant)
PSV here SV is there then PSV is redundant. QTR here T is extra as QR is
already there.
Answer: d
Q72. Consider the relational schema R = {A,B,C,D,E,F,G,H} and the set of functional
dependencies FD:
A → E, BE→ D, AD →BE, BDH→ E, AC→ E, F→ A, E →B, D →H, BG →F, CD →A
Which of the following is a minimum cover of the FD?
(a) A→E, E→D, BD→E, F→A, E→B, D→H, BG→F, CD→A
(b) A→E, E→D, BD→E, F→A, E→B, D→H, BG→F, CD→A, AD→B
(c) A→E, BD→E, F→A, E→B, D→H, BG→F, CD→A, AD→B
(d) A→E, E→D, B→E, F→A, E→B, D→H, BG→F, CD→A
Answer: A
Solution
AE , BED , ADBE, BDHE, ACE, FA , EB, DH , BGF, CDA.
AE
BED
ADB extra as AE and EB. AB is extra.
ADE extra as AE is there .can be remove.
BDH E here H is extra as DH is there .
 BDE is minimal.
DH
BGF
CDA
EB
FA
Minimal cover is AE , ED , BDE, FA,EB , DH, BGF, CDA
Answer:A
Q73. Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional

dependencies F: {AB → C, A → DE, B → F, F →GH, D → IJ}. If we decompose above
relation into 3NF using standard algorithm, then minimum numbers of relations will
be__________.
Answer: 5
Solution
ABC, ADE,BF, FGH,DIJ
Decompose in 3NF.
AB is the key.
This FDs has minimal cover.
This FDs has minimal cover.
(ADE) (ABC) (BF)(FGH) and(DIJ)
These 5 relations are there in 3NF.
Answer: 5
Q74. [MSQ]
Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional
dependencies F {AB → C, A → DE, B → F, F → GH, D → IJ}. If we decompose above
relation into BCNF using standard algorithm, then which of the following set of
relations are possible:
(a) (A, D, E) (B, F) (F, G, H) (D, I, J) (A, B, C)
(b) (A, D, E) (B, F) (D, I, J) (A, B, C)
(c) (A, D, E) (D, I, J) (A, B, C)
(d) None of the above.
Answer: A
Solution
ABC, ADE,BF,FGH,DIJ
AB is the key.
(ABCDEFGHIJ)
FD‟s violate BCNF are
ADE
BF
FGH
DIJ
Remove right hand side from FD and make separate relation.
(ADE) (BF)(FGH)(DIJ)(ABC)
If first convert (B,F) into relation then .
Q75. Let R(A, B, C, D, E, F, G, H) be a relational schema in which the following FDs are
known to hold:{A→B, A→C, A→D, AE→H, E→D, E→F and E→G}.Suppose we

decompose the relation into two relations, R1(ABCD) and R2(DEFGH). The above
decomposition is
(a) Dependency preserving and lossless join
(b) Lossless join but not dependency preserving
(c) Dependency preserving but not lossless join
(d) Not dependency preserving and not lossless join
Answer: D
Solution
A→B, A→C, A→D, AE→H, E→D, E→F and E→G
A B C D E F G H
R1    
R2     
AEH is not preserved.
neither dependency preserving neither lossless.
Answer:D
Q76. Let R(A, B, C, D, E, F) be a relational schema in which the following FDs are known to
hold:{AB → C, AC → B, AD → E, BC → A, B → F }. Suppose we decompose the
relation R into two relations, R1(AB), R2(BC), R3(ABDE) and R4(EF). The above
decomposition is:
(a) Dependency preserving and lossless join
(b) Lossless join but not dependency preserving
(c) Dependency preserving but not lossless join
(d) Not dependency preserving and not lossless join
Answer: D
Solution
AB → C, AC → B, AD → E, BC → A, B → F
R1(AB) ,R2(BC), R3(ABDE), R4(EF)
A B C D E F
R1  
R2  
R3    
R4  
Not lossless and not dependency preserving.
Answer:D

Q77. Assume you have a table Employee with the following fields {SSN, LastName, Sex,
Email, DeptCode, DeptName, DeptLocation}. What problem would occur when
deleting employees from this table that is not in third normal form?
(a) You might delete an employee you didn‟t mean too
(b) If the employee you are deleting is the last employee in a department, then when
you delete that employee, you also accidentally lose all information about that
department
(c) You can no longer delete employees by using referencing their department code
(d) No problems occur when you do deletions from a table that is NOT in third normal
form
Answer: B
Solution
Data for the next two questions. Assume we have the relation R: {A, B, C, D, E, F, G, H, I, J, K,
L, M, N, O, P, Q} with the following functional dependencies:
 {A, B, E, F} → {C, G}
 {A} → {D, I}
 {A, F} → {J}
 {B, E} → {K}
 {B} → {M, N}
 {E} → {O}
 {F} → {P}
 {K} → {H, L}
 {D} → {Q}
Q78. [MSQ]

If we put the above relation R into BCNF, we will end up with a total number of
___________ relations.
(a) 7 (b) 8 (c) 9 (d) 10
Answer: B,C,D
Solution

Q79. [MSQ]
If we put the above relation R into 3NF, we will end up with a total number of
____________ relations.
(a) 7 (b) 8 (c) 9 (d) 10
Answer: (c)
Solution:
Q80. [MSQ]
Consider the following collection of relations and dependencies. Assume that each
relation is obtained through decomposition from a relation with attributes
ABCDEFGHI and that all the known dependencies over relation ABCDEFGHI are
listed for each question. The options are independent of each other, obviously, since
the given dependencies over ABCDEFGHI are different. Which of the following
relation(s) is/are not in BCNF?
(a) R1(A, C, B, D, E), A → B, C → D
(b) R2(A, B, F), AB → F, B → F
(c) R3(A, D), A → D, D → A
(d) R4(D, C, H, G), D → H, C → G
Answers: (a) (b) and (d)

Q81. Consider the relational schema R = {A, B, C, D} and the set of functional dependencies
FDs: {ABC → D, D → A}. The highest normal form satisfied by the relation R is
(a) 1NF (b) 2NF (c) 3NF (d) BCNF
Answer: (c)
Solution:
(a) Candidate keys: ABC, BCD
(b) R is in 3NF but not BCNF.
(c) ABCD is not in BCNF since D → A and D is not a key. However, if we splitup R as
AD, BCD we cannot preserve the dependency ABC → D. So, thereis no BCNF
decomposition.
Q82. Consider the relational schema R = {A, B, C, D} and the set of functional dependencies
FDs: {AB → C, AB → D, C → A, D → B}. The highest normal form satisfied by the
relation R is
(a) 1NF (b) 2NF (c) 3NF (d) BCNF
Answer: (c)
Solution:
(a) Candidate keys: AB, BC, CD, AD
(b) R is in 3NF but not BCNF (because of the FD: C → A).
(c) C → A and D → B both cause violations because determinant is neither candidate
key and super key. So decompose into: AC, BCD
but this does not preserve AB → C and AB → D, and BCD is still not
BCNF because D → B. So we need to decompose further into: AC, BD,
CD. However, when we attempt to revive the lost functional dependencies
by adding ABC and ABD, we that these relations are not in BCNF form.
Therefore, there is no BCNF decomposition.
Q83. [MSQ]
Suppose that we have the following four tuples in a relation S with three attributes
ABC: (1, 2, 3), (4, 2, 3), (5, 3, 3), (5, 3, 4). Which of the following multivalued (→→)
dependencies can you infer does hold over relation S?
(a) A →→ B (b) BC →→ A
(c) B →→ C (d) None of the above

Answer: A,B,C
Solution
Q84. Consider the following two schemas

Schema 1: R (A,B,C,D)
Schema 2: R1 (A,B,C) and R2 (B,D)
Which of the following statement(s) is/are true?
(a) If the Schema 1 have the only functional dependencies that hold on the relation are
A → B and C → D, then Schema 1 in BoyceCodd Normal Form (BCNF).
(b) If the Schema 2 have the only functional dependencies that hold on the relation are
A→B, A→ C, B→ A, A→D and all possible implicit FDs., then Schema 2 in
BoyceCodd Normal Form (BCNF).
(c) If the Schema 2 have the only functional dependencies that hold on the relation are
A→B, A→ C, B→ A and all possible implicit FDs., then Schema 2 in BoyceCodd
Normal Form (BCNF).
Answer: (b) and (c)

Solution:
Since both relations schema is lossless and individual relation is also in BCNF with
given FD set. Therefore both in BCNF.
Q85. [MSQ]
Consider a relation R with five attributes ABCDE. Which of the following instance(s)
of R will never violated the FD: BC → D and the MVD: BC →→ D for the any value of
'a'?
(a) { } (i.e., empty relation)
(b) {(a,2,3,4,5), (2,a,3,5,5)}
(c) {(a,2,3,4,5), (2,a,3,5,5), (a,2,3,4,6)}
(d){(a,2,3,4,5), (2,a,3,4,5), (a,2,3,6,5)}
Answer: (a)
Solution:
(a) { } (i.e., empty relation):

does not violate either dependency.
(b) {(a,2,3,4,5), (2,a,3,5,5)}:
If a = 2, then BC → D is violated (otherwise it is not).
BC →→ D is not violated (for any value of a)
(c) {(a,2,3,4,5), (2,a,3,5,5), (a,2,3,4,6)}:
BC → D is violated if a = 2 (otherwise not).
If a = 2 then BC →→ D is violated (consider the tuples (2,a,3,5,5) and
(a,2,3,4,6); if a equals 2 must also have (2,a,3,5,6) )
(d) {(a,2,3,4,5), (2,a,3,4,5), (a,2,3,6,5)}:
BC → D is violated (consider the first and the third tuples (a,2,3,4,5) and
(a,2,3,6,5) ).
BC →→ D is not violated.
Q86. [MSQ]
We say that a set of attributes X is closed (with respect to a given set of FDs) if the
closure of X is X itself. Consider a relation R (A, B, C, D) and an unknown set of FDs. If
we are told that all the sets of four attributes are closed i.e. each set in the power set of
four attribute are closed, what can you conclude from this case?
(a) R is in 2 NF (b) R is in 3 NF
(c) R is in BCNF (d) No conclusion can be drawn
Answer: A,B
Solution
Q87. [MSQ]
Consider a table R(A, B, C, D). A set of attributes X is a super key if X+ = ABCD, a set X

is a candidate key if X is a super key and no subset of X is a super key and a set X is
closed if X+ = X. Which of the following statement(s) is/are TRUE?
(a) If AB is a candidate key, then ABC cannot be closed.
(b) If AB is closed then ABC cannot be a key.
(c) If X, Y are closed then X ∪ Y is closed.
(d) If X, Y are closed then X ∩ Y is closed.
Answer: A,D
Solution
(a) TRUE (ABC+ = ABCD in this case).
(b) FALSE (Consider the case where there is only one dependency: ABC → ABCD).
(c) FALSE (Consider the case where there is only one dependency AB → C and X =
{A}, Y = {B}).
(d) TRUE (Let Z = X∩Y. If Z is not closed then there is an attribute A ∈ Z+\Z. Since A 
Z, either A  X or A  Y. Without loss of generality, assume that A  X. Remember
that A ∈ Z+. Since Z ⊆ X, Z+⊆ X+. Therefore, A ∈ X+. This means X+ ≠ X, i.e. X is not
closed (contradiction!)).
Correct answer is (a) and (d)
Q88. [MSQ]
Consider a relation R that contains r tuples with five attributes ABCDE. Assume that R
is decomposed into two smaller relations ABC and CDE. Let S be the relation ABC ⋈
CDE that contains s tuples. Assume that the decomposition is lossless, but not
dependency preserving. Which of the following statements can infer to be always true:
(a) r = s (b) r ⊆ s (c) r ⊇ s (d) r ≠ s
Answer: A
Solution

Q89. Consider an unordered file of 106 records with a record size of 100 bytes stored on
blocks of 4 Kbytes. We will assume that no system related information is stored within
a block. How many blocks we can save in storing the file using the spanned over the
unspanned record organization? ________
Answer: 585
Solution

Q90. A data file consisting of 1,00,000 student-records is stored on a hard disk with block
size of 2048 bytes. The data file is sorted on the primary key RollNo. The size of a
record pointer for this disk is 9 bytes and block pointer is 6 bytes. Each student-record
has a candidate key attribute called Enroll_No. of size 12 bytes. Assume that the
records of data file and index file are not split across disk blocks. Suppose the
secondary index is built on the candidate key attribute of the file, and a multi-level
index scheme is used to store the secondary index, then the total number of blocks
required by the multilevel index is_______________
Answer: 1042
Solution

.
Q91. Consider a disk with block size B = 1024 bytes. A block pointer is P = 6 bytes long, and

a record pointer is PR = 9 bytes long. A file has r = 1000,0000 EMPLOYEE records of
fixed length. Each record is 128 bytes long and its key field is of size 9 bytes. The file is
unordered on a key field, and the file organization is unspanned. If the secondary
index is built on the key field of the file, and a multi-level index scheme is used to store
the secondary index, then the total number of blocks required by the multilevel index
is ______________
Answer: 0
Solution

ordered on a key field, and the file organization is unspanned. If the primary index is
built on the key field of the file, and a multi-level index scheme is used to store the
primary index, then the total number of blocks required by the multilevel index is
______________
Answer: 1494
Solution
Data for the next two questions. consider a relational DBMS with the following employee

relation E:
 E has attributes Id, Name, Age, Salary.
 Id is the primary key attribute (no duplicate ids).
 E holds 1024 tuples.
 All records (tuples) in E are stored sequentially (in increasing order) based on their Id.
 Each data block that holds E records contains 4 records. (Blocks have been compacted so
they are all full.)
We are building a traditional index (not a B+-tree) for this sequential file. Each index block can
hold up to 8 (value, pointer) entries (where the pointer can identify either a block or a record).
Index entries are sorted by Id. Index blocks are stored contiguously so we use binary search to
look for a key in the index. (We know the address of the first block and the number of index
blocks, so we can probe the block in “the middle” at each step.)
Q93. Suppose we construct a dense index INDEX1 on E.id, and we search for a record with
a given id (Assume that the record with the given id exists). In the worst-case scenario,
how many blocks must the system read to retrieve the record? _________
Answer: 129
Solution:
1024 records in emp table and 1 index block contain 8 entries
1024
 = 𝑙𝑜𝑔2 128 + 1 index block =8
8
So, to read total access. 8+1= 9 data
Answer: 129 blocks
Q94. Suppose to improve access speed, we add a sparse index INDEX2 on INDEX1. After
building INDEX2 we want to find a record with a given id. In the worst-case scenario,
how many blocks must the system read to retrieve the contents of the (existing)
record? ______
Answer:7
Solution:
Index 1 on,Index 2
128
128 in index 2 now this = 16 blocks at index 1.
8
For index 2 = 16 blocks = 𝑙𝑜𝑔2 16 + 1 = 5

Index 2 = 1block =1
and 1 data block =1
Total 7 blocks

Q95. Suppose the search field is V = 9 bytes long, the disk block size is B = 512 bytes, a
record (data) pointer is Pr = 7 bytes, and a block pointer is Pb = 6 bytes. Each B-tree
node can have at most p tree pointers, p - 1 data pointers, and p - 1 search key field
values. These must fit into a single disk block if each B-tree node is to correspond to a
disk block. Then the largest possible value of p is____________
Answer:24
Solution:
B(n-1) keys +n*block pointer + (n-1) *record pointer
512+(n-1)9 +n*6+(n-1)7
5129n-9+6n+7n-7
528
n= = 24
22
n=24
Answer:24
unordered on a key field, and the file organization is unspanned. If the B-Tree access
structure is built on the key field of the file, then the total number of blocks required by
the B-Tree access structure is ______________
Answer: 14288
Solution

unordered on a key field, and the file organization is unspanned. If the B+-Tree access
structure is built on the key field of the file, then the total number of blocks required by
the B+-Tree access structure is ______________
Answer: 23810
Solution
Q98. A database system maintains dense B-trees index on key field. Assume the following:
 Block size: 4096 bytes.
 Database record size: 300 bytes.
 Block pointer: 10 bytes
 Record pointer: 12 bytes.
 Key field: 8 bytes.
 The B-tree nodes are approximately 85% occupied.
 The database has 1,000,000 rows
How many blocks will B-tree index (excluding data block) use? __________
Answer: 8696
Solution
Order of B- tree:-
n*c+(n-1)r+(n-1)k = 4096
n*10+(n-1)+(n-1) *6+4096
=10n +12n -12+8n-8<4096
30n-20 137
85
n= 137* = 116.67
100
n=116
115 keys in 1 node
1= 115keys
(13340 keys) = 115*116 keys (nodes) = 116
115*116*116 keys 116*116 nodes
but 3rd level contain keys >106
106 -13340+115=986545 keys left and 1 block contain 115 keys
916545
hence block require =
115
last level
8579+116+1= 8696 blocks
Answer: 8696
Q99. Consider a B+ tree with order of non-leaf node is n and leaf node are n – 1 that has a
depth L > 1 (The depth of a node is the number of edges from the node to the tree's
root node and the depth of the root is 0). What is the maximum number of record
pointers the B+ tree can contain?
(a) nL × (n + 1) (b) n L – 1 × (n + 1)
(c) n L – 1 × (n – 1) (d) n L × (n – 1)
Answer: D
Solution
Q100. Consider a B+ tree with order of non-leaf node is n and leaf node are n – 1 that has a
depth L > 1 (The depth of a node is the number of edges from the node to the tree's
root node and the depth of the root is 0). What is the minimum number of record
pointers the B+ tree can contain?
𝑛 𝐿 𝑛 −1 𝑛 𝐿 𝑛 −1
(a) 2 x × (b) 2 × ×
2 2 2 2
𝑛 𝐿−1 𝑛 −1 𝑛 𝐿−1 𝑛 −1
(c) 2 × × (d) 2 × ×
2 2 2 2

Answer: C
Solution
Q101. Construct a B+-tree of order 3 for the following set of key values: 2, 3, 5, 7, 11, 17, 19,
23, 29, and 31. Assume that the tree is initially empty and values are added in
ascending order. The total number of nodes in the B+-trees for the cases where the
number of pointers that will fit in one node is three? ______
Answer: 16
Solution:
2,3,5,7,11,17,19,23,29,31

Answer:16
Q102. Construct a B+-tree of order 4 for the following set of key values: 2, 3, 5, 7, 11, 17, 19,
23, 29, and 31. Assume that the tree is initially empty and values are added in
ascending order. The total number of nodes split in the construction of B+-trees for the
cases where the number of pointers that will fit in one node is four? ____________
Answer: 8
Solution

2 3 5 7
= 8 nodes
Answer: 8
Q103. Suppose you have a B+-tree with 3 levels (root at level 1) in which each node has
exactly 10 keys. There is a record for each key 1, 2, 3, . . ., N, where N is the number of
records. How many nodes must be examined to find all records with keys in the range
[95, 134]? _________
Answer: 7
Solution
Q104. Consider the B+-tree index shown in the figure below. Each intermediate node can
hold up to five pointers and four key values. Each leaf can hold up to four records, and
leaf nodes are doubly linked as usual.

How many nodes that must be fetched to answer the following query: “Get all records
with search key greater than 38.”? ___________
Answer: 9
Solution:
I1I2L2, L3, L4 , L5, L6,L7, L8
9 nodes.
Answer: 9
Q105. Consider the B+-tree index shown in the figure below. Each intermediate node can
hold up to five pointers and four key values. Each leaf can hold up to four records, and
leaf nodes are doubly linked as usual.
What is the maximum number of keys you could insert that would NOT change the
height of the tree more than 1? __________
Answer: 82
Solution
4 keys
 5 children 4 keys at 1st level.
2nd 4*5 = 20 keys
4*5*5 = 100
B+ total node are at leaf node = 100 maximum
out of 100 ; 18 are present.
100-18 =82
Answer: 82

Answer: 82
Q106. [MSQ]
Which of the following statements is/are incorrect?
(a) In ordered index, entries in the index file are stored on the search-key value.
(b) Clustering index is an ordered index whose search key defines the sequential order
of the file.
(c) An ordered sequential file with a primary index is called an index-sequential file
(d) The search key value must be the primary key of the file
Answer: (a), (c) and (d)
Q107. [MSQ]
Which of the following statements about multilevel index is/are correct?
(a) If the primary index does not fit in memory, access becomes expensive.
(b) Outer index should be a sparse index of primary index.
(c) Indices at all levels are not necessarily updated on insertion or deletion from the
file.
(d) We can have a primary index with an index record for each search-key value.
Answer: A,B,C,D
Solution
Q108. A database administrator add indexes to a table that is accessed frequently by

applications. A clustered index delivers better performance than an unclustered index,
but most indexes created by administrator are unclustered. Why?
(a) Because clustered indexes take more space than unclustered indexes
(b) Because a relation can have only one clustered index, but many unclustered
indexes.

(c) Because clustered indexes will slow down updates
Answer: B
Solution
Q109. Which of the following statements is/are true for a B+-tree of order m containing n
items?
(a) The depth of the B+-tree is O(logmn) and this bounds the total number of disk
seeks.
(b) A node contains a maximum of m - 1 keys, and this bounds the number of disk
seeks at each level of the tree.
(c) Every Binary Search Tree is also an order 2 B+-Tree.
Answer: A
Solution
Q110. If the SQL statement SELECT C1, C2, C3 FROM T4 WHERE C2='Smith' is frequently
executed, which column(s) should be considered for indexing based only on the
statement itself?

(a) C1 only (b) C2 only (c) C3 only (d) C1, C2, and C3
Answer: B
Solution
Q111. If the SQL statement SELECT R1.A, R2.B FROM R1, R2 WHERE R1.K = R2.F AND
R2.K = 10 is frequently executed, which indexes will prove most useful?
(a) Index on R1.K and index on R2.K
(b) Index on R1.A and index on R2.B
(c) Index on R1.K and index on R2.F
(d) Composite index on (R2.K, R2.F)
Answer: A
Solution
Q112. Consider a relation R (a, b, c, d) containing 1,000,000 records, where each page of the
relation holds 10 records. R is organized as a heap file with dense secondary indexes,
and the records in R are randomly ordered. Assume that attribute a is a candidate key
for R, with values lying in the range 0 to 999,999. Consider the list of approaches in
LIST - I and list of queries in LIST - II:
LIST - I LIST - II
1. Scanning through the whole heap i. Find all R tuples.
file for R. ii. Find all R tuples such that a < 50.
2. Using a B+ tree index on attribute iii. Find all R tuples such that a = 50.

R.a. iv. Find all R tuples such that a > 50 and
3. Using a hash index on attribute R.a. a < 100
Match the approach that would most likely require the fewest I/Os for processing the
queries.
(a) i – 1, ii – 2, iii – 3, iv – 2 (b) i – 1, ii – 2, iii – 3, iv – 3
(c) i – 3, ii – 2, iii – 3, iv – 2 (d) i – 2, ii – 2, iii – 3, iv – 3
Answer: A
Solution
Q113. Suppose that the following keys, with the given hash values, are inserted into an
initially empty table of size 7 using linear-probing hashing.
Key: A B C D E F G
Hash: 3 5 3 4 5 6 3
Select the line on the list below that could not possibly result from inserting these keys,
no matter in which order they are inserted.
(a) B D F A C E G (b) C G B A D E F
(c) F G B D A C E (d) G E C A D B F
Answer: C
Solution
a) Using order ACEGBDF we get:
B 0
D 1
F 2
A 3
C 4
E 5
G 6
i.e. (a)
Using order ADEFCGB we get seq(b).

Using order ADBFGEC we get sequence (D)
Answer: C
Q114. Linear-probing hash table of length 10 uses the hash function h(x) =x mod 10 . After
inserting six integer keys into an initially empty hash table, the array of keys is:
0 1 2 3 4 5 6 7 8 9
42 23 34 52 46 33
Assume that the length of the hash table does not change during the insertions. Which
of the following choice(s) are insertion sequences resulting in the above hash table?
(a) 46, 42, 34, 52, 23, 33 (b) 34, 42, 23, 52, 33, 46
(c) 46, 34, 42, 23, 52, 33 (d) 42, 46, 33, 23, 34, 52
Answer: C
Solution
Q115. The hash function h(k) = k mod 7 and linear probing are used to insert keys < 37, 38,
72, 48, 98, 11, 56 > into the hash table with indices 0…6. The order of the keys in the
table are?
(a) 72, 11, 37, 38, 56, 98, 48, (b) 11, 48, 37, 38, 72, 98, 56
(c) 98, 11, 37, 38, 72, 56, 48 (d) 98, 56, 37, 38, 72, 11, 48
Answer: D
Solution

Q116. Suppose that the following keys are inserted into an initially empty linear-probing
hash table, but not necessarily in the order given
and it result in the following hash table
Assuming that the initial size of the hash table was 7 and that it did not grow or
shrink, how many possible keys from the given keys which could be the last inserted
key? ___________
Answer: 3
Solution

Q117. A certain hash function h(x) on a key field places records with the following key fields:
62 79 81 12 54 97 34
into a hash table. Collisions are handled with a secondary hash function r(x), which
takes as an argument the result of applying h(x). If the key values are entered in the
order shown to produce the following table:
Then h(x) and r(x) are, respectively,

(a) key % 20, (result + 13) % 20 (b) key % 20, (result +14) %20
(c) key % 20, (result + 7) % 20 (d) key % 30, (result + 7) % 30
Answer: B
Solution
Q118. Suppose many items for a hash table are initially hashing such that the first two hash
to index i, the next two to index i + 1, the next two to index i + 2, etc. Which
implementation approach or approaches are likely to lead to very poor performance?
(a) Separate chaining
(b) Open addressing with linear probing.
(c) Open addressing with quadratic probing.
(d) Open addressing with double hashing.
Answer: (b)
Solution:

A B C D E F
Hash i i i+1 i+2 i+3 i+4 i+5 i+6 i+7
A B C D E F
i i+1 i+2 i+3 i+4 i+5 i+6 i+7
In linear probing all value except first the will collide in the table. and it performs
worst.
Answer:B
Q119. Consider the following schedule

S: r2(x); r3(y); w3(x); r3(x); r1(z); r2(y); w3(y); r1(z); w2(y); c1; c2; c3
Which of the following concurrency problem(s) is/are exists in S?
(a) Unrepeatable read (b) Dirty read
(c) Lost Update (d) Phantom read
Answer: (c)
Solution
So, in this schedule T3 update y and then T2update y. There is no reading of y between
w3(y) and w2(y). Therefore, update of T3 is overwritten by the update done by T2. So,
this schedule has lost update concurrency problem.
Correct answer is (c)
Data for the next two questions. Consider the following schedule of reads and writes to
pages.
And a reference graph is provided for your convenience. The dotted edges represent all
possible edges; each edge is labelled with the transaction IDs of the nodes it connects, in order.
e.g., the edge fromT4 → T1 is labelled 41.

Q120. What edges are included in the precedence graph for the above schedule?
(a) 12, 43, 14, 24 (b) 21, 34, 41, 42
(c) 21, 42, 14, 43 (d) 12, 24, 14, 43
Answer: (b)
Solution:
21, 34, 41, 42. 21 comes from the RW conflict at time steps 2 and 7. I comes from the
RW conflictat time steps 4 and 6. J comes from the WR conflict at time steps 6 and 8.
K comes from the WRconflict at time steps 6 and 9.
Q121. Which of the following serial schedules are conflict equivalent to the schedule above?
(a) T4, T2, T1, T3 (b) T3, T2, T4, T1
(c) T3, T4, T2, T1 (d) T1, T2, T4, T3
Answer: (c)
Solution:
The graph is acyclic, so it is conflict serializable. Topologically sorting the abovegraph
gives this ordering.
Q122. Consider the transactions T1, T2, and T3 and the schedules S1 and S2 given below.
T1: r1(X); r1(Z); w1(X); w1(Z)
T2: r2(Y); r2(Z); w2(Z)
T3: r3(Y); r3(X); w3(Y)
S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z)
S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z)
Which one of the following statements about the schedules is TRUE?
(a) Only S1 is conflict-serializable.
(b) Only S2 is conflict-serializable.
(c) Both S1 and S2 are conflict-serializable.

(d) Neither S1 nor S2 is conflict-serializable.
Answer: A
Solution
precedence graph of S1 .
As here no cycle in precedence graph .
 S1 is conflict serializable.
S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z);r2(Z); w3(Y); w1(X); w2(Z); w1(Z)
There exist a cycle .

not conflict serializable.
S1 is conflict serializable but S2 is not conflict serializable.
Answer:A
Q123. [MSQ]
Which of the following is/are conflict serializable schedules?
(a) r1(x); r2(x); w1(x); r3(x); w2(x);
(b) r2(x); r1(x); w2(x); r3(x); w1(x);
(c) r3(x); r2(x); r1(x); w2(x); w1(x);
(d) r2(x); w2(x); r3(x); r1(x); w1(x);
Answer: D
Solution
S1: r1(x) r2(x) w1(x) r3(x) w2(x)

Not conflict serializable as there exist a cycle in precedence graph.
S2: r2(x) r1(x)w2(x) r3(x) w1(x)
not conflict serialzable .

S3:
Not conflict seriablizable.

S4: r2(x), w2(x) r3(x) r1(x)w1(x)
Conflict serializable.
Answer:D
Q124. [MSQ]
Given the following two schedules:
S1: r1(x); r2(x); w2(x); r3(x); w1(x); w2(y); r3(y); w3(x)
S2: r2(x); r1(x); w1(x); w2(x); w2(y); r3(x); w3(x); r3(y)
(a) S1 and S2 are conflict equivalent.
(b) S1 and S2 are view equivalent.
(c) Both S1 and S2 are conflict serializable
(d) Both S1 and S2 are view serializable
Answer: B
Solution

S1:r1(x), r2(x) w2(x) r3(x), w1(x) w2(y) r3(y),w3(x)
Not conflict serializable as there exist a cycle

S2: r2(x) r1(x) w(x) w2(x)r3(x) w3(x)r3(y)
Precedence graph of S1 precedence graph of S2.

As not same  not conflict equivalent.
Here S1 and S2 are view equivalent.
As here the read write sequence of t1and T2 and T3 is same in both the schedule and
final value of x and y is also being read and write in same order in T1 and T2.
Answer:B
Q125. Consider the following schedule S:

Time T1 T2 T3 T4
t0 Read(A);
t1 Write(A);
t2 Read(B);
t3 Read(B);
t4 Write(C);
t5 Write(C);
t6 Read(C);
t7 Write(A);
t8 Write(B);
t9 Commit;
t10 Commit;
t11 Commit;
t12 Commit;
How many conflict equivalent serial orderings is possible for the above schedule? ____
Answer:3

Solution
R2(A) ,w1(A),r2(A) , r2(B),r3(B) w2(C) ,w3(C), r4(C), w1(A),w4(B)
3 schedule are possible.

T2T3T4T1
T2T1T3T4
T2T3T1T4
Answer:3
Q126. Consider the following partial Schedule S involving two transactions T1 and T2.
Time T1 T2
t0 read(A);
t1 write(A);
t2 read(C);
t3 write(C);
t4 read(B);
t5 write(B);
t6 read(A);
t7 Commit;
t8 read(B)
Suppose that the transaction T1 fails immediately after time instance 8. Which one of
the following statements is correct?
(a) T2 must be aborted and then both T1 and T2 must be re-started to ensure
transaction atomicity
(b) Schedule S is non-recoverable and cannot ensure transaction atomicity
(c) Only T2 must be aborted and then re-started to ensure transaction atomicity
(d) Schedule S is recoverable and can ensure atomicity and nothing else needs to be
done.
Answer: B
Solution
T1 get fails after T8.
Here T1 is uncommiteed at t1 time and write A and T2 read A at t6 and get commit
hence here uncommitted transaction changes get read by other transaction and get
committed itself hence non- recoverable.
T1 get failed hence not atomic.
Answer:B
Q127. Consider the following two transactions T1 and T2 involving data items A and B. The

values of A and B are initially 100 and 200 respectively.
T1: read(A); read(B); B = A + B; write(B);
T2: read(B); read(A); A = A − B; write(A);
Any non-serial interleaving of transactions T1 and T2 allowing concurrent execution
will lead to a serial that is
(a) Conflict equivalent to only T2, T1
(b) Conflict equivalent to only T1, T2
(c) Conflict equivalent to both the serial schedules
(d) Conflict equivalent to none of the serial schedules
Answer: D
Solution
D is correct. Both serial schedules T1 → T2 and T2 → T1, In the very first step one
transaction reads the value written by other transaction so any non-serial interleaving
will never lead to conflict
serializable
Q128. [MSQ]
Consider the following schedule
Time T1 T2 T3
t0 Write(A);
t1 Read(B);
t2 Read(B);
t3 Write(A);
t4 Read(B);
t5 Write(C);
t6 Commit;
t7
t8 Write(B);
t9 Commit;
t10 Commit;
Which of the following statements is/are true regarding to above schedule?
(a) This schedule is possible under basic two-phase locking.
(b) This schedule is possible under strict two-phase locking.
(d) This schedule is possible under time stamp protocol.
(c) This schedule is free from cascading roll backing.
Answer: A
Solution
Time T1 T2 T3

X(A)
t0 Write(A);
S(B) X(C)
Read(B);
t1
unlock(A)
S(B)
t2
Read(B);
X(A) X(B)
Write(A);
t3
Read(B)
unlock
t4 Read(B);
Write(C);
t5
unlock
t6 Commit;
t7
X(B)
t8
Write(B);
t9 Commit;
t10 Commit;
Possible under 2PL but not in strict all X-lock unlock after the commit only  can‟t
possible in strict 2PL.
here uncommitted changes are not read write by any transaction .free from case cade
rollbacking.
Answer: A
Q129. For the schedule S given below, if transaction T1 aborts after the last operation of
schedule S, then which of the following statements will be true?
S: r1(x); r2(z); w1(x); r3(x); r2(y); w2(y); w3(x); r3(y); r2(x)
(a) Only T3 will be rolled back.
(b) First T2 will be rolled back followed by T3 rollback.
(c) First T3 will be rolled back followed by T2 rollback.
(d) There will be no cascading rollbacks.
Answer: C
Solution
S: r1(x); r2(z);w1(x); r3(x); r2(y);w2(y);w3(x); r3(y); r2(x)
After this T1 get abort.
hence first T3 is to be rollback followed by T2. as change of T1 is read by T2 .
Answer:C
Q130. [MSQ]

Which of the following schedule is/are possible under basic 2PL?
(a) r1(A), w1(A), w2(B), w3(C), r1(D), w2(D);
(b) r3(A), w4(B), r1(C), r3(D), w3(B), w2(D), r3(A), w1(D), r3(B), r2(C), r1(A);
(c) r1(A), r2(B), w2(B), w1(A), c1, r2(A) w2(A), c2;
Answer: (a), (b), (c)
Solution:
S1:r1(A), w1(A), w2(B), w3(C), r1(D), w2(D)
T1 T2 T3
X(A)
r1(A)
X(B)
S(D)
unlock
A,B
X(B)
w2(B)
X(C)
w3( C)
r1(D)
unlock
D
X(D)
w2(D)
possible under basic 2PL.

S2: r3(A), w4(B), r1(C), r3(D), w3(B), w2(D), r3(A), w1(D), r3(B), r2(C), r1(A)
T1 T2 T3 T4
S(A)
r3(A)
X(B)
S(C) w4(B)
r1(C) unlock B
S(D)
r3(D)
X(B)
w3(B)
X(D)
w2(D)

S ( C) r3(A)
unlockD
X(D)
w1(D)
r3(B)
r2(D)
S(A)
r1(A)
Possible under basic 2PL.

S3: T1: r1(A), r2(B), w2(B), w1(A), c1, r2(A) w2(A), c2
T1 T2
X(A)
r1(A)
X(B)
r2(B)
w2(B)
w2(A)
commit
unlock A
X(A)
r2(B)
w2(B)
w1(A)
commit
unlock A
X(A)
r2(A)
w2(A)
commit
possible under 2PL.
Q131. Consider the following two transactions,

T1: r1(X); w1(Y); c1
T2: w2(Y); r2(X); c2
L-Si(X) indicates that Ti acquires a shared lock on X, L-Xi(X) indicates that Ti acquires

an exclusive lock on X, and Ui(X) indicates that Ti releases its locks on X. Which of the
following schedule is generated by both 2PL and Strict 2PL?
(a) L-X2(Y); L-S2(X); w2(Y); r2(X); U2(Y); U2(X); c2; L-S1(X); r1(X); U1(X); L-X1(Y);
w1(Y); U1(Y); c1
(b) L-S1(X); r1(X); L-X1(Y); w1(Y); U1(Y); L-X2(Y); U1(X); c1; w2(Y); L-S2(X); U2(Y);
r2(X); U2(X); c2
(c) L-S1(X); r1(X); L-X1(Y); U1(X); L-S2(X); w1(Y); U1(Y); c1; L-X2(Y); w2(Y); r2(X);
U2(X); U2(Y); c2
Answer: (c)
Solution:
(a) Is neither generated by both, (b) is generated by 2PL (c) is generated by both.
Q132. Conservative 2PL” is a variation of 2PL where transactions are required to lock all the
items before they start to access (both read and write) them. If any of the items cannot
be locked, the transaction does not lock any items and waits. Of the properties below,
check which ones are held by Conservative 2PL but not by 2PL:
(a) No conflicting actions in the schedule.
(b) No transaction aborts because of deadlock.
(c) Transactions that Ti reads from commit earlier than Ti
(d) none of the above
Answer: B
Solution
Q133. [MSQ]
Consider a schedule of three transactions, T1, T2, and T3 that access three database
elements, A, B, and C. The real time at which events occur increases down the page, as
usual. However, we have also indicated the timestamps of the transactions and the

read and write times of the elements. We assume that at the beginning, each of the
database elements has both a read and write time of 0. The timestamps of the
transactions are acquired when they notify the scheduler that they are beginning.
Notice that even though T1 executes the first data access, it does not have the least
timestamp. Presumably T2 was the first to notify the scheduler of its start, and T3 did
so next, with T1 last to start.
T1 T2 T3 A B C
200 150 175 RT = 0, WT = 0 RT = 0, WT = 0 RT = 0, WT = 0
r(B) RT = 200
r(A) RT = 150
r(C) RT = 175
w(B) WT = 200
w(A) WT = 200
w(C)
w(A)
(a) The schedule executes successfully under basic timestamp-based protocol.
(b) The schedule is not possible under basic timestamp-based protocol and the
transaction T1 needs to be rollback first.
(c) The schedule is not possible under basic timestamp-based protocol and the
(d) The schedule is not possible under basic timestamp-based protocol and the
Answer: C
Solution
Here this schedule run correctly upto w1(A)operation but when T2 request for w2(C).
The data item C‟s read and write timestamp will be checked.
RT of C = 175
WT of C =0
T of T2= 150
So it can‟t write C as read time stamp of C is 175 which is after 150 .
T2 needs to be rollback first.
So, The schedule is not possible under time stamp based protocol . T2 need to rollback
first.
Answer:C
Q134. Consider the following schedule under timestamp-based protocol, the protocol
allocates timestamps to transactions in the order of their starts. r1(A), r2(A), w1(B),
w2(B), What action the protocol will take for the last request?
(a) the request is accepted,
(b) the request is ignored,
(c) the transaction is delayed,
(d) the transaction is rolled back.
Answer: A
Solution
allocates timestamps to transactions in the order of their starts.
r2(A), co2, r1(A), w1(A)
What action the protocol will take for the last request?
(a) the request is accepted,
(b) the request is ignored,

(c) the transaction is delayed,
(d) the transaction is rolled back
allocates timestamps to transactions in the order St1: St2: St3
r1(A), w3(A), co3, r2(B), w2(A)
What action the protocol will take for the last request?
(a) The request is accepted,
(b) The request is ignored,
(c) The transaction is delayed,
(d) The transaction is rolled back
Answer: D
Solution
r1(A),w3(A),co3, r2(B),w2(A)
here timestamp of T3> timestamp of T2 .
w2(A) can‟t be granted . hence T2 needs to rollback.
Answer:D
Q137. Consider four transactions T1, T2, T3, and T4 operating on three data objects X, Y, and Z.
Let a Timestamp Ordering scheduler allow the following schedule of operations:
r1(X), r2(Y), w2(Z), r3(Z), r4(X), w4(Y), w3(Z), r1(Z), w1(Y). Let ts1, ts2, ts3, and ts4 be the
timestamps allocated by the Timestamp Ordering scheduler to the four transactions T1,
T2, T3, and T4, respectively. Assume that the timestamps of X, Y, and Z are at or below
60 before the above schedule of operations is executed. identify in the list below the set
of timestamp values for the four transactions that will allow the schedule above.
(a) ts1 = 110, ts2 = 120, ts3 = 80, ts4 = 95.
(b) ts1 = 110, ts2 = 80, ts3 = 95, ts4 = 90.
(c) ts1 = 125, ts2 = 70, ts3 = 130, ts4 = 80.
(d) ts1 = 125, ts2 = 130, ts3 = 70, ts4 = 80.
Answer: C
Solution

Q138. [MSQ]
Consider a multi-granularity locking system, with lock modes S, X, IS, IX, SIX. The
object hierarchy is as follows: There is a root R, with blocks A and B under it. Block A
contains records a1 and a2, while block B contains records b1 and b2. Given below the
pair of locks identify the pair which are compatible. If a lock is on something other
than the root, assume that other suitable locks on parents have been taken, and
consider compatibility with those other locks as well. For example, S on B is
considered incompatible with X on b1, since the X on b1 will require IX on B. Which of
the following below condition(s) is/are possible?
(a) S on b1, X on B (b) IX on R, IX on R
(c) SIX on A, X on a2 (d) IS on A, S on a1
Q139. Consider two transactions that simultaneously attempt to acquire locks on object Z.
The types of locks requested are given in four options below. Assume that each
transaction has requested the appropriate lock on Z's parent in the hierarchy. Which of
the following case(s) below, write the transactions can obtain the lock at the same

time?
(a) Requested lock types are: SIX, SIX.
(b) Requested lock types are: IX, IX.
(c) Requested lock types are: IX, X.
(d) Requested lock types are: S, IS.
Q140. Consider the following lock requests in Table given below. And note that
 S(_) and X(_) stand for 'shared lock' and 'exclusive lock', respectively.
 T1, T2 and T3 represent three transactions.
 LM stand for `lock manager'.
For the lock requests in above Table, which lock will be blocked by the lock manager?
(a) t1, t3, t4 (b)t2, t5 (c)t6, t7 (d) t3, t5
Q141. [MSQ]
(a) Wound-wait always outperforms wait-die.
(b) If we know all records that each transaction will access, we can prevent deadlocks
by
imposing a total order on lock acquisitions.
(c) Wait-die always outperforms wound-wait.
(d) In general, locking uses fewer resources than optimistic methods that rely on
validation.
Answer: B,D
Solution

Q142. In wait-die scheme, transactions T1 and T2 have timestamps 10 and 15 respectively. If
T2 requests a data item held by T1 then
(a) T2 will be rolled back (b) T2 will wait
(c) T1 will be rolled back (d) T1 will wait
Answer: (a)
Explanation: In wait-die scheme, when transaction Ti requests a data item currently
held by T j and timestamp of Tjis smaller than that of Ti, then Tiis rolled back. Thus,
when T2 requests a data item held by T1,then T2 will be rolled back.
Q143. In a wound-wait scheme, transactions T1 and T2 have time stamp 2 and 5 respectively.
If T1 requests a data item held by T2, then
(a) T1 will be rolled back. (b) T1 will wait
(c) T2 will be rolled back (d) T2 will wait
Answer: (c)
Explanation:In wound-wait scheme, when transaction Ti requests a data item
currently held by Tj and time stamp of Tj is greater than that of Ti, then Tj is rolled
back. Thus, when T1 requests a data item held by T2, then T2 will be rolled back.
Q144. Consider the following statements based on the wait die scheme for deadlock
prevention:
1. Older transaction may wait for younger one to release data item. (older means
smaller timestamp).
2. Older transaction forces rollback of younger transaction instead of waiting for it.
Younger transactions may wait for older ones.
3. A transaction may die several times before acquiring needed data item.
4. It is non-preemptive scheme.
5. It is preemptive scheme.

The group of statements which are correct is
(a) 1, 3, 4 (b) 2, 5
(c) 1, 3, 5 (d) 2, 3, 4
Answer: (a)
Explanation:
Wait-die scheme: It is a non-preemptive technique for deadlock prevention. When
transaction Ti requests a data item currently held by Tj, Ti is allowed to wait only if it
has a timestamp smaller than that of Tj (That is Ti is older than Tj), otherwise Ti is
rolled back (dies). For example, Suppose that transaction T22, T23, T24 have time-
stamps 5, 10 and 15 respectively. If T22requests a data item held by T23 then T22 will
wait. If T24 requests a data item held by T23, then T24 will be rolled back.
Correct answer is (a)
Q145. Two transactions T1 and T2 are given as
T1: r1(X)w1(X)r1(Y)w1(Y)
T2: r2(Y)w2(Y)r2(Z)w2(Z)
The total number of interleaving that can be formed by T1 and T2 is ___________
Answer: 70
Solution
Total number of interleaving that can be formed by T1 and T2.
4+4 ! 8∗7∗6∗5∗4!
= =
4!4! 4!∗4!
Answer:70

T1: r1(X)w1(X)r1(Y)w1(Y)
T2: r2(Y)w2(Y)r2(Z)w2(Z)
The total number of concurrent schedules which are conflict serializable formed by T1
and T2 is______
Answer: 52
Solution
There are four cases:
Case 1: r2(y) r1(x)w1(x)r1(y)w1(y)
(a)r2(y) w2(y)w1(x) w1(x) r1(y) w1(y)
To insert r2(z) w2(z).

6!
The total number of ways = =15
2!∗4!
(b)r2(y)r1(x)w2(y)w1(x)r1(y)w1(y)
5!
To inset r2(z),w2(z) the total number of ways = =10
3!∗2!
( c) r2(y) r1(x) w1(x) r1(y)w1(y)
4!
To inset r2(z) ,w2(z) the total number of ways = =6
2!∗2!
𝑟1(𝑥)
Case II:-
𝑓𝑖𝑥𝑒𝑑
(a) r1(x) r2(y)w2(y)w1(x)
To insert r2(z),w2(z)
3!
Number of ways = = 10
2!∗3!
(b) r1(x)r2(y)w1(x)w2(y)
for r2(z),w(z) total number of ways.
4!
= =6
2!∗2!
( c) r1(x) w1(x) r2(x) w2(y)....r2(y)w2(y)
4!
To insert r2(z), w2(z) . Total number of ways =
2!∗2!
=6
The total number of conflict serializable schedule
= 15+10+6+610+6+6= 53
Which contain one serial schedule T2.T1.
Required number of ways = 53-1
(Concurrent schedule) =52
Answer:52

T1: r1(A)w1(A)r1(B) w1(B)
T2: r2(B)w2(B)r2(A) w2(A).
The total number of concurrent schedules which are conflict serializable formed by T1
and T2 is______.
Answer: 0
Solution
There doesn‟t exist any current conflict serializable schedule.
Answer:0
Q148. Three transactions T1, T2 and T3 are given as
T1: r1(A)w1(A)r1(B)w1(B)
T2: r2(B) w2(B)r2(C)w2(C).
T3: r3(C)w3(C)r3(D)w3(D)
The total number of concurrent schedules which are conflict serializable formed by T1,
T2 and T3 are______.
Answer: 0

Solution
Q149. [MSQ]
How many of the following statements is/are TRUE?
1. For any schedules S1 and S2, if S1 and S2 are conflict serializable, then S1 and S2 are
conflict equivalent.
2. A SIX lock is compatible with IS and IX locks, i.e. if a transaction holds a SIX lock
on an object, then another transaction can take an IS or IX lock on the same object.
3. An IX lock is compatible with an IS lock, i.e. if a transaction holds an IS lock on an
object, then another transaction can take an IX lock on the same object.
4. Strict 2PL prevents deadlocks.
5. In timestamp-based concurrency control, if a transaction gets aborted, it will be
restarted with a new timestamp.
(a) 1 (b) 2
(c) 3 (d) 4
Answer:B
Solution

Q150. Which of the following prevent(s) deadlock?
(a) Rigorous 2-phase locking protocol
(b) Conservative 2-phase locking protocol
(c) Both rigorous and conservative 2-phase locking protocols
(d) None of rigorous and conservative 2-phase locking protocols
Answer: (b)
Deadlock can only be prevented by Conservative 2-Phase Locking Protocol
Q151. [MSQ]
Which of the following statements is/are TRUE?
(a) Some conflict serializable schedules cannot be produced when using 2PL.
(b) Schedules that are conflict serializable will not produce a cyclic dependency graph.
(c) Both Strict 2PL and 2PL enforce conflict serializability.
(d) In Strict 2PL, we can give up locks after aborting but before rollback is complete
Answer: (a), (b), and (c)

Solution:
For (a), 2PL enforces conflict serializability but may not allow all conflict serializable
schedules (e.g., W1(X), R2(X), W1(Y), R2(Y) is impossible under 2PL). For (b), a
schedule is conflict serializable if and only if the dependency graph is acyclic, so
conflict serializable implies an acyclic dependency graph. For (c), because you cannot
get any new locks after releasing a lock in both strict and non-strict 2PL, the
dependency graph for the resulting schedule can never be cyclic. Only (d) is false. For
(d), you must wait until rollback is complete before giving up locks
Q152. [MSQ]
Consider two schedules S1 and. S2 of the same set of transactions. Suppose we know
that S1 is conflict serializable, and the precedence graphs of the two schedules are the
same, i.e., P(S1) = P(S2). Which of the following claims about S2 is/are TRUE?
(a) S2 is a serial schedule. (b)S2 is conflict equivalent to S1.
(c) S2 is conflict serializable. (d) None of the above
Answer: C
Solution
Q153. [MSQ]
Which of the following statements is /false?
(a) Blind writes appear in every view-serializable schedule that is not conflict
serializable.

(b) In serialization graph, an edge between two nodes exists if and only if the pair of
transactions corresponding to the nodes have conflicting operations.
(c) Every strict schedule is recoverable.
(d) Every view serializable schedule is recoverable.
Answer: D
Solution
Every view serializable schedules
recoverable.
Answer:D
Q154. [MSQ]
Which of the following statements is/are TRUE?
(a) Serializability ensures correctness of a schedule.
(b) A conflict serializable schedule is always equivalent to one and only one single
serial schedule.
(c) Every cascade-less schedule is recoverable.
Answer: C
Solution
A conflict serializable schedule is always equivalent to one and only one single serial
schedule –False.
not one may be more than one.
Answer:C
Q155. [MSQ]
(a) Under 2PL protocol, once a transaction releases a lock, it can no longer acquire any
new locks.
(b) Every conflict serializable can be produced by 2PL locking protocol.
(c) Schedules under 2PL are guaranteed to prevent cascading aborts.
(d) Strict two-phase locking is both necessary and sufficient to guarantee conflict
serializability.
Answer: (a)
Solution:
(a) Under 2PL protocol, once a transaction release a lock, it can no longer acquire a
lock- True
(b) Every conflict serializable can be produced by 2PL locking protocol- False.
(c) Schedules under 2PL are guaranteed to prevent cascading abort- False
(d) Strict two-phase locking is both necessary and sufficient to guarantee conflict

serializable- False
Data for the next two questions. Consider a database with three elements, X, Y and Z. The
initial values of X, Y and Z are 0. We consider three transactions T1, T2 and T3 that modify
these elements concurrently:
• T1: X := 42
• T2: Y := 20, X := 10
• T3: X := 100, Z := 101
While the transactions execute, the database system crashes just after the last statement has
return. Consider the following log entries:
1. < START T2 >
2. < START T3 >
3. < T2, X, 10 >
4. < T2, Y, 20 >
5. < COMMIT T2 >
6. < START CKP T(T3) >
7. < T3, X, ??? >
8. < START T1 >
9. < T3, Z, ??? >
10. < T1, X, 42 >
11. < END CKP T >
12. < COMMIT T3 >
13. < START CKP T(T1) >
14. < COMMIT T1 >
Q156. If the pure redo logging (Deferred database modification) with log entry <txn, item,
after -val) is used then the missing value of line 7 and 9 is respectively
(a) 100, 101 (b) 42, 100 (c) 10, 100 (d) 10, 101
Answer: A
Solution
Q157. If the database crashes immediately after writing the above log entries, then

subsequently performs recovery, which of the following statements is true:
(a) After a successful recovery, the state of X is 100.
(b) At the time of crash, the state of X (on disk) must be 42.
(c) At the time of crash, the state of Y (on disk) must be 20.
(d) After a successful recovery, the state of Z is 0.
Answer: C
Solution
Data for the next two questions. Consider a database with three elements, X, Y and Z. The
initial values of X, Y and Z are 0. We consider three transactions T1, T2 and T3 that modify
these elements concurrently:
• T1: X := 42
• T2: Y := 20, X := 10
• T3: X := 100, Z := 101
While the transactions execute, the database system crashes just after the last statement has

return. Consider the following log entries:
1. < START T1 >
2. < START T2 >
3. < START T3 >
4. < T3, X, 0, 100 >
5. < T3, Z, 0, 101 >
6. < COMMIT T3 >
7. < T2, X, 100, 10 >
8. < START CKP T(T1, T2) >
9. < T2, Y, ???, ??? >
10. < END CKP T >
11. < START CKP T(T1, T2) >
12. < COMMIT T2 >
13. < T1, X, ???, ??? >
Q158. If the undo redo logging (immediate database modification) with log entry <txn, item,
before-val, after -val) is used then the missing value of line 9 and 13 is respectively
(a) Line 9: 0, 20; Line 13: 10, 42 (b) Line 9: 20, 0; Line 13: 42, 10
(c) Line 9: 0, 20; Line 13: 10, 100 (d) Line 9: 20, 0; Line 13: 10, 42
Answer: A
Solution
Q159. [MSQ]
If the database crashes immediately after writing the above log entries, which of the
following statements is/ are true:
(a) During recovery, the log entry in line 4 will be redone.
(b) During recovery, the log entry in line 7 will be ignored.

(c) During recovery, the log entry in line 9 will be ignored.
(d) During recovery, the log entry in line 13 will be undone.
Answer: B,D
Solution
Q160. Assume a basic check pointing recovery protocol. Suppose the following schedule is
being run:
1. <START, T1>;
2. <T1, A, 120, 100>;
3. <CHECKPOINT>;
4. <COMMIT, T1>;
5. <START, T2>;
6. <T2, B, 150, 180>;
7. <START, T3>;
8. <T3, A, 100, 50>;
9. <START, T4>;
10. <T4, C, 300, 400>;
11. <T3, D, 300, 200>;
12. <COMMIT, T3>;
13. <T2, A, 50, 150>;
Suppose the schedule crashes after the last entry. What are the undo and redo lists in
the correct order?
(a) Undo List: T4, T2; Redo List: T1
(b) Undo List: T2, T4; Redo List: T3
(c) Undo List: T4, T2; Redo List: T1, T3
(d) Undo List: T4, T2; Redo List: T3

Answer: C
Solution
Q161. Which one of the following options does not follow deferred database modification
scheme in the system crash situation?
(a) After system crash if there is no “commit T” instruction in log file then it requires
nothing to recover
(b) After system crash all transactions are redone in the backward order of their log
record (i.e., last log record first)
(c) No undo operations are required in the deferred database scheme.
Answer: C
Solution
Q162. Consider the simple nested-loop join of the following two relations r and s.
Assuming the worst-case memory availability, i.e., the memory can hold only one

block of each relation at a time, what is the number of block transfers? _______
Answer: 60050
Solution:
Outer relation should be smaller;thus, s should be the outer loop and r the inner.
Transfers = bs + ns × br = 50 + 1500 × 40 = 60050
Q163. Consider the block nested-loop join of the following two relations r and s.
Assuming the worst-case memory availability, i.e., the memory can hold only one
Answer: 2050
Solution:
Outer relation should be smaller; thus, s should be the outer loop and r the inner.
Transfers = bs + bs × br = 50 + 50 × 40 = 2050
Data for the next two questions. Consider the following ER diagram:
Q164. Which attribute(s) can be the primary key of relationship set C?

(a) a and d (b) a, d, and f (c) a and f (d) a only
Answer: D
Solution
Many to one relation in which many has total participation we don‟t make table for
relationship and only the primary key of many is the primary key of relation C.
Answer: D
Q165. [MSQ]
(a) The relationship attribute “f” can be shifted to either entity set without losing any
information.
(b) The relationship attribute “f” can only be shifted to entity set A without losing any
information.
(c) The relationship attribute “f” can only be shifted to entity set B without losing any
information.

(d) The relationship attribute “f” cannot be shifted to either entity set without losing
any information.
Answer: B
Solution
Data for the next three questions. Consider the following ER diagram
Q166. How many tables will be created if we map this ER diagrams in to table? __________
Answer: 4
Solution
Here in question there is written that vehicle is super type and Truck and Bus is
subtype but Truck and Bus do not cover vehicle and Truck, Bus also not overlook .
We have to make table for super type and subtype.
3 tables are created.( vehicle, truck, bus)
Now driving _log is weak entity. we make one table for this using primary key of
vehicle. and generate is weak relationship.
 4 tables are created. vehicle, Bus, Truck, Driving_log.
Answer:4
Q167. Which of the following is correct relation for the entity set Driving_Log?
(a) Driving_Log (log_number, distance)
(b) Driving_Log (plate_number, log_number, distance)
(c) Driving_Log (plate_number, log_number, distance)
(d) Driving_Log (plate_number, log_number, distance)

Answer: C
Solution
Driving_log is a weak relation and have to make relation using Primary relation of
vehicle .i.e. we have to create partial key i.e (platemo. +log_no)
Drining _log (plate no, log_number, distance).
Answer:C
Q168. Which of the following is correct relation for the entity set Bus?
(a) Bus(max_passanger)
(b) Bus (plate_number, max_passanger)
(c) Bus (plate_number, max_passanger)
(d) Bus (plate_number, manufactured_year, max_passanger, purchased_year)
Answer: C
Solution
Bus here total generalization is not supertype and subtype both.
Bus will contain primary of vhicle and all its attribute.
Bus (plat_no, max passanger)
Answer:C
Q169. Consider the following SQL schema:

 CREATE TABLE Company (name VARCHAR(100) PRIMARY KEY, address
VARCHAR(200));
 CREATE TABLE Product (name VARCHAR(100) PRIMARY KEY, price FLOAT,
made_by VARCHAR(100) FOREIGN KEY REFERENCES Company);
Which of the following E/R diagrams could produce that schema?
Answer: D
Solution

Answer:D
Q170. Consider the following ER diagram
Which of the following statements is true based on the given ER diagram?

(a) Each <ssn,acct_number> pair can identify a unique branch_code.
(b) Each <ssn,branch_code> pair can identify a unique acct_number.
(c) Each <branch_code,acct_number> can identify a unique ssn.
Answer: A
Solution
Ternary relation:-
Here SSn, account number will provide the unique branch code.
Answer: A
Q171. Consider the binary relationship type BiologicalMother between entity types Person
and Woman. Suppose the cardinality ratio (Person : Woman) constraint of the
relationship is expressed using (min, max) notation as (u, v) on the line connecting
Person to BiologicalMother and (x, y) on the line connecting Woman to BiologicalMother,
which one of the following is correct:
(a) (u,v) = (1,1); (x,y) = (1, N)
(b) (u,v) = (1,N); (x,y) = (1, N)
(c) (u,v) = (1,1); (x,y) = (0, N)
(d) (u,v) = (1,N); (x,y) = (0, N)
Answer: C
Solution

Every person has their biological mother
Woman (0, N) because woman can be mother of more than 1 person and it is not
necessary every woman has child.
Correct answer is (c)
Q172. [MSQ]
Consider the following statements: Which of the following is/are true?
(a) An entity set E can be associated with a relationship set R more than once.
(b) At most 2 distinct entity sets can be associated with any relationship set R.
(c) A weak entity set must have total participation in its identifying relationship set.
Answer: C
Solution
Only 1 and 3 are true.
Answer:C
Which one of the following problem descriptions could have led to the ER model
above?
(a) We are modeling a volleyball tournament. A volleyball game is played by two
teams. Multiple referees make sure during a single game that the rules are
respected.
(b) A volleyball game in a tournament is played by two teams. A referee has to be
present at each game to make sure the rules are respected.

(c) Two teams play volleyball in a tournament. In each game, one has the role of
“home” team and the other one has the role of “away” team. Each play is
supervised by at most one referee.
Answer: A
Solution
I is false as multiple refers can‟t be there .
II is true .
III is false as atmost one refre (means 0 or 1) but 0 refree is not allowed. false
Answer:A
Data for the next two questions, Consider the following ER diagram:
Q174. How many tables will be created if we map this ER diagrams in to table? ________
Answer: 8
Solution
Following tables
(1)Reader (2)Borrows (3) Copy (4) Book (5)category (6)incat(7)publisher (8)contain
(Self join)in contain self join is there hence have to create table.
8 table
Answer:8
Q175. Which of the following is not the primary key of any table?

(a) (ISBN, CopyNr) (b) (ISBN, Catname)
(c) (ReaderNr, ISBN) (d) (ISBN)
Answer: C
Solution
(a)ISBN, Cop_Nr Primary key of copy
(b)ISBN, catname Primary key of incat.
(d)ISBN Primary key of book.
Answer:C
Q176. Consider an Entity-Relationship (ER) model in which entity sets E1 and E2 are
connected by an M : N relationship R12. E2 and E3 are connected by a 1 : N (1 on the
side of E2 and N on the side of E3) relationship R23. E1 has three single-valued
attributes a11, a12 and a13 of which a11 is the key attribute. E2 has two single-valued
attributes a21 and a22 of which is a21 the key attribute. E3 has two single-valued
attributes a31 and a32, and one multi-valued attribute of which a31 is the key attribute.
The relationships do not have any attributes. If a relational model is the derived from
the above ER model, then the minimum number of relations that would be generated
if all the relations are in 3NF is ________
Answer: 5
Solution

Q177. Consider the ER models 1 to 4 and the relational models (i) to (iv) given below. For
each ER model, there is a relational model that represents it correctly. Match the ER
models to it corresponding relational model.

(a) 1-iv, 2-i, 3-iii, 4-ii (b) 1-i, 2-i, 3-iv, 4-ii
(c) 1-ii, 2-iv, 3-iii, 4-i (d) 1-iv, 2-ii, 3-iii, 4-i
Answer: A
Solution
If a relational model is the derived from the above ER model, then the total number of
attributes in all relations that would be generated if all the relations are in 3NF is
______
Answer: 19
Solution
3 attribute in worker (Name, Persnr, stations)
3 attribute in doctor (PersNr,exprestise, Degree)
2in nurse (perNr, siklls)
2 in treats (persnr, patientNr)
6 in patient(Name, patientNr Room Nr from, To, illness)
3 in room/RoomNr ,Bed, station Nr)
19 attributes are there.
Answer:19
Q179. Consider the following ER-diagram

If a relational model is the derived from the above ER model, then the minimum
number of relations that would be generated if all the relations are in 3NF is ________
Answer: 6
Solution
 For every entity set, create table:
1. Employee
2. Dependent
3. Department
4. Project
 For every multivalued attribute, create table for that
5. Location
 For every many to many relation create table
6. Works_on
So, correct answer is 6.
Q180. Consider the following Entity-Relationship (ER) model:

If a relational model is derived from the above ER model and the minimum number of
relations generated is (x), and also the number of attributes in primary key of relation
enrolled_in is (y) if all the relations are in 3NF, then the value of x and y is
(a) 7, 4 (b) 7, 5 (c) 6, 3 (d) 6, 4
Answer: B
Solution
If a relational model is derived from the above ER model, then following relation will
be made:

So, The number of relation x = 7 and the primary key of relation enrolled_in will made
of the attributes (dname, number, section#, semester, sid). Therefore, number of
attributes in the primary key is y = 5.
Correct answer is (b)
Other than the FD's that can be derived by ER diagram one more FD hold
Program, course# ⟶ cname
If we translate this ER diagram into table highest normal form satisfied by the schema
is
(a) 1 NF (b) 2 NF (c) 3 NF (d) BCNF
Answer: A
Solution

Q182. Consider the following E-R Diagram:
How many of the following conclusion can be inferred from the above diagram?______
(a) Each account must be associated with a bank branch.
(b) Each customer must have an account.
(c) Each loan must be owned by more than one customer, and each loan must be
associated with exactly one bank branch.
(d) A bank can have more than one branch.
Each customer must have at least one loan.
Answer: 2
Solution


Advanced DBMS Sheet With Sloution_2022

Uploaded by

Copyright:

Available Formats

Advanced DBMS Sheet With Sloution_2022

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Advanced DBMS Sheet With Sloution_2022

Uploaded by

Copyright:

Available Formats

GATE Computer Science & IT

DATABASE MANAGEMENT SYSTEM GATE SYLLABUS

DBMS REFERENCE BOOKS

Q1. Consider the following relational query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 1

ADVANCED DATABASE MANAGEMENT SYSTEM Page 2

Q3. Consider the following relational query on the above database:

Q4. Consider the following relational query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 3

Q5. Consider the following relational query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 4

10>9 – False. , 10<9 – False.  Not exist will become true at 5.

Q6. Consider the following relational query on the above database:

Data for the next three questions.

ADVANCED DATABASE MANAGEMENT SYSTEM Page 5

Q7. Consider the following relational query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 6

 5 rows are there.

Q8. Consider the following relational query on the above database:

Brand name Cou

ADVANCED DATABASE MANAGEMENT SYSTEM Page 7

Brand name Count

Output has 4 rows.

Q9. Consider the following relational query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 8

Inner query‟s output where type is basket ball .

not in select 1 row only from table (1.2)

CREATE TABLE DEPARTMENT (DEPT_NO NUMBER, DEPT_NAME VARCHAR2(20),

ADVANCED DATABASE MANAGEMENT SYSTEM Page 9

Answer: (b) and (c)

ADVANCED DATABASE MANAGEMENT SYSTEM Page 10

ADVANCED DATABASE MANAGEMENT SYSTEM Page 11

ADVANCED DATABASE MANAGEMENT SYSTEM Page 12

ADVANCED DATABASE MANAGEMENT SYSTEM Page 13

ADVANCED DATABASE MANAGEMENT SYSTEM Page 14

Q16. Consider the following SQL query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 15

Q17. Consider the following SQL query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 16

Q18. Consider the following SQL query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 17

Q19. Consider the following SQL query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 19

ADVANCED DATABASE MANAGEMENT SYSTEM Page 20

ADVANCED DATABASE MANAGEMENT SYSTEM Page 21

ADVANCED DATABASE MANAGEMENT SYSTEM Page 22

Larry west is also printed so updated answer is 5.

ADVANCED DATABASE MANAGEMENT SYSTEM Page 23

Dept(did: integer, dname: string, budget: real, managerid: integer)

Works(eid: integer, did: integer, pct_time: integer)

ADVANCED DATABASE MANAGEMENT SYSTEM Page 24

ADVANCED DATABASE MANAGEMENT SYSTEM Page 25

Q27. Consider the following SQL query on the above database:

ADVANCED DATABASE MANAGEMENT SYSTEM Page 27

ADVANCED DATABASE MANAGEMENT SYSTEM Page 28

(b) SELECT DISTINCT tempD.managerid

ADVANCED DATABASE MANAGEMENT SYSTEM Page 29

ADVANCED DATABASE MANAGEMENT SYSTEM Page 30

ADVANCED DATABASE MANAGEMENT SYSTEM Page 31

ADVANCED DATABASE MANAGEMENT SYSTEM Page 32