Code: C-14 / T-11                                       Subject: DATABASE MANAGEMENT SYSTEMS

Time: 3 Hours                                                                                                     Max. Marks: 100

NOTE: There are 11 Questions in all.

· Question 1 is compulsory and carries 16 marks. Answer to Q. 1. must be written in the space provided for it in the answer book supplied and nowhere else.

· Answer any THREE Questions each from Part I and Part II. Each of these questions carries 14 marks.

· Any required data not explicitly given, may be suitably assumed and stated.

Q.1       Choose the correct or best alternative in the following:                                         (2 x 8)

a. In an E-R diagram double lines indicate

(A)  Total participation.                        (B)  Multiple participation.

(C) Cardinality N.                              (D)  None of the above.

b. The operation which is not considered a basic operation of relational algebra is 

(A) Join.                                             (B)  Selection.

(C)  Union.                                          (D)  Cross product.

             c.   Fifth Normal form is concerned with

(A)  functional dependency.                 (B)  multivalued dependency.

(C) Join dependency.                          (D)  domain-key.

             d.   Block-interleaved distributed parity is RAID level

(A) 2.                                                 (B)  3

(C)  4.                                                 (D)  5.

             e.   Immediate database modification technique uses                            

(A) Both undo and redo.                    (B)  Undo but no redo.

(C)  Redo but no undo.                        (D)  Neither undo nor redo.

             f.    In SQL the statement select * from R, S is equivalent to

(A) Select * from R natural join S.      (B)  Select * from R cross join S.

(C)  Select * from R union join S.        (D)  Select * from R inner join S.

             g.   Which of the following is not a consequence of concurrent operations?

(A)  Lost update problem.                   (B)  Update anomaly.

(C)  Unrepeatable read.                       (D)  Dirty read.

             h.   As per equivalence rules for query transformation, selection operation distributes over

(A)  Union.                                          (B)  Intersection.

(C)  Set difference.                              (D)  All of the above.

PART I

Answer any THREE Questions. Each question carries 14 marks.

Q.2     a.   Describe five main functions of a database administrator.                                     (5)

             b.   Define the following with respect to an E-R diagram.  Explain the manner in which each is mapped to a table.  Illustrate with an example.                   

(i)    Relationship set.                           (ii)  Aggregation.

                   (iii)  Multivalued attribute.                                                                                   (9)

Q.3           Consider the following relations with primary keys underlined.                                

Salesperson (SNo, Sname, Designation)

                   Area (ANo, Aname, ManagerNo)

                   Product (PNo, Pname, Cost)

                   SAP (SNo, ANo, PNo)

                   (a)  Define the schema in SQL specify the attributes, and keys assuming that ManagerNo is a foreign key.  Specify the constraint that the cost of a product cannot be greater than Rs.10000/-. (5)

                   (b)  Answer using SQL

(i) Get the names of all the products that are sold.

(ii) Get the product numbers which are marketed by alteast two sales persons.

(iii) Get the names of all salespersons who are not Managers.            (9)

Q.4     a.   What is the basic difference between relational algebra and relational calculus?  Define the atoms in tuple relational calculus.  Use these to define the formulae.                                                          (5)

             b.   Consider the following relations

                   Person (name, street, city)

                   Owns (name, reg_no, model, year)

                   Accident (date, reg_no)

                   Answer the following using tuple relational calculus

                   (i)   Find the names of persons who are not involved in any accident.

                   (ii)  Find the names and street of persons who own a maruti car.

                   (iii) Find the registration numbers of the cars manufactured in the year 2004.                       (9)

Q.5     a.   Define functional and multivalued dependencies.                                                  (2)

b.   Consider the relation Student (stid, name, course, year)

Given that

                        A student may take more than one course but has unique name and the year of joining.

(i) Identify the functional and multivalued dependencies for Student.                      (4)

(ii) Identify a candidate key using the functional and multivalued dependencies arrived at in step (b).                                         (4)

(iii) Normalize the relation so that every decomposed relation is in 4NF.                 (4)

  Q.6     a. Explain the following

(i) ISA relationship.

(ii) NULL value.

(iii) Trigger.

(iv) EXEC statement in SQL.                                                        (2 x 4)

b.   Define a view ProductArea in relational algebra and SQL, using the relations of question (3) above, which contains the area name and the names of products sold in that area.                                    (6)

PART II

Answer any THREE Questions. Each question carries 14 marks.

Q.7     a.   Compare the two method for storing variable length records – byte string representation and fixed length representation.  Discuss the merits and demerits of the two.                                                (6)

             b.   Describe the different RAID levels.  Discuss the choices of the different RAID levels for different applications.                                        (8)

Q.8     a.   Define two-phase locking protocol.                                                                    (2)

             b.   Differentiate between strict two-phase and rigorous two-phase with conversion protocols.              (4)

             c.   Consider the transactions t1, t2 and t3 and a schedule S given below.

 Where the subscript denotes the transaction number.  Assume that the time stamp of t1<t2<t3.  Using time-stamp ordering scheme for concurrency control find out if the schedule will go through.  If there is to be a rollback, which transaction(s) will be rolled back?                              (8)

Q.9     a.   Describe the nested-loop join and block-nested loop join.  Compare them.                        (8)

     b.   Two relations R with 60000 tuples and occupying of 300 blocks is to be joined with a relation S with 40000 tuples and occupying 400 blocks.  What is the total cost using the two algorithms of (a) above in terms of block transfers.  Give both the best case and the worst case figures.                    (6)

Q.10     a.   Explain the ACID properties of a transaction.                                                     (6)

             b.   Compare wait-die deadlock prevention scheme with wait-wound scheme.                          (4)

c.   What are the costs to be considered when a transaction has to be rolled back when recovering from deadlock?                                           (4)

Q.11                                                                      Write short notes on

(i) Views in relational algebra.

(ii) Data dictionary.

(iii) Assertions in SQL.

(iv)  tree.                                                                      (3.5 x 4=14)