DATABASE MANAGEMENT (DBM) S.E.I.T/SEM - III (December 2013)

DATABASE MANAGEMENT (DBM)

 S.E.I.T/SEM - III (December 2013)

Con.9956-13.                                                                                                 GX-12200

                                                                  (3 Hours)                             [Total Marks: 80]

N.B: (1) Question No.1 is compulsory.
        (2) Solve any three questions out of remaining five.

1.(a)	Define Data Independence and explain types of data Independence.	5
(b)	List all Functional dependencies satisfied by the relation.	5
(c)	Explain Generalization and Specialization.	5
(d)	Explain the steps in query processing.	5
2.(a)	Explain the steps of an algorithm for ER to relational mapping.	10
(b)	Explain different Integrity Constraint.	10
3.(a)	Draw an E-R diagram for university database consisting of 4 entities.	10
	(i) Student
	(ii) Department
	(iii) Class
	(iv) Faculty and convert it to tables.
	A student has a unique id, the student can enroll for multiple classes and has at most one major.
	Faculty must belong to department and faculty can take multiple classes.
	Every student will get a grade for the class he/she was enrolled.
(b)	Draw and explain Database system structure.	10
4.(a)	Consider the following employee database.	10
	Employee (empname, street, city, date_of_joining)
	Works (empname, company_name, salary)
	Company (company_name, city)
	Manages (empname, manager_name).
	Write SQL queries for the following statements :-
	(i) Modify the database so that ‘Jhon’ Now lives in ‘Mumbai’.
	(ii) Give all employees of ‘ABC Corporation’ a 10% raise.
	(iii) List all employees who live in the same cities as their managers.
	(iv) Find all employees who earn more than average salary of all employees of their company
(b)	Explain Time –stamp ordering protocol.	10
5.(a)	Consider a dependency diagram of relation R and normalize it up to third normal form.	10
(b)	Explain log-based Recovery.	10
6.(a)	Draw a query tree for the following SQL query.	10
	Select            P.Pnumber, P.Pdnum, E.Lname, E.Address, E.Bdate.
	From             Project as P, Department as D, Employee as E.
	Where           P.Dnum = D.Dnumber
	AND             D.Mgr_ssn = E.ssn.
	AND             P.Plocation = ‘Chennai ‘.
(b)	Explain following relational algebra operations with proper examples.	10
	(i) Project
	(ii) Left outer join.
	(iii) Division
	(iv) Rename
	(v) Natural join

Also see Database management question papers May 2012