: No:R2022052

Code R20 SET - 1

II B. Tech II Semester Regular Examinations, June/July - 2022
DATABASE MANAGEMENT SYSTEMS
(Common to CSE,CST,CSE(CS),CSE(IOTCSIBCT),CSE(CSBS),CSE(IOT),&CS)
Time: 3 hours Max. Marks: 70
Answer any FIVE Questions each Question from each unit
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~~
UNIT-I
1 a) Why would you choose a database system instead of simply storing data in [7M]
operating system files? When would it make sense not to use a database system?
b) Draw the generic architecture of database systems and briefly explain different [7M]
components in it.
OR
2 a) How database users are classified according to their roles? Write at least 5 functions [7M]
of database administrators.
b) Explain about different levels of abstractions. [7M]
UNIT-II
3 a) Define the following terms: relation schema, relational database schema, domain, [7M]
attribute, attribute domain, relation instance, and relation cardinality.
b) When are integrity constraints enforced by a DBMS? What is referential integrity? [7M]
Explain with examples.
OR
4 Consider the following relational schema. An employee can work in more than one [14M]
department; the pct time field 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)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, dname: string, budget: real, managerid: integer)
Write the following queries in SQL:
a) Print the names and ages of each employee who works in both the Hardware
department and the Software department.
b) For each 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.
c) Print the name of each employee whose salary exceeds the budget of all of
the departments that he or she works in.
d) Find the managerids of managers who manage only departments with
budgets greater than $1 million.
e) Find the enames of managers who manage the departments with the largest
budgets.
f) If a manager manages more than one department, he or she controls the sum
of all the budgets for those departments. Find the managerids of managers
who control more than $5 million.

1 of 3

|''|''|||''|'''|||'|
 : No:R2022052
Code R20 SET - 1

UNIT-III
5 a) Define the following terms with examples: Attribute, Composite Attribute, Multi [7M]
valued Attribute and derived attribute.
b) What are nested queries? What is correlation in nested queries? How would you use [7M]
the operators IN, EXISTS, UNIQUE, ANY, and ALL in writing nested queries?
Explain with examples.
OR
6 Answer each of the following questions based on the below relational schema: [14M]

Emp(eid: integer, ename: string, age: integer, salary: real)

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

a) Give an example of a foreign key constraint that involves the Dept relation.
What are the options for enforcing this constraint when a user attempts to delete
a Dept tuple?
b) Write the SQL statements required to create the preceding relations, including
appropriate versions of all primary and foreign key integrity constraints.
c) Define the Dept relation in SQL so that every department is guaranteed to have
a manager.
d) Write an SQL statement to add John Doe as an employee with eid = 101, age =
32 and salary = 15, 000.
e) Write an SQL statement to give every employee a 10 percent raise.
f) Write an SQL statement to delete the Toy department. Given the referential
integrity constraints you chose for this schema, explain what happens when this
statement is executed.
UNIT-IV
7 a) Define and explain about redundancy and the problems that it can cause. [7M]
b) Consider the schema R=(A,B,C,D,E) and the functional dependencies: A→BC, [7M]
CD→E, B→D, E→A. Give a lossless join decomposition into BCNF of the schema
R.
OR
8 a) Suppose that we have the following four tuples in a relation S with three attributes [7M]
ABC: (1,2,3), (4,2,3), (5,3,3), (5,3,4). Which functional and multivalued
dependencies hold over relation S?
b) Explain how to preserve functional dependencies during decomposition. [7M]

2 of 3

|''|''|||''|'''|||'|
 : No:R2022052
Code R20 SET - 1

UNIT-V
9 a) Consider a database with objects X and Y and assume that there are two transactions [7M]
Tl and T2. Transaction T1 reads objects X and Y and then writes object X.
Transaction T2 reads objects X and Y and then writes objects X and Y. Give an
example schedule with actions of transactions T1 and T2 on objects X and Y that
results in a write-read conflict.
b) What are ACID properties? Define with examples. [7M]
OR
10 a) What is an index? Discuss important properties of an index that affect the efficiency [7M]
of searches using the index.
b) Discuss in detail about multilevel indices. [7M]

