http://www.rtmnuonline.
com
B.E. (Computer Science Engineering) Fifth Semester (C.B.S.)
DBMS (Database Management System)
P. Pages : 2 NJR/KS/18/4490
Time : Three Hours *0212* 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. Illustrate your answers whenever necessary with the help of neat sketches.
1. a) Explain different types of data models used in database. 7
b) Consider the following relation for a database
employee ( emp _ name , street, city)
works ( emp _ name , company_name, salary)
company ( company _ name , city)
manager ( emp _ name , manager_name)
where the primary keys are underlined.
Give an expression in SQL for each of the following queries.
a) Find the names and cities of residence of all employees who work for first Bank 2
corporation.
b) Find the names, street address and cities of residence of all employees who work for 2
first Bank corporation and earn more than $10000.
c) Find the name of employee who works for 'IBM' and earns salary greeter than 20,000. 1
c) Find all employees in the database who do not work for First Bank corporation. 2
OR
2. a) Draw and explain ER diagram for college Management system. 6
b) What is significance of view? Also mention its syntax in SQL. 4
c) What are the drawbacks of file processing system? 4
3. a) Discuss primary key and foreign key with suitable example. 5
b) Describe with suitable example in relational algebra 8
i) Union ii) Natural Join iii) Intersection iv) Set difference
OR
4. a) What do you mean by referential integrity? How it is achieved in SQL? 6
NJR/KS/18/4490 1 P.T.O
b) Discuss any three aggregate function and any three string function with example. 7
5. a) Define Normalization. Explain 1NF, 2NF, 3NF and BCNF with suitable example. 7
b) Compute the closure of the following set F of FD's for the relation R = (A, B, C, D, E) where 7
A BC, CD E, B D, E A List all candidate key of R.
OR
6. a) Write short note on: 6
i) Primary and Secondary indexing. ii) Sparse and Dense indexing.
b) Construct B+ tree for the following set of key values 20, 15, 5, 1, 3, 9, 2 having 8
n = 4, 6 & 8.
7. a) What is pipelining? Discuss its types. 6
b) What is mean by Materialization? Explain it with the help of example. 7
OR
8. a) What is Query optimization & its various techniques. 6
b) What is Query processing? Explain steps involved in query processing. 7
9. a) Define Transaction. What are the different states of transactions? Give ACID properties of 7
transactions.
b) Explain two phase commit protocol in detail. 6
OR
10. a) What is serializability? Explain conflict & view serializability. 7
b) What are the different buffer management techniques? 6
11. a) Describe different types of failures that occurs in the system? How they are recovered. 7
b) Write a short note on checkpoint. 3
c) Write a short note on shadow paging. 3
OR
12. Write a short note on any three.
i) Distributed database. 5
ii) Web database. 4
iii) Data warehouse 4
iv) Data Mining. 4
************
NJR/KS/18/4490 2
