Database Management Systems - Jun 2014

Computer Science Engg. (Semester 5)

TOTAL MARKS: 100
TOTAL TIME: 3 HOURS (1) Question 1 is compulsory.
(2) Attempt any four from the remaining questions.
(3) Assume data wherever required.
(4) Figures to the right indicate full marks. 1 (a) What is database? Explain the implicite properties of database.(8 marks) 1 (b) With a neat diagram, explain "three schema architecture"(8 marks) 1 (c) Define the following terms:
(i) Data model (ii) Schema (iii) Metadata (iv) Snapshot(4 marks) 2 (a) Explain with sketch the different phases of database design.(10 marks) 2 (b) Write 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) 3 (b) Consider the following schema and write the relational algebra expression 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 same data given in questions 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 sides such that supplier with first sid charges more for some part than the supplier with second sid.
(iii) Find the pids of parts supplied by at least two different suppliers(10 marks) 4 (b) Write a note on NULL and three valued logic.(10 marks) 5 (a) Explain insert, delete and update statements in SQL, with example.(9 marks) 5 (b) How is a 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 of these guidline may be harmful.(12 marks) 6 (b) What is normalization? Explain third normal form with example.(8 marks) 7 (a) Define multi valued dependency. Explain 4NF with an example.(10 marks) 7 (b) Let R={ Ssn, Ename, Pnumber, Pname, Plocation, Hours } and
D={ R₁, R₂, R₃ }, where
R₁=EMP = {Ssn, Ename}
R₂ = PROJ = (Pnumber, Pname, Plocation)
R₃ = 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)

Write a short note on:

8 (a) Two phase locking protocol.(5 marks) 8 (b) Transaction support in SQL.(5 marks) 8 (c) Write ahead log protocol.(5 marks) 8 (d) Time stamp ordering algorithm.(5 marks)