Code: DC-10                                                 Subject: DATABASE MANAGEMENT SYSTEM Flowchart: Alternate Process: JUNE 2007

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.       Which of the following is record based logical model?

 

                   (A)  Network Model                           (B)  Object oriented model

(C)    E-R Model                                  (D)  None of these

       

b.      A data dictionary is a special file that contains:

 

(A)    The name of all fields in all files.    

(B)    The width of all fields in all files.

(C)    The data type of all fields in all files.                                                                          

(D)     All of the above.

            

             c.   A file manipulation command the extracts some of the records from a file is called

                  

(A)    SELECT                                      (B)  PROJECT

(C) JOIN                                            (D)  PRODUCT

 

             d.   The physical location of a record is determined by a mathematical formula that transforms a file key into a record location is :

 

(A)    B-Tree File                                 (B) Hashed File

(C)  Indexed File                                (D) Sequential file. 

 

             e.   Using Relational Algebra the query that finds customers, who have a balance of over 1000 is 

                  

(A)     *Customer_name(balance >1000(Deposit))        

(B)     *Customer_name(balance >1000(Deposit))

(C)  *Customer_name(balance >1000(Borrow))        

(D)  *Customer_name(balance >1000(Borrow))

 

             f.    A primary key is combined with a foreign key creates

 

(A)     Parent-Child relation ship between the tables that connect them.         

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

(C)     Network model between the tables that connect them.           

(D)    None of the above.

             g.   In E-R Diagram derived attribute are represented by

 

(A)     Ellipse                                          (B)  Dashed ellipse

(C)  Rectangle                                     (D)  Triangle

 

             h.   In a relation

 

(A)    Ordering of rows is immaterial.    

(B)    No two rows are identical.

(C)    (A) and (B) both are true.            

(D)    None of these.

 

             i.    Which of the following is correct?

 

(A)   An SQL query automatically eliminate 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 the above.

 

             j.    Cross Product is a:

 

(A)    Unary Operator                            (B)  Ternary Operator

(C)  Binary Operator                           (D)  Not a operator

 

 

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

 

  Q.2     a.   What is DBMS and what are components of DBMS?  What are the advantages of DBMS over file oriented approach.                                            (10)

 

             b.   What are the DBMS languages?  Briefly explain.                                                (6)

 

  Q.3     a.   Explain the relevance of Data Dictionary in a Database System.                          (4)

 

             b.   Discuss the role of DBA.                                                                                    (4)

 

             c.   Explain the terms primary key, candidate key, alternate key and secondary key.  In the given table identify each key.

                                 STUDENT(SID,Regno, Name, City)                                                   (8)

       

  Q.4     a.   In an organisation several projects are undertaken.  Each projects can employ one or more employees.  Each employee can work on one or more projects.  Each project is undertaken on the request of client.  A client can request for several projects.  Each project has only one client.  A project can use a number of items and a item may be used by several projects.  Draw an E-R diagram and convert it to a relational schema.                                               (10) 

                  

             b.   Define

(i)                  Identifying relationship.

(ii)                Specialisation / generalization.

(iii)               Aggregation.                                                                              (6)          

  Q.5     a.   What is hash file organization?  What are the causes of bucket overflow in a hash file organization?  What can be done to reduce the occurrence of bucket overflow?                                                 (8)

 

             b.   What do you understand by RAID?  Explain RAID Level 4 and Level5.             (8)

                                                                             

  Q.6     a.   Consider the relations:

 

                   PROJECT(proj#,proj_name,chief_architect)

                   EMPLOYEE(emp#,emp_name)

                   ASSIGNED(proj#,emp#)

 

                   Use relational algebra to express the following queries:

 

(i)                  Get details of employees working on project COMP33.

(ii)                Get the employee number of employees who work on all projects. 

(iii)               Get details of project on which employee with name ‘RAM’ is working.                 (9)                                          

 

             b.   Differentiate between join and outer join.                                                            (4)

 

             c.   Consider a table student (std_id, std_name, date_of_birth, percent_marks, dept_name).  Write a QBE query to display names of Computer Science department students who have scored more than 80%.                                                           (3)

                                                          

  Q.7     a.   Consider the relations                         

                   EMP(ENO,ENAME,AGE,BASIC_SALARY)

                   WORK_IN(ENO,DNO)

                   DEPT(DNO,DNAME,CITY)

 

                   Express the following queries in SQL

 

(i)                  Find names of employees who work in a deptt. in Delhi.

(ii)                Get the deptt. number in which more than one employee is working.

(iii)               Find name of employee who earns highest salary in ‘HR’ department.                     (9)

 

             b.   Explain various kinds of constraints that can be specified using CREATE TABLE command.  Explain CASCADE and RESTRICT clauses of DROP SCHEMA command.                                (7)

 

  Q.8           Write short notes on following:

 

(i)                  Extension and Intension.

(ii)                Weak and strong entity type.

(iii)               Views in SQL.

(iv)              Built in function in QBE.                                                          (4  4 = 16)

 

  Q.9     a.   What is Oracle Process?  Explain any four processes started by Oracle.             (8)

 

             b.   Explain Storage Organisation of Oracle.                                                             (8)