Database Management System | Nagpur university | Winter-17


B.E. (Information Technology) Sixth Semester (C.B.S.)

Database Management Systems

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. Assume suitable data whenever necessary.
9. Illustrate your answers whenever necessary with the help of neat sketches.

1. a) Give classification of DBMS and explain using the followingi) Classification on the basis of number of users.  [08 M]
ii) Classification on the basis of site location.
iii) Classification on the basis of Type and extent of use.

b) What do you mean by data abstraction? Explain three levels of data abstraction and also three level architecture of database system with reference to above levels.  [06 M]


2. a) Consider the following relational scheme- R = (A, B, C) Let  r 1& r2 both be relations on schema R. Give an expression in Tuple & domain relational calculus that is equivalent to each of the following. [08 M]
i) π(r1)
ii) σB=17 (r1)
iii) r1 ∪ r2
iv) r1 ∩ r2

b) What do you mean by data independence? What are its types? Differentiate between the types of data independence. [06 M]

3. a) Explain various factors which affects on selection of file organization methods.  [05 M]

b) Construct B+ tree for the following set of key values – (2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and the number of pointers that will fit in one node   is  [08 M]
i) n=4
ii) n=6
iii) n=8


4. a) Differentiate between Hashing and Indexing. Also discuss open and closed hashing.  [08 M]

b) On what factors, ordered indexing and hashing must be evaluated? [05 M]

5. a) Design a generalization – specialization hierarchy for a motor vehicle sales company. The company sells motor cycle, passenger cars, vans and buses. Justify your placement of attribute at each level of the hierarchy. Explain why they should not be placed at a higher
or lower level.  [08 M]

b) List various E -R diagram notations and Explain meaning associated with each notation.  [05 M]


6. a) Consider the relation R (A, B, C, D, E) with following dependencies:
AB → C,  CD → E,  DE → B              
         Is AB or ABD a candidate key of the relation? Explain answer with proper Justification.    [06 M]

b) Consider the relation schema R (A, B, C, D, E) and set F of functional dependency
F= {AB → CE, 
      E AB
what is the highest normal form of this relation? Explain.
.Also find its prime, nonprime attributes along with several candidate keys.       [07 M]

7. a) Explain the following terms:     [06 M]

i) Equivalence of Expression.
ii) Selection operation in query processing.

b) What is meant by term heuristic optimization? Discuss the main heuristics that are applied during query optimization.    [07 M]


8. a) How an expression can be evaluated with the help of materialization and pipeline approach? Explain in detail.   [07 M]

b) Let relations R1=(A,B,C) & R2=(C,D,E)  have following properties.
R1 has 20,000 tuple and R2  has 45,000 tuples, 25 tuples of R1 on one block and 30 tuples of R2 on one block estimate number of block accesses required using each of the following Join strategies for R1 ∞ R2      [06 M]

i) Nested loop join
ii) Block nested loop join
iii) Merge join

9. a) What is serializability? Explain conflict and view serializability.    [07 M]

b) What is transaction? Explain states and ACID properties of transaction.    [06 M]


10. a) Describe different types of failures that occurs in the system? How are they recovered?     [07 M]

b) Explain different concurrency problems and give solutions for it.
[06 M]

11. a) Consider the following relational database.  [06 M]
Salesperson (name, percent_of_quota, sal)
order (number, custname, salespersonname, amt)
customer (name, city, industry type)
for each of the queries given below, give expression in SQL.
i) Find names and quota percentage of salespeople who have an order with ASIAN CONSTRUCTION in descending order of quota percentage.
ii) Find quota percentage of salespeople who have an order with a customer in MUMBAI.
iii) Find names of salespeople who have two or more orders.
iv) Find the names of salespeople who have an order with all customers.

b) Define integrity constraints. Explain different types of integrity with suitable example.  [08 M]


12. a) Write short note on:  [07 M]
i) Dynamic SQL                                 ii) Embedded SQL

b) Consider the schema customer (Id, name, age, address, salary). Write a trigger that would fire for insert or update or delete operations performed on customer table.    [07 M]


Scroll to Top
error: Alert: Content is protected !!