Time : Three Hours
Max. Marks : 80
Notes : 1. All questions carry marks as indicated.
2. Solve Question 1 OR Questions No. 2.
3. Solve Question 3 OR Questions No. 4.
4. Solve Question 5 OR Questions No. 6.
5. Solve Question 7 OR Questions No. 8.
6. Solve Question 9 OR Questions No. 10.
7. Solve Question 11 OR Questions No. 12.
8. Due credit will be given to neatness and adequate dimensions.
9. Assume suitable data whenever necessary.
1. a) Explain DBMS architecture in detail with the help of neat sketch. [10 M]
b) Explain data independence concept in DBMS. [03 M]
2. a) Differentiate between file processing system and DBMS. [07 M]
b) Let R = (A, B, C) and let r1 and r2 both be relations on schema R Give an expression in domain relational calculus that is equivalent to [06 M]
i) πA (r1)
ii) σB=17 (r1)
iii) r1 ∪ r2
iv) r1 – r2
3. Explain the node structure of B+ tree. Construct B + tree for following set of key value. Assume that tree is initially empty and values are added in ascending order
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31) no. of pointers = THREE & FOUR in two different cases. Construct a tree for both. [13 M]
4. a) List various file organization methods & Explain different ways of organizing records in a file. [07 M]
b) Differentiate between [06 M]
i) B and B+ trees.
ii) Sparse and dense index.
5. a) Draw an ER diagram for college management system. [07 M]
b) What is an attribute? Explain different types of attributes. [06 M]
6. What is normalization and why is it needed? Explain the process in detail. Also explain 1NF, 2NF and 3NF with suitable example. [13 M]
7. a) Let relations r1 (A, B, C) and r2 (C, D, E) have following properties. r1 has 20,000 tuples and r2 has 45,000. 25 tuples of r1 on 1 block and 30 tuples of r2 on 1 block.
Estimate number of block access required using each of the following Join strategies of r1 ∞ r2. [07 M]
i) Nested loop join
ii) Block nested loop join
iii) Merge join
iv) Hash join
b) Describe query optimization in detail. [07 M]
8. a) Describe functioning of each step involved in query processing. [07 M]
b) Explain materialized view in detail. [07 M]
9. a) What is serializability? Discuss different types of serializability. [07 M]
b) Explain 2-phase locking protocol. [06 M]
10. a) What is a transaction and its ACID properties? [05 M]
b) Write notes on any three. [08 M]
i) Time stamp based protocol.
ii) Recovery in DBMS.
iii) States of transaction
iv) Deadlock Handling.
11. Explain with suitable example any four. [14 M]
i) GROUP BY…….HAVING clause.
ii) Aggregate operations
iii) Relational set operators.
iv) Dynamic SQL & Embedded SQL
12. a) Consider the relation customer (Id, name, age, address, salary). Write a PL/SQL function that will return total number of customers in customer table. [04 M]
b) What is nested sub query? Explain with the help of example. [07 M]
c) Explain CREATE TABLE command with example. [03 M]