DBMS LAB MANUAL.pdf - DATABASE MANAGEMENT SYSTEMS LAB...

Y.V.R
DATABASE MANAGEMENT SYSTEMS
LAB MANUAL
of
R13 Regulation
Prepared by
Y.VENKATA RAMESH
(Assistant Professor)
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING
BRAHMAIAH COLLEGE OF ENGINEERING
NORTH RAJUPALEM
S.P.S.R. NELLORE DISTRICT
JAWAHARLAL NEHRU TECHNOLOGICAL UNIVERSITY, ANANTAPUR, A.P, INDIA

LIST OF EXPERIMENTS
1. Practice session: Students should be allowed to choose appropriate DBMS software, install it,
configure it and start working on it. Create sample tables, execute some queries, use SQLPLUS
features, use PL/SQL features like cursors on sample database. Students should be permitted to
practice appropriate User interface creation tool and Report generation tool.
2. A college consists of number of employees working in different departments. In this context,
create two tables employee and department . Employee consists of columns empno, empname,
basic, hra, da, deductions, gross, net, date-of-birth. The calculation of hra,da are as per the rules
of the college. Initially only empno, empname, basic have valid values. Other values are to be
computed and updated later. Department contains deptno, deptname, and description columns.
Deptno is the primary key in department table and referential integrity constraint exists
between employee and department tables. Perform the following operations on the the
database:
1. Create tables department and employee with required constraints.
2. Initially only the few columns (essential) are to be added. Add the remaining columns
separately by using appropriate SQL command
3. Basic column should not be null
4. Add constraint that basic should not be less than 5000.
5. Calculate hra,da,gross and net by using PL/SQL program.
6. Whenever salary is updated and its value becomes less than 5000 a trigger has to be
raised preventing the operation.
7. The assertions are: hra should not be less than 10% of basic and da should not be less
than 50% of basic.
8. The percentage of hra and da are to be stored separately.
9. When the da becomes more than 100%, a message has to be generated and with user
permission da has to be merged with basic.
10. Empno should be unique and has to be generated automatically.
11. If the employee is going to retire in a particular month, automatically a message has to
be generated.
12. The default value for date-of-birth is 1 jan, 1970.
13. When the employees called daily-wagers are to be added the constraint that salary
should be greater than or equal to 5000 should be dropped.
14. Display the information of the employees and departments with description of the fields.

Y.V.R