INSTITUTE OF AERONAUTICAL ENGINEERING

(Autonomous)
Dundigal, Hyderabad - 500 043

COMPUTER SCIENCE AND ENGINEERING

Course Title DATABASE MANAGEMENT SYSTEMS LAB

Course Code A40584
Regulation R13 – JNTUH
Lectures Tutorials Practicals Credits
Course Structure
- - 3 2
Course Coordinator Mr. A Krishna Chaitanya, Associate Professor, CSE
Team of Instructors Ms. E Uma Shankari, Assistant Professor, CSE

I. COURSE OVERVIEW:
This laboratory introduces techniques required to the design and implementation of database
systems. This laboratory focuses on relational database management systems, including database
design theory: E-R modeling, data definition and manipulation languages, database security and
administration. It also covers various database programming concepts such as: Transaction
Processing, Concurrency Control and Recovery, and various types of databases like distributed
database, and intelligent database, Client/Server.

II. COURSE OBJECTIVES:

At the end of the course, the students will be able to:

I. Analyze the problem and identify the Entities and Relationships, keys for given database.
II. Design, develop and query a database.
III. Able to construct queries and maintain a simple database using SQL.
IV. Normalization of data present in database tables.
V. Provide database security and administration.
VI. Develop triggers programs using PL/SQL.
VII. Develop procedures and cursor programs using PL/SQL.

III. COURSE OUTCOMES:

After completing this course the student must demonstrate the knowledge and ability to:

1. Identify the entities, attributes, relationships, keys for given database.

2. Design a database schema for given problem.
3. Formulate queries using SQL DML, DDL commands.
4. Formulate SQL queries using constraints and set comparison operators.
5. Apply the normalization techniques for development of application software to realistic problems.
6. Ability to provide security to a database. Develop PL/SQL programs using triggers, procedures,
cursors.
7. Ability to design and implement given case study.

1|P a ge
IV. COURSE PLAN:

Division of Experiments List of Experiments

Week -1
1. Create a table called Employee with the following structure.
Name Type
empno Number
ename varchar2(20)
job varchar2(20)
mgr Number
Sal Number

a) Add a column commission with domain to the Employee

table.
b) Insert any five records into the table.
c) Update the column details of job
d) Rename the column of Employ table using alter command.
e) Delete the employee whose empno is 19.
2. Create department table with the following structure.
Name Type
deptno Number
dname varchar2(20)
Queries on Data Definition Language location varchar2(20)
(DDL) Commands and Data
manipulation Language a) Add column designation to the department table.
(DML)commands in b) Insert values into the table.
RDBMS c) List the records of emp table grouped by deptno.
d) Update the record where deptno is 9.
e) Delete any column data from the table.
3. Create a table called Customer table
Name Type
cust_name varchar(20)
cust_street varchar2(20)
cust_city varchar2(15)

a) Insert records into the table

b) Add salary column to the table
c) Alter the table column domain.
d) Drop salary column of the customer table
e) Delete the rows of customer table whose cust_city is ‘hyd’
4. Create a table called branch table.
Name Type
branch_name varchar(20)
branch_city varchar2(20)
asserts Number

2|P a ge
 Division of Experiments List of Experiments
a) Increase the size of data type for asserts to the branch.
b) Add and drop a column to the branch table.
c) Insert values to the table.
d) Update the branch name column
e) Delete any two columns from the table
5. Create a table called sailor table
Name Type
sid interger
sname varchar2(20)
rating varchar2(15)

a) Add column age to the sailor table.

b) Insert values into the sailor table.
c) Delete the row with rating >8.
d) Update the column details of sailor.
e) Insert null values into the table.
6. Create a table called reserves table
Name Type
Boat_id integer
Sid integer
day Integer

a) Insert values into the reserves table.

b) Add column time to the reserves table.
c) Alter the column day data type to date.
d) Drop the column time in the table.
e) Delete the row of the table with some condition.
Week-2
1. a) Create a user and grant all permissions to the user.
b) Insert the any three records in the employee table and use
rollback. Check the result.
c) Add primary key constraint and not null constraint to the
employee table.
d) Insert null values to the employee table and verify the result.
2. a) Create a user and grant all permissions to the user.
b) Insert values in the department table and use commit.
c) Add constraints like unique and not null to the department
Queries on Data Control Language
table.
(DCL)commands and Integrity
d) Insert repeated values and null values into the table.
constraints
3. a) Create a user and grant all permissions to the user.
b) Insert values into the table and use commit.
c) Delete any three records in the department table and use
rollback.
d) Add constraint primary key and foreign key to the table.
4. a) Create a user and grant all permissions to the user.
b) Insert records in the sailor table and use commit.
c) Add save point after insertion of records and verify save
point.
d) Add constraints not null and primary key to the sailor table.

