Hall Ticket No Question Paper Code: ACS005
INSTITUTE OF AERONAUTICAL ENGINEERING
(Autonomous)
B.Tech III Semester End Examinations (Supplementary) January/February, 2018
Regulation: IARE R16
DATABASE MANAGEMENT SYSTEMS
(Information Technology)
Time: 3 Hours Max Marks: 70
Answer ONE Question from each Unit
All Questions Carry Equal Marks
All parts of the question must be answered in one place only
UNIT I
1. Design an E-R diagram for keeping track of the exploits of your favourite sports team. You should
store the matches played, the scores in each match, the players in each match, and individual
player statistics for each match, summary statistics should be modelled as derived attributes.

Define the concept of aggregation. Give two examples of where this concept is useful.
2. Illustrate the concepts of class hierarchies, Weak Entities and aggregation in E-R diagram.

Explain with examples the importance and advantages of database management system.
UNIT II
3. Consider the following relational schema
Employee(empno, name, office, age)
Books (isbn, title, authors, publishers)
Loan (empno, isbn, date)
Write the following queries in relational algebra
i. Find the names of the employees who have borrowed a book published by McGraw Hill.
ii. Find the names of employees who have borrowed all the books published by McGraw Hill.
iii. Find the names of employees who have borrowed more than five different books published
by McGraw Hill.
iv. For each publisher, find the names of employees who have borrowed more than five books of
that publisher.
List and explain the fundamental operations in relational algebra. How will you perform modifications
using relational algebra?
4. Explain the importance of triggers in active databases with examples.
Consider the set of schema: 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 SQL queries
for the following.
Page 1 of 2
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 room R12 or have five or more students
enrolled.
iv. Find the names of all students who are enrolled in two classes that meet at the same time.
UNIT III
5. Let the following relation schemas be given: R
Let relations and be given. Given an expression in SQL that is equivalent to each of
the following queries
i. Q
A ii. iii. rXs. iv. Q
A;F
Describe how to translate join expressions in SQL to relational algebra.
6. What is Join dependency and explain Third Normal form with example.
Given the schema (EFCD) with FD's EF C;EF D;C E;D F
i. Identify the candidate key(s) for R.
ii. Identify the best normal form that R satisfies 2NF, 3NF, or BCNF).
iii. If R is not in BCNF, decompose it into a set of BCNF relations that preserve the dependencies.
UNIT IV
7. Explain the benefits of logical logging. Give examples of one situation where logical logging is
preferable to physical logging and one situation where physical logging is preferable to logical
logging.
Show by example that there are schedules possible under the tree protocol that is not possible
under the two-phase locking protocol, and vice versa.
8. Why concurrent execution of transactions are important in the case of long transactions or
transactions working with (slow) disk, and not important for short transactions?
What is a recoverable schedule? Why recoverability is desirable? Are there any circumstances
in which it would be desirable to allow non-recoverable schedules?
UNIT V
9. Construct a tree for the following set of key values: 11, 17,19, 23,29, 31)
Assume that the tree is initially empty and values are added in ascending order. Construct
tree for the cases where the number of pointers that will fit in one node is as follows eight.
Show the steps involved in the following queries:
i. Find records with a search-key value of 11.
ii. Find records with a search-key value between 7 and 17(inclusive).
What are the causes of bucket overflow in a hash file organization? What can be done to reduce
the occurrence of bucket overflows?
10. How does data encryption affect index schemes? In particular, how might it affect schemes that
attempt to store data in sorted order?
Explain the distinction between closed and open hashing. Discuss the relative merits of each
technique in database applications.