DipIETE – CS (OLD SCHEME)

 

Code: DC10                                                Subject: DATABASE MANAGEMENT SYSTEMS

Flowchart: Alternate Process: JUNE 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.  Student and courses enrolled, is an example of

       

                  (A) one-one relationship.                      (B) one-many relationship.

                  (C) many-one relationship.                    (D) many-many relationship.

 

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

       

                  (A) query language and utilities

                  (B) query language and DML

                  (C) data dictionary and transaction log

                  (D) data dictionary and query language

 

             c.  Updating a database means:

       

                  (A) revising the file structure                 

                  (B) reorganizing the database

                  (C) modify or add record occurrences 

                  (D) all of the above

 

             d.  Which language has recently become the defacto standard for interfacing application programs with RDBMS?

       

                  (A) QBE                                              (B) SQL

                  (C) QUEL                                            (D) 4GL

 

             e.  E-R modeling technique is a

 

(A) top-down approach.                       (B) bottom-up approach.

                  (C) left-right approach.                         (D) right-left approach.

       

             f.                                                               The physical location of a record is determined by a mathematical formula that transforms a key into a record location in

                 

                  (A) a B-tree file.                                   (B) an indexed file.

                  (C) a hashed file.                                  (D) a sequential file.

 

 

             g   If a relation scheme is in BCNF, then it is also in

            

                  (A) 1 NF.                                             (B) 2 NF

                  (C) 3 NF.                                             (D) none.

 

             h.  Which of the following is/are correct?

 

                  (A) An SQL query automatically eliminates duplicates.                                                      

                  (B) An SQL query will not work if there are no indexes on the relations

                  (C) SQL permits attribute name to be repeated in the same relation                                   

                  (D) None of these

 

             i.   Which of the following is not a logical database structure?

 

                  (A) tree                                                (B) relation

                  (C) chain                                              (D) network

 

             j.   Manager’s salary details are hidden from the employee. This is

 

                  (A) conceptual level data hiding             (B) external level data hiding

                  (C) physical level data hiding                 (D) logical level data hiding

 

 

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

 

 

  Q.2     a.   What are the different types of database end users? Discuss the main activities of each.                   (8)

 

             b.   What are the components of DBMS? Explain each.                                           (8)

 

  Q.3     a.   Discuss the role of high level data model in the database design process.             (8)

                  

             b.   Draw an E-R diagram of Hospital with following information:                              (8)

·         patients, entity set with attributes SSNo, LastName, FirstName, HomePhone, Sex, DateofBirth, Age, Street, City, State, Zip.

·         doctors, entity set with attributes SSNo, LastName, FirstName, OfficePhone, Pager, Specialty.

·         examines, relational set with attributes Date, Time, Diagnosis, Fee.

·         beds, entity set with attributes RoomNumber, BedNumber, Type, Status, PricePerHour.

·         Bed_assigned, relational set with attributes DateIn, TimeIn, DateOut, TimeOut, Amount.

·         accounts, weak entity set with attributes DateIn, DateOut, Amount.

·         has_account, relational set with no Attributes

   Q.4    a.   How does double buffering improve block access time?                                      (8)

 

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

            


  Q.5     a.   Discuss the features of  (i) an unordered file (ii) an ordered file.                           (8)

            

                   b.                                                        Consider the relations                                             (8)

            EMPLOYEE(name, emp_id, dob, address, salary, dnum)

            DEPARTMENT( dname, dnum, mgreno)

We apply the following update operations on the relations:

(i)                  Insert (’ram’, null, ’07-09-1978’, ’14-sector’, 25000, 5) into EMPLOYEE.

(ii)                Delete the DEPARTMENT tuple with dnum=’234’.

(iii)               Modify the address of the EMPLOYEE  tuple with emp_id= ‘238’ .    

(iv)              Update the emp_id of the EMPLOYEE  tuple with emp_id= ‘235’ to ‘256’.

Discuss all integrity constraints violated by each operations , if any.

 

  Q.6     a.   Define the five basic relational algebra operations. Define the remaining three relational algebra operations in terms of the basic operations.               (8)

 

             b.   Define the following terms with suitable example super key, primary key, secondary key, candidate key, alternate key and foreign key.               (8)

 

    Q.7   a.   Consider a relation ANIMAL(animalid, category, breed, dateborn, gender, registered, color, price). Write the following queries in SQL                (8)

                   (i)   List all dogs with yellow in their color born after 6/1/2001.

                   (ii)  List all dogs who are male and registered or who were born before

                       6/1/2001 and have white in their color.

     

            b.    What is view in SQL, and how is it defined? Discuss the problems that may arise when one wants to update the view.                                              (8)

 

    Q.8   a.   What is System Global Area (SGA)?  What does it consist of?                           (8)

 

             b.   What are the key features of Oracle?                                                                 (8)

 

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

 

                   (i)   Database languages

                   (ii)  Single-Valued vs. Multi-valued Attributes

                   (iii) Relational constraint

                   (iv) Strong vs. weak entity set                                                                                                                            (4 x 4 = 16 )