Department of CSE Lab Manual

AURORA’S TECHNOLOGICAL AND RESEARCH INSTITUTE

(Accredited by NAAC with ‘A’ Grade)
(Approved by AICTE and Affiliated to JNTU, Hyderabad)
Parvathapur, Uppal, Hyderabad-500 098
2023-24

LAB MANUAL
DATABASE MANAGEMENT SYSTEM

Aurora’s Technological & Research Institute 1

Department of CSE Lab Manual

INDEX
PAGE
WEEK CONTENT
NO.

1 Concept design with E-R Model

2 Relational Model

3 Practicing DDL commands

4 Practicing DML commands

A. Querying (using ANY,

ALL, UNION, INTERSECT,
5 JOIN, Constraints etc.)
B. Nested, Correlated
subqueries
Queries using Aggregate
functions, GROUP BY,
6 HAVING and Creation and
dropping of Views

7 Normalization

Triggers (Creation of insert

8 trigger, delete trigger,
update trigger)
9 Procedures

Usage of Cursors
10

Aurora’s Technological & Research Institute 2

Department of CSE Lab Manual

Course Objectives:
 Introduce ER data model, database design and normalization
 Learn SQL basics for data definition and data manipulation

Course Outcomes:
● Design database schema for a given application and apply normalization
● Acquire skills in using SQL commands for data definition and data
manipulation.
● Develop solutions for database applications using procedures, cursors and
triggers

List of Experiments:

1. Concept design with E-R Model

2. Relational Model
3. Normalization
4. Practicing DDL commands
5. Practicing DML commands
6. A. Querying (using ANY, ALL, UNION, INTERSECT, JOIN,
Constraints etc.)
B. Nested, Correlated subqueries
7. Queries using Aggregate functions, GROUP BY, HAVING and
Creation and dropping of Views.
8. Triggers (Creation of insert trigger, delete trigger, update trigger)
9. Procedures
10. Usage of Cursors

Aurora’s Technological & Research Institute 3

Department of CSE Lab Manual

Code of Conduct

 Students should report to the labs concerned as per the timetable.

 Students who turn up late to the labs will in no case be permitted to perform the experiment
scheduled for the day.

 After completion of the experiment, certification of the staff in-charge concerned in the
observation book is necessary.

 Students should bring a notebook of about 100 pages and should enter the
readings/observations/results into the notebook while performing the experiment.

 The record of observations along with the detailed experimental procedure of the experiment
performed in the immediate previous session should be submitted and certified by the staff
member in-charge.

 Not more than three students in a group are permitted to perform the experiment on a set up.

 The group-wise division made in the beginning should be adhered to, and no mix up of student
among different groups will be permitted later.

 The components required pertaining to the experiment should be collected from Lab – in -
charge after duly filling in the requisition form.

 When the experiment is completed, students should disconnect the setup made by them, and
should return all the components/instruments taken for the purpose.

 Any damage of the equipment or burnout of components will be viewed seriously either by
putting penalty or by dismissing the total group of students from the lab for the semester/year.

 Students should be present in the labs for the total scheduled duration.

 Students are expected to prepare thoroughly to perform the experiment before coming to
Laboratory.

 Procedure sheets/data sheets provided to the students’ groups should be maintained neatly
and are to be returned after the experiment.

Aurora’s Technological & Research Institute 4

Department of CSE Lab Manual

Suggested Reference Books for solving the problems:

1. Database Systems design, Implementation, and Management, Peter Rob & Carlos Coronel
7th Edition.

2. Fundamentals of Database Systems, Elmasri Navrate, Pearson Education

3. Introduction to Database Systems, C.J. Date, Pearson Education

4. Oracle for Professionals, The X Team, S. Shah and V. Shah, SPD.

5. Database Systems Using Oracle: A Simplified guide to SQL and PL/SQL, Shah, PHI.

6. Fundamentals of Database Management Systems, M. L. Gillenson, Wiley Student Edition .

Aurora’s Technological & Research Institute 5

Department of CSE Lab Manual

EXPERIMENT NO: 1

Concept design with E-R Model

ER Diagram for Bank Database

An “entity-relationship diagram” is a kind of flowchart of a database that helps us to
analyze the requirements and design of the database. It conveys the relationship between
several entities of a specified system and their attributes.

It is a basic diagrammatic structure to represent a database and is considered good to start

with an ER diagram before implementing the database system.

In this er diagram of the bank database, we have eight entities,

1. Customer: To represent the customers.

2. Banker: To represent the Banker, who manages the entire branch.
3. Branch: To represent a branch of a bank.
4. Loan: To represent the loan granted by the branch to the customer’s account.
5. Account: To represent the bank account of any customer.
6. Depositor: To represent the depositor for any account.
7. Borrower: To represent the borrower.
8. Employee: To represent the employee of the bank.

ER Diagram consists of some shapes which have their significance i.e. Rectangles are used
to represent the entities, Rhombus is used to represent the association between the entities,
and Oval represents the attributes of an entity.

Aurora’s Technological & Research Institute 6

Department of CSE Lab Manual

E-R diagram for Banking Enterprise

Aurora’s Technological & Research Institute 7

Department of CSE Lab Manual

DESCRIPTION:

The following relations keep track of a banking enterprise.

 BRANCH (branch-name: string, branch-city: string, assets: int)

 ACCOUNT (acc-no: int, branch-name: string, balance: int)
 CUSTOMER (customer-id: int, customer-name: string, customer-street: string,
customer-city: string)
 DEPOSITOR (customer-name: string, acc-no: int, access-date: date)
 LOAN (loan-number: int, branch-name: string, amount: float)
 BORROWER (customer-name: string, loan-number: int)
 EMPLOYEE (employee-id: int, dependent-name: string, employment-length: int,
employee-name: string, telephone-number: int, start-date: date, branch-name:
string, salary: int)