3 of 3

|''|''|||''|'''|||'|
 : No:R2022052
Code R20 SET - 2
II B. Tech II Semester Regular Examinations, June/July - 2022
DATABASE MANAGEMENT SYSTEMS
(Common to CSE, CST, CSE(CS), CSE(IOTCSIBCT), CSE(CSBS), CSE(IOT), & CS)
Time: 3 hours Max. Marks: 70
Answer any FIVE Questions each Question from each unit
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~~
UNIT-I
1 a) Draw and describe the layered architecture of a DBMS. [7M]
b) What are the drawbacks in file systems for storing enterprise data? How database [7M]
systems overcome them?
OR
2 a) Explain the difference between logical and physical data independence. Discuss [7M]
their importance.
b) What is a data model? Briefly explain about different data models. [7M]
UNIT-II
3 a) What is the difference between a candidate key and the primary key for a given [7M]
relation? What is a superkey?
b) With an example for each, explain about arithmetic and logical operations in SQL. [7M]
OR
4 The following relations keep track of airline flight information: [14M]
Flights(flno: integer, from: string, to: string, distance: integer, departs: time, arrives:
time, price: real)
Aircraft(aid: integer, aname: string, cruisingrange: integer)
Certified(eid: integer, aid: integer)
Employees(eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as
well; every pilot is certified for some aircraft, and only pilots are certified to fly.
Write each of the following queries in SQL.
a) Find the names of aircraft such that all pilots certified to operate them have
salaries more than $80,000.
b) For each pilot who is certified for more than three aircraft, find the eid and
the maximum cruisingrange of the aircraft for which she or he is certified.
c) Find the names of pilots whose salary is less than the price of the cheapest
route from Los Angeles to Honolulu.
d) For all aircraft with cruisingrange over 1000 miles, find the name of the
aircraft and the average salary of all pilots certified for this aircraft.
e) Find the names of pilots certified for some Boeing aircraft.
f) Find the aids of all aircraft that can be used on routes from Los Angeles to
Chicago.
g) Identify the routes that can be piloted by every pilot who makes more than
$100,000.

1 of 2

|''|''|||''|'''|||'|
 : No:R2022052
Code R20 SET - 2
UNIT-III
5 a) Explain the process of identifying entities and their relationships from requirements [7M]
gathered. What kind of information needs to be avoided while doing so?
b) What are binary and ternary relationships? Explain with examples. Also explain [7M]
with an example, relationships that connect an entity to itself.
OR
6 A university database contains information about professors (identified by social [14M]
security number, or SSN) and courses (identified by courseid). Professors teach
courses; each of the following situations concerns the Teaches relationship set. For
each situation, draw an ER diagram that describes it (assuming no further constraints
hold).
a) Professors can teach the same course in several semesters, and each offering
must be recorded.
b) Professors can teach the same course in several semesters, and only the most
recent such offering needs to be recorded. (Assume this condition applies in
all subsequent questions.)
c) Every professor must teach some course.
d) Every professor teaches exactly one course (no more, no less).
e) Every professor teaches exactly one course (no more, no less), and every
course must be taught by some professor.
UNIT-IV
7 a) Consider the instance of a relation shown below: [7M]
X Y Z
x1 y1 z1
x1 y1 z2
x2 y1 z1
x2 y1 z3
List all the functional dependencies that this relational instance satisfies.
b) What is meant by the closure of functional dependencies? Illustrate with an [7M]
example.
OR
8 a) When is the decomposition of a relational schema R into two relational schemas X [7M]
and Y said to be lossless-join decomposition? Why is this property so important?
Give a necessary and sufficient condition to test whether a decomposition is
lossless-join.
b) Define multivalued dependencies and join dependencies. Discuss the use of such [7M]
dependencies in database design.
UNIT-V
9 a) Write about the anomalies that can be caused by concurrent access to same data [7M]
object.
b) Define the terms transaction, schedule, complete schedule, and serial schedule. [7M]
OR
10 a) Explain the role of clustered indices and composite search keys in performance [7M]
tuning.
b) Explain the difference between Hash indexes and B+ tree indexes. [7M]

