22319--Sample-question-paper--Data-Base-Management-System I Scheme 3rd Semester Computer MSBTE

Scheme – I 

Sample Question Paper

Program Name        : Computer Engineering Program Group

Program Code          : CO/CM/CW

Subject: Database Management System

 

Instructions:

(1)  All questions are compulsory.

(2)  Illustrate your answers with neat sketches wherever necessary.

(3)  Figures to the right indicate full marks.

(4)  Assume suitable data if necessary.

(5)  Preferably, write the answers in sequential order.

Q.1) Attempt any FIVE of the following.			10 Marks
a)  List any four applications of DBMS. b)  State the four database users. c)  Define normalization. Enlist its type. d)  Enlist DDL and DML commands. e)  Define   the following      i) Primary Key
ii) Foreign Key f)   Enlist four aggregate functions. g)  Draw PL/SQL block structure.
Q.2) Attempt any THREE of the following.			12 Marks

a)   Write and explain syntax for creating procedure.

b)  Explain aggregate function with syntax and example.

c)   Explain grant and revoke command with syntax and example.

d)  Distinguish any four points between network model and hierarchical model.

Q.3) Attempt any THREE of the following.                                                         12 Marks

a)   Explain create and rename command with syntax and example.

b)  Write and explain the syntax for creating and dropping synonyms with an example.

c)   Explain predefined and user defined exception handling with the help of example.

d)  Explain ACID properties of transaction.

Q.4) Attempt any THREE of the following.                                   a)   List and explain DCL commands b)  Write and explain syntax for creating function. c)   Explain steps of cursor implementation with syntax and example. d)  Explain 2NF with example. e)   Explain the four roles of database administrator.		12 Marks
Q.5) Attempt any TWO of the following.		12 Marks

a) Draw ER diagram for Hospital Management System 

   (Use DOCTOR,PATIENT,HOSPITAL and MEDICAL_RECORD Entity).

    Identify Primary Key and Foreign Key.  b)          Normalize database

Employee(emp_id,emp_name,phone,skill,salary,deptno,dept_name,jobno,jo b_title) upto 3NF

c) Write SQL query for following consider table

    EMP(empno , deptno, ename ,salary, Designation, joiningdate, DOB,city)

i) Display names of employees whose experience is more than 10 years  

ii) Display age of employees

iii) Display average salary of all employee

iv) Display name of employee who earned highest salary

Q.6) Attempt any TWO of the following.                                        12 Marks 

      a) Create  table

 EMP(   empno , deptno, ename ,salary, Designation, joiningdate, DOB,city).

i) Insert one row into the table 

ii) Save the data 

iii) Insert second row into the table

iv) Undo the insertion of second row 

v) Insert two rows into the table 

vi) Create Savepoint s1 

vii) Insert one row into the table 

viii) Undo upto savepoint s1

b)  Write a PL/SQL program to check whether specified employee is present in EMP table or not. Accept empno from user. If employee does not exist display message using exception handling.

c)   Write SQL query for following consider table

    EMP(empno , deptno, ename ,salary, Designation, joiningdate, DOB,city) 

i) Display employees name and number in an increasing order of salary 

ii) Display employee name and employee number dept wise 

iii) Display total salary of all employee 

iv) Display number of employees dept wise

             v) Display employee name having experience more than 3 years 
             vi) Display employee name staring with “S” and working in deptno 1002