The Institution of Engineers,India 2006 Diploma Electronics and Communication DATABASE MANAGEMENT SYSTEMS - - Question Paper

Code: DC-10        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.       It is better to use files than a DBMS when there are

                   (A)  Stringent real-time requirements.  

                   (B)  Multiple users wish to access the data.

(C)    Complex relationships among data.                                                                          

(D)   All of the above.

b.      The conceptual model is

(A)    dependent on hardware.              

(B)    dependent on software.

(C)  dependent on both hardware and software                                                                .

(D)  independent of both hardware and software.

             c.   What is a relationship called when it is maintained between two entities?

(A)    Unary                                           (B)  Binary

(C)  Ternary                                        (D)  Quaternary

             d.   Which of the following operation is used if we are interested in only certain columns of a table?

(A)    PROJECTION                           (B)  SELECTION

(C)  UNION                                      (D)  JOIN 

             e.   Which of the following is a valid SQL type?

(A)     CHARACTER                            (B)  NUMERIC

(C)  FLOAT                                       (D)  All of the above

             f.    The RDBMS terminology for a row is

(A)     tuple.                                           (B)  relation.

(C)  attribute.                                       (D)  degree.

             g.   Which of the following operations need the participating relations to be union compatible?

(A)     UNION                                       (B)  INTERSECTION

(C)  DIFFERENCE                            (D)  All of the above

             h.   The full form of DDL is

(A)    Dynamic Data Language               (B) Detailed Data Language

(C) Data Definition Language               (D) Data Derivation Language

             i.    Which of the following is an advantage of view?

(A)   Data security                                (B) Derived columns

(C) Hiding of complex queries              (D) All of the above

             j.    Which of the following is a legal expression in SQL?

(A)    SELECT NULL FROM EMPLOYEE;         

(B)    SELECT NAME FROM EMPLOYEE;

(C)    SELECT NAME FROM EMPLOYEE WHERE SALARY = NULL;

(D)    None of the above

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

  Q.2           Define the following terms

                   (i)    Catalog and meta data                  (ii)    Parametric end users

                   (iii)  DBA                                            (iv)   Controlled redundancy

                   (v)   Snapshot                                      (vi)   Data sublanguage

                   (vii) High level DML                            (viii) Data abstraction                            (16)

  Q.3           Differentiate between

(i)                  Procedural and non procedural DML

(ii)                Logical and physical data independence

(iii)               Forms based and graphical interface

(iv)              Internal and external schema                                        (4 x 4 = 16)

  Q.4     a.   Explain the conventions for displaying an ER schema as an ER diagram.              (8)

             b.   Describe the two alternatives for specifying structural constraints on relationship types.  What are their advantages and disadvantages?            (8)                                                             

  Q.5     a.   Discuss the techniques for a hash file to expand and shrink dynamically.  What are the advantages and disadvantages of each?                        (8)

             b.   What are the reasons for having variable length records?  What are the various ways to store variable length records?                                              (4)

             c.   Discuss the mechanism to read data from and write to a disk.                              (4)

  Q.6     a.   Define the following operations of the relational algebra in terms of the basic operations:

                   (i)   Intersection                                   (ii)   Join

                   (iii) Division                                                                                               (2+2+3)          

             b.   Consider the relations

                   City (city_name, state)

                   Hotel (name, address)

                   City_hotel (hotel_name, city_name, owner)

                   Answer the following queries in relational algebra

(i)                  Find the names and address of hotels in Agra.

(ii)                List the names of cities which have no hotel.

(iii)               List the names of the hotels owned by Taj Group.                                (9)

  Q.7     a.   Explain the difference between using functions with and without grouping attributes in relational algebra.  Give examples.                                            (6)                                                             

             b.   Define the following with respect to SQL

(i)                  Specifying alias                        (ii)  UNIQUE function

(iii)      ORDER BY clause                   (iv)  LIKE predicate

(v)       Asterisk (*)                                                                                          (10)                      

  Q.8           Consider the relations given below

                   Borrower (id-no, name)

                   Book (accno., title, author, borrower-idno)

                   (a)   Define the above relations as tables in SQL making real world assumptions about the type of the fields.  Define the primary keys and the foreign keys.                                                                     (7)

                   (b)     For the above relations answer the following queries in SQL

(i)                  What are the titles of the books borrowed by the borrower whose id-no in 365.

(ii)                Find the numbers and names of borrowers who have borrowed books on DBMS in ascending order in id-no.

(iii)               List the names of borrowers who have borrowed at least two books.                 (9)

  Q.9     a.   Describe the structure of Oracle data dictionary.                                                 (4)

             b.   In Oracle what is system global area and how is it organized?                              (6)          

             c.   Write a short note on QBE.                                                                                (6)