14

15

16

17

18

19

b)

௦)

8)

b)

a)
b)

a)
b)

௦)
a)

D1071 Pages: 4

Given a relation R(A,B,C). Find the minimal cover of the set of functional
dependencies given;
F= {A>BC, BC, AB, AB>C}
What is the lossless (or nonadditive) join property of decomposition? Why is it
important?
Consider the relation R = {A, B, C, D, E, F, G, H} and the setof functional
dependencies F ಎ {A—DE, B—F, AB—C, CGH, G—H}. What is the key for R?
Decompose R into 2NF andthen 3NF relations.
PART E

Answer any four full questions, each carries 10 marks.
Suppose that we have an ordered file with 400,000 records stored on a disk with
block size 4,096 bytes. File records are of fixed size and are unspanned,with record
length 200 bytes. How many blocks are needed for the file? Approximately, how
many block accesses are required for a binary search in this file? On an average, how
many block accesses are required for a linear search, if the file is nonordered?
Based on question 15.a, give an example to illustrate that indexing can improve the
search time.
Explain the structure of an internal node and a leaf node in a B+-tree.
Illustrate with an example how searching for a record with search key field value is
done using a B+-Tree.
Why Concurrency Control Is Needed? What are the different types of problems we
may encounter when two transactions run concurrently? Illustrate each problem with
suitable examples.
What are the desirable properties of transactions? Explain.
“If every transaction in a schedule follows the two-phase lockingprotocol, the
schedule is guaranteed to be serializable’, justify the statement.
What are the different types of lock that are commonly used in concurrency control?
Consider the following tables representing courses taken by instructors in an
institute:
INSTRUCTOR(UD, NAME, DEPT, SALARY)
TEACHES(ID, COURSE-ID, SEMESTER, YEAR)
COURSE(COURSE-ID, TITLE, DEPT, CREDITS)

where, ID and COURSE-ID are foreign keys referring to the primary keys with the

Page 3 of 4

(3)

(3)

(9)

(6)

(4)

(5)
(5)

(10)

(4)

(3)

(3)
(10)