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) *****************

Database Management System Question - Pune University December 2012

Database Management System Question - Pune University December 2012 / DBMS Computer Engineering Question of Pune University / DBMS for BE-BTech CSE and IT of Pune University

[4263] – 341

T.E. (Computer / IT Engg.)

DATABASE MANAGEMENT SYSTEMS

(2008 Pattern) (Semester - I)

Time : 3 Hours                                                             Max. Marks : 100

Instructions : i) Answers to the two Sections must be written in separate books.

ii) Neat diagrams must be drawn whenever necessary.

iii) Assume suitable data if necessary.

iv) Solve Section I : Q.1 or Q.2, Q.3 or Q.4, Q.5 or Q.6.

v) Solve Section II : Q.7 or Q.8, Q.9 or 10, Q.11 or Q.12.

SECTION – I

Q1) a) Explain various components and database system structure with the help of neat diagram. [10]

b) Explain multi-user DBMS architecture. [4]

c) Specify the CODD’s norms to be specified by RDBMS. [4]

OR

Q2) a) What are the various features of extended E-R diagrams [EER]. Explain in details with the help of example. [10]

b) What is a data abstraction? [4]

c) What is integrity constraint? Explain referencial integrity constraints. [4]

Q3) a) Consider the relational database: [8]

dept (dept_no, dname, LOC)

emp (emp_no, ename, designation)

project (proj_no, proj_name, status)

empproject (emp_no, proj_no)

dept and emp are related as 1 to many

proj and emp are related as 1 to many

Write relational algebra expressions for the following:

i) List all employees of ‘INVENTORY’ department of ‘PUNE’ location.

ii) Give the names of employees who are working on ‘Blood Bank’ project.

iii) Give the name of managers from ‘MARKETING’ department.

iv) Give all the employees working under status ‘INCOMPLETE’ projects.

b) Explain stored procedures and triggers with example. [8]

OR

Q4) a) Explain various join operations with example. [8]

b) What is cursor? Explain various types of cursor. [8]

Q5) a) State and prove Armstiong’s axioms for functional dependencies. [8]

 b) A set of FD’s for the relation R (A, B, C, D, E, F) is AB → C, C → A, BC → D, ACD → B, BE → C, EC → FA, CF → BD, D → E. Find the minimum canonical cover for this set of FD’s. [8]

OR

Q6) a) What is decomposition? Suppose that we decompose the schema R = (A, B, C, D, E) into (A, B, C) and (A, D, E), show that this decomposition is a loseless decomposition if the following set F of functional dependencies holds:

A → BC, CD → E, B → D, E → A. [8]

b) Define Boyce-Codd normal form. How does it differ from 3NF? Why is it considered a stronger form of 3NF? [8]

SECTION – II

Q7) a) Construct a B+ tree for the following set of key values.

(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)

Assume order of tree is 4. [8]

b) Explain the role of “selection” operation in query processing. [8]

OR

Q8) a) What is ordered indices? Explain the types of ordered indices with suitable example. [8]

b) Explain equivalence rules for query optimization. [8]

Q9) a) Which are different crash recovery methods? Explain any one in detail? [8]

b) What is meant by ‘Transaction’? Explain obstruct transaction model with state diagram. [8]

OR

Q10) a) Explain two-phase locking protocol. How does it ensure resializality? [8]

b) Explain shadow paging recovery and log-based recovery scheme. [8]

Q11) Write a short note on any three: [18]

a) Data warehouse.

b) Association rules for data mining.

c) Distributed database system.

d) Persistent programming languages.

OR

Q12) a) Compare relational model Vs object-oriented model. [6]

b) What is fragment of a relation? What are the main types of fragments? Why is fragmentation a useful concept in distributed database design? [8]

c) Explain the need of backup and replication. [4]

——————

Database Management Systems (DBMS) Question - Pune University May 2012

Database Management Systems (DBMS) Question - Pune University May 2012 / Pune University BE (CSE) and BTech (IT) DBMS Question - May 2012

[4163] – 341

T.E. (Computer Engg.) (Semester – I) Examination, 2012

DATABASE MANAGEMENT SYSTEMS

(Common to IT)

(2008 Pattern) (New)

Time: 3 Hours                                           Max. Marks: 100

Instructions : 1) Answers to the two Sections should be written in separate books.

2) Neat diagrams must be drawn wherever necessary.

3) Assume suitable data, if necessary.

4) Section I: Q 1 or Q 2, Q 3 or Q 4, Q 5 or Q 6.

5) Section II: Q 7 or Q 8, Q 9 or Q 10, Q 11 or Q 12.

SECTION – I

Q1) a) Compare various data models. [10]

b) Explain in detail the different levels of data abstraction. [4]

c) Compare DBMS and file processing system with following points. [4]

i) Redundancy. ii) Access Control.

OR

Q2) a) What is difference between specialization and generalization? Why do we not display this difference in schema diagram? [6]

b) Specify the CODD’s norms to be specified by RDBMS. [6]

c) What are the enhancements that distinguish the EER model from the ER Model? Explain with example. [6]

Q3) a) Consider following relational database;             [8]

Employee (emp_name, street, city).                                                     

Works (emp_name, company_name, salary).

Company (company_name, city).

Manages (emp_name, manager_name).

For each of the given query, give expression in relational algebra;

i) Find emp _ name, street and cities of residence whose salary exists in between 30,000 to 40,000 and work for XYZ Ltd.

ii) Find the name, street and cities of employees who live in the same city as the company they work for.

b) Write a short note on dynamic and embedded SQL. [8]

OR

Q4) a) Explain stored procedures and triggers. [8]

b) Explain create, Insert, update and delete operations with respective to views. [8]

Q5) a) Let R = (A, B, C, D, E) and let M be the following set of multi-valued dependencies A BC, B CD, E AD.

List the non-trivial dependencies in M⁺. [8]

b) Explain why 4NF is more desirable than BCNF. Rewrite the definition of 4NF and BCNF using the notions of domain constraints. [8]

OR

Q6) a) What is decomposition? Suppose that we decompose the schema R = (A, B, C, D, E) into (A, B, C) and (A, D, E), show that this decomposition is a lossless decomposition if the following set F of functional dependencies holds. A → BC, CD → E, B → D, E → A. [8]

b) Describe the concept of Transitive dependency and explain how this concept is used to define 3NF. [8]

SECTION - II

Q7) a) Explain in detail use of B-Tree as an indexing technique. Compare B-Tree and B+-Tree. [8]

b) Explain roll of “selection” operation in query processing. [6]

c) How cost of query is measured? [4]

OR

Q8) a) What are the steps involved in query Processing? Explain each in brief. [8]

b) What are the various techniques to handle variable length records? Explain any one in details. [8]

c) Define dense index. [2]

Q9) a) Explain the concept of ‘Transaction’. Describe ACID properties for transaction. [8]

b) Explain deferred database modifications and immediate database modifications and their difference in the context of recovery. [8]

OR

Q10) a) Explain two phase locking protocol. How does it insure serializability? [8]

b) Explain recoverable and cascadeless schedules. [8]

Q11) a) Explain how persistant pointer is implemented, compare this implementation with that of pointers as they exists in general purpose language such as ‘C’. [8]

b) Specify advantages and disadvantages of distributed database system. [8]

OR

Q12) a) Write a short note on any two: [12]

i) Pointers swizzling techniques.

ii) Persistent programming language.

iii) Association rules for data mining.

b) Explain the need of backup and replication. [4]

————————