Aurora’s Technological & Research Institute 8

Department of CSE Lab Manual

E-R diagram for Railway Reservation System

Aurora’s Technological & Research Institute 9

Department of CSE Lab Manual

E-R diagram for University

Aurora’s Technological & Research Institute 10

Department of CSE Lab Manual

E-R diagram for Library Management System

Aurora’s Technological & Research Institute 11

Department of CSE Lab Manual

E-R diagram for Inventory Management System

Aurora’s Technological & Research Institute 12

Department of CSE Lab Manual

EXPERIMENT NO: 2

Relational Model

Relation: Branch

branch_id branch_city assets branch_name

5 Delhi 50000000 Greenpark

4 Bangalore 25000000 Jaynagar

3 Mumbai 45000000 Marine drive

2 Delhi 35000000 Noida

1 Hyderabad 15000000 Uppal

Aurora’s Technological & Research Institute 13

Department of CSE Lab Manual

Relation: Account

account_no branch_id balance acc_holder_name

113 3 73848 Urvasi

118 4 1000 Ganesh

123 1 50000 Sai Kiran

143 4 100000 Sriman

456 2 4000 Karthik

731 5 11345 Saina

789 2 15000 Sandeep

999 4 27000 Pradeep

9494 4 49000 Sahithi

Aurora’s Technological & Research Institute 14

Department of CSE Lab Manual

Relation: Customer

customer_id customer_name customer_street customer_city phone_no

5 Kiran Greenpark Delhi 9374092738

2 Kumar Uppal Hyderabad 7339364734

1 Ramu Jaynagar Bangalore 9467575487

4 Ravi Marine drive Mumbai 9473627837

3 Sachin Noida Delhi 9575791679

Aurora’s Technological & Research Institute 15

Department of CSE Lab Manual

Relation: Depositor

customer_id account_no access_date deposite_amount

1 123 2021-02-23 50000

2 456 2019-10-15 150000

3 789 2023-08-01 30000

4 113 2015-07-07 60000

5 118 2010-02-04 100000

6 143 2019-10-16 70000

7 731 2018-12-10 10000

8 9494 2024-01-15 20000

Aurora’s Technological & Research Institute 16

Department of CSE Lab Manual

Relation: Loan

loan_number branch_name amount

1111 Uppal 300000.00

2222 Greenpark 350000.00

3333 Jaynagar 650000.00

4444 Noida 1500000.00

Aurora’s Technological & Research Institute 17

Department of CSE Lab Manual

Relation: Borrower

customer_id loan_number

1 1111

2 2222

3 3333

4 4444

Aurora’s Technological & Research Institute 18

Department of CSE Lab Manual

Relation: Employee

employe dependent employment employee_ telephone_n start_

e_id _name _length name umber date

1 Saachi 5 Lokesh 94736278 2020

37 -10-
19
2 Ananya 10 Ganesh 94675754 2022
87 -11-
10
3 Jaya 7 Karthik 73393647 2019
34 -02- EXPERIMENT
13 NO: 3

4 Bhavani 2 Shravan 93740927 2024

38 -04-
01 Practicing
DDL
commands

DDL---Data Definition Language

 These commands are used to create and modify the structure of the table.

 There are 4 DDL commands.

Aurora’s Technological & Research Institute 19
Department of CSE Lab Manual
1) Create

2) Alter

3) Drop

4) Rename

COMMANDS:

 Create:

Create command is used to create the structure of the table.

Syntax:

create table <table name>

(<column name data type>, ...............);

 Alter:

*Alter command is used to change or modify the structure of the table.

 Syntax:

Alter table <tablename>

add (col1 datatype, col2 datatype....)

Alter table <tablename> modify column datatype

Aurora’s Technological & Research Institute 20

Department of CSE Lab Manual
ex: Alter table student branch char (10);

Alter table <tablename> drop columnname

ex: Alter table student drop branch;

 Drop:

*Drop command is used to delete the columns of the table.

 Syntax:

Drop table <table name>

ex: Drop table student;

 Rename:

*Rename command is used to modify or change the name of the table.

 Syntax:

Rename<oldtablename> to <newtablename>

ex: Rename student to student 10;

ENTITY: Branch
TABLE CREATION

Aurora’s Technological & Research Institute 21

Department of CSE Lab Manual
mysql > create table branch (

 branch_id int (5) primary key,

 branch_city varchar (10) not null,
 assets int (10) not null
 );

TABLE DESCRIPTION

mysql > Desc branch;

Field Type Null Key Default Extra
branch_id int NO PRI NULL
branch_city varchar (10) NO NULL
assets int NO NULL

TABLE CREATION

mysql > alter table branch

 add branch_name varchar (20) not null;

mysql > alter table branch

 add branch_manager varchar (10);

mysql > alter table branch

 modify branch_name varchar (15);

mysql > alter table branch

 drop branch_manager;

TABLE DESCRIPTION
Aurora’s Technological & Research Institute 22
Department of CSE Lab Manual
mysql > Desc branch;

Field Type Null Key Default Extra

branch_id int NO PRI NULL
branch_city varchar (10) NO NULL
assets int NO NULL
branch_name varchar (15) NO NULL

ENTITY: Account
TABLE CREATION

mysql > create table account (

 account_no int (20) primary key,

 branch_id int (5) not null,
 balance int (20) not null,
 foreign key(branch_id) references branch(branch_id)
 );

TABLE DESCRIPTION

Aurora’s Technological & Research Institute 23

