Database Management Systems - Dec 2013

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 are the responsibilities of DBA and Database designer?(4 marks) 1 (b) With a diagram, explain the components modules of DBMS and their interactions.(8 marks) 1 (c) List the advantages and disadvantages of DMBS. Discuss any five advantages by comparing with file systems.(8 marks) 2 (a) Define the following terms:
(i) Recursive relationship
(ii) Weak entity type attributes
(iii) Participation role(4 marks) 2 (b) Discuss the conventions for displaying an ER schemas as an ER daigram.(4 marks) 2 (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.(6 marks) 3 (b) List set theory operations used in relational data model. Explain any two with example.(6 marks) 3 (c) Consider the following relations for a sailor database that keeps track of reservation of boats by sailors.
SAILORS (<u>SID,</u>, SNAME, RATING, AGE)
BOATS(<u>BID,</u> BNAME, COLOR)
RESERVES (<u>SID, BID,</u> DAY)
Specify the following queries in relational algebra.
(i) Find the sids of sailors with age over 20 who nonreserved 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.(8 marks) 4 (a) Describe the six clauses in the syntax of an SQL Retrieval Query. Show what type of construct can be specified in each of the six clauses. Which of the six clauses are required and which are optional.(6 marks) 4 (b) Explain how the group by clause works. What is the difference between the Where and Having clause?(4 marks) 4 (c) Consider the following relations for a database.
Supplier (<u>Sno,</u> Sname, Status, City)
Product (<u>Pno,</u> Pname, Color, Weight, City)
Shipments (<u>Sno, Pno</u> Qty)
Specific the following queries in SQL.
(i) Retrieve names of supplier who supply part P₂.
(ii) Retrieve the names of suppliers who do not supply any part supplied by S₂.
(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 less than the current maximum in the supplier table.(10 marks) 5 (a) List the difference between Independent nasted and co-related nested query,(4 marks) 5 (b) Discuss main approaches to database programming. What you mean by Impedance mismatch.(8 marks) 5 (c) With program segment, explain retrieving of tuples with embedded SQL.(8 marks) 6 (a) Discuss insertion, detection and modification anomalies. Why are they considered bad? Illustrate with examples.(8 marks) 6 (b) What you mean by closure of attributes? Write an algorithm to find closure of attributes.(6 marks) 6 (c) Give 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.(6 marks) 7 (a) Consider the following Universal relation
R={ A, B, C, D, E, F, G, H, I, J} and the set of function dependencies.
F={ {A,B} ? C, A ? {D,E}, B ? F, F ? {G,H}, D ? {J,J} }
What is the key of R? Decompose R into 2NF, then 3NF relations.(8 marks) 7 (b) What is the dependency preservation property for a decomposition? Why is it important?(6 marks) 7 (c) Define fourth normal form. When is it violated? Why is it useful?(6 marks) 8 (a) What are the anamalies occur due to interleave execution? Explain them with example.(8 marks) 8 (b) Consider the three transactions T₁, T₂ and T₃ and schedules, S₁ and S₂ given below. Determine whether each schedule is serializable or not. If a schedule is serializable. Write down the equivalent serial schedule (S).
$$ \begin {align*}&T_1: R_1(X); R_1(Z);W_1(X); \\&T_2: R_2(X); R_2(Y);W_2(Z);W_2(Y);\\&T_3: R_3(X);R_3(Y);W_3(Y); \\&S_1:R_1(X);R_2(Z);R_1(Z);R_3(X);R_3(Y);W_1(X);W_3(Y);R_2(Y):W_2(Z);W_2(Y);\\&S_2:R_1(X);R_2(Z);R_3(X);R_1(Z);R_2(Y);R_3(Y);W_1(X);W_2(Z);W_3(Y);W_2(Y); \end{align*} $$(8 marks) 8 (c) Describe the three steps in crash Recovery in Aries. What is the goal of the each phase?(4 marks)