Code: A-20

Code: AC-14 / AT-11 Subject: DATABASE MANAGEMENT SYSTEMS

Time: 3 Hours Max. Marks: 100

NOTE: There are 9 Questions in all.

· Question 1 is compulsory and carries 20 marks. Answer to Q. 1. must be written in the space provided for it in the answer book supplied and nowhere else.

· Out of the remaining EIGHT Questions answer any FIVE Questions. Each question carries 16 marks.

· Any required data not explicitly given, may be suitably assumed and stated.

Q.1 Choose the correct or best alternative in the following: (2x10)

a. Which of the following is a reason to model data?

(A) Understand each user’s perspective of data

(B) Understand the data itself irrespective of the physical representation

(C) Understand the use of data across application areas

(D) All of the above

b. If an entity can belong to only one lower level entity then the constraint is

(A) disjoint (B) partial

(C) overlapping (D) single

c. The common column is eliminated in

(A) theta join (B) outer join

(C) natural join (D) composed join

d. In SQL, testing whether a subquery is empty is done using

(A) DISTINCT (B) UNIQUE

(C) NULL (D) EXISTS

e. Use of UNIQUE while defining an attribute of a table in SQL means that the attribute values are

(A) distinct values (B) cannot have NULL

(C) both (A) & (B) (D) same as primary key

f. The cost of reading and writing temporary files while evaluating a query can be reduced by

(A) building indices (B) pipelining

(C) join ordering (D) none of the above

g. A transaction is in __________ state after the final statement has been executed.

(A) partially committed (B) active

(C) committed (D) none of the above

h. In multiple granularity of locks SIX lock is compatible with

(A) IX (B) IS

(C) S (D) SIX

i. The statement that is executed automatically by the system as a side effect of the modification of the database is

(A) backup (B) assertion

(C) recovery (D) trigger

j. The normal form that is not necessarily dependency preserving is

(A) 2NF (B) 3NF

(C) BCNF (D) 4NF

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

Q.2 a. Briefly describe the different kinds of users of a DBMS. (6)

b. Define the concept of aggregation. Give two examples where this concept is useful. (4)

c. Explain the following. Give an example

(i) Superkey

(ii) Weak entity set

(iii) Attribute inheritance (6)

Q.3 a. Define the following

(i) A relation.

(ii) Atom of domain relational calculus. (4)

b. Given the relations R(A, B, C) and S(C,D,E,F) give an expression in tuple relational calculus that is equivalent to each of the following

(i)

(ii)

(iii)

(iv) (12)

Q.4 Given the relations Staff (staffNo, position, salary) and Property (number, rent, staffNo) given below. The staff looks after a given property.

Staff

staffNo	position	salary
SL21	Manager	50000.00
SL37	Assistant	15000.00
SG14	Supervisor	25000.00
SG5	Manager	45000.00

Property

Number	Rent	Staffno
PA14	5000.00	SL21
PG4	6000.00	SG5
PL94	10000.00	SL21

Give the result table for the following SQL queries

(i) SELECT position, COUNT(staffNo) AS POS, my count

FROM Staff

(ii) SELECT staffNo

FROM Staff

WHERE salary > (SELECT AVG(salary) FROM Staff)

(iii) SELECT staffNo

FROM Property

GROUP By staffNo

HAVING COUNT(*) > 1

(iv) INSERT INTO Staff

VALUES (‘SG33’, ‘Assistant’) (16)

Q.5 a. Derive the union rule, decomposition rule and the pseudotransitvity rule using the three Armstrong’s axioms. (6)

b. Define multivalued dependency. What do understand by trivial multivalued dependency? (4)

c. Given R(A, B, C, D, E) and M the set of multivalued dependencies

(i)

(ii)

(iii)

Is R in 4NF? Justify your answer. If it is not, decompose R into 4NF. (6)

Q.6 a. Describe the four main ways of optimising disk block access. (8)

b. Describe the algorithm for updating indices for a single level index when a record is

(i) inserted (ii) deleted

What will be the modification if there are multilevel indices. (8)

Q.7 a. How do you estimate the query cost for natural join when

(i)

(ii) is a foreign key (6)

b. Given two relations R (A,B) and S (B,C) with number of tuples in R and S equal to 500 and 1000 respectively and B is the foreign key in R, what is the number of tuples in . (4)

c. Explain Thomas’ Write rule. Show how it is different from timestamp ordering protocol. (6)

Q.8 a. Explain

(i) recoverable schedule. (ii) cascadeless schedule. (8)

b. Define two-phase locking protocol. (2)

c. Consider the following two transactions

T1 : read (A);

read (B);

B = A + B;

write (B)

T2 : write (A)

read (B)

Add lock and unlock instructions so that the transaction T1 and T2 observe two-phase locking protocol. Is it deadlock free? (6)

Q.9 a. Explain the recovery process of a checkpoint mechanism. How does the frequency of checkpoints affect

(i) system performance when no failure occurs.

(ii) the time it takes to recover from a system crash. (8)

b. Write short notes on

(i) hash file organization.

(ii) physical and logical independence. (8)