Database Management Systems - Visvesvaraya Technological University June July 2014 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 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 is database? Explain the implicit properties of database. (08 marks) b. With neat diagram, explain “three schema architecture”. (08 marks) c. Define the following terms: (04 marks) i) Data model       ii) Schema   iii) Metadata                  iv) Snapshot 2. a. Explain with sketch the different phases of database design. (10 marks) b. Draw an ER diagram of hospital management system. Assume your own entities (minimum 4), attributes and relations. (10 Marks) 3. a. What is constraint? Give the detailed explanation of key constraints.         (10 marks) b. Consider the following schema and write the relational algebra expressions for the queries given below; Suppliers (sid : integer, sname : string, address : string) Parts (pid : integer, pname : string, color : string) Catalog (sid : integer, pid : integer, cost : real) i) Find the names of suppliers who supply some red parts. ii) Find the sids of suppliers who supply some red parts or at 221 packer street. iii) Find the sids of suppliers who supply some red part and some green part. (10 marks) 4. a. Consider the data given in question 3(b) and write the following queries in SQL: i) Find the sids of suppliers who supply some red and some green parts. ii) Find the pairs of sids such that the supplier with first sid charges more for some part than the supplier with the second sid. iii) Find the pids of parts supplied by at least two different suppliers.      (10 marks) b. Write a note on NULL and three valued logic. (10 marks) PART – B 5. a. Explain insert, delete, and update statements in SQL, with example. (09 marks) b. How is view created and dropped? What problems are associated with updating of views? (11 marks) 6. a. State the informal guidelines for relational schema design. Illustrate how violation fo these guidelines may be harmful. (12 marks) b. What is normalization? Explain third normal form with example. (08 marks) 7. a. Define multi-valued dependency. Explain 4NF with example. (10 marks) b. Let R = {ssn, ename, pnumber, pname, plocation, hours} and D = {R1, R2, R3} where R1 = EMP = {ssn, ename} R2 = PROJ = {pnumber, pname, plocation} R3 = WORKS_ON = {ssn, pnumber, hours} The following functional dependencies hold on relation R. F = {ssn → ename; pnumber → {pname, plocation}; {ssn, pnumber} → hours}. Prove that the above decomposition of relation R has the lossless join property. (10 marks) 8. Write a short note on: a. Two phase locking protocol. b. Transaction support in SQL. c. Write ahead log protocol. d. Time stamp ordering algorithm.                       (20 marks) *****************