3|P a ge
 Division of Experiments List of Experiments
5. a) Create a user and grant all permissions to the user.
b) Use revoke command to remove user permissions.
c) Change password of the user created.
d) Add constraint foreign key and not null.
6. a) Create a user and grant all permissions to the user.
b) Update the table reserves and use savepoint and rollback.
c) Add constraint primary key , foreign key and not null to the
reserves table
d) Delete constraint not null to the table column.
Week-4
1. a) By using the group by clause, display the enames
who belongs to deptno10 along with average salary.
b) Display lowest paid employee details under each department.
c) Display number of employees working in each department
and their department number.
d) Using built in functions, display number of employees
working in each department and their department name from
dept table. Insert deptname to dept table and insert deptname
for each row, do the required thing specified above.
e) List all employees which start with either B or C.
f) Display only these ename of employees where the maximum
salary is greater than or equal to 5000.
2. a) Calculate the average salary for each different job.
b) Show the average salary of each job excluding manager.
c) Show the average salary for all departments employing more
than three people.
d) Display employees who earn more than the lowest
salary in department 30
Practice Sessions on Group by clause, e) Show that value returned by sign(n) function.
Having clause, order by clause and f) How many days between day of birth to current date.
Built in functions in RDBMS 3. a) Show that two substring as single string.
b) List all employee names, salary and 15% rise in salary.
c) Display lowest paid emp details under each manager
d) Display the average monthly salary bill for each deptno.
e) Show the average salary for all departments employing more
than two people.
f) By using the group by clause, display the eid who belongs to
deptno 05 along with average salary.
4. a) Count the number of employees in department 20
b) Find the minimum salary earned by clerk.
c) Find minimum, maximum, average salary of all employees.
d) List the minimum and maximum salaries for each job
type.
e) List the employee names in descending order.
f) List the employee id, names in ascending order by
empid.
5. a) Find the sids ,names of sailors who have reserved all
boats called “INTERLAKE
b) Find the age of youngest sailor who is eligible to
vote for each rating level with at least two such

4|P a ge
 Division of Experiments List of Experiments
sailors.
c) Find the sname , bid and reservation date for each
reservation.
d) Find the ages of sailors whose name begin and end
with B and has at least 3 characters.
e) List in alphabetic order all sailors who have reserved
red boat.
f) Find the age of youngest sailor for each rating level.
6. a) List the Vendors who have delivered products within
6 months from order date.
b) Display the Vendor details who have supplied both
Assembled and Sub parts.
c) Display the Sub parts by grouping the Vendor type
(Local or Non Local).
d) Display the Vendor details in ascending order.
e) Display the Sub part which costs more than any of
the Assembled parts.
f) Display the second maximum cost Assembled part.
Week-5
1. a) Write a PL/SQL program to swap two numbers.
b) Write a PL/SQL program to find the largest of three numbers.
2. a) Write a PL/SQL program to find the total and average of 6
subjects and display the grade.
b) Write a PL/SQL program to find the sum of digits in a given
number.
3. a) write a PL/SQL program to display the number in
reverse order.
b) Write a PL / SQL program to check whether the given
number is prime or not.
4. a) Write a PL/SQL program to find the factorial
of a given number.
b) Write a PL/SQL code block to calculate the area of a circle
for a value of radius varying from 3 to 7. Store the radius
PL/SQL Programs using control and the corresponding values of calculated area in an empty
structures table named areas, consisting of two columns radius & area.
5. a) Write a PL/SQL code block that will accept an
account number from the user, check if the
users balance is less than minimum balance,
only then deduct rs.100/- from the balance.
This process is fired on the acct table.
b) Write the PL/SQL program for the sum of square of
the given number.
6. a) Write a PL/SQL program to accept a string and
remove the vowels from the string.( When ‘hello’
passed to the program it should display ‘Hll’
removing e and o from the world Hello).
b) Write a PL/SQL program to accept a number and a
divisor. Make sure the divisor is less than or equal to
10. Else display an error message. Otherwise
Display the remainder in words.

