Anna University Chennai 2005 B.E Computer Science DATABASE MANAGEMENT SYSTEMS - Question Paper

diagram for every table. (4+5)
f. In 2NF
(A) No functional dependencies (FDs) exist
Q.5 a. Consider the subsequent relations with underlined primary keys
Product(P_code, Description, Stocking_date, QtyOnHand, MinQty, Price, Discount, V_code)
Vendor(V_code, Name, Address, Phone)
Here a vendor can supply more than 1 product but a product is supplied by only 1 vendor. Write SQL queries for the subsequent :
(i) List the names of all the vendors who supply more than 1 product.
(ii) List the details of the products whose prices exceed the avg.
product price.
(iii) List the Name, Address and Phone of the vendors who are currently
not supplying any product. (3 x 3)

b. Define the domain relational calculus. (5)

Q.6 a. Given R(A,B,C,D,E) with the set of FDs, . Is the decomposition of R into R1(A,B,C),R2(B,C,D) and R3(C,D,E) lossless? Prove. (5)

b. Given R(A,B,C,D,E) with the set of FDs,

(i) obtain any 2 candidate keys of R
(ii) What is the normal form of R? Justify. (9)
PART II
ans any 3 ques.. every ques. carries 14 marks.
Q.7 a. How does a query tree represent a relational algebra expression? explain any 3 rules for query optimisation, giving example as to when should every rule be applied. (8)

b. Consider the subsequent database with primary keys underlined
Project(P_No, P_Name, P_Incharge)
Employee(E_No, E_Name)
Assigned_To(P_No, E_No)
Write the relational algebra for the subsequent :
(i) List details of the employees working on all the projects.
(ii) List E_No of employees who do not work on project number DB2003. (6)

Q.8 a. What is a hashing function? elaborate the properties of a good hashing function? define the folding technique for hashing functions. (7)

b. Describe the issues of lost update, inconsistent learn and phantom phenomenon which arise as a outcome of concurrency. (7)
.
(B) No multivalued dependencies (MVDs) exist.
Q.9 a. Define
(i) Shared locks.
(ii) Serializable schedule.
(iii) Thomas write rule.
(iv) 2 phase commit. (4)
b. Let transactions T1, T2 and T3 be described to perform the subsequent operations :
T1 : Add 1 to A
T2 : Double A
T3 : Display A on the screen and then set A to one.
(where A is a few item in the database)
Suppose transactions T1, T2 and T3 are allowed to execute concurrently. If A has initial value zero, how many possible accurate outcomes are there? Enumerate them. (10)
Q.10 a. Define and differentiate ranging from the subsequent :-
(i) Deadlock prevention.
(ii) Deadlock detection.
(iii) Deadlock avoidance. (6)
b. The subsequent represents the sequence of events in a schedule involving transactions T1, T2, T3, T4 and T5. A,B, C, D, E, F are items in the database.

T2 : learn B
T4 : learn D
T2 : learn E
T2 : Write E
T3 : learn F
T2 : learn F
T1 : learn C
T5 : learn A
T5 : Write A
T1 : learn E
T5 : learn C
T3 : learn A
T5 : Write C
T2 : Write F
T4 : learn A
Draw a wait-for-graph for the data above and obtain whether the transactions are in a deadlock or not? (8)
Q.11 a. Write short notes on any 4 of the following: (3.5x4=14)

(i) 2 phase locking protocol.
(ii) Audit Trails.
(iii) Query Processing.
(iv) Disadvantages of file based systems.
(v) Query-by-Example.
(vi) B-tree.

(C) No partial FDs exist.
(D) No partial MVDs exist.
g. The language that requires a user to specify the data to be retrieved without specifying exactly how to get it is

(A) Procedural DML. (B) Non-Procedural DML.
(C) Procedural DDL. (D) Non-Procedural DDL.
h. Precedence graphs help to obtain a
(A) serializable schedule. (B) recoverable schedule.
(C) deadlock free schedule. (D) cascadeless schedule.

Earning:   Approval pending.