Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) December 2011

Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) December 2011
MP-4339 [Total Marks : 100]
N.B:	(1)	Question no.1 is compulsory.
	(2)	Attempt any four questions out of ruminating six questions.
1.	(a)	What are the five main function of a database administrator?
	(b)	List all functional dependencies satisfied by the relation.   A B C a1 a1 a2 a2 b1 b1 b1 b1 c1 c2 c1 c3	10
	(c)	Define the concept of aggregation. Give two example of where this concept is useful.	05
	(d)	When a transaction is rolled back under timestamp ordering, it is assigned a new timestamp. Why can it not simply keep its old timestamp?	05
2.	(a)	Explain the following terms :- (i) Data Independence      (ii) Data Model (iii) Normalization            (iv) Assertion	10
	(b)	What is view in SQL, how it is defined? Discuss the problem that may arise when we attempt to update a view. How views are implemented?	10
3.	(a)	Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents.	10
	(b)	Explain concurrence control in Database system with the help of any two protocols.	10
4.	(a)	List the three design goals for relational database and explain why each is desirable .	10
	(b)	Explain organization of records in files.	10
5.	(a)	List the ACID properties. Explain the usefulness of each.	10
	(b)	Compare shadow page recovery scheme with log based recovery scheme.	10
6.	(a)	Explain the following relational algebra operations with examples :- (i) Generalized Projection     (ii) Set Intersection (iii) Natural Join                   (iv) Assignment.	10
	(b)	Consider the employee database where the primary keys are underlined. Give an expression in SQL for the following gueries : employee (employee-name, street, city) Works (employee-name, comapny-name, salary) Comapny (company-name, city) Manages (employee-name, site     (i) Find all employees ion the database who earn more than each employee of small bank Corporation.   (ii) Find all employees in the database who do not work for First Bank Corporation.   (iii) Find all employees who earn more than the averages salary of all employees of their company.   (iv) Find the names of all employees who work for first Bank corporation.	10
7.	Write short notes on :-		20
	(a)	Data Dictionary Storage
	(b)	Authorization in SQL
	(c)	Insertion of entry in a B+ tree
	(d)	Stored procedures.