Department of CSE Lab Manual
mysql > Desc account;
Field Type Null Key Default Extra
account_no int NO PRI NULL
branch_id int NO MUL NULL
balance int NO NULL

TABLE CREATION

mysql > alter table account

 add acc_holder_name varchar (10) not null;

mysql > alter table account

 add account_type varchar (10);

mysql > alter table account

 modify balance int (10);

mysql > alter table account

 drop account_type;

TABLE DESCRIPTION

mysql > Desc account;

Field Type Null Key Default Extra
account_no int NO PRI NULL
branch_id int NO MUL NULL
balance int NO NULL
acc_holder_name varchar (10) NO NULL

Aurora’s Technological & Research Institute 24

Department of CSE Lab Manual

ENTITY: Customer
TABLE CREATION

mysql > create table customer (

 customer_id int (10) not null primary key,

 customer_name varchar (20) not null,
 customer_street varchar (20) not null,
 customer_city varchar (10)
 );

TABLE DESCRIPTION

mysql > Desc customer;

Field Type Null Key Default Extra
customer_id int NO PRI NULL
customer_nam varchar (20) NO NULL
e
customer_stree varchar (20) NO NULL
t
customer_city varchar (10) YES NULL

TABLE CREATION

mysql > alter table customer

 add phone_no int (12) not null;

mysql > alter table customer

 add customer_details varchar (10);

mysql > alter table customer

Aurora’s Technological & Research Institute 25

Department of CSE Lab Manual
 modify customer_street varchar (10);

mysql > alter table customer

 drop customer_details;

TABLE DESCRIPTION

mysql > Desc customer;

Field Type Null Key Default Extra
customer_id int NO PRI NULL
customer_nam varchar (20) NO NULL
e
customer_stree varchar (10) NO NULL
t
customer_city varchar (10) YES NULL
phone_no int NO NULL

ENTITY: Depositor
TABLE CREATION

mysql > create table depositor (

 customer_id int (10) not null,

 account_no int (20) not null,
 access_date date not null,
 foreign key(customer_id) references customer(customer_id),
 foreign key(account_no) references account(account_no)
 primary key(account_no,customer_id)
 );

Aurora’s Technological & Research Institute 26

Department of CSE Lab Manual

TABLE DESCRIPTION

mysql > Desc depositor;

Field Type Null Key Default Extra
customer_id int NO MUL NULL
account_no int NO MUL NULL
access_date date NO NULL

TABLE CREATION

mysql > alter table depositor

 add deposit_amount int (10) not null;

mysql > alter table depositor

 add deposit_date date;

mysql > alter table depositor

 modify account_no int (15);

mysql > alter table depositor

 drop deposit_date;

TABLE DESCRIPTION

mysql > Desc depositor;

Field Type Null Key Default Extra

Aurora’s Technological & Research Institute 27

Department of CSE Lab Manual

customer_id int NO MUL NULL

account_no int NO MUL NULL
access_date date NO NULL
deposit_amount int NO NULL

ENTITY: Loan

TABLE CREATION

mysql > create table loan (

 loan_number int (10) primary key,

 branch_id int (5) not null,
 amount float (10,2) not null,
 foreign key (branch_id) references branch(branch_id)
 );

TABLE DESCRIPTION

mysql > Desc loan;

Field Type Null Key Default Extra
loan_number int NO PRI NULL
branch_id int NO MUL NULL
amount float (10,2) NO NULL

Aurora’s Technological & Research Institute 28

Department of CSE Lab Manual
ENTITY: Borrower
TABLE CREATION

mysql > create table borrower (

 customer_id int (10) not null,

 loan_number int (10) not null,
 foreign key (customer_id) references customer(customer_id),
 foreign key (loan_number) references loan(loan_number),
 primary key (customer_id, loan_number)
 );

TABLE DESCRIPTION

mysql > Desc borrower;

Field Type Null Key Default Extra
customer_id int NO NULL
loan_number int NO NULL

ENTITY: Employee
TABLE CREATION

mysql > create table employee (

 employee_id int not null primary key,

 dependent_name varchar (10) not null,
 employment_length int not null,
 employee_name varchar (20) not null,
 telephone_number varchar (20) unique not null,
 start_date date,

Aurora’s Technological & Research Institute 29

Department of CSE Lab Manual
 );

TABLE DESCRIPTION

mysql > Desc employee;

Field Type Null Key Default Extra

employee_id int NO PRI NULL
dependent_name varchar (10) NO NULL
employment_length int NO NULL
employee_name varchar (20) NO NULL
telephone_number varchar (20) NO NULL
start_date Date YES NULL

EXPERIMENT NO: 4

Practicing DML commands

DML --- Data Manuplation Language

DML commands are used to manupilate the data of the table.

Aurora’s Technological & Research Institute 30

Department of CSE Lab Manual
There are 3 DML commands

1)Insert

2)Update

3)Delete

COMMANDS:

 Insert:

Insert command is used for inserting rows in the table.

Syntax:

insert into <tablename>

values ( );

ex: insert into student

values(1,'dhanush',19,40,40,40,40,40);

ex: insert into student

values(1,'dhanush',19,40,40,40,40,40),

(2,'ganesh',19,30,30,30,30,30);

ex: mysql > select * from student;

select name, age, dbmsmarks from student;

Aurora’s Technological & Research Institute 31

Department of CSE Lab Manual

ex: insert into student (student id, name, dbmsmarks)

values(1,'Karthik',40);

Update:

Update command is used to change the values of the table.

Syntax

update<table name>set<column>=value where <conditions>

Delete:
 Delete command is used to delete the values of the table.

Syntax:

Delete from <tablename> where <conditions>

Aurora’s Technological & Research Institute 32

