AMIETE – CS/IT (NEW SCHEME)   –   Code: AC61/AT61

 

Subject: DATABASE MANAGEMENT SYSTEMS

Flowchart: Alternate Process: JUNE 2010Time: 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.  The ___________ schema describes the physical storage structure of the database.

 

                  (A) Internal                                           (B) Conceptual

                  (C) External                                          (D) None

 

             b. _________ is the capacity to change the conceptual schema without having to change the external schema or application programs.   

 

                  (A)  Physical Data Independence         

                  (B)  Logical Data Independence

                  (C)  Fragmentation Transparency         

                  (D)  None

 

             c.  Retrieval, Insertion, deletion and modification of the data is done through ______ commands.  

 

                  (A) DDL                                              (B) DML

                  (C) DTL                                               (D) VDL

 

             d.  The process of transforming requests and results between levels is called _______.     

 

                  (A) independence                                 (B) transformation

                  (C) mapping                                         (D) translation

 

             e.  The __________ constraint states that no primary key value can be null.  

 

                  (A) null                                                 (B) entity integrity

                  (C) domain                                           (D) referential integrity

            

              f.  The ______ constraint states that a tuple in one relation that refers to another relation must refer to an existing tuple.

 

                  (A) entity integrity                                 (B) domain

                  (C) referential integrity                          (D) transition

 

             g. The _________ operation is used to select the subset of truples from a relation.

 

                  (A) project                                           (B) select

                  (C) join                                                (D) intersection

 

             h.  Following steps are used to map binary M:N relationships types during ER to Relational mapping.   

 

(A)  Create a new relation                     

(B) Make the primary keys of two relations as foreign key in the new relation                    

                  (C) Only b is valid                               

                  (D) Both a and b are valid

 

             i.   The ________ command is used to modify attribute values of one or more tuples.

 

                  (A) update                                            (B) after

                  (C) modify                                            (D) set

 

             j.   Which of the following is a valid Boolean value in SQL?

 

                  (A) 0                                                    (B) 1

                  (C) Unknown                                       (D) None of the above

 


Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

 


   Q.2    a.   What are the characteristics of a database approach?                                         (8)

                  

             b.   What are the advantages of using database approach?                                        (8)

 

Q.3     a.   Explain the characterization of schedules based on Serializability.            (8)

            

b.      What is recoverable schedule?  Why is recoverability of schedule desirable?  Are there any circumstances under which it would be desirable to allow non recoverable schedules?  Explain your answer.         (8)

            

 Q.4      a.   Explain briefly how do you deal with constraint violations with three types of update operations?                                                                             (8)

                   

             b.   Consider the following relation for a database that keeps track of auto sales in a car dealership (option refers to same optional equipment installed on an auto): AAR(serial-No, Model , Manufacturer, Price)     (8)

                            OPTIONS(serial-No, Option-Name, Price)

                            SALES(Salesperson-id, Serial-No, Date, Sale-price)

                            SALESPERSON(Salesperson-id, Name, Phone)

                   First, specify the foreign keys for this schema, stating any assumptions         you make. Next, populate the relation with a few example tuples, and then give an example of an insertion in the SALES and SALESPERSON relations that violates the referential integrity constraints and of another insertion that does not.

                 

       

   Q.5    a.   Discuss the reasons for converting S Q L queries into relational algebra queries before optimization is done.                                                            (5)

                  

b.      Define query optimization and explain its significance for a DBMS.                      (5)

 

c.       Explain the outer join operation of relational algebra.                                           (6)

            

  Q.6     a.   Explain with an example how do you specify basic constraints in SQL?               (8)

 

             b.   Explain with examples some of the pattern matching algorithms used in SQL.                                                                                             (5)

             c.   Explain how the GROUP BY clause works in SQL.                                            (3)

 

  Q.7     a.   Explain BCNF with suitable example.   (6)

                  

             b. A set of FDs for the relation R{ A, B, C, D, E, F}is A B→ C, C→ A, BC→ D, ACD→ B, BE→ C, EC→ FA, CF→ BD, D→ E. Find a minimum cover for this set of FDs.                                                                                           (7)

 

             c. Why should NULLs in a relation be avoided as for as possible?                             (3)

                  

   Q.8    a.  Explain the concept of Buffering of Blocks used in data transmission.                    (8)

 

             b. Explain the different types of operation typically performed on files.                       (8)

            

  Q.9     a.   Write short note on

                   (i)   Transaction roll back.

                   (ii)  Shadow paging.                                                                                           (8)

                                     

 
 

             b.  Explain lock and unlock operation of binary locks                                                (8)