Code: DC10                                                   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 best alternative in the following:                                         (2x10)

             a.  A logical schema

(A) is the entire database.

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

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

                  (D) none of these

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

(A) m                                                   (B) m+1                                                                            

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

             c.  _________ function divides one numeric expression by another and returns the remainder.

(A) POWER (B) MOD

(C) ROUND                                        (D) REMAINDER

             d.  A data manipulation command that combines the records from one or more tables is called

(A) SELECT                                        (B) PROJECT

(C) JOIN                                             (D) PRODUCT

             e.  In E-R Diagram generalization is represented by

                  (A) Ellipse                                            (B) Dashed ellipse

(C) Rectangle                                       (D) Triangle

             f.   ______ is a virtual table that draws its data from the result of an SQL SELECT statement.

(A) View                                              (B) Synonym

(C) Sequence                                       (D) Transaction

             g.  The method of access which uses key transformation is known as

(A) Direct                                             (B) Hash

(C) Random                                         (D) Sequential

             h.  A table joined with itself is called

(A) Join                                                (B) Self Join

(C) Outer Join                                      (D) Equi Join

             i.   _______ data type can store unstructured data.

(A) RAW                                             (B) CHAR

(C) NUMERIC                                    (D) VARCHAR

             j.   Which two files are used during operation of the DBMS?

(A) Query languages and utilities          

                  (B) DML and query language                                                                                            

                  (C) Data dictionary and transaction log

                  (D) Data dictionary and query language

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

  Q.2     a.   Explain the disadvantages of a file processing system.                                          (8)

             b.   What is data model? Explain object based and record based data models.         (8)

Q.3     a.   Discuss the correspondence between the E-R model construct and the relation model construct. Show how each E-R model construct can be mapped to the relational model using suitable example?   (8)

             b.   Draw an E-R Model for the following:

                   An organization uses number of items of a equipment to produce goods. Each item is at one LOCATION, of one TYPE and has a DETAILED_DISCRIPTION. Faults on the equipment are identified by a unique FAULT_ID  and are reported at a TIME_REPORTED. Any number of persons may be assigned to a fault and work on the fault until it is fixed. The TIME_FIXED is recorded as is the TIME_SPENT by each person on a fault. Any number of parts may be used to repair a fault. The QTY_USED of each parts is recorded against the fault. Each part is identified by a PART_ID and has a given weight and MAX_DIMENSION and can have any number of colours.                                (8)

Q.4    a.   Draw a B-tree by inserting the following data in a four-way tree; each node can contain three values and have 4 branches.                                  (8)

                             (2   10   12   4   16   22   26   36   40   14   18)

             b.   Explain the concept of generalization and aggregation in E-R diagrams. Give one example for each one of them.                                                   (8)

  Q.5     a.   Explain (a) Heap file (b) Sorted file. Also discuss their advantages and disadvantages.                     (8)

                   b.                                                        Describe a method for direct search? Explain how data is stored in a file so that direct searching can be performed.                                                     (8)

  Q.6     a.   Discuss the two principal integrity rules for the relational model. Discuss why it is desirable to enforce these rules?                                                  (4)

             b.   Discuss the differences between the candidate keys and the primary key of a relation. Give example to illustrate your answer.                          (4)

             c.   Consider the following relations

                   RENTER(rno, fname, lname, address, tel_no, pref_type, max_rent)

                   VIEWING(rno, pno, date, comment)

                   PROPERTY_FOR_RENT( pno, street, area ,city, pcode, type, rooms, rent)

                   Express the following queries in relational algebra.

                   (i) List the name and comments of all renters who have viewed a property.

                   (ii) Identify all renters who have viewed all properties with three rooms.               (8)

Q.7   a.   What are the various types of the update operations on relations?                        (8)

            b.    Consider the following relations:

                   BRANCH( bno, street, area, city, pcode, Tel_no, Fax_no)

                   STAFF( Sno, Fname, Lname, address,  position , salary, bno)

                   Express the following queries in SQL:

                   (i) List the staff who work in the branch at ‘163 main street’

                   (ii) Find staff whose salary is larger than the salary of every member of staff at branch B3.               (8)

    Q.8   a.   Perform the following with syntax and a suitable example

(i) Create a table from existing table.

                   (ii) Insert data in your table from another table.                                                   (8)

             b.   What is the purpose of tables, private synonyms and public synonyms. If there are multiple objects of same name on an Oracle database, which order are they accessed in?                                         (8)

    Q.9         Write short notes on :                                                                               (4x4=16)

                   (i) Weak and strong entity sets.

                   (ii) Types of attributes.

                   (iii) Oracle Instance.

                   (iv) Mid square method of hashing.                                                                                                                    (4 x 4 = 16 )