AMIETE – CS/IT (OLD SCHEME)

Code: AC14 / AT11                                    Subject: DATABASE MANAGEMENT SYSTEMS

Time: 3 Hours                                                                                                     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 the best alternative in the following:                                 (2 10)

a. Normalization of database is used to

(A)  Eliminate redundancy.                     (B)  Improve security.

(C)  Improve efficiency.                         (D)  Minimize error.                                            

b. A compound key

(A)  is made up of several pieces of information.

(B)  uniquely identifies an item in a list.

(C)  is a combination of each unique key.

(D)  both (A) & (B).

c. The way a particular application views the data from the database that the application uses is a

(A)  Module.                                         (B)  Relation.

(C)  Schema.                                         (D)  Subschema.

d. Which of the following hardware component is the most important to the operation of a Database Management System?

(A)  High resolution video display          

(B)  Printer

(C)  High speed, large capacity disk      

                  (D)  Plotter

e. Which of the following contains a complete record of all activities that affect the contents of a database during a certain period of time?

(A)  report writer                                   (B)  transaction log

(C)  query language                                (D)  data manipulation language

f. The master list of an indexed file

(A)  is sorted in ascending order.

(B)  contains only a list of  keys and record numbers.

(C)  has a number assigned to each record.

(D)  both (B) & (C) above.

g.   A logical schema

(A)  is the entire database.

(B)  is a standard way of organizing information into accessible part.

(C)  describe how data is actually stored on disk.

(D)  None of these.

h. A B-tree of order m has maximum of_________ children.

(A)  m                                                   (B)  m+1

(C)  m–1                                               (D)  m/2

i. In a large DBMS

(A)  each user can see only a small part of the entire database.

(B)  each subschema contains every field in the logical schema.

(C)  each user can access every subschema.

(D) All of these.

j. Assume transaction A holds a shared lock R. If transaction B also requests for a shared lock on R.

(A)  it will result in a deadlock situation

(B)  it will immediately be granted

(C)  it will immediately be rejected

(D)  it will be granted as soon as it is released by A

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

  Q.2     a.   What is the difference between generalization and specialization? Explain with the help of a suitable example.                                                                                                                         (8)

             b.   Design an E-R diagram for bank and customer relationship, where each bank can have multiple branches and each branch can have multiple accounts and loan.                                                               (8)

Q.3.     a.   What is relational database approach and what are the advantages and   disadvantages of this approach?                                                               (8)

             b.   Consider the relations:                                                                                        (4)

                   PROJECT(proj#, proj_name,chief_architect)

                   EMPLOYEE(emp#, emp_name)

                   ASSIGNED(proj#, emp#)

                   Use tuple and domain relational calculus to express the following query:

                   Get the employee number of employees who work on the ‘SOS 454’ project.

             c.   Differentiate between Data Definition Language (DDL) and Data Manipulation Language (DML).                                                              (4)

  Q.4     a.   How are views defined in SQL? What are the problems associated with views for updates on RDBMS? How does SQL approach these problems?                                                        (8)

             b.   What is the purpose and syntax of the following SQL commands: INSERT, UPDATE, ALTER, COMMIT, ROLLBACK and CREATE.               (8)

  Q.5     a.   Consider the following relations which are in 2NF                                               (8)

                   REL2{S#, SUPPLYSTATUS, SUPPLYCITY} and

                   REL3{S#, P#, PARTQTY} with following FD’s

                   {S#®SUPPLYSTATUS, S#®SUPPLYCITY, SUPPLYCITY®SUPPLYSTATUS , (S#,p#)®PARTQTY }

                   Discuss the anomalies in these relations and how can we convert these relations in 3NF?

             b.   What is a B-tree? What are the drawbacks of B-tree and how these are solved by B⁺ trees.                       (8)

  Q.6     a.   Explain insertion anomalies, deletion anomalies and modification anomalies with an example of each.                                                                      (8)

             b.   What is hashing? Describes its functions, advantages and disadvantages.             (8)

  Q.7     a.   Discuss the importance of sorting in query processing?                                        (8)

             b.   What is query processing? Discuss the steps involved in Basic algorithm of query processing.                                                                     (8)

Q.8     a.   Define granularity, hierarchy of granularity of locks and multiple granularity locking.  Describe the modified two phase locking with multiple granularity locking.                                                             (8)

             b.   Discuss two multiversion techniques for concurrency control.                              (8)

  Q.9           Explain the following:

                   (i)   Functions of Database Management System

                   (ii)  Disadvantages of Relational Approach

                   (iii) File organisation

                   (iv) ACID properties of a transaction   (4x4=16)