Database Management Systems - Visvesvaraya Technological University December 2013 January 2014 Question

Database Management Systems - Visvesvaraya Technological University December 2013 January 2014 Questions / December 2013 January 2014 DBMS questions of Visvesvaraya Technological University / DBMS University questions with answers

USN                                                          10CS54 Fifth Semester B.E. Degree Examination, Dec. 2013/Jan. 2014 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 responsibilities of DBA and Database designers? (04 marks) b. With a neat diagram, explain the component modules of DBMS and their interactions. (08 marks) c. List the advantages and disadvantages of DBMS. Discuss any five advantages by comparing with files systems. (08 marks) 2. a. Define the following terms: i) Recursive relationship ii) Weak entity type iii) Atomic attributes iv) Participation role. (04 marks) b. Discuss the conventions for displaying an ER schema as an ER diagram. (04 marks) c. Draw an ER diagram for Musicians who perform for album. Assume any four entities. Indicate all key and cardinality constraints and any assumptions that are made. (12 marks) 3. a. List and explain characteristics of relations. (06 marks) b. List set theory operations used in relational data model. Explain any two with example. (06 marks) c. Consider the following relations for a sailor database that keeps track of reservation of boats by sailors. SAILORS (SID, SNAME, RATING, AGE) BOATS (BID, BNAME, COLOR) RESERVES (SID, BID, DAY) Specify the following queries in relational algebra. i) Find the SIDs of sailors with age over 20 who have not reserved a ‘Red’ boat. ii) Find the names of sailors who have reserved all boats. iii) Find the names of sailors who have reserved boat 103.     (08 marks) 4. a. Describe the six clauses in the syntax of an SQL retrieval query. Show what type of constructs can be specified in each of the six clauses. Which of the six clauses are required and which are optional.      (06 marks) b. Explain how the GROUP BY clause works. What is the difference between WHERE and HAVING clause. (04 marks) c. Consider the following relations of a database; Supplier (Sno, Sname, Status, City) Product (Pno, Pname, Color, Weight, City) Shipments (Sno, Pno, Qty) Specify the following queries in SQL. i) Retrieve names of supplier who supply part P2. ii) Retrieve the names of suppliers who do not supply any part supplied by S2. iii) Retrieve parts number for all parts supplied by more than one supplier. iv) For each part supplied, get the part number, maximum quantity, minimum quantity supplied for that part. v) Retrieve supplier numbers for suppliers with status less than the current maximum in the supplier table. (10 Marks) PART – B 5. a. List the differences between Independent nested and co-related nested query. (04 marks) b. Discuss main approaches to database programming. What you mean by Impedance mismatch. (08 Marks) c. With program segment, explain retrieving of tuples with embedded SQL. (08 Marks) 6. a. Discuss insertion, deletion and modification anomalies. Why are they considered bad? Illustrate with examples. (08 Marks) b. What you mean by closure of attribute? Write an algorithm to find closure of attribute. (06 Marks) c. Given below are two sets of FDs for a relation R(A, B, C, D, E).  Are they equivalent? i) A → B , AB → C, D → AC, D → E                ii) A → BC, D → AE    (06 Marks) 7. a. Consider the following Universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies. F = {{A, B} → C, A → {D, E}, B → F, F → {G, H}, D → {I, J}}. What is the key of R? Decompose R into 2NF, then 3NF relations. (08 Marks) b. What is the dependency preservation property for a decomposition? Why is it important? (06 Marks) c. Define fourth normal form. When is it violated? Why is it useful? (06 Marks) 8. a. What are the anomalies occur due to interleave execution? Explain them with example. (08 Marks) b. Consider the three transaction T1, T2, and T3 and schedules S1 and S2 given below. Determine whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule (S).                         (08 marks) T1: R1(X); R1(Z); W1(X); T2: R2(Z); R2(Y); W2(Z); W2(Y); T3: R3(X); R3(Y); W3(Y); S1: R1(X); R2(Z); R1(Z); R3(X); R3(Y); W1(X); W3(Y); R2(Y); W2(Z); W2(Y); S2: R1(X); R2(Z); R3(X); R1(Z); R2(Y); R3(Y); W1(X); W2(Z); W3(Y); W2(Y); c. Describe the three steps in crash recovery in ARIES. What is the goal of each phase? (04 marks) *****************

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]

——————