Department of CSE Lab Manual
ENTITY: Branch
INSERTING TUPLES

mysql > insert into branch

 values (1,’Hyderabad’,1500000, ‘Uppal’),

 (2,’Bangalore’,2500000, ‘Jaynagar’),
 (3,’Delhi’,3500000, ‘Noida’),
 (4,’Mumbai’,4500000, ‘Marine drive’),
 (5,’Delhi’,5000000, ‘Greenpark’);

TUPLES DESCRIPTION

mysql > select * from branch;

branch_id branch_city assets branch_name
5 Delhi 5000000 Greenpark
4 Bangalore 2500000 Jaynagar
3 Mumbai 4500000 Marine drive
2 Delhi 3500000 Noida
1 Hyderabad 1500000 Uppal

ENTITY: Account
INSERTING TUPLES

mysql > insert into account

 values (123,1,50000,’Sai Kiran’),

 (456,2,4000, ’Karthik’),
 (789,2,15000, ’Sandeep’),
 (143,4,100000, ’Sriman’),
 (118,4,1000, ’Ganesh’),

Aurora’s Technological & Research Institute 33

Department of CSE Lab Manual
 (999,4,27000, ’Pradeep’),
 (9494,4,49000, ’Sahithi’),
 (731,5,11345, ’Saina’),
 (113,3,73848, ’Urvasi’);

TUPLES DESCRIPTION

mysql > select * from account;

account_no branch_id balance acc_holder_name
113 3 73848 Urvasi
118 4 1000 Ganesh
123 1 50000 Sai Kiran
143 4 100000 Sriman
456 2 4000 Karthik
731 5 11345 Saina
789 2 15000 Sandeep
999 4 27000 Pradeep
9494 4 49000 Sahithi

ENTITY: Customer
INSERTING TUPLES

mysql > insert into customer

 values (1, ‘ramu’,’Jaynagar’,’Bangalore’, 9467575487),

 (2, ‘kumar’,’Uppal’,’Hyderabad’, 7339364734),
 (3, ‘sachin’,’Noida’,’Delhi’, 9575791679),
 (4, ‘ravi’,’Marine drive’,’Mumbai’, 9473627837),
 (5, ‘kiran’,’Greenpark’,’Delhi’, 9374092738);
Aurora’s Technological & Research Institute 34
Department of CSE Lab Manual

TUPLES DESCRIPTION

mysql > select * from customer;

customer_id customer_name customer_street customer_city phone_no
5 Kiran Greenpark Delhi 9374092738
2 Kumar Uppal Hyderabad 7339364734
1 Ramu Jaynagar Bangalore 9467575487
4 Ravi Marine drive Mumbai 9473627837
3 Sachin Noida Delhi 9575791679

ENTITY: Depositor
INSERTING TUPLES

mysql > insert into depositor

 Values (1,123,'2021-02-23',50000),
 (2,456,'2019-10-15', 150000),
 (3,789,'2023-08-01', 30000),
 (4,113,'2015-07-07', 60000),
 (5,118,'2010-02-04', 100000),
 (6,143,'2019-10-16', 70000),
 (7,731,'2018-12-10', 10000),
Aurora’s Technological & Research Institute 35
Department of CSE Lab Manual
 (8,9494,'2024-01-15', 20000);

TUPLES DESCRIPTION

mysql > select * from depositor;

customer_id account_no access_date deposite_amount
1 123 2021-02-23 50000
2 456 2019-10-15 150000
3 789 2023-08-01 30000
4 113 2015-07-07 60000
5 118 2010-02-04 100000
6 143 2019-10-16 70000
7 731 2018-12-10 10000
8 9494 2024-01-15 20000

ENTITY: Loan
INSERTING TUPLES

mysql > insert into loan

 values(1111,'Uppal',250000),
 (2222,'Greenpark',350000),
 (3333,'Jaynagar',450000),
 (4444,'Noida',1500000),
 (5555,'Marine drive',750000)
 );

Aurora’s Technological & Research Institute 36

Department of CSE Lab Manual

mysql > update loan set amount = 30000.00 where loan_number = 1111;

mysql > update loan set amount = 65000.00 where loan_number = 3333;

mysql >delete from loan where loan_number = 5555;

TUPLES DESCRIPTION

mysql > select * from loan;

loan_number branch_name amount
1111 Uppal 300000.00
2222 Greenpark 350000.00
3333 Jaynagar 650000.00
4444 Noida 1500000.00

ENTITY: Borrower

INSERTING TUPLES

mysql > insert into borrower

 insert into borrower

 Values (1,1111),
 (2,2222),
 (3,3333),
 (4,444),

Aurora’s Technological & Research Institute 37

Department of CSE Lab Manual
 (5,5555);

mysql > update borrower set customer_id = 1 where loan_number = 1111;

mysql > update borrower set customer_id = 2 where loan_number = 2222;

mysql >delete from borrower where loan_number = 5555;

TUPLES DESCRIPTION

mysql > select * from borrower;

customer_id loan_number
1 1111
2 2222
3 3333
4 4444

ENTITY: Employee

INSERTING TUPLES

mysql > insert into employee

 values(1,'saachi',10,'Lokesh',9473627837,'2020-10-19'),
 (2,'Ananya',5,'Ganesh',9467575487,'2022-11-10'),
 (3,'Jaya',7,'Karthik',7339364734,'2019-02-13'),
 (4,'Bhavani',2,'Shravan',9374092738,'2024-04-01'),
 (5,'Sushma',4,'Himesh',9575791679,'2023-01-15');

Aurora’s Technological & Research Institute 38

Department of CSE Lab Manual
mysql > update employee set employment_length = 5 where employee_id = 1;

