Code No: R1631054 R16 SET - 1

III B. Tech I Semester Regular/Supplementary Examinations, October/November - 2019

DATA BASE MANAGEMENT SYSTEMS
(Common to Computer Science and Engineering, Information Technology)
Time: 3 hours Max. Marks: 70
Note: 1. Question Paper consists of two parts (Part-A and Part-B)
2. Answer ALL the question in Part-A
3. Answer any FOUR Questions from Part-B
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PART –A (14 Marks)
1. a) What is logical data independence and why it is important? [2M]
b) Write a query to illustrate join operation. [2M]
c) Write a SQL query on except operation. [2M]
d) What is meant by trivial functional dependencies? [3M]
e) Describe the properties of transaction. [2M]
f) Write short notes on dense and sparse indices. [3M]
PART –B (56 Marks)
2. a) How to model the data? Explain various ways of data modelling with suitable [7M]
examples.
b) Write and explain the working principle of client server architecture. [7M]
3. a) What is a relation? Differentiate between relation schema and relation instance. [7M]
Define the terms unity and degree of relation. What are domain constraints?
b) How to ensure the integrity with key constraints in database systems? Explain with [7M]
primary and foreign key constraints.
4. a) Describe the importance of nested queries. Give its application in performing various [7M]
aggregation operations.
b) Write queries for the following using the given information: [7M]
Emp Name DOB DOJ EmpSalary Bonus DOR
Buelin 06-12-1983 07-08-2005 15000 600 07-08-2055
Andy 12-01-1970 06-12-1973 20000 1200 06-12-2043
Lubber 07-08-1985 12-04-2006 13000 500 12-04-2056
Zobra 08-02-1960 07-03-1982 25000 1500 07-03-2032
i) Find total salary of employees from emptable.
ii) Find months between employee DOJ and DOR from emp-schema.
iii) Arrange empnames in ascending and descending order.
5. a) Consider the relation R(A,B,C,D,E,F) and FDs [10M]
A → BC, F → A, C → A, D → E, E → D. A→D is the decomposition of R into
R1(A,C,D), R2(B,C,D) and R3(E,F,D) lossless? Explain the requirement of Lossless
decomposition.
b) What are the advantages of normalized relations over the un-normalized relations? [4M]
6. a) Explain the process of rollback and crash recovery. And also specify the role of log [7M]
record in it.
b) How to use time stamp ordering for Concurrency control? Explain the Wait/Die and [7M]
Wound/Wait Schemes of concurrency control.
7. Explain the following: [14M]
i) Cluster indexes; ii) Primary and secondary indexes; iii) Clustering file
organization.
*****
|''|''|||''|'''|||'|
 Code No: R1631054 R16 SET - 2

III B. Tech I Semester Regular/Supplementary Examinations, October/November - 2019

DATA BASE MANAGEMENT SYSTEMS
(Common to Computer Science and Engineering, Information Technology)
Time: 3 hours Max. Marks: 70
Note: 1. Question Paper consists of two parts (Part-A and Part-B)
2. Answer ALL the question in Part-A
3. Answer any FOUR Questions from Part-B
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PART –A (14 Marks)
1. a) Write the functions of database administrator. [2M]
b) Discuss entities and attributes. [2M]
c) Write SQL queries to perform modifications of relational databases. [2M]
d) Write about concept of surrogate key. [3M]
e) Describe latches and convoys in lock managers. [3M]
f) Explain about external storage media. [2M]
PART –B (56 Marks)
2. a) Why would choose a database system instead of simply storing data in operating [7M]
system files? When would it make sense not to use a database system?
b) With a neat sketch explain the three-tier architecture of DBMS systems with data [7M]
independence.

3. Consider the following database: [14M]

Employee (employee-name, street, city)
Works (employee-name, company-name, salary)
Company (company-name, city)
Manager (employee-name, manager-name)
Express the following query in relational algebra, Tuple relational calculus, and
domain relational calculus:
i) Find the names of all employees who work for estate bank.
ii) Find the name of employees whose salary is above 10k.
iii) Find the names of employees who are working as Managers.

4. a) Explain the basic structure of SQL query and write the queries to illustrate union [7M]
and intersect operations.
b) Discuss about Triggers and active Database in SQL with examples. Write the [7M]
differences between constraints and triggers in SQL.

5. a) Explain the concepts of Functional dependency with suitable example and its [7M]
operations like closure and minimal cover.
b) Describe an algorithm for computing the minimal cover of a set of Functional [7M]
Dependency and illustrate its application in 3NF with an example.

6. a) Define the concept of schedule for a set of concurrent transaction. Give a suitable [7M]
example.
b) Explain read-only, write-only & read-before-write protocols in serializability. [7M]

7. Compare the working principles, advantages and disadvantages of following: [14M]

i) Ordered Indexing with Hashing.
ii) Linear Hashing with extendable Hashing
*****
|''|''|||''|'''|||'|
 Code No: R1631054 R16 SET - 3
