University of Delhi 2011-2nd Sem Post Graduate Diploma Computer Application Pgdca- -cs-2.2 database design - Question Paper
This question paper contains 6 printed pages.]
1757 Your Roll No. ..
PGDCA/IISem. A
Paper-CS-2.2 - DATABASE DESIGN (Admissions of 1998 and onwards)
Time : 3 Hours Maximum Marks : 100
(Write your Roll No. on the top immediately on receipt of this question paper.)
Attempt all questions. AU parts of a question must be answered together.
1. (a) Give the SQL statements for the following: 3x3
(i) Add a column dob (having datatype as Date) to the student table. No Null value is allowed and it is a unique key.
(ii) Create a sequence which generates the nos. from 100 onwards with an increment of 5 each. The maximum number generated should be 500. The numbers should be generated in cyclic manners.
(iii) Give object privileges of select and update to user 'userl'. This user should not be able to pass these privileges to other user. The user is provided privileges for the employee table.
(b) What is Natural or Equality join? How do we use it ? Illustrate with the help of an example. 2+1+2
(c) What are Procedures? Why do we use procedures? Give command with an example' to create a procedure. 2+2+2
2. (a) Differentiate between following: 2x4
(i) Views and Tables
(ii) Control Block and Data Block
(iii) Packages and Procedures.
(iv) Canvas and Window
(b) What is PL/SQL ? What are the sections in
PL/SQL? Explain them. 1+3
(c) What are cursor ? Give the types of attributes of cursors. 1+2
(d) Draw an E-R Diagram for a Railway Reservation System. 5
Illustrate with the help of example the use of the ; following : . . . 1x8
1 !(i LOWER ! "
:u< ,
Ay- ' < >.f-r
(b)
(ii) INSTR
(iii) SUBSTR
(iv) CONCAT
(v) LTRIM
.,((vi)-rSyM.,v
- (v'i'i) Months BETWEEN (viii) TO_DATE
What are Database Triggers ? Explain with example when the use of the following triggers will be made: ll/zx4
(i) INSTEAD OF
(ii) AFTER DELETE
(iii) BEFORE TRIGGER- , . (
:: (iv) after Update triggers
(c)
What is Package ? Why do we use Packages? Give commands to compile a package. 2+2+2
4. (a) Given Department table giving the list of departments with their code, name and location. Another table Employee j gives details about employees in the company. ( 8
DEPARTMENT (Dept_Code, Dept_Name, Floor)
EMPLOYEES (Emp_Code, Emp_Name, Desig, Head, DOJ, Basic, Dept_Code)
Write a PL/SQL block to retrieve the Dept_Code from the table Department one by one using cursors, pass these codes as parameter to another cursor defined to retrieve all the employees in that department passed ;as parameter from the Department Table.
(b) In reference to the above tables write SQL " queries for the following : 2x4
(i) Display the list of employees who are working in Sales Department.
(ii) Display the Salary and Names of all the employees, whose salary is less than of the employee 'Raj'.
(iii) Display the list of all the employees whose name starts with 'B1.
(iv) List the desig. and no. of employees having that desig. The result should be in ascending order of the no. of employees.
(c) Write a database Trigger which is fired on deleting a Dept, from the Department table. The Trigger should delete all the employees of that departments from department table. 4
5. (a) Use the Employee table: 2x2
EMPLOYEES (Emp_Code, Emp_Name, Desig, Head, DOJ, Basic, Dept_Code)
Write a PL/SQL block to dispaly the details of the employees with the given name
(i) If there are two or more faculty with the same desig. a suitable exception should be raised giving approprite message.
(ii) If only one employee is there with the given name, display the details.
(b) Discuss use/ purpose of following in oracle forms:
(i) Text items
(ii) List item
(iii) Check box
(c) Differentiate between Data aware and Control block. 3
(d) What are Frames? Why they are used in Oracle forms? 2
(e) What is a Property Class? How do you create a Property Class object? 1+2
1757 6 200
|
Attachment: |
| Earning: Approval pending. |