mysql > update employee set employment_length = 10 where employee_id = 3;

mysql >delete from employee where employee_id = 5;

TUPLES DESCRIPTION

mysql > select * from employee;

employee dependent_na employment_len employee_na telephone_num start_da
_id me gth me ber te

1 Saachi 5 Lokesh 9473627837 2020-

10-19
2 Ananya 10 Ganesh 9467575487 2022-
11-10
3 Jaya 7 Karthik 7339364734 2019-
02-13
4 Bhavani 2 Shravan 9374092738 2024-
04-01

EXPERIMENT NO: 5

A. Querying (using ANY, ALL, UNION, INTERSECT, JOIN,

Constraints etc.)

 Find the customer name in Hyderabad.

 Select customer_name from customer where customer_city = ‘Hyderabad’;
 Kumar

Aurora’s Technological & Research Institute 39

Department of CSE Lab Manual
 Find the customer details who took loan amount greater than 30,00,000.
 Select * from customer c, loan l, borrower b, where c.customer_id = b.customer_id and
l.loan_number = b.loan_number and l.amount > 30,00,00;
 Kumar
 Sachin
 Ravi

 Find the customer name and account balance amount of the depositer in the bank whose
balance is greater than 10,000.
 Select c.customer, a.balance from customer c, account a,depositor d .where c.customer_id =
d.customer.id and a.account_no = d.account_no and a.balance > 10,000;
 Urvasi 73848
 Sai Kiran 50000
 Sriman 100000
 Saina 11345
 Sandeep 15000
 Pradeep 27000
 Sahithi 49000

Nested Query:
 Find the customer details who took loan amount greater than 30,00,000.
 Select * from customer c where c.customer_id in(select customer_id from borrower b where
b.loan_number in(select loan_number where amount > 30,00,000));
 Kumar
 Sachin
 Ravi

Using ANY:

 Find customer information whose loan amount greater than 20,00,000.

Aurora’s Technological & Research Institute 40

Department of CSE Lab Manual
 Select * from customer c where c.customer_id in(select customer_id from borrower where
loan_number in(select loan_number from loan where amount > 20,00,000));
 Ramu
 Kumar
 Sachin
 Ravi

PERFORMING QUERIES ON TABLES

Sailors (sid: integer, sname: string, rating: integer, age: real)

Boats (bid: integer, bname: string, color: string)

Reserves (sid: integer, bid: integer, day: date)

ENTITY: Sailors
TABLE CREATION

mysql > create table sailors (

 sid int (10) primary key not null,

 sname varchar (20) not null,
 rating int (10) not null,
 age real not null
 );

TABLE DESCRIPTION

mysql > Desc sailors;

Field Type Null Key Default Extra
sid int NO PRI NULL
sname varchar (10) NO NULL

rating int NO NULL

Aurora’s Technological & Research Institute 41

Department of CSE Lab Manual
age double NO NULL

ENTITY: Boats
TABLE CREATION

mysql > create table boats (

 bid int (10) primary key not null,

 bname varchar (10) not null,
 color varchar (10) not null
 );

TABLE DESCRIPTION

mysql > Desc boats;

Field Type Null Key Default Extra
bid int NO PRI NULL
bname varchar (10) NO NULL

color varchar (10) NO NULL

ENTITY: Reserves
TABLE CREATION

mysql > create table reserves (

 sid int (10) not null,

 bid int (10) not null,
 day date not null,
 foreign key(sid) references customer(sid),

Aurora’s Technological & Research Institute 42

Department of CSE Lab Manual
 foreign key(bid) references account(bid)
 primary key (sid, bid)
 );

TABLE DESCRIPTION

mysql > Desc reserves;

Field Type Null Key Default Extra
sid int NO PRI NULL
bid int NO PRI NULL

day date NO NULL

ENTITY: Sailors
INSERTING TUPLES

mysql > insert into sailors

 values (22,'Dustin',7,45.0),
 (29,'Brutus',1,33.0),
 (31,'Lubber',8,55.5),
 (32,'Andy',8,25.5),
 (58,'Rusty',10,35.0),

 (64,'Horatio',7,35.0),
 (71,'Zorba',10,16.0),
 (74,'Horatio',9,35.0),
 (85,'Art',3,25.5),
 (95,'Bob',3,63.5);

TUPLES DESCRIPTION

mysql > select * from sailors;

Aurora’s Technological & Research Institute 43

Department of CSE Lab Manual
sid sname rating age
22 Dustin 7 45
29 Brutus 1 33
31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35
64 Horatio 7 35
71 Zorba 10 16
74 Horatio 9 35
85 Art 3 25.5
95 Bob 3 63.5

ENTITY: Boats
INSERTING TUPLES

mysql > insert into boats

 values (101,'Interlake','blue'),
 (102,'Interlake','red'),
 (103,'Clipper','green'),

 (104,'Marine','red');

TUPLES DESCRIPTION

mysql > select * from boats;

bid bname color
101 Interlake blue

Aurora’s Technological & Research Institute 44

Department of CSE Lab Manual
102 Interlake red
103 Clipper green
104 Marine red

ENTITY: Reserves
INSERTING TUPLES

mysql > insert into reserves

 values (22,101,'1998-10-10'),
 (22,102,'1998-10-10');
 (22,103,'1998-08-10'),
 (22,104,'1998-07-10'),
 (31,102,'1998-10-11'),
 (31,103,'1998-06-11'),
 (31,104,'1998-12-11'),
 (64,101,'1998-05-09'),
 (64,102,'1998-08-09'),
 (74,103,'1998-08-09');

TUPLES DESCRIPTION

mysql > select * from reserves;

sid bid day
22 101 1998-10-10
22 102 1998-10-10

