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. An instance of relational schema R(A,B,C) has distinct values of A including NULL values. Which one of the following is true?

(A)  A is a candidate key                     (B)  A is not a candidate key

(C)   A is a primary Key                      (D)  Both (A) and (C)

b. Consider the join of a relation R with relation S. If R has m tuples and S has n tuples, then the maximum size of join is:

(A) mn                                               (B) m+n

(C) (m+n)/2                                         (D) 2(m+n)

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

(A) SELECT                                      (B)  PROJECT

(C)  JOIN                                           (D)  PRODUCT

             d.   The natural join is equal to :

(A)  Cartesian Product

(B)  Combination of Union and Cartesian product

(C)  Combination of selection and Cartesian product        

(D)  Combination of projection and Cartesian product

             e.   Which one of the following is not true for a view:

(A) View is derived from other tables.                 

(B) View is a virtual table.

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

(D)  View never contains derived columns.

             f.    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 that connect them.           

(D) None of the above.

g.   In E-R Diagram relationship type is represented by

(A) Ellipse                                          (B)  Dashed ellipse

(C)  Rectangle                                     (D)  Diamond

             h.   Hierarchical model is also called

(A) Tree structure                               (B) Plex Structure

(C) Normalize Structure (D) Table Structure

             i.    To delete a particular column in a relation the command used is:

(A) UPDATE                                     (B) DROP

(C) ALTER                                         (D) DELETE

             j.    The ______ operator is used to compare a value to a list of literals values that have been specified.

(A) BETWEEN                                  (B)  ANY

(C)  IN                                                (D)  ALL

Answer any FIVE Questions out of EIGHT Questions.

Each question carries 16 marks.

Q.2     a.   What is DBMS and what are functions of DBMS?                                           (10)

b.   How many types of users works on database?                                                    (6)

  Q.3     a.   Explain the various levels of abstraction in a database system.                             (8)

             b.   Describes the various relationship constraints by giving suitable example.             (8)

Q.4     a.   What is an E-R model? Draw an E-R Diagram for the company database with following Descriptions:

                   The company is organized into departments. Each department has a unique name and a unique number with several locations.

                   A department controls a number of projects, each of which has a unique name, unique number and a single location.

                   We store each employees name, social security number, address, and salary. An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same departments.

                   We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s name, age and relationship to the employee.                                                   (8)

             b.   Define the following

(i) Record-Based Logical Models

(ii) Data Independence                                                                                 (8)

Q.5     a.   What is bucket overflow and how bucket overflow is handled by Over Flow Chaining or Closed Hashing?                                                            (8)

b.   What are the Constituents of File? Also explain all the possible file

                   operations.                                                                                                        (8)

  Q.6     a. Express the following queries in SQL assume that the data is stored in EMPLOYEE table with relevant fields.

                    (i)    Display name, job, salary, and hire date of employee who are hired between May 10, 1975 and December 20, 1980. Order the query in ascending order of hire date.

                    (ii)    Display name and hire date of employee who are employed after employee ‘RAGHAV’.                                                           (6)

             b.   What are the various types of the update operations on relations? Also explain the constraints on these update operation. Give examples in support of your answer.                                               (10)

Q.7     a.   Consider the relations                         

EMPLOYEE(emp#, name)

                   ASSIGNED_TO(project#, emp#)

                   PROJECT(project#, project_name, chief)

                   Express the following queries in Relational Algebra

(i) Get details of employee working on both c354 and c345 project numbers.

(ii) Find the employee number of employee who do not work on project c678.                (8)

b.   Explain the SQL operators BETWEEN-AND, IN, LIKE and IS_NULL by taking suitable examples.                                                                   (8)

Q.8           Write short note on followings:

(i) Relational Constraints

(ii) Storage Organization in Oracle

(iii) Disadvantages of Relational Approach

(iv) Instances and Schemas                                                           (4  4 = 16)

Q.9     a.   What are the key features of Oracle?                                                                 (8)

             b.   Explain the following functions of Oracle with suitable examples:

                   (i)   To_Char( )                                    (ii)  Count( )     

                   (iii) Trim( )                                           (iv) Length( )                                           (8)