JNTU Database Management Systems question paper - April 2011 Regulation 2009 / JNTU Hyderabad-Kakinada Previous Year 2011 Exam Questions / JNTU Previous Year 2011 Computer Science and Engineering, Information Technology, and Computer Science and System Engineering Question Papers / Jawaharlal Nehru Technological University Database Management System Previous Year Question Papers
Code No: R09220502 R09 Set No. 1
II B.Tech II Semester Examinations, APRIL - 2011
DATABASE
MANAGEMENT SYSTEMS
Common to ME, IT, MECT, AME, CSE, ECE
Time: 3 hours Max
Marks: 75
Answer any FIVE Questions
All Questions carry equal marks
*****
1. (a) Give a note on storage manager component of database system
structure.
(b) Make a comparison between Database system and File system. [7+8]
2. (a) How the Data is stored in External Storage?
(b) Explain file organization & indexing? [7+8]
3. (a) Construct an ER diagram for university registrar's office. The
office maintains data about each class, including the instructor, the
enrollment and the time and place of the class meetings. For each student
class pair a grade is recorded. Determine the entities and relationships.
(b) What is the composite attribute? How to model it in the ER
diagram? Explain with an example. [7+8]
4. Explain the difference between three storage types, Volatile,
Non-Volatile and Stable in terms of I/O cost. [15]
5. (a) Discuss about Tuple Relational Calculus in detail.
(b) Write the following queries in Tuple Relational Calculus for
following Schema.
Sailors (sid: integer, sname: string, rating: integer, age: real)
Boats (bid: integer, bname: string, color: string)
Reserves (sid: integer, bid: integer, day: date)
i. Find the names of sailors who have reserved a red boat
ii. Find the names of sailors who have reserved at least one boat
iii. Find the names of sailors who have reserved at least two boats
iv. Find the names of sailors who have reserved all boats. [7+8]
6. (a) Explain constraints on an Entity set.
(b) Explain constraints on a Relationship set. [7+8]
7. (a) Write the following queries in SQL using Nested queries
concept for following Schema.
Sailors (sid: integer, sname: string, rating: integer, age: real)
Boats (bid: integer, bname: string, color: string)
Reserves (sid: integer, bid: integer, day: date)
i. Find the names of sailors who have reserved both red and green
boat
ii. Find the names of sailors who have reserved all boats
iii. Find the names of sailors who have not reserved red boat
iv. Find sailors whose rating is better than some sailor called
raghu.
(b) What is a correlated nested query? Explain with an example.
[11+4]
8. Consider the following two transactions:
T1:
read(A);
read(B);
if
A=0 then B:= B + 1;
write(B).
T2:
read(B);
read(A);
if
B=0 then A:= A + 1;
write(A).
Let the consistency requirement be A=0 V B=0, with A=B=0 the initial
values.
(a) Show that every serial execution involving these two transactions
preserves the consistency of the Database?
(b) Show a concurrent execution of T1 and T2 that produces a non
serializable Schedule?
(c) Is there a concurrent execution of T1 and T2 that produces a
serializable Schedule? [5+5+5]
___________________
|
Code No: R09220502 R09 Set No. 2
II B.Tech II Semester Examinations, APRIL - 2011
DATABASE
MANAGEMENT SYSTEMS
Common to ME, IT, MECT, AME, CSE, ECE
Time: 3 hours Max
Marks: 75
Answer any FIVE Questions
All Questions carry equal marks
*****
1. (a) Briefly explain the Database Design
process.
(b) Define these terms: Entity, Entity set,
Attribute, Key. [7+8]
2. Explain schema refinement in Database Design?
[15]
3. (a) Write a note on DBMS? Explain Database
System Applications.
(b) What is a Data Model? Explain ER data model.
[7+8]
4. During its execution, a transaction passes
through several states, until it finally commits or aborts. List all possible
sequences of states through which a transaction may pass. Explain why each
state transition may occur? [15]
5. (a) How is Data organized in Tree based
index?
(b) When would users use a Tree based index?
[7+8]
6. Explain ARIES. [15]
7. (a) Consider the following Relations
Student (snum: integer, sname: string, major:
string, level: string, age: integer)
Class (name: string, meets_at: time, room:
string, fid: integer)
Enrolled (snum: integer, cname: string)
Faculty (fid: integer, fname: string, deptid:
integer)
Write the following queries in SQL.
i. Find the names of students not enrolled in
any class.
ii. Find the names of students enrolled in the
maximum number of classes.
iii. Print the level and the average age of
students for that level, for each level.
iv. Print the level and the average age of the
students for that level, for all levels except JR.
(b) Explain following in brief
i.
Triggers
ii.
Assertions [11+4]
8. Consider the following Schema:
Suppliers (sid : integer, sname: string,
address: string)
Parts (pid : integer, pname: string,
color: string)
Catalog (sid : integer; pid : integer,
cost: real)
The key fields are underlined. The catalog
relation lists the price changes for parts by supplies. Write the following
Queries in Relational Algebra and tuple relational calculus.
(a) Find the sids of suppliees who supply some
red part and some green part
(b) Find the sids of suppliees who supply every
part
(c) Find the sids of suppliees who supply every
red or green part
(d) Find the pids of parts supplied by at least
two different suppliees. [15]
__________________________
|
Code No: R09220502 R09 Set No. 3
II B.Tech II Semester Examinations, APRIL - 2011
DATABASE
MANAGEMENT SYSTEMS
Common to ME, IT, MECT, AME, CSE, ECE
Time: 3 hours Max
Marks: 75
Answer any FIVE Questions
All Questions carry equal marks
*****
1. (a) Explain covering constraints & overlap constraints.
(b) Give a detail note on weak entity set. [7+8]
2. (a) Explain functional dependency with an example?
(b) Compare Third NF and BCNF, explain with examples? [11+4]
3. (a) What is the relationship between files & indexes?
(b) What is the search key for an index?
(c) What is Data entry in an index? [7+4+4]
4. (a) Explain the Database users and user interfaces.
(b) Discuss the function of Database Administrator. [9+6]
5. (a) Discuss about joins in Relational Algebra with examples.
(b) Explain about set operations in Relational Algebra with examples.
[7+8]
6. Explain shadow-copy technique for Atomicity and Durability? [15]
7. (a) Consider the following Relations
Student (snum: integer, sname: string, major: string, level: string,
age: integer)
Class (name: string, meets_at: time, room: string, fid: integer)
Enrolled (snum: integer, cname: string)
Faculty (fid: integer, fname: string, deptid: integer)
Write the following queries in SQL.
i. Find the names of all juniors (level = JR) who are enrolled in a
class taught by I. teach.
ii. Find the age of the oldest student who is either a History major
or enrolled in a course taught by I. teach.
iii. Find the names of all classes that either meet in a room R128 or
have five or more students enrolled.
iv. Find the number of all students who are enrolled in two classes
that meet at the same time.
(b) What is a trigger and what are its 3 parts. Explain in detail. [11+4]
8. Stable Storage can’t be Implemented, Explain why it can't be? [15]
___________________
|
Code No: R09220502 R09 Set No. 4
II B.Tech II Semester Examinations, APRIL - 2011
DATABASE
MANAGEMENT SYSTEMS
Common to ME, IT, MECT, AME, CSE, ECE
Time: 3 hours Max
Marks: 75
Answer any FIVE Questions
All Questions carry equal marks
*****
1. (a) Explain how to differentiate attributes
in Entity set?
(b) Explain all the functional dependencies in
Entity sets? [7+8]
2. (a) Discuss about DDL and DML.
(b) What are five main functions of Database
Administrator? Explain [8+7]
3. (a) Construct an ER diagram for a bank
Database. Bank maintains data about customers, their loans, their deposits,
lockers. Determine the entities and relationships.
(b) Define the terms: Entity Set, Role,
Relationship set, Aggregation. [7+8]
4. (a) Explain dynamic Data structure?
(b) Explain overflow of pages? [7+8]
5. (a) Explain about the basic form of a SQL
queries.
(b) Write the following queries in SQL for the
following schema.
Sailors (sid: integer, sname: string, rating:
integer, age: real)
Boats (bid: integer, bname: string, color:
string)
Reserves (sid: integer, bid: integer, day: date)
i. Compute increments for the ratings of persons
who have sailed two different boats on the same day.
ii. Find the ages of sailors whose names begins
and ends with B and has at least three characters.
iii. Find the colors of boats reserved by Raghu.
iv. Find the sids of all sailors who have
reserved red boats but not green boats. [7+8]
6. (a) Define the divisible operation in terms
of the basic relational algebra operations. Describe a typical query that
calls for division. Unlike join, the division operation is not given special
treatment in database systems. Explain why.
(b) Database Systems use some variant of
Relational Algebra to represent query evaluation plans. Explain why Algebra
is suitable for this purpose. [7+8]
7. (a) What is Thomas write rule?
(b) Explain the time-stamp ordering protocol?
[7+8]
8. Explain advanced recovery Techniques? [15]
_____________________
|