Aurora’s Technological & Research Institute 45

Department of CSE Lab Manual

22 103 1998-08-10
22 104 1998-07-10
31 102 1998-10-11
31 103 1998-06-11
31 104 1998-12-11
64 101 1998-05-09
64 102 1998-08-09
74 103 1998-08-09

Queries

1.Find the names and ages of all sailors.

mysql> select distinct s.sname, s.age from sailors s;

Aurora’s Technological & Research Institute 46

Department of CSE Lab Manual
sname age

Dustin 45
Brutus 33
Lubber 55.5
Andy 25.5
Rusty 35
Horatio 35
Zorba 16
Art 25.5
Bob 63.5

2.Find all sailors with a rating above 7.

mysql> select s.sid,s.sname,s.rating,s.age from sailors as s where s.rating > 7;

sid sname rating age

31 Lubber 8 55.5
32 Andy 8 25.5
58 Rusty 10 35
71 Zorba 10 16
74 Horatio 9 35

3.Find the names of sailors who have reserved boat number 103.

mysql> select s.sname from sailors s,reserves r where s.sid=r.sid and r.bid=103;

sname
Dustin

Aurora’s Technological & Research Institute 47

Department of CSE Lab Manual
Lubber
Horatio

4.Find the sids of sailors who have reserved a red boat.

mysql> select r.sid from boats b,reserves r where b.bid=r.bid and b.color='red';

sid
22
31
64
22
31

5.Find the names of sailors who have reserved a red boat.

mysql> select s.sname from sailors s,reserves r,boats b where s.sid=r.sid an

d b.color = 'red';

sname
Dustin
Lubber
Horatio

6.Find the colors of boats reserved by Lubber.

mysql> select b.color from sailors s,reserves r,boats b where s.sid=r.sid an

d r.bid=b.bid and s.sname ='Lubber';

color
red

Aurora’s Technological & Research Institute 48

Department of CSE Lab Manual
green
red

7.Find the names of sailors who have reserved at least one boat.

Mysql> select s.sname from sailors s,reserves r where s.sid=r.sid;

sname
Dustin
Lubber
Horatio

8.Find the ages of sailors hose name begins and ends with Band has atleast three characters.

mysql> select s.age from sailors s where s.sname like 'b_%b';

age
63.5

UNION AND INTERSECT

9.Find the names of sailors who have reserved a red or a green boat.

mysql> select s.sname from sailors s,reserves r,boats b where s.sid=r.sid and r.bid=b.bid
and (b.color ='red' or b.color ='green');

sname

Aurora’s Technological & Research Institute 49

Department of CSE Lab Manual
Dustin
Lubber
Horatio
Dustin
Lubber
Horatio
Dustin
Lubber

The OR query (query 9) can be rewritten as follows:

mysql> select s.sname from sailors s,reserves r,boats b where s.sid=r.sid an

d r.bid=b.bid and b.color='red' union select s2.sname from sailors s2,boats
b2,reserves r2 where s2.sid=r2.sid and r2.bid=b2.bid and b2.color='green';

sname
Dustin
Lubber
Horatio

10.Find the names of sailors who have reserved both a red and a green boat.

mysql> select s.sname from sailors s,reserves r1,boats b1,reserves r2,boats

b2 where s.sid = r1.sid and r1.bid=b1.bid and s.sid=r2.sid and r2.bid=b2.bid
and b1.color='red' and b2.color='green';

sname

Aurora’s Technological & Research Institute 50

Department of CSE Lab Manual
Dustin
Lubber
Dustin
Lubber

The AND query (query 10) can be rewritten as follows:

mysql> select s.sname from sailors s,reserves r,boats b where s.sid=r.sid and r.bid=b.bid and
b.color='red' intersect select s2.sname from sailors s2, b
oats b2,reserves r2 where s2.sid=r2.sid and r2.bid=b2.bid and b2.color='green';

sname
Horatio
Lubber
Dustin

11.Find all sids of sailors who have a rating of 10 or reserved boat 104.

mysql> select s.sid from sailors s where s.rating=10 union select r.sid from
reserves r where r.bid=104;

sid
58
71
22
31
Aurora’s Technological & Research Institute 51
Department of CSE Lab Manual

NESTED QUERIES

12.Find the names of sailors who have reserved boat 103.

mysql> select s.sname from sailors s where s.sid in(select r.sid from reserv
es r where r.bid = 103);

sname
Dustin
Lubber
Horatio

13.Find the names of sailors who have reserved a red boat.

mysql> select s.sname from sailors s where s.sid in (select r.sid from reser
ves r where r.bid in (select b.bid from boats b where b.color='red'));

sname
Dustin
Lubber
Horatio

14.Find the names of sailors who have not reserved a red boat.

mysql> select s.sname from sailors s where s.sid not in (select r.sid from
reserves r where r.bid in (select b.bid from boats b where b.color='red'));

sname
Brutus

Aurora’s Technological & Research Institute 52

Department of CSE Lab Manual
Andy
Rusty
Zorba
Horatio
Art
Bob

CORRELATED NESTED QUERIES

15.Find sailors whose rating is better than some sailor called Horatio.

mysql> select s.sid from sailors s where s.rating > any (select s2.rating fr
om sailors s2 where s2.sname='Horatio');

sid
31
32
58
71
74

16.Find the sailors with the highest rating.

mysql> select s.sid from sailors s where s.rating >= all (select s2.rating f
rom sailors s2);

sid
58

Aurora’s Technological & Research Institute 53

Department of CSE Lab Manual
71

EXPERIMENT NO: 6

Queries using Aggregate functions, GROUP BY, HAVING and

Creation and dropping of Views