5|P a ge
 Division of Experiments List of Experiments
Week -6
1. Write a function to accept employee number as parameter and
return Basic+HRA together as single column.
2. Accept year as parameter and write a Function to return the total
net salary spent for a given year.
3. Create a function to find the factorial of a given number and
Functions using PL/SQL
hence find NCR.
4. Write a PL/SQL block o pint prime Fibonacci series using local
functions.
5. Create a procedure to find the lucky number of a given birth
date.
6. Create function to the reverse of given number
Week -7
1. Create a row level trigger for the customers table that would
fire for INSERT or UPDATE or DELETE operations
performed on the CUSTOMERS table. This trigger will display
the salary difference between the old values and new values:

CUSTOMERS table
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Mumbai 8500.00
6 Komal 22 MP 4500.00

2. Creation of insert trigger, delete trigger, update trigger practice

triggers using the passenger database.
Passenger( Passport_ id INTEGER PRIMARY KEY, Name
Triggers using PL/SQL VARCHAR (50) Not NULL,
Age Integer Not NULL, Sex Char, Address VARCHAR (50)
Not NULL);
 Write a Insert Trigger to check the Passport_id is exactly six
digits or not.
 Write a trigger on passenger to display messages ‘1 Record
is inserted’, ‘1 record is deleted’, ‘1 record is updated’ when
insertion, deletion and updation are done on passenger
respectively.
3. Insert row in employee table using Triggers. Every trigger is
created with name any trigger have same name must be replaced
by new name. These triggers can raised before insert, update or
delete rows on data base.The main difference between a trigger
and a stored procedure is that the former is attached to a table
and is only fired when an INSERT, UPDATE or DELETE
occurs.
4. Convert employee name into uppercase whenever an employee
record is inserted or updated. Trigger to fire before the insert or
update.

6|P a ge
 Division of Experiments List of Experiments
5. Trigger before deleting a record from emp table. Trigger will
insert the row to be deleted into table called delete _emp and
also record user who has deleted the record and date and time of
delete.
6. Create a transparent audit system for a table CUST_MSTR. The
system must keep track of the records that are being deleted or
updated.
Week -8
1. Create the procedure for palindrome of given number.
2. Create the procedure for GCD: Program should load two
registers with two Numbers and then apply the logic for GCD of
two numbers. GCD of two numbers is performed by dividing
the greater number by the smaller number till the remainder is
zero. If it is zero, the divisor is the GCD if not the remainder
and the divisors of the previous division are the new set of two
numbers. The process is repeated by dividing greater of the two
Procedures using PL/SQL numbers by the smaller number till the remainder is zero and
GCD is found.
3. Write the PL/SQL programs to create the procedure for factorial
of given number.
4. Write the PL/SQL programs to create the procedure to find sum
of N natural number.
5. Write the PL/SQL programs to create the procedure to find
Fibonacci series.
6. Write the PL/SQL programs to create the procedure to check the
given number is perfect or not.
Week -9
1. Write a PL/SQL block that will display the name, dept no,
salary of fist highest paid employees.
2. Update the balance stock in the item master table each time a
transaction takes place in the item transaction table. The change
in item master table depends on the item id is already present in
the item master then update operation is performed to decrease
the balance stock by the quantity specified in the item
transaction in case the item id is not present in the item master
table then the record is inserted in the item master table.
Cursors using PL/SQL 3. Write a PL/SQL block that will display the employee details
along with salary using cursors.
4. To write a Cursor to display the list of employees who are
working as a Managers or Analyst.
5. To write a Cursor to find employee with given job and deptno.
6. Write a PL/SQL block using implicit cursor that will display
message, the salaries of all the employees in the ‘employee’
table are updated. If none of the employee’s salary are updated
we get a message 'None of the salaries were updated'. Else we
get a message like for example, 'Salaries for 1000 employees are
updated' if there are 1000 rows in ‘employee’ table.
Week -10
Case study: Book Publishing Company: A publishing company
V. Case Studies
produces scientific books on various subjects. The books are written

7|P a ge
Division of Experiments List of Experiments
by authors who specialize in one particular subject. The company
employs editors who, not necessarily being specialists in a particular
area, each take sole responsibility for editing one or more
publications.
A publication covers essentially one of the specialist subjects and is
normally written by a single author. When writing a particular book,
each author works with on editor, but may submit another work for
publication to be supervised by other editors. To improve their
competitiveness, the company tries to employ a variety of authors,
more than one author being a specialist in a particular subject for the
above case study, do the following.
1. Analyze the data required.
2. Normalize the attributes.
3. Create the logical data model (ER diagrams).

Week -11
Case study: General Hospital: A General Hospital consists of a
number of specialized wards (such as Maternity, Pediatric,
Oncology, etc). Each ward hosts a number of patients, who were
admitted on the recommendation of their own GP and confirmed by
a consultant employed by the Hospital. On admission, the personal
details of every patient are recorded. A separate register is to be held
to store the information of the tests undertaken and the results of a
prescribed treatment. A number of tests may be conducted for each
patient. Each patient is assigned to one leading consultant but may
be examined by another doctor, if required. Doctors are specialists
in some branch of medicine and may be leading consultants for a
number of patients, not necessarily from the same ward. For the
above case study, do the following.
1. Analyze the data required.
2. Normalize the attributes.
3. Create the logical data model ( ER diagrams).

Week -12
Case study: Car Rental Company: A database is to be designed
for a Car Rental Company (CRC). The information required
includes a description of cars, subcontractors (i.e. garages),
company expenditures, company revenues and customers. Cars are
to be described by such data as: make, model, year of production,
engine size, fuel type, number of passengers, registration number,
purchase price, purchase date, rent price and insurance details. It is
the company policy not to keep any car for a period exceeding one
year. All major repairs and maintenance are done by subcontractors
(i.e. franchised garages), with whom CRC has long-term
agreements. Therefore the data about garages to be kept in the
database includes garage names, addresses, range of services and the
like. Some garages require payments immediately after a repair has
been made; with others CRC has made arrangements for credit
facilities. Company expenditures are to be registered for all
outgoings connected with purchases, repairs, maintenance, insurance
etc. Similarly the cash inflow coming from all sources - car hire, car
sales, insurance claims - must be kept of file.CRC maintains a
reasonably stable client base. For this privileged category of

8|P a ge
Division of Experiments List of Experiments
customers special credit card facilities are provided. These
customers may also book in advance a particular car. These
reservations can be made for any period of time up to one month.
Casual customers must pay a deposit for an estimated time of rental,
unless they wish to pay by credit card. All major credit cards are
accepted. Personal details (such as name, address, telephone
number, driving license, number) about each customer are kept in
the database. For the above case study, do the following.
1. Analyze the data required.
2. Normalize the attributes.
3. Create the logical data model (ER diagrams).

Week -13
Case study: Student Progress Monitoring System A database is to
be designed for a college to monitor students' progress throughout
their course of study. The students are reading for a degree (such as
BA, BA(Hons) MSc, etc) within the framework of the modular
system. The college provides a number of modules, each being
characterized by its code, title, credit value, module leader, teaching
staff and the department they come from. A module is coordinated
by a module leader who shares teaching duties with one or more
lecturers. A lecturer may teach (and be a module leader for) more
than one module. Students are free to choose any module they wish
but the following rules must be observed: some modules require pre-
requisites modules and some degree programmes have compulsory
modules. The database is also to contain some information about
students including their numbers, names, addresses, degrees they
read for, and their past performance (i.e. modules taken and
examination results). For the above case study, do the following.
1. Analyze the data required.
2. Normalize the attributes.
3. Create the logical data model (ER diagrams).
4. Comprehend the data given in the case study by
creating respective tables with primary keys and foreign
keys wherever required.
5. Insert values into the tables created (Be vigilant about
Master- Slave tables).
6. Display the Students who have taken M.Sc course.
7. Display the Module code and Number of Modules taught
by each Lecturer.
8. Retrieve the Lecturer names who are not Module
Leaders.
9. Display the Department name which offers ‘English’
module.
10. Retrieve the Prerequisite Courses offered by every
Department (with Department names).
11. Present the Lecturer ID and Name who teaches
‘Mathematics’.
12. Discover the number of years a Module is taught.
13. List out all the Faculties who work for ‘Statistics’
Department.
14. List out the number of Modules taught by each
Module Leader.

9|P a ge
Division of Experiments List of Experiments
15. List out the number of Modules taught by a particular
Lecturer.
16. Create a view which contains the fields of both
Department and Module tables. (Hint- The fields
like Module code, title, credit, Department code and
its name).
17. Update the credits of all the prerequisite courses to
5.
18. Delete the Module ‘History’ from the Module table.

HOD, CSE

10 | P a g e