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

Time: 3 Hours                                            June 2006                                                          Max. Marks: 100

NOTE: There are 9 Questions in all.

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

· Out of the remaining EIGHT Questions answer any FIVE Questions. Each question carries 16 marks.

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

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

a. The metdata is created by the

(A)  DML compiler                             (B)  DML preprocessor

(C)   DDL interpreter                           (D)  Query interpreter

b. When an E-R diagram is mapped to tables, the representation is redundant for  

(A) weak entity sets                            (B)  weak relationship sets

(C) strong entity sets                            (D)  strong relationship sets

             c.   When , then the cost of computing  is

(A) the same as R  S                        (B)  greater the R  S

(C)  less than R  S                            (D)  cannot say anything

             d.   In SQL the word ‘natural’ can be used with

(A)  inner join                                     (B)  full outer join

(C)  right outer join                             (D)  all of the above          

             e.   The default level of consistency in SQL is 

(A) repeatable read                            (B)  read committed

(C)  read uncommitted                         (D)  serializable

             f.    If a transaction T has obtained an exclusive lock on item Q, then T can

(A) read Q                                         (B)  write Q

(C)  both read and write Q                  (D)  write Q but not read Q

             g.   Shadow paging has

(A) no redo                                        (B)  no undo

(C)  redo but no undo                          (D)  neither redo nor undo

h.   If the closure of an attribute set is the entire relation then the attribute set is a

(A) superkey                                      (B) candidate key

(C) primary key                                   (D) not a key

             i.    DROP is a ______________ statement in SQL.

(A) Query                                           (B) Embedded SQL

(C) DDL                                             (D) DCL

             j.    If two relations R and S are joined, then the non matching tuples of both R and S are ignored in

(A)  left outer join                                (B)  right outer join

(C)  full outer join                                (D)  inner join

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

Q.2     a.   What is completeness constraint on generalization?  Explain the difference between total and partial design constraint.  Give an example each.        (6)

             b.   Design a generalization-specialization hierarchy for a motor-vehicle sales company.  The company sells motorcycles which have an engine number and cost; cars which have a chassis number, an engine number, seating capacity and cost; trucks which have chassis number, an engine number and cost.                (10)

Q.3     a.   Define the following operations of relational algebra and give an example each

(i) Division.

(ii) Cartesian product.                                                                     (7)

             b.   Let R(A, B) and S(A, C) be two relations.  Give relational algebra expressions for the following domain calculus expressions.

                   (i)   

                   (ii)  

                   (iii)  (9)

Q.4           Consider the following relations with key underlined

lives (person_name, street, city)

                   works (person_name, company_name, salary)

                   located (company_name, city)

                   manages (person_name, manager_name)

                   Answer the following using SQL:

(i) Find the names and city of persons who work for manager John.

(ii) Find the names of persons who live in the same city as the company they work for.

(iii) John’s manager has changed.  The new manager is Anna.

(iv) Susan doesn’t work anymore.

(v) Create a view BangWork (person_name, company_name, manager_name) of all people who work in Bangalore in ascending order of person name.                                                   

  Q.5     a. What are the problems if one were not to normalize?  When do these problems surface?                 (2)

             b.   Consider the relation

                   Book (accno, author, author_address, title, borrower_no, borrower_name, pubyear)

                   with the following functional dependencies

                   accno  title                                      accno  pubyear

                   author  accno

                   accno  author                                  author  author_address

                   accno  borrower_no                       borrower_no  borrower_name

(i)    Normalize the relation.  Clearly show the steps.                                           (6)

(ii)  For each decomposed relation identify the functional dependencies that apply and identify the candidate key.                                                     (8)

Q.6   a.    Describe sequential file organization.  Explain the rules for

(i) inserting a new record.

(ii) Deleting an existing record.  (8)

     b.   Define and differentiate between ordered indexing and hashing.                           (8)

Q.7     a.   How do you compute the query cost for the following:

(i)  Selection with linear search.

                   (ii)  Negation.                                                                                                     (9)

b.   Explain the statement ‘Projection operation distributes over the union operation’.  Give an example.                                                                       (4)

             c.   Explain pipelining.                                                                                              (3)

Q.8     a.   Explain the rules for creating a labelled precedence graph for testing view serializability.                   (8)

             b.   Explain the difference between the three storage types – volatile, non volatile and stable.                 (8)

Q.9     a.   How does the system recover from a crash?                                                       (8)

b.   Write short notes on

(i) Structure of  tree.

(ii) ACID properties of a transaction.                                                           (8)