1.Find the average age of all sailors.

Aurora’s Technological & Research Institute 54

Department of CSE Lab Manual

mysql> select avg(s.age) from sailors s;

avg(s.age)
36.9

2.Find the average age of sailors with a rating of 10.

mysql> select avg(s.age) from sailors s where s.rating=10;

avg(s.age)
25.5

3.Find the name and age of the oldest sailor.

mysql> select s.sname,s.age from sailors s where s.age=(select max(s2.age) from sailors
s2) ;

sname age
Bob 63.5

4.Count the number of sailors

mysql> select count( * ) from sailors s;

count ( * )
10

Aurora’s Technological & Research Institute 55

Department of CSE Lab Manual

5.Count the number of different sailors names.

mysql> select count( distinct s.sname )from sailors s;

count (distinct
s.sname )
9

6.Find the names of sailors who are older than the oldest sailor with a rating of 10.

mysql> select s.sname from sailors s where s.age>(select max(s2.age) from sa

ilors s2 where s2.rating = 10);

sname
Dustin
Lubber
Bob

This query can also be written as follows:

mysql> select s.sname from sailors s where s.age > all (select s2.age from s
ailors sailors s2 where s2.rating=10);

sname
Dustin
Lubber
Bob

7.Find the age of the youngest sailor who is eligible to vote(i.e is atleast 18 years old) for
each rating level with atleast two such sailors.

Aurora’s Technological & Research Institute 56

Department of CSE Lab Manual

mysql> select s.rating,min(s.age) as minage from sailors s where s.age>=18 group by

s.rating having count( * )>1;

rating minage
7 35
8 25.5
3 25.5

8.For each red boat, find the number of reservations for this boat.

mysql> select b.bid,count( * ) as reservationcount from boats b,reserves r w

here r.bid=b.bid and b.color='red' group by b.bid;

bid reservationcount
102 3
104 2

9.Find the average age of sailors for each rating level that has atleast two sailors.

mysql> select s.rating,avg(s.age) as avgage from sailors s group by s.rating

having count ( * ) > 1;

rating avgage
7 40
8 40.5
10 25.5
3 44.5

Aurora’s Technological & Research Institute 57

Department of CSE Lab Manual

EXPERIMENT NO: 7

NORMALIZATION

 Normalization is the process of organizing the data in the database.

 It is performed to reduce the data redundancy in a database. Data
redundancy in DBMS means having the same data at multiple places. It is
necessary to remove data redundancy because it causes anomalies in a
database which makes it very hard for a database administrator to maintain
it.
 Normalization works through a series of stages called Normal forms the
normal form applied to table is said to be in the particular normal form if it
satisfies constraints.

NORMAL FORMS:

 First Normal Form (1F)

 Second Normal Form (2F)

 Third Normal Form (3F)

 Boyce-Codd Normal Form (BCNF)

 Fourth Normal Form (4F)

 Fifth Normal Form(5NF)

Aurora’s Technological & Research Institute 58

Department of CSE Lab Manual
1.First Normal Form(1NF):

 A relation will ne 1NF if it contains an atomic value.

 It states that an attribute of a table cannot hold multiple values. It must hold only
single valued attribute.
 First normal form disallows the multi-valued attribute

Example: Relation “Student” is not in 1NF because of multivalued attribute

“languages_known”.

Student table:

name rollno branch Languages_known

Sai 21 CSE Telugu,english

Shiva 22 AIML English,hindi

Raju 24 DS English,hindi

After 1NF

Student Table:

name rollno branch Languages_known

Sai 21 CSE Telugu

Sai 21 CSE English

Shiva 22 AIML English

Shiva 22 AIML Hindi

Raju 24 DS English

Aurora’s Technological & Research Institute 59

Department of CSE Lab Manual
Raju 24 DS Hindi

2.Second Normal Form(2NF):

 A relation will be in 2NF if it is in 1NF and not contain any partial dependency i.e No
Partial Dependency.

Example: Student

name rollno branch Languages_known

Sai 21 CSE Telugu

Sai 21 CSE English

Shiva 22 AIML English

Shiva 22 AIML Hindi

Raju 24 DS English

Raju 24 DS Hindi

After 2NF

name Rollno(primary key) branch

Sai 21 CSE

Shiva 22 AIML

Raju 24 DS

Aurora’s Technological & Research Institute 60

Department of CSE Lab Manual

 Creating separate table for partial dependency

rollno Languages_known

21 Telugu

21 English

22 English

22 Hindi

24 English

24 Hindi

3.Third Normal Form(3NF):

 A table will be 3NF if it is in 2NF and if there is no transitive dependency for non-
prime attributes then the table is in third normal form.

2NF => 1NF + no partial dependency

3NF => 2NF + no transitive dependence

name rollno branch Fee

Sai 21 CSE 30000

Shiva 22 AIML 20000

Aurora’s Technological & Research Institute 61

Department of CSE Lab Manual
Ramesh 23 DS 10000

Suresh 24 ECE 5000

After 3NF

name rollno branch

Sai 21 CSE

Shiva 22 AIML

Ramesh 23 DS

Suresh 24 ECE

branch fee

CSE 30000

AIML 20000

DS 10000

ECE 5000

4.Boyce-Codd Normal Form (BCNF):

Aurora’s Technological & Research Institute 62

Department of CSE Lab Manual
 Boyce-Codd Normal form (BCNF) is an extension to the third normal form. It is also
known as 3.5 NF. BCNF is the advanced version of 3NF. It is stricter than 3NF.

Rules: A table is in BCNF

1.if it is in 3NF

2.And, for any dependency A  B,

A should be a Super Key.

Example: Student

rollno Std_name Branch_id Branch_name

