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

Database Management Systems (DBMS) Semester 4 (Revised Course) (3 Hours) December 2009
SP-7805 [Total Marks : 60]
N.B:	(1)	Question no.1 is compulsory.
	(2)	Attempt any four questions out of remaining six questions.
1.	(a)	Define following terms with example :-      (i) Single and composite attributes      (ii) Single valued and multivalued attributes      (iii) Entity Set      (iv) Relationship Set      (v) Aggregation	10
	(b)	What do you mean by Database Management System? Why would you choose a database system instead of simply storing the data in operating system files?	10
2.	(a)	For the given database write SQL queries :-      employee (eid, employee_name, street, city)      Works (eid, cid, salary)      Comapny (cid, company_name, city)      Manager (eid, Manager_name)             (i) Find the names, street and city of all employees who work for "ABC" and earn more than Rs. 20,000. 04 (ii) Find the name of all employees having "i" as the second letter in their names. 03 (iii) Display the annual salary of all employees. 03
	(b)	Discuss the different security and authorization mechanisms in database management system	10
3.	(a)	For the following problem draw an E-R diagram :- "ABC Company" is a software company, that has several employees working on different types of projects, on different platforms. Projects have different schedules and may be in one of several phases. Each project has a project leader and team member at different levels.	10
	(b)	List the ACID properties. Explain the usefulness of each.	10
4.	(a)	Explain type of constraints with an example.	10
	(b)	Explain 1NF, 2NF and 3NF with suitable example.	10
5.	(a)	What is Recoverable Schedule? Why recoverability of schedule is desirable? Are there any circumstances under which it would be desirable to allow non-recoverable schedule? Explain.	10
	(b)	Explain the insertion of entry in a B* tree.	10
6.	(a)	Compare shadow page recovery scheme with log based recovery scheme.	10
	(b)	Explain following relational algebra operations :-      (i) Generalized Projection      (ii) Set Difference      (iii) Assignment      (iv) Aggregate.	10
7.	Write short notes on the following :-		20
	(a)	Timestamp ordering protocol
	(b)	Triggers
	(c)	Database administrator
	(d)	Group by and order by clause in SQL.