Hall Ticket No Question Paper Code: ACS005
INSTITUTE OF AERONAUTICAL ENGINEERING
(Autonomous)
B.Tech III Semester End Examinations (Supplementary) January, 2019
Regulation: IARE R16
DATABASE MANGEMENT SYSTEMS
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. What is Database Administrator(DBA)? Discuss the functions of DBA?
An E-R diagram can be viewed as a graph. What do the following mean in terms of the structure
of an enterprise schema?
i. The graph is disconnected.
ii. The graph is acyclic.
2. Why query processor component of database system is important? Briefly discuss about all
components of query processor?
Design a generalization-specialization hierarchy for a motor-vehicle sales company. The company
sells motorcycles, passenger cars, vans, and buses. Justify your placement of attributes at each
level of the hierarchy. Explain why they should not be placed at a higher or lower level.
UNIT II
3. Explain how the renaming operator is used? If this operator is not allowed is there any query
that can no longer be expressed in algebra?
Consider the following schema:
Sailors integer,sname: string, rating :integer, age :real)
Boats (bid integer, bname string, colour :string)
Reserves (sid integer, bid integer, day: date)
Write the following queries in relational algebra
i. Find the names of sailors who have reserved boat 103
ii. Find the colours of boats reserved by lubber?
iii. Find the names of sailors who reserved all boats called Interlake?
4. Define the following terms with respect to the tuple calculus: tuple variable, range relation,atom,
formula,and expression.
Create the following tables with the mapping given below:
i. Emp_details emp_name address,doj, mobile_no, dept_no, salary)
ii. Dept_details(dept_no, dept_name ,Location)
iii. Display the structure of details emp_details
iv. Count maximum number of employees in department.
v. Truncate the table dept_details.
vi Display emp_name getting highest salary.
Page 1 of 2
UNIT III
5. List the data types that are allowed for SQL attributes?
Write appropriate SQL DDL statements for declaring the LIBRARY relational database schema
of following and specify the keys and referential triggered actions.
BOOK Book_id, Branch_id, No_of_copies)
BOOK_AUTHORS(Book_id, Author_name)
LIBRARY_BRANCH( Branch_id, Branch_name, Address)
PUBLISHER( Name, Address, Phone)
BOOK_LOANS (Book_id,Branch_id,Card_no,Date_out,Due_date)
BORROWER(Card_no, Name, Add)
6. Given the database schema and a relation r on the schema write an SQL query to
test whether the functional dependency b!c holds on relation r. Also write an SQL assertion
that enforces the functional dependency. Assume that no null values are present.
A functional dependency  is called a partial dependency if there is a proper subset
of
such that
. We say that  is partially dependent on A relation schema R is in second
normal form if each attribute A in R meets one of the following criteria:
• It appears in a candidate key.
• It is not partially dependent on a candidate key.
Show that every 3NF schema is in 2NF.
UNIT IV
7. Define Deadlock. Explain in detail the Deadlock Detection and Prevention.
Show by example that there are schedules possible under the tree protocol that are not possible
under the two-phase locking protocol, and vice versa.
8. Explain in detail the types of Concurrency Control Protocols.
Compare the deferred- and immediate-modification versions of the log-based recovery scheme in
terms of ease of implementation and overhead cost.
UNIT V
9. Explain in detail about the Heap file organization with an example.
Since indices speed up query processing, why might they not be kept on several search keys? List
as many reasons as possible.
10. Define the following terms: disk, disk pack, track, block, cylinder, sector, interblock gap, read/write
head.
Write an algorithm for Searching for a record with search key field value using a B+-tree.