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.  E-R modeling technique is a:

(A) Top-down approach                      (B) Bottom-up approach

(C) Left-right approach                         (D) Right-left approach

             b.                                                              A primary key, if combined with a foreign key creates        

(A) Parent child relationship between the tables that connect them

(B) many to many relationship between the tables that connect them

(C) Network model between the tables connect them.

(D) None of the above

             c.  The employee salary should not be greater than Rs 2000 .This is

(A) Integrity constraint                          (B) Referential constraint

(C) Over defined constraint                   (D) Feasible constraint

             d.  The database environment has all the following components except

(A) Users                                             (B) Separate files

(C) Database                                        (D) Database administration

             e.  A data dictionary does not provide information about

(A) Where data is located                    

(B) The size of the disk storage disk

(C) Who owns or  responsible for the data

(D) How the data is used

             f.                                                               A schema describes

(A) Data elements                                 (B) Records and files

(C) Records relationship                       (D) All of these

             g   The PROJECT command will create new table that has

(A) More fields than the original table

(B) More rows than original table

(C) Both (A) & (B)

(D) None of these

             h.  The database administrator is the coordinator between the________ and _______.

(A) DBMS, database                           (B) Application program, database

(C) Database, users                              (D) Application programs, users

             i.   Two files may be joined into a third file if

(A) They have row in common

(B) They have field in common

(C) They have no records with the same value in the common field

(D) Both (B) & (C).

             j.   In E-R Diagram, Key Attribute is represented by

(A) Ellipse                                            (B) doubled ellipse

(C) Ellipse with underlined key              (D) dashed ellipse

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

  Q.2     a.   Describe the approach taken to handling of data in Data base management

                   Systems? Discuss the disadvantages of this approach.                                         (8)

             b.   To address the issue of data independence, the ANSI-SPARC three – level     architecture was proposed. Compare and contrast the three levels of this     model.                                                       (8)

Q.3     a.   Explain the following concepts associated with E-R model                                  (8)

                   (i) Relationship Type

                   (ii) Recursive Relationship

                   (iii) Relationship degree

                   (iv) Relationship Instance

             b.   Draw an E-R diagram for conduct of examination by a university. The                  university has examination centres and students request/register for                examination. The students are issued hall tickets. An examination centre has many classrooms where examinations are conducted as per a defined schedule.                                                                  (8)

Q.4    a.   Could an E-R diagram have more than one relationship between the same  two entity set and can be the total system included in the E-R diagram? Explain.                                                                     (8)

             b.   What are properties of a B-tree? Draw a B-tree of order 5, each node holding four values by inserting the following data  :                                   (8)

                   (8 16  22 52 4 32 34 30 2 38)

  Q.5     a.   What are the main goals of RAID technology? How does it achieve them?          (8)

                   b.                                                        What is collision in hashing? What are the different methods to minimize the collision?                                                                                        (8)

  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.   Consider the following relations:                                                                         (8)

                   EMPLOYEE(fname,lname,eno,dob,address,salary,dno)

                   DEPARTMENT(dname,dnumber,mgreno,mgrstartdate)

                   PROJECT(pname,pnumber,plocation, dnum)

                   WORKS_ON(eeno,pno,hours)

                   Express the following queries using relational algebra:

                   (i) List the project numbers for projects that involve an employee whose last name is ‘Batra’, either as a worker or as a manager of the department that controls the project.

                   (ii) List the name of the employees who work on all the projects controlled by department number 10.

Q.7   a.   Define the following terms: domain, attribute, relation schema, relational                   database schema, relational database state.                      (8)

            b.    Consider the following relations:                                                                         (8)

                  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 all staff whose salary is greater than the average salary, and list how much their salary is greater than the average.

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

    Q.8   a.   Describe CREATE TABLE command, its syntax and also explain all data                  integrity constraints that can be applied on columns in a table during creation of  table.                                      (8)

             b.   Explain with the help of suitable example the following clauses and  uses in Oracle SQL:  Where, order by, In, like.                                                (8)

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

                   (i) Key features of Oracle

                   (ii) Data Dictionary

                   (iii) DDL and DML

                   (iv) Functions in QBE                                                                                                                                        (4 x 4 = 16 )