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