2 of 2
|''|''|||''|'''|||'|
 : No:R2022052
Code R20 SET - 3
II B. Tech II Semester Regular Examinations, June/July - 2022
DATABASE MANAGEMENT SYSTEMS
(Common to CSE, CST, CSE(CS), CSE(IOTCSIBCT), CSE(CSBS), CSE(IOT), & CS)
Time: 3 hours Max. Marks: 70
Answer any FIVE Questions each Question from each unit
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~~
UNIT-I
1 a) Present some popular applications of database systems and role of databases in those [7M]
applications.
b) Explain the difference between external, internal, and conceptual schemas. How are [7M]
these different schema layers related to the concepts of logical and physical data
independence?
OR
2 a) List the advantages and disadvantages of using database systems. [7M]
b) Explain the concept of physical data independence and its importance in database [7M]
systems.
UNIT-II
3 a) What is a foreign key constraint? Why are such constraints important? What is [7M]
referential integrity?
b) Define the following terms with examples: relation schema, relational database [7M]
schema, domain, and relation degree.
OR
4 Consider the following schema: [14M]
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers. Write the
following queries in SQL:
a) Find the pnames of parts for which there is some supplier.
b) Find the snames of suppliers who supply every part.
c) Find the snames of suppliers who supply every red part.
d) Find the pnames of parts supplied by Acme Widget Suppliers and no one
else.
e) Find the sids of suppliers who charge more for some part than the average
cost ofthat part (averaged over all the suppliers who supply that part).
f) For each part, find the sname of the supplier who charges the most for that
part.
g) Find the sids of suppliers who supply only red parts.
UNIT-III
5 a) Explain the main steps in database design. What is the goal of each step? In which [7M]
step is the ER model mainly used? Why?
b) What is grouping in SQL? With examples explain GROUP BY& HAVING clauses. [7M]

1 of 2

|''|''|||''|'''|||'|
 : No:R2022052
Code R20 SET - 3

OR
6 Define the following terms and give an example in ER diagram: attribute, domain, [14M]
entity, relationship, entity set, relationship set, one-to-many relationship, many-to-
many relationship, participation constraint, overlap constraint, covering constraint,
weak entity set, aggregation, and role indicator.
UNIT-IV
7 a) Discuss various problems that arise with redundant storage of Information? [4M]
b) Describe the desirable properties of Schema decomposition? [3M]
c) Give a set of FDs for the relation schema R(A, B, C, D) with primary key AB under [7M]
which R is in 1NF but not in 2NF.
OR
8 a) Explain 1NF, 2NF, and 3NF with suitable examples? [7M]
b) Let R = (A, B, C, D, E) and let M be the following set of multivalued dependencies: [7M]
A →→ BC, B →→ CD, E →→ AD. List the nontrivial dependencies in M+.
UNIT-V
9 a) Compare the Ordered Indexing with Hashing. [7M]
b) Consider the following two transactions: [7M]
T13: read(A);
read(B);
if A = 0 then B := B + 1;
write(B).
T14: read(B);
read(A);
if B = 0 then A := A + 1;
write(A).
Write a concurrent execution of T13 and T14 that produces a non-serializable
schedule.
OR
10 a) Construct a B+ tree of order 3 for the following set of key values: [7M]
1,2,5,8,10,13,18,21,26,37. Assume that the tree is initially empty and elements are
entered in the given order.
b) What is a locking protocol? Describe the Strict Two-Phase Locking protocol. [7M]

2 of 2

