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.NELLOREDISTRICT 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.