--Your friends at LectureNotes

Lab Manuals for Database Management System - DBMS By virinchi p

  • Database Management System - DBMS
  • Practical
  • Uploaded 2 months ago
0 User(s)
Download PDFOrder Printed Copy

Share it with your friends

Leave your Comments

Text from page-1

Database Management Systems Lab (R15) LIST OF EXPERIMENTS 1. Practicesession: Students shouldbe allowed choosing appropriate DBMS software, installing it, configuring it and starting working on it. Create sample tables, execute some queries,use SQLPLUS features, use PL/SQLfeatures like cursors onsample database. Students should be permitted to practice appropriate Userinterface creation tool and Report generation tool. 2. A college consists of number of employees‟ workingin 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, daareas pertherules of the college. Initiallyonly empno, empname, basic have valid values. Other value sareto becomputed and updatedlater. Department contains department deptname, and descriptioncolumns. Deptno is the primarykey indepartment table and referential lintegrity constraint exists between employee and department tables. Perform the following operations on the database:  Create tables department and employee with required constraints.  Initially only the fewcolumns (essential) are to be added. Addtheremaining columns separately by using appropriate SQL command.  Basic column should not be null.  Addconstraint that basic should not be lessthan 5000.  Calculate hra, da, gross and net byusing PL/SQL program.  When ever salary is updated and its value becomes less than 5000 atriggerhas to be raised preventing the operation.  The assertions are: hra should not belessthan 10% of basic and dash should not be less than 50% of basic.  The percentage of hra and daare to be stored separately.  When the data becomes more than100%, a message has to be generated and with user permission dahasto be merged with basic.  Empno should be unique and has to be generated automatically.  If the employee is going to retireina particular month, automatically a message has to be generated.  The default value for date-of-bir this 1 jan, 1970.  When the employees called daily – wagersare to be added the constraint that salary should be greater than or equal to 5000 should be dropped.  Display the information of the employees and departments with description of the fields.  Display the average salary of all the departments.  Display the average salary department wise.  Display the maximum salary of each department and also all departments put together.  Commit the changes when ever required and rollback if necessary.  Use substitution variables to insert values repeatedly.  Assume some of the employees shave given wrong information about date-ofbirth. Update the corresponding tables to change the value.  Find the employees whose salary is between 5000 and 10000 but not exactly 7500. SRI SAI INSTITUTE OF TECHNOLOGY AND SCIENCE:RAYACHOTY Page 1

Text from page-2

Database Management Systems Lab (R15)                  Find the employees whose name contains„en‟. Try to delete aparticular deptno. What happens if there are employees in it and if there are no employees. Create alias for colums and use the minqueries. List the employees according to a scending order of salary. List the employees according to ascending order of salary in each department. Use „&&‟ wher ever necessary. Amount 6000 has to be deducted as CM relief fund in a particular month which has to be accepted as input from the user. When ever the salary becomes negative eithas to be maintained as 1000 and the deduction amount for those employees is reduced appropriately. The retirement age is 60 years. Display the retirement day of all the employees. If salary of all the employees is increased by 10% every year, what is the salary of all the employees at retirement time. Find the employees who are born in leap year. Find the employees who are born on feb 29. Find the departments where the salary of atleast one employee is more than 20000. Find the departments where the salary of all the employees is lessthan 20000. On first January of every year a bonus of 10% has to be given to all the employees. The amount has to be deducted equally in the next 5months. Write procedures for it. As a designer identify the views that may have to be supported and createviews. As a designer identify the PL/SQL procedures necessary and create them using cursors. Use appropriate Visual programming tools like oracle form sand reports, visual basic etc to create user interface screens and generate reports. Note: As a designerid entity other operations that may be required and add to the above list. The above operations are notin order. Order them appropriately. Use SQL or PL/SQL depending on the requirement. 3. Students may be divided into batch esand the following experiments may be given to them to better underst and the DBMS concepts. Students should gather the required information, draw ERdiagrams, map them to tables, normalize, create tables, triggers, procedures, execute queries, create user interfaces, and generate reports.           Student information system APSRTC reservation system Hostel management Library management Indian Railways reservation Supermarket management Postal system Banking system Courier system Publishinghousesystem SRI SAI INSTITUTE OF TECHNOLOGY AND SCIENCE:RAYACHOTY Page 2

Text from page-3


Text from page-4

Database Management Systems Lab (R15) PART - I INTRODUCTION SQL is divided into the following  Data Definition Language (DDL)  Data Manipulation Language (DML)  Data Retrieval Language (DRL)  Transaction Control Language (TCL)  Data Control Language (DCL) DDL -- create, alter, drop, truncate, rename DML -- insert, update, delete DRL -- select TCL -- commit, rollback, savepoint DCL -- grant, revoke CREATE TABLE SYNTAX Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen); Ex: SQL> create table student (no number (2), name varchar (10), marks number (3)); INSERT This will be used to insert the records into table. We have two methods to insert.  By value method  By address method a) USING VALUE METHOD Syntax: insert into <table_name) values (value1, value2, value3 …. Valuen); Ex: SQL> insert into student values (1, ‟sudha‟, 100); SQL> insert into student values (2, ‟saketh‟, 200); To insert a new record again you have to type entire insert command, if there are lot of records this will be difficult. This will be avoided by using address method. b) USING ADDRESS METHOD Syntax: insert into <table_name) values (&col1, &col2, &col3 …. &coln); This will prompt you for the values but for every insert you have to use forward slash. Department of C.S.E, SSITS. Page 4

Lecture Notes