|''|''|||''|'''|||'|
 : No:R2022052
Code R20 SET - 4
II B. Tech II Semester Regular Examinations, June/July - 2022
DATABASE MANAGEMENT SYSTEMS
(Common to CSE, CST, CSE(CS), CSE(IOTCSIBCT), CSE(CSBS), CSE(IOT), & CS)
Time: 3 hours Max. Marks: 70
Answer any FIVE Questions each Question from each unit
All Questions carry Equal Marks
~~~~~~~~~~~~~~~~~~~~~~~~~
UNIT-I
1 a) Explain the difference between two-tier and three-tier architectures. Which is better [7M]
suited for Web applications? Why? Justify your answer with an example.
b) Differentiate between database systems and conventional file systems. [7M]
OR
2 a) What are the responsibilities of a DBA? If we assume that the DBA is never [7M]
interested in running his or her own queries, does the DBA still need to understand
query optimization? Why?
b) Discuss the main benefits of using a DBMS to manage data in applications [7M]
involving extensive data access.
UNIT-II
3 a) What are integrity constraints? Define the terms primary key constraint and foreign [7M]
key constraint. Give Examples.
b) Explain with examples, different DDL and DML operations in SQL. [7M]
OR
4 Consider the following relations: [14M]
Student(snum: integer, sname: string, major: string, level: string, age: integer)
Class(name: string, meets at: string, room: string, fid: integer)
Enrolled(snum: integer, cname: string)
Faculty(fid: integer, fname: string, deptid: integer)
Write the following queries in SQL:
a) Find the names of all Juniors (level = JR) who are enrolled in a class taught
by Prof. Krishna.
b) Find the age of the oldest student who is either a History major or enrolled in
a course taught by Prof. Joseph.
c) Find the names of all classes that either meet in room R128 or have five or
more students enrolled.
d) Find the names of all students who are enrolled in two classes that meet at
the same time.
e) Find the names of faculty members who teach in every room in which some
class is taught.
f) Find the names of faculty members for whom the combined enrollment of
the courses that they teach is less than five.
g) For each level, print the level and the average age of students for that level.

1 of 2

|''|''|||''|'''|||'|
 : No:R2022052
Code R20 SET - 4

UNIT-III
5 a) What is a view? How do views support logical data independence? How are views [7M]
used for security?
b) Discuss the strengths and weaknesses of the trigger mechanism. Contrast triggers [7M]
with other integrity constraints supported by SQL.
OR
6 A company database needs to store information about employees (identified by ssn, [14M]
with salary and phone as attributes), departments (identified by dno, with dname and
budget as attributes), and children of employees (with name and age as attributes).
Employees work in departments; each department is managed by an employee; a
child must be identified uniquely by name when the parent (who is an employee;
assume that only one parent works for the company) is known. We are not interested
in information about a child once the parent leaves the company.
Draw an ER diagram that captures this information.
UNIT-IV
7 a) Consider the relational schema R(A, B, C), which has the FD: B → C. If A is a [7M]
candidate key for R, is it possible for R to be in BCNF? If so, under what
conditions? If not, explain why not.
b) Define functional dependencies. How are primary keys related to FDs? Explain with [7M]
example.
OR
8 a) When is a decomposition said to be dependency-preserving? Explain with example. [7M]
b) What is the need for normalization of schemas? Are they any demerits of [7M]
normalization?
UNIT-V
9 a) On what factors techniques for indexing and hashing must be evaluated? Explain. [7M]
b) Consider the following actions taken by transaction T1 on database objects X and Y: [7M]
R(X), W(X),R(Y), W(Y)
Give an example of another transaction T2, such that if run concurrently to
transaction T1 without some form of concurrency control, could interfere with T1.
Suggest a solution to avoid the interference.
OR
10 a) Describe in detail about algorithms for updating single level indices. [7M]
b) Explain in detail about the B+ tree file organization. [7M]

2 of 2
|''|''|||''|'''|||'|