Database Management Systems - Jun 2013

Computer Science Engg. (Semester 5)

TOTAL MARKS: 100
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any four from the remaining questions.
(3) Assume data wherever required.
(4) Figures to the right indicate full marks. 1 (a) What are the main characteristics of database approach over the file processing approach?(8 marks) 1 (b) Explain the different categories of data models.(6 marks) 1 (c) Explain the three-schema architecture.(6 marks) 2 (a) Explain the different types of attributes that occur in the ER model. Write their corresponding notations.(8 marks) 2 (b) Write the ER diagram for an employee database. The constraint are as follows:
(i) An employee works for a department
(ii) Every department is handed by a manager
(iii) An employee works on one or more projects
(iv) An employee has dependents
(v) A department controls the projects.(12 marks) 3 (a) Write the realtional algebra operations to perform the following queries:
(i) Retrieve the name address of all employees who work for the "Accounts" department
(ii) Retrieve the names of employee who have no dependents
(iii) Find the names of employees who work on all the projects controlled by department number 2. (12 marks) 3 (b) Explain the relational algebra operations from set theory, with examples.(8 marks) 4 (a) Explain the different constraint that can be applied during table creation in SQL, with a suitable example.(8 marks) 4 (b) Write the SQL, queries for the following database schema
STUDENT (USN, NAME, BRANCH, PERCENTAGE)
FACULTY (FID, FNAME, DEPARTMENT, DESIGNATION, SALARY)
COURSE (CID, CNAME, FID)
ENROLL (CID, USN, GRADE)
(i) Retrieve the names of students enrolled for the course 'CS-54'
(ii) List all the departments having an average salary of the faculties above Rs.10,000
(iii) List the names of the students enrolled for the course 'CS-51' and having 'B' grade.(12 marks) 5 (a) Define views, Give an example to create a view.(8 marks) 5 (b) Explain the different approaches for database programming. Explain the problem that arise in some of the approaches.(12 marks) 6 (a) List the inference rule for functional dependencies. Write the algorithm to determine the closure of X (set of attributes) under F(Set of functional dependancies). (8 marks) 6 (b) Define the INF, 2XF and 3NF with a suitable example for each.(12 marks) 7 (a) Write the algorithm fro testing non additive join property.(10 marks) 7 (b) Explain the 4NF with a suitable example.(10 marks) 8 (a) Explain the ACID properties of a database transaction.(4 marks) 8 (b) Briefly explain the two phase locking protocol used in concurrency control(8 marks) 8 (c) Explain the three phase of the ARIES recovery model.(8 marks)