III B. Tech I Semester Regular/Supplementary Examinations, October/November - 2019
DATA BASE MANAGEMENT SYSTEMS
(Common to Computer Science and Engineering, Information Technology)
Time: 3 hours Max. Marks: 70
Note: 1. Question Paper consists of two parts (Part-A and Part-B)
2. Answer ALL the question in Part-A
3. Answer any FOUR Questions from Part-B
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PART –A (14 Marks)
1. a) Write a short on data independence. [3M]
b) What are the cardinalities used in ER models? [2M]
c) Write SQL queries to perform various operations on relational schemas. [3M]
d) Describe the problem of redundancy. [2M]
e) Write about log record buffering. [2M]
f) Explain about tertiary storage media in detail. [2M]
PART –B (56 Marks)
2. a) Describe the following levels in DBMS architecture in detail: [7M]
i) Internal level ii) Conceptual level.
b) Discuss the query processing in database system structure. [7M]
3. a) Describe the application of relational algebra with selection and projection [7M]
operations.
b) What SQL construct enables the definition of a relation? What constructs allow [7M]
modification of relation instances?
4. a) Write the SQL expressions for the following relational database: [7M]
Sailor schema (sailor id, Boat id, sailor name, rating, age)
Reserves (Sailor id, Boat id, Day)Boat Schema (boat id, Boat name, color)
i) Find the age of the youngest sailor for each rating level.
ii) Find the age of the youngest sailor who is eligible to vote for each rating level
with at lead two such sailors.
iii) Find the number of reservations for each red boat.
iv) Find the average age of sailor for each rating level that at least 2 sailors.
b) Write about triggers and execution of triggers. [7M]
5. a) Consider the relation R with 5 attributes ABCDE with dependencies AB, BCE [7M]
and EDA then;
i) List all Keys for R ii) Is R in 3NF? iii) Is R in BCNF?
b) Describe decomposition operation? Explain variants of decomposition operation [7M]
with examples.
6. a) What are the reasons for strict 2PL is used in many database systems? How the use [7M]
of 2PL would prevent interference between the two transactions? Explain.
b) Write a short note on [7M]
i) Thomas Write rule ii) Deadlock handling strategies iii) transaction recovery.
7. a) Explain about Fixed-Length file organization with an example. [7M]
b) Explain the distinction between closed and open hashing of hash based indexing. [7M]
Discuss the relative merits of each technique in database applications.
*****

|''|''|||''|'''|||'|
 Code No: R1631054 R16 SET - 4
III B. Tech I Semester Regular/Supplementary Examinations, October/November - 2019
DATA BASE MANAGEMENT SYSTEMS
(Common to Computer Science and Engineering, Information Technology)
Time: 3 hours Max. Marks: 70
Note: 1. Question Paper consists of two parts (Part-A and Part-B)
2. Answer ALL the question in Part-A
3. Answer any FOUR Questions from Part-B
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PART –A (14 Marks)
1. a) Write a short note on relational databases with example. [2M]
b) Discuss the importance of relational calculus. Give some examples. [2M]
c) Write the structure of triggers. [3M]
d) Explain the role of normalization. [2M]
e) Write short note on commit, rollback and save point. [3M]
f) Differentiate primary and secondary indexes. [2M]
PART –B (56 Marks)
2. a) What is the importance of data independence? Explain with suitable examples. [7M]
b) Explain different managers in DBMS model and their functionalities. [7M]

3. a) How to print Date and Time? Write queries to perform Numeric, String conversion. [7M]
Write some tuple relational calculus queries.
b) Construct an ER diagram for Car Insurance company Database. Identify entities, [7M]
attributes for each entity, relationship among entities. Represent necessary
constraints in this database design process in detail.

4. a) How to specify the conditions in SQL? Explain various operators used in it. [7M]
b) Describe the usage of null values? Compare various SQL operations with and with- [7M]
out null values.

5. a) Explain BCNF. How does BCNF differ from 3NF? Explain with an example. [7M]
b) Consider the relation R(A,B,C,D,E,F) and the FDs ABC→DE, ABC→D, [7M]
DE→ABCE, E→C. Decompose the relation by considering the given set of FDs
into BCNF relation. Is the decomposition lossless and dependency preserving?

6. a) Describe each of the following locking protocols: [7M]

i) Two phase lock ii) Conservative two phase lock.
b) Discuss the implementation of lock and unlock requests and atomicity of locking [7M]
and unlocking in lock managers.

7. a) Construct a B+−tree for set of key values: (2, 3, 5, 7, 11, 17, 19, 23, 29, 31). [7M]
Assume that the tree is initially empty and values are added in ascending order.
Construct B+tree for the cases where the number of pointers that will fit in one
node is 4, 6 and 8.
b) Perform insertion and deletion operations directory of buckets in extendible [7M]
hashing with an example.
*****

|''|''|||''|'''|||'|