Hall Ticket No Question Paper Code: ACS005
INSTITUTE OF AERONAUTICAL ENGINEERING
(Autonomous)
B.Tech III Semester End Examinations (Regular) November, 2018
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. Explain Database system structure with a neat sketch.
Construct an E-R diagram for a car-insurance company whose customers own one or more cars
each. Each car has associated with it zero to any number of recorded accidents.
2. Explain in detail the Network and Hierarchical models with examples.
Construct an alternative E-R diagram that uses only a binary relationship between students and
course-offerings. Make sure that only one relationship exists between a particular student and
course-offering pair, yet you can represent the marks that a student gets in different exams of a
course offering.
UNIT II
3. Consider the following schema:
Suppliers integer, sname:string, address: String)
Parts (pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
Write the following queries in relational algebra
i. Find the name of suppliers who supply some red part.
ii. Find the sids of suppliers who supply some red part and some green part.
Define all the variations of the join operation? Can't we express every join operation in terms of
cross product, selection and projection?
4. Consider the relational database of Employee.
employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)
Give a relational-algebra expression for each of the following queries:
i. Find the company with the most employees.
ii. Find the company with the smallest payroll.
iii. Find those companies whose employees earn a higher salary, on average, than the average
salary at First Bank Corporation.
Page 1 of 3
Explain the following:
Tuple relational calculus with an example.
ii) Domain relational calculus with an example.
UNIT III
5. Using the relations of sample bank database, write an SQL expression to define the following
views:
i. A view containing the account numbers and customer names (but not the balances) for all
accounts at the Deer Park branch.
ii. A view containing the names and addresses of all customers who have an account with the
bank, but do not have a loan.
iii. A view containing the name and average account balance of every customer of the Rock Ridge
branch.
Consider the insurance database given below:
person (driver-id, name, address)
car (license, model, year)
accident (report-number, date, location)
owns (driver-id, license)
participated (driver-id, car, report-number, damage-amount)
where the primary keys are underlined. Construct the following SQL queries for this relational
database.
i. Find the total number of people who owned cars that were involved in accidents in 1989.
iii. Find the number of accidents in which the cars belonging to "John Smith" were involved.
iii. Add a new accident to the database; assume any values for required attributes.
iv. Delete the Mazda belonging to "John Smith".

6. Use the definition of functional dependency to argue that each of Armstrong's Axioms (reflexivity,
augmentation, and transitivity) are sound.
Write an SQL trigger to carry out the following action: On delete of an account, for each owner
of the account, check if the owner has any remaining accounts, and if she does not, delete her
from the depositor relation.
UNIT IV
7. List the ACID properties. Explain the usefulness of each.
Consider the following two transactions:
T1:

if A 0then B B
write(B).
T2:

if B 0then A A
write(A).
Let the consistency requirement be A 0 B with A B 0 the initial values.
Page 2 of 3
i. Show that every serial execution involving these two transactions preserves the consistency of
the database.
ii. Show a concurrent execution of T1 and T2 that produces a non serializable schedule.
iii. Is there a concurrent execution of T1 and T2 that produces a serializable schedule?
8. Write short note on Time Stamp based concurrency control.
Consider the following two transactions:
T31:

if A 0then B B
write(B).
T32:

if B 0then A A
write(A).
Add lock and unlock instructions to transactions T31 and T32, so that they observe the two-phase
locking protocol. Can the execution of these transactions result in a deadlock?
UNIT V
9. Demonstrate the typical steps in query processing with a neat sketch?
Briefly discuss about operations on files?
10. Discuss the techniques for allowing a hash file to expand and shrink dynamically. What are the
advantages and disadvantages of each?
What are the several types of ordered indexes?