www.nagpurstudents.

org
 B.E. (Computer Science Engineering) Fifth Semester (C.B.S.)
Database Management System

P. Pages : 3 NIR/KW/18/3435
Time : Three Hours *1899* Max. Marks : 80
_____________________________________________________________________
Notes : 1. All questions carry marks as indicated.
2. Solve Question 1 OR Questions No. 2.

rg
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.

s.O
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.
10. Illustrate your answers whenever necessary with the help of neat sketches.

1. a) What are the draw-backs of file processing system? 6

b) Define:
i) Entity
nt ii) Entity Set
8
de
iii) Attribute iv) Internal schema
v) Conceptual schema vi) External schema
vii) Database schema viii) Database
tu

OR

2. a) Enlist different database users & explain their role. 6

S

b) Consider the following relational database 8

ur

SALESPERSON (Name, Percent_of_Quta, salary)

ORDER (Number, Custname, salespersonname, amount)
CUSTOMER (Name, city, Industrytype)
gp

for each of the query given below, give the expression in SQL.
i) Find the names and quota percentages of salespeople who have an order with ASIAN
CONSTRUCTION in descending order of quota percentage.
ii) Find the quota percentage of salespeople who have an order with a customer in
Na

MUMBAI.
iii) Find the names of salespeople who have two or more orders.
iv) Find the names of salespeople who have an order with all customers.

3. a) Describe any three aggregate function and any three string functions in SQL. 6

b) Explain different types of keys that are used in RDBMS. 7

OR

NIR/KW/18/3435 1 P.T.O
4. a) What do you mean by referential Integrity? How it is achieved in SQL? 6

b) Consider the following database schema : 7

Employee (ename, SS#, Add, Salary, Sex)
Dept (D_name, Dno, Magrss#, mgrstart_date)
Dept_Location (Dno, Dlocation)
Project (Pname, Pno, Plocation, Dno)
Works_On (SS#, Pno, hours)

rg
Solve the following queries in relational Algebra
i) Retrieve average salary of all female employees.
ii) Retrieve the names & addresses of all employees who work in “Research Department”.

s.O
iii) For each project, List the project name and total hours per week spent on that project.
iv) Find all employee in dept. No. 4 who work for more than 12 hours per week.

5. a) Define functional dependency. Explain the rules of Inference or Armstrong axioms with 6
supporting rules.

nt
Differentiate between B tree & B+ tree. Why B+ tree is usually preferred over B tree?
b) 7

OR
de
6. a) What do you mean by Primary & Secondary Indexing? Also explain sparse & dense 6
Indexing.
tu

b) Define Normalization? Explain 1NF, 2NF & 3NF, 4NF with example. 7

7. a) Let Relation R1(A, B, C) & R 2 (C, D, E) have following properties. 8

R1 has 10,000 tuples & R 2 has 15,000 tuples where 20 tuples of R1 on one block and 15
S

tuples of R 2 on one block. Estimate no. of block access required using each of the following
ur

join strategies of R1 & R 2

a) Merge Join b) Hash Join
c) Block Nested Loop Join d) Nested Loop Join
gp

b) Explain different phases involved in query processing. 6

OR
8. a) What is meant by materialization? Explain it with the help of example. 6
Na

b) Let relation r1(A, B, C) and r2 (C, D, E) have the following properties : r1 has 20,000 tuples, 8
r2 has 45,000 tuples, 25 tuples of r1 fit on one block and 30 tuples of r2 fit on – one block.
Estimate the number of block transfers required using each of the following join strategies
for r1 ⋈ r2 .
i) Nested loop Join ii) Block nested loop join
iii) Merge Join iv) Hash Join

NIR/KW/18/3435 2
9. a) Explain two phase commit protocol in detail. 6

b) Define transaction. Explain atomicity, durability, isolation & consistency preservation 7

properties of a database transaction.

OR

10. a) What is deadlock? Explain different deadlock prevention techniques. 7

rg
b) Explain the working of ARIES recovery algorithm. 6

11. a) Describe the issues in Data Security. 7

b) What is log based recovery technique. 6

s.O
OR

12. Write a short note on any four. 13

nt
i) Shadow Paging.

ii) Check points.

de
iii) Distributed database.

iv) Recovery in database.

v) Data Mining.
tu

**********
S
ur
gp
Na

NIR/KW/18/3435 3 P.T.O
NIR/KW/18/3435
Na
gp
ur
S

4
tu
de
nt
s.O
rg
 www.nagpurstudents.org

All our dreams can come true if we have the courage to

pursue them.
~ Walt Disney