Netaji Subhas Open University (NSOU) 2009 M.C.A Database Management System - Question Paper

MCA 3.1

MCA (3rd Semester)

Term End Examination December, 2009

Database Management System

Paper 3.1

Time : 3 Hours Full Marks : 75

Answer question 1 and any two from the rest.

1. Write down the queries considering the following tables: (any seven) [Marks 5x7=35]

EMP (EMPNO, ENAME, SAL, HIREDATE, MGR, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

a) Display details of employees working in Accts department having A in their names.

b) Display details of employees who have joined before their managers.

c) Display details of employees who have not been assigned any department and

salary is less than 10000.

d) Display details of employees having their commission greater than ten percent of

their salary.

e) Count the number of departments having number of employees more than 20.

f) Show the average salary of all employees having more than 15 yrs of experience.

g) Display the name of the manager having highest number of employees working

under him / her.

h) Display the number of employees joined the company in the last century.

2. Explain how Database System has overcome the limitation of a File Based System. How Data

Independence helps in the design and development of a Database ? How Occurrence Diagram

helps in determining the cardinality ? Explain with an example. Define entity.

[Marks 6+6+6+2=20]

3. Write short notes on any four from the following : [Marks 4x5=20]

a)      Normalization

b)      View

c)      Procedure

d)     Cursor

e)      PL/SQL Composite Data Types.

4. a) Write a trigger which will check update operation of salary column of EMP table and if the

new salary entered is less than the existing salary, then it will show an error message and

restrict such operation.

b) Write a procedure which will take an employee number as input and if the employee number

is valid then returns the name of the highest salary earner who works in the same department

as that of this employee.