Hall Ticket No Question Paper Code: ACS005
INSTITUTE OF AERONAUTICAL ENGINEERING
(Autonomous)
B.Tech IV Semester End Examinations (Regular) May, 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. Consider the relations STUDENT(Table and DEPT(Table
Table Student
Sname Usn Gender Dno
Sangeeta 1 fe 10
Samartha 2 ma 20
Suraksha 3 fe 30
Suvarna 4 ma 10
Table Dept
Dname Dno
CSE 10
ECE 20
MECH 30
NOTE: Gender attribute in STUDENT relation is allowed to take only two possible values. They
are ma}. Write update operations for the following
i.Write an INSERT operation on STUDENT relation which will violate domain constraint.
ii. Write a DELETE operation on DEPT which will violate referential integrity constraint.
iii. Write an UPDATE operation to modify department number of a student which will not
violate any of the relational model constraint.
When is the concept of weak entity used in data modeling? Define the terms owner entity type,
weak entity type, identifying relationship type and partial key.
Page 1 of 3
2. Draw Three Schema Architecture for DBMS and explain in detail.
What are advantages of DBMS in real world applications.
UNIT II
3. Discuss about unary relational operations in relational algebra with examples.
How the statement "the sids of suppliers who supply some red or green part" can be represented
in the form of relational algebra and tuple relational calculus from the above relations.
Suppliers scheme:
Suppliers(sid: INTEGER, sname: STRING, address: STRING)
Parts(pid: INTEGER, pname: STRING, color: STRING)
Catalog(sid: INTEGER, pid: INTEGER, cost: REAL)
4. Consider the following relational schema of CAR ACCIDENT database
CAR(Car_Model, Car_Company, Car_num, Car_Price, Year_manufacture)
DRIVER(Dri_Name, Dri_id, Dri_sex, Dri_salary, Dri_dob, Addr)
PARTICIPATED(Carnum, Driid, Date_of_Accident, Place_of_Accident, Damage_amount)
Write the following queries in relational calculus.
i. Retrieve car model and car number of all the cars whose price is greater than 500000
ii. For each driver retrieve driver name along with places of accidents for that particular driver.
iii. For each car which has met with at least one accident retrieve car model, Car Company along
with total damage amount.
How natural JOIN operation is different when compared to JOIN operation. Explain with an
example.
UNIT III
5. Write a note on INSERT, DELETE, UPDATE commands in SQL
R(ABCD) is relation with FD set C→A, B→C}.
Find Candidate Key
Normal form that can be existed
Decompose in BCNF relations
6. By using supplier scheme which are given in write the SQL statements for the following
queries
i.Find pairs of sids such that the supplier with the first sid charges more for some part than the
supplier with the second sid.
ii.Find the sids of suppliers who supply some red part and some green part.
Explain the key constraints Primary key and Foreign key with examples
UNIT IV
7. Explain the need of concurrency control in transaction processing
Consider the following schedule S1.
Check whether S1 is serializable or not. If it is serializable,
write its equivalent serial schedule.
Page 2 of 3
8. Explain the time stamp ordering concurrency control technique with an example.
With a neat diagram explain NO-UNDO/NO-REDO recovery mechanism in transaction
processing.
UNIT V
9. How external hashing is used for disk files.
Buffering process speeds up the transfer. Justify your answer.
10. What is the minimum space utilization for ISAM index?
What does it mean to say that a page is pinned in the buffer pool? Who is responsible for pinning
pages? Who is responsible for unpinning pages?