Hall Ticket No Question Paper Code: ACS005
INSTITUTE OF AERONAUTICAL ENGINEERING
(Autonomous)
Four Year B.Tech III Semester End Examinations (Supplementary) July, 2018
Regulation: IARE R16
Database Management 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. With a neat diagram explain the various components of a database system and their connections.

Explain the differences between physical and logical data independence.
2. Explain the following terms briefly: entity, relationship, entity set, relationship set, one-to-many
relationship, many-to-many relationship, participation constraint, overlap constraint, covering
constraint and role indicator.
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.
UNIT II
3. Given two relations R1,R2 with N1,N2 tuples respectively state the assumptions in the resultant
about the schemas needed to make the expression meaningful and the number of tuples for the
following R1 R2 R1 R2, R1 R2 R1 × R2 with examples.
Consider the set of schema: Suppliers sname, address), Parts pname, color) Catalog
pid, cost)
i. Find the names of the suppliers who supply some red part
ii. Find the sids of suppliers who supply every part
iii. Find the sids of suppliers who supply every red or green part
Write the above queries in relational algebra, relational calculus.
4. Consider the following relational database
author(author_id,firstname,last_name)
authorpub(author_id,pubid,author_position)
book(book_id,booktitle,month,year,editor)
pub(pub_id,title,book_id)
Give a relational algebra expression for each of the following queries:
Find the names of all authors who are not book editors
Find the names of all authors who have at least one publication in the database.
Which Query does the following relational algebra expression answer?
author1(authorpub1(month='July'(book)1pub))
Find the names of all authors who are book editors.
Page 1 of 3
Consider a relation that contains r tuples, and a relation that contains s tuples;
assume r 0 and s 0. Make no assumptions about keys. For each of the following relational
algebra expressions, state in terms of r and s the minimum and maximum number of tuples that
could be in the result of the expression.
i. R
ii.
iii. BR
iv.
UNIT III
5. Consider the following relational database:
Employee (employee-name, street, city) Works(employee-name, company-name, salary) Company
(company-name, city) Manages (employee-name, manager-name)
Give an SQL DDL definition of this database. Identify referential integrity constraints that
should hold, and include them in the DDL definition.
Suppose there are two relations r and such that the foreign key B of r references the primary
key A of s. Describe how the trigger mechanism can be used to implement the on delete cascade
option, when a tuple is deleted from s.
6. Let R let r1and r2 both be relations on schema R. Givean expression in SQL that
is equivalent to each of the following queries.
i. r1 r2
ii. r1 r2
iii. r1 r2
iv.
SQL allows a foreign-key dependency to refer to the same relation, as in the following example:
create table manager (employee-name char(20), manager-name char(20), primary key employeename,
foreign key (manager-name) references manager on delete cascade
Here, employee-name is a key to the table manager, meaning that each employee has at most
one manager. The foreign-key clause requires that every manager also be an employee. Explain
exactly what happens when a tuple in the relation manager is deleted.
UNIT IV
7. Explain View serializability and conflict serializability.
In Time stamp-based concurrency control transactions are assigned time stamp at the startup.
How it is used to ensure serializability? How does Thomas Write Rule improve concurrency
8. Explain the structure of Log records and compensation log records.
Explain in detail all the activities that occurs in ARIES crash recovery algorithm by taking a
suitable log record with crash.
Page 2 of 3
UNIT V
9. Discuss the structures of Fixed length and variable length records.
Explain the Insertion, deletion of nodes in tree with examples.
10. List an advantage and a disadvantage of each of the following strategies for storing a relational
database:
Store each table in a separate file,
Store multiple tables (or even the entire database) in a single file.
Discuss clustered indexing, Hash based indexing and tree based indexing.