1 Sai 121 CSE

2 Shiva 122 AIML

3 Ramesh 123 DS

4 Raju 121 CSE

{rollno}{std_name} {branch_id}{branch_name}

Super key Not a Super Key

rollno Std_name

1 Sai

2 Shiva

3 Ramesh

Aurora’s Technological & Research Institute 63

Department of CSE Lab Manual
4 Raju

{rollno}{std_name}

Super Key

Branch_id Branch_name

121 CSE

122 AIML

123 DS

{branch_id}{branch_name}

Super Key

5.Fourth Normal Form(4NF):

A relation will be in 4NF, if it is in BCNF and has no multi valued dependency.

Example:

Std_id course hobby

1 CSE Dancing

2 ECE Cricket

3 AIML Singing

Aurora’s Technological & Research Institute 64

Department of CSE Lab Manual
After 4NF

Std_id Course

1 CSE

2 ECE

3 AIML

Std_id hobby

1 Dancing

2 Cricket

3 Singing

6.Fifth Normal Form(5NF):

 A relation is in fifth normal form if it is in 4NF and does not contain any join
dependency.

 5NF is satisfied when all the tables present in the DBMS are broken down into as many
more tables to ensure there is no redundancy.

Example:

subject faculty year

C Sai 1

C Shiva 1

Aurora’s Technological & Research Institute 65

Department of CSE Lab Manual
Java Raju 2

DBMS Nagendra 3

After 5NF

Table 1

subject faculty

C Sai

C Shiva

Java Raju

DBMS Nagendra

Table 2

faculty year

Sai 1

Shiva 1

Raju 2

Nagendra 3

Aurora’s Technological & Research Institute 66

Department of CSE Lab Manual
Table 3

subject year

C 1

Java 2

DBMS 3

EXPERIMENT NO: 8

TRIGGERS

Mysql> delimiter $$

create table account (acc_no int (5), amount int (8)) $$

Query OK, 0 rows affected (0.04 sec)

Aurora’s Technological & Research Institute 67

Department of CSE Lab Manual

mysql> create table account_operations (account_no int,operation varchar (10));

-> $$

Query OK, 0 rows affected (0.04 sec)

INSERT TRIGGER

mysql> create trigger ins_account before insert on account

-> for each row

-> begin

-> set @sum=@sum+ new.amount;

-> insert into account_operations values(new.acc_no,'insert');

-> end$$

Query OK, 0 rows affected (0.04 sec)

UPDATE TRIGGER

mysql> create trigger upd_check before update on account

-> for each row

-> begin

-> if new.amount<0 then

-> set new.amount=0;

-> end if;

-> insert into account_operations values(new.acc_no,'update');

Aurora’s Technological & Research Institute 68

Department of CSE Lab Manual
-> end$$

Query OK, 0 rows affected (0.04 sec)

DELETE TRIGGER

mysql> create trigger del_account before delete on account

-> for each row

-> begin

-> insert into account_backup values(old.acc_no,old.amount,'deleted');

-> end$$

Query OK, 0 rows affected (0.04 sec)

INPUT-OUTPUT

mysql> insert into account values (1,1000), (2,2000);

-> $$

Query OK, 2 rows affected (0.03 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from account$$

acc_no amount

1 1000

2 2000

2 rows in set (0.00 sec)

mysql> select * from account_operations$$

account_no operation

Aurora’s Technological & Research Institute 69

Department of CSE Lab Manual
1 insert

2 insert

2 rows in set (0.00 sec)

mysql> select @sum as 'total_sum'$$

total_sum

3000

1 row in set (0.00 sec)

mysql> update account set amount=3000 where acc_no=1$$

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from account$$

acc_no amount

1 3000

2 2000

2 rows in set (0.00 sec)

mysql> select * from account_operations$$

account_no operation

1 insert

2 insert

3 update

3 rows in set (0.00 sec)

mysql> delete from account where acc_no=2$$

Aurora’s Technological & Research Institute 70

Department of CSE Lab Manual
Query OK, 1 row affected (0.03 sec)

mysql> select * from account_backup$$

acc_no amount opt

2 2000 deleted

1 row in set (0.00 sec)

mysql> select * from account$$

acc_no amount

1 3000

1 row in set (0.00 sec)

EXPERIMENT NO: 9

PROCEDURES & CURSORS

mysql> create table emp (

 empno int (3) primary key,

 name varchar (20),

Aurora’s Technological & Research Institute 71

Department of CSE Lab Manual
 sal int (7)
 );

mysql> insert into emp

 values(1,'aparna',10000),
 (2,'dhanush',20000),
 (3,’shiva’,30000),
 (4,’rakesh’,40000),
 (5,’gokul’,50000)
;

mysql> select * from emp;

empno name sal

1 Aparna 10000

2 Dhanush 20000

3 Shiva 30000

4 Rakesh 40000

5 Gokul 50000

mysql> delimiter $$

 create procedure emp_name (inout name_list varchar (4000))

 begin
 declare is_done integer default 0;
 declare e_name varchar (100);
 declare emp_cur cursor for select name from emp;
 declare continue handler for not found set is_done=1;
 open emp_cur;
 get_list:loop
 fetch emp_cur into e_name;
 if is_done=1 then

Aurora’s Technological & Research Institute 72

Department of CSE Lab Manual
 leave get_list;
 end if;
 set name_list=concat(e_name, ";" ,name_list);
 end loop get_list;
 close emp_cur;
 end$$

mysql> set @name_list="";

 call emp_name(@name_list) $$

mysql> select @name_list$$

@name_list

Gokul;Rakesh;Shiva;Dhanush;Aparna;

Aurora’s Technological & Research Institute 73