Code: C-14 / T-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. The data models defined by ANSI/SPARC architecture are
(A) Conceptual, physical and internal.
(B) Conceptual, view and external.
(C) Logical, physical and internal.
(D) Logical, physical and view.
b. Whenever two independent one-to-many relationships are mixed in the same relation, a ___________ arises.
(A) Functional dependency (B) Multi-valued dependency
(C) Transitive dependency (D) Partial dependency
c. A table can have only one
(A) Secondary key. (B) Alternate key.
(C) Unique key. (D) Primary key.
d. Dependency preservation is not guaranteed in
(A) BCNF. (B) 3NF.
(C) PJNF. (D) DKNF.
e. Which is the best file organization when data is frequently added or deleted from a file?
(A) Sequential (B) Direct
(C) Index sequential (D) None of the above
f. Which of the following constitutes a basic set of operations for manipulating relational data?
(A) Predicate calculus (B) Relational calculus
(C) Relational algebra (D) SQL
g. An advantage of views is
(A) Data security. (B) Derived columns.
(C) Hiding of complex queries. (D) All of the above.
h. Which of the following is not a recovery technique?
(A) Deferred update (B) Immediate update
(C) Two-phase commit (D) Shadow paging
i. Isolation of the transactions is ensured by
(A) Transaction management. (B) Application programmer.
(C) Concurrency control. (D) Recovery management.
j. The keyword to eliminate duplicate rows from the query result in SQL is
(A) DISTINCT. (B) NO DUPLICATE.
(C) UNIQUE. (D) None of the above.
Answer any FIVE Questions out of EIGHT Questions.
Each question carries 16 marks.
Q.2 a. Define cardinality and participation constraints on a relationship type and completeness constraint on generalization. (6)
b. Consider the following ER diagram
|
Map the diagram to tables. Specify the table names and their attributes. (10)
Q.3 a. Describe the structure of a well formed formulae in relational calculus. (6)
b. Consider the relations given below with keys underlined
Branch(branchno, street, city)
Staff(staffNo, name, salary, branchNo, position, DOB)
PropertyForRent(propertyNo, staffNO, rent)
Answer the following queries in relational algebra
(i) Find the names of Staff who work in Delhi.
(ii) List the staffNo of Staff who have not rent any property. (6)
c. What is the result relation if we perform the relational algebra operator as shown below
OFFICE 
for the relations given below (4)
|
PUBLISHER |
|
|
OFFICE |
||
|
PID |
NAME |
HQ |
|
PID |
CITY |
|
01 |
Acer |
Mumbai |
|
01 |
New York |
|
02 |
Signet |
New York |
|
01 |
London |
|
03 |
Dell |
London |
|
02 |
London |
|
04 |
HP |
Sydney |
|
02 |
Sydney |
|
|
|
|
|
03 |
London |
|
|
|
|
|
03 |
New York |
|
|
|
|
|
04 |
New York |
|
|
|
|
|
04 |
London |
Q.4 a. In SQL, differentiate between
(i) a subquery and a join
(ii) WHERE and HAVING Clauses
(iii) DROP and DELETE
(iv) LEFT OUTER JOIN and RIGHT OUTER JOIN (8)
b. For the relations given in Q3 (b), answer the following queries in SQL
(i) How many properties cost more than Rs.5000/- per month?
(ii) Give all Managers a 5% pay increase.
(iii) Find the number of staff working in each branch and the sum of their salaries. (2+3+3)
Q.5 a. Define a B+ tree. (2)
b. Consider a B+ tree with order 2 with the following elements
|
(i) Insert entry 8.
(ii) Delete entry 19 and 20. (5*2)
c. Describe the definition of a transaction in SQL. (4)
Q.6 a. Define dependency preserving and lossless join decomposition. (4)
b. Given
R = ABCD with the FD set F = 
Determine all 3NF violations. Decompose the relations into relations which are in 3NF. (8)
c. Determine a candidate key for R = ABCDEG with FD set
F
= 
(4)
Q.7 a. What are the objectives of query processing? (3)
b. Explain the transformation rules that apply to
(i) Selection (ii) Projection
(iii) Theta join operations (9)
c. When are two schedules said to be view equivalent? (4)
Q.8 a. Define deadlock. Produce a wait-for graph for the following transaction scenario and determine whether a deadlock exists
|
Transaction |
Data items locked |
Data items waiting for |
|
T1 |
x2 |
x1, x3 |
|
T2 |
x3, x10 |
x7, x8 |
|
T3 |
x8 |
x4, x5 |
|
T4 |
x7 |
x1 |
|
T5 |
x1, x5 |
x3 |
|
T6 |
x4, x9 |
x6 |
|
T7 |
x6 |
x5 |
(2+8)
b. Define granularity, hierarchy of granularity of locks and multiple granularity locking. Describe the modified two phase locking with multiple granularity locking. (6)
Q.9 a. What are the advantages of using a DBMS? (8)
b. Define the following:-
(i) Attribute Inheritance.
(ii) Cascading rollback.
(iii) Exec statement in SQL.
(iv) Project-Join normal form. (8)