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

Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) December 2013
LJ-10494 [Total Marks : 100]
N.B:	(1)	Question no.1 is compulsory.
	(2)	Out of the remaining six solve any four questions.
	(3)	Assume suitable data if required.
1.	(a)	Galleries keep information about artists, their names (which are unique birthplace, age, and style of art For each piece of artwork the artist. The year it was made its unique title, its type of art (e.g. painting, sclpture), and its price must be stored.   (i) Pieces of artwork are also classified into groups of various kinds e.g. portraits, still life works by Picasso or works by 19th century a given pieces may belong to more than one group. (ii) Each group is identified by a name (like those given) that describes the group. (iii) Galleries keep information about customers like persons (unique name, address, total amt spent, artist and the group of art that the customer tends to like). (iv) Draw ER diagram for the database and convert it into equivalent scheme.	08
	(b)	Explain the following terms with example (2 marks each)   (i) Weak entity set (ii) Project operator in relational algebra (iii) Foreign Key (iv) Join (v) Data manipulation language.	10
2.	(a)	Explain first, escond and third normal forms with example.	10
	(b)	Explain two phase locking protocol.	10
3.	(a)	Person (driver-id, name, address) car (license, model, year) accident (report-number, date location) owns (driver-id, license) participated (driver-id, car, report-number, damage amount)   (i) Create relations persons owns in SQL (ii) Add a new accident to the database, assume any values for required attribute. (iii) Delete the SKODA belogning to 'Sachin Parker'. (iv) Find the total number of people who owned cars that were involved in accident in 1999. (v) Find the person whose names starts with 'S' and arrange in decreasing order of driver-id.	10
	(b)	Explain any five relational algebra operators.	10
4.	(a)	What is a transaction discuss ACID properties of transaction.	10
	(b)	Explain data dictionary storage.	05
	(c)	Explain data independence.	05
5.	(a)	Explain UNDO and REDO operations for log based recovery. How are they used during recovery.	08
	(b)	Describe methods for deadlock recovery.	06
	(c)	Give one protocol that prevents deadlock.	06
6.	(a)	Explain differed modification technique for log based recovery.	10
	(b)	Explain conflict serializability.	10
7.	Write short notes on (any four) :-		20
	(a)	Mapping Cardinality
	(b)	Aggregate functions in SQL
	(c)	Shadow paging
	(d)	Checkpoints
	(e)	Views in SQL.