IT 501: Database Management Systems

Full Marks: 70 Time: 3 Hours

Question No. 1 is Mandatory and Answer any Four from the rest.

1. a) Design an Entity-Relationship Diagram of the Library of your department. Choose the

relevant entities and use mapping cardinality according to the requirements. Write the
assumptions of your ER Diagram.
b) Convert the ER-Diagram you have created in Question 1.a). to its equivalent Relational
Model.
10+4=14
2.a) Why is spurious tuple generated? How could this be prevented?
b) Why is Weak Entity defined?
c) Explain the concept of entity integrity.
d) Why is controlled redundancy required?
(3+2)+3+3+3=14

3.a) Write the steps of insertion in Sparse Index and deletion in Dense Index.
b) Why is Secondary Index required?
c) Which one is preferred for Database: Open Hashing or Closed Hashing? Explain
(4+4)+3+3=14

4. a) Derive Natural Join operations of relational algebra using basic relational algebra
operations only.
b) Consider the following relational schema
Employee(Empno, EmpName, Deptno, Salary)
Dept(Deptno, Dname, Location)
Write the relational algebra using only basic relational algebra operations for the following
queries:
i) Give the name of the employee who earns lowest salary.
ii) Give the employee names those are located at Kolkata.
4+6+4=14

5.a) Write an algorithm to find out the candidate keys from a given functional dependency of
a relation.
b) Why is functional dependency defined in relational model?
c) What do you understand by attribute preservation?
d) What is Pseudo-transitivity?
6+3+3+2=14
6.a) Consider the following relational schema
r(A,B, C, D,E,F,G)
The following functional dependency is held in the relation
A ->B ; B->C ; C-> D,E ; F ->G
 Maintaining the functional dependency the relation RR is broken as follows
R1(A, B) ; R2(B, C) ; R3(C, D, E) ; R4(F, G)
Verify whether this decomposition is lossless or not.
b) Write the algorithm to check Conflict Serializability of a schedule.
c) Explain Lost-Update problem with example.

5+5+4=14
7. a) Consider the following schedule written in log:
<T0, Start>
< T0, A, 95>
< T0, B, 600>
< T0,Commit>
< T1, Start>
< T1, A, 76>
< T1, B, 300>
What actions will be taken for recovery if i) Deferred Database Modification & ii)
Immediate Database Modification are applied?
b) Why the recovery process of Strict Schedule does not work for Cascadeless Schedule?
c) Consider the following Precedence Graph consisting of Transactions T 1, T2, T3 and T4
T1 T2 T3 T4

What are the possible serial schedules?

d) Describe Wound-Wait scheme. Explain how this scheme prevents Deadlock.

(2+2)+3+3+4=14
 B.Tech. Information Technology 5th Semester Examination, 2021
IT 501: Database Management Systems
Full Marks: 70 Time: 3 Hours
Answer Question 1 and any Six from the rest.

1. Design ER-diagram for an University Examination System. Must use the following entities:
i) Student ii) Teacher iii) Question-Set iv) Marksheet
You can add more entities of your choice.
10

2.a) What is the necessity of secondary index?

b) “Primary index is formed based on primary keys only.” – Justify the statement.
c) Which one is preferable Open Hashing or Closed Hashing in database applications? Explain.
d) For what type of queries indexing preferred over hashing?
3+2+3+2=10

3.a) Consider the relation r(X, Y, Z, B, M, N) and the given functional dependency is
X → Y, Z; Z → B; Y → M, N
The relations is decomposed into 2 relations r 1(Y, Z, B) and r2(Y, X, M, N).
Tell whether this decomposition is lossless or not.
b) How spurious tuples are generated? What strategy should be employed to prevent the
generation of spurious tuples?
5+(2+3)=10

4. Write the following queries using relational algebra (try to use basic relational algebra
operations as much as possible)
Emp(empno, ename, sal,deptno)
Dept(deptno, dname, city)

i) Give the name of the employees who are located at the same city where “Mary” is located.
ii) Give the department name where the employee with highest salary is posted.
5+5=10

5.a) From the given data identify the candidate key(s) of the relation r1(A, B, C, D, E)

A B C D E
a1 b1 c1 d1 e1
a1 b2 c2 d1 e1
a2 b1 c1 d2 e2
a3 b3 c2 d2 e1
a2 b2 c3 d1 e1
b) Write an algorithm to determine α+ (the closure of α) from a given set of functional
dependencies where α is a set of attribute(s).
c) Explain controlled redundancy.
4+4+2=10
6.a) Consider the following relationship r2(A, B, C, X, Y, Z)
Functional Dependencies are given below:
A → X ; C →A; Y→B; B→Z
What is the current Normal form? Normalize it upto 3NF step by step.
b) Explain full functional dependency with example.
c) Give an example of many-to-many relationship.
5+3+2=10

7.a) Write the algorithm of Unlock() operation in exclusive lock.

b) Explain whether the following schedule is conflict serializable or not. If serializable, what
is the serial schedule?

T1 T2 T3
R(Y)
R(Z)
R(X)
W(X)

W(Y)
W(Z)
R(Z)
R(Y)
W(Y)
R(Y)
W(Y)
R(X)
W(X) R(X)
W(X)

5+5=10
8.a) Consider the following schedule written in log:
<T0, Start>
< T0, X, 80>
< T0, Y, 300>
< T0,Commit>
< T1, Start>
< T1, A, 70>
< T1, B, 200>
What actions will be taken for recovery if i) Deferred Database Modification & ii) Immediate
Database Modification are applied?
b) What is the significance of “partially committed” state in transaction state?
c) Explain whether following schedule is recoverable or not?
r1(X), r2(X), w1(X), r1(Y), w2(X), A1
Also check whether the schedule has lost-update problem or not.
d) Explain cautious waiting protocol.
(2+2)+2+(1+1)+2=10