Database Management Systems - Visvesvaraya Technological University June July 2013 Question

Database Management Systems - Visvesvaraya Technological University June July 2013 Questions / June July 2013 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers

USN                                                      10CS54 Fifth Semester B.E. Degree Examination, June/July 2013 Database Management Systems Time: 3 hrs.                                                     Max. Marks: 100 Note: Answer any FIVE full questions, selecting at least TWO questions from each part. PART – A 1. a. What are the main characteristics of the database approach over the file processing approach? (08 marks) b. Explain the different categories of data models. (06 marks) c. Explain the three-schema architecture. (06 marks) 2. a. Explain the different types of attributes that occur in ER model. Write their corresponding notations. (08 marks) b. Draw an ER diagram for an employee database. The constraints are as follows; i) An employee works for a department. ii) Every department is headed 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 relational algebra operations to perform the following queries on the schema specified in question 2: i) Retreive the name and addresses of all employees who work for the ‘Accounts’ department. ii) Retrieve the names of all employees who have no dependents. iii) Find the names of employees who work on all projects controlled by department number 2.             (12 marks) b. Explain the relational algebra operations from set theory, with examples. (08 marks) 4. a. Explain the different constraints that can be applied during table creation in SQL with a suitable example.          (08 marks) b. Write the SQL queries for the following database schema; (12 marks) STUDENT (USN, NAME, BRANCH, PERCENTAGE) FACULTY (FID, FNAME, DEPARTMENT, DESIGNATION, SALARY) COURSE (CID, CNAME, FID) ENROLL (CID, USN, GRADE) i) Retrieve the names of all 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. PART – B 5. a. Define views. Give an example to create a view. (08 marks) b. Explain the different approaches for database programming. Explain the problem that arises in some of the approaches. (12 marks) 6. a. List the inference rules for functional dependencies. Write the algorithm to determine the closure of X (set of attributes) under F (set of functional dependencies). (08 marks) b. Define the 1NF, 2NF, and 3NF with a suitable example for each. (12 marks) 7. a. Write the algorithm for testing non-additive join property. (10 marks) b. Explain the 4NF with suitable example. (10 marks) 8. a. Explain the ACID properties of a database transaction. (04 marks) b. Briefly explain the two phase locking protocol used in concurrency control. (08 marks) c. Explain the three phases of ARIES recovery model. (08 marks) *****************