DipIETE – CS (OLD SCHEME)

 

Code: DC10                                                Subject: DATABASE MANAGEMENT SYSTEMS

Flowchart: Alternate Process: DECEMBER 2009Time: 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:                                  (210)

       

             a.  Which of the following is not a DBMS package?

 

                  (A) DBASE                                         (B) ORACLE

                  (C) Microsoft ACCESS                       (D) Microsoft WORD

 

             b.  Which of the following design is both software and hardware independent?

 

                  (A) Logical                                           (B)  Physical

                  (C) Conceptual                                     (D) None of the above

 

             c.  What is the degree of a table with 1000 rows and 10 columns?

 

                  (A) 10                                                  (B) 100

                  (C) 1000                                              (D) None of the above

 

             d.  Degree of a relationship is

 

                  (A)  number of attributes of the relationship.                                                                      

                  (B)  number of participating entity types.

                  (C) number of rows that will be there in the table corresponding to the         relationship.    

                  (D) None of the above.

 

             e.  Which of the following aggregate functions does not ignore nulls in its results?

 

                  (A) COUNT                                        (B) COUNT(*)

                  (C) MAX                                             (D) MIN

 

             f.   Which of the following commands is used to get all the columns in a table?

 

                  (A) #                                                    (B) *

                  (C) %                                                   (D) @

 

            

 

 

             g.  Oracle database is a collection of

 

                  (A) stored data, log and control files.     (B) stored data and user processes.

                  (C) user processes and log files.            (D) user processes and control files.

 

             h.  Which of the following is not a relational algebra operator?

 

                  (A) Join                                                (B) Projection

                  (C) Selection                                        (D) Cartesian Product

 

             i.   Which of the following is a graphical query language?

 

                  (A) SQL                                              (B) Relational algebra

                  (C) Relational calculus                          (D) QBE

 

             j.   Which of the following is not true for a view?

 

                  (A) View is a virtual table.

                  (B) View is derived from other tables.

                  (C) There are no restrictions on update operation on a view.

                  (D) A view definition is permanently stored as part of the database.

 

 

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

 

 

  Q.2     a.   Describe the responsibilities of the DBA and the database designer.                    (8)

 

             b.   Explain the physical, logical and conceptual data models. Which of these     models is hidden from the database users?                                   (8)

 

  Q.3           Differentiate between                                                                                                                                                                                                                                                                          16

                   (i)    Logical and physical data independence.

                   (ii)   WHERE and HAVING clause in SQL.

                   (iii)  Strong entity set and weak entity set.

                   (iv)   Instances and Schemas                                                                            (16)

 

  Q.4     a    Briefly explain the various ways to deal with constraint violations during delete operation.                (6)

 

             b.   Describe the storage organization in oracle.                                                       (10)

 

  Q.5     a.   What is an ER model?  Draw an ER diagram for the following banking

                   Environment:  

                   A bank can have multiple branches and each branch can have multiple accounts.  Every customer can have multiple accounts in a branch.  Assume necessary attributes for the entities.       (10)

 

             b    What is a complex attribute?  Give an example for the same.                               (6)

       

  Q.6     a.  Explain and differentiate between sequential and direct file organization.               (8)

 

            

 

 

             b.   What is hashing?  Explain the extendible and linear hashing techniques.                (8)

 

  Q.7           Consider the following relations:

                   Employee(employee_name,street,city)

                   Works(employee_name,company_name,salary)

                   Company(company_name,city)

                   Manages(employee_name,manager_name)

                  

                   Write SQL queries for the followings:

(i)          Find the names of all managers who work for First Bank          Corporation.

(ii)        Find the names and cities of residence of all employees who work    for First Bank Corporation.

(iii)       Find the names of all employees who earn more than $10,000.

(iv)      Find all employees in the database who live in the same city as    companies for which they work.                                                                                                           (16)

 

  Q.8     a.   Explain basic operations used in relational algebra with examples.                        (8)

 

             b.   Explain with the help of examples, following types of keys:

                   (i)    Primary key.                                 (ii)   Foreign key.

                   (iii)  Candidate keys.                            (iv)  Superkey.                                        (8)

       

  Q.9           Write short notes on                                                                                         (16)

                   (i)   Domain Contraint

                   (ii)  Natural Language Interface.

                   (iii) End Users

                   (iv) Disadvantages of RDBMS