Week: 1

E-R Model
Analyze the problem carefully and come up with entities in it. Identify what date has to
be persisted in the database. This contains the entities, attributes etc.
Identify the primary keys for all the entities. Identify the other keys like candidate keys,
partial keys, if any.

Definitions:
1. Entity: It is a “thing” or “object” in the real world that is distinguishable from all other
objects. ER Model represents an entity with an independent existence.
Eg:

2. Attributes: The properties that characterize an entity set are called its attributes. An
attribute is referred to by the terms data items, data element, data field item.
Ex: attributes for bus entity and ticket entity.

2.1. Types of Attributes :

2.1.1. Simple and Composite attributes: Simple attribute cannot be divided into simpler
components.
Ex: Busno in Bus entity.
Composite attribute can be split into components.
Ex: DataOfBirth.

2.1.2. Single valued and multivalued attributes: Single valued attribute can take on
only a single value for each entity instance.
Ex: Age of passenger in Passenger entity.
Multivalued attribute can take up many values.
Ex: Phno in Passenger entity.

2.1.3. Derived attributes:In some cases, two or more attribute values are related. With
the help of one attribute we get the value of another attribute. Age and DOB
attributes. With the DOB we get the age of the person to the current date.

1
2.2. Types of Keys :

2.2.1. Candidate key:

It can be defined as minimal super key or irreducible super key. In other words an
attribute or combination of attributes that identifies the record uniquely but none of its proper
subsets can identify the record uniquely.

Busno, serviceno--------------->candidate key

2.2.2. Primary key:

A candidate key that is used by the database designer for unique identification of each
row in a table is known as primary key. A primary key consists of one or more attributes of the
table.

2.2.3. Foreign key:

An attribute (or) combination of attributes in a table whose value matches a primary
key in another table.
Ex: Busno is a primary key in Bus entity
Busno is a foreign key in Ticket entity.

2.2.4. Partial key:

A weak entity type normally has a partial key which is the set of attributes that can
uniquely identify weak entity that are related to the same owner entity.

The entities in the “Roadway travels” are:-

1) Bus 2) Ticket 3) Passenger 4) Branch

Entities and their attributes :

I. Bus (Busno, Name, Type)

II. Ticket (Tktno, jour_date, source, destination, busno, amount, dept_time, reach_time)
III. Passenger (Pnrno, name, age, Gender, Tktno, ppno)
IV. Branch (Name, location)

Datatypes :
1. Bus entity:
Busno : Varchar(8): Primary key
Name : Varchar(15)
Type: Varchar (10)

2. Ticket entity:
Tktno: Numeric(8): Primary key
Jour_date : Date
Src: Varchar(10)
dest: Varchar(10)
Dept_time: Time
Busno: Varchar(8): Foreign key
Amt: Varchar(4)
Reach_time: Time

2
3. Passenger entity:
Pnr_no: Numeric(9): Primary key
Tktno: Numeric(8): Foreign key
Name: Varchar(15)
Age: Int(4)
Gender: Char(10): Male/Female
Ppno:Varchar(15)

4. Reservation entity:
Pnr_no: Numeric(9): Foreign key
Jour_date : Date
Noofseats: Int(8)
Address: Varchar(50)
Contactno: numeric(9)
Status: Char (3): Yes/No

5. Cancellation entity:
Pnr_no: Numeric(9): Foreign key
Jour_date : Date
Noofseats: Int(8)
Address: Varchar(50)
Contactno: numeric(9)
Status: Char (3): Yes/No

All these entities are used for extract data when necessary while writing the queries in
the coming weeks. There are some extra entities which are not used for writing queries.

6. Branch entity:
Name: Varchar(8)
Location: Varchar(20)

3
Week: 2
Concept design with E – R model
Relate the entities appropriately. Apply cardinalities for each relationship. Identify
strong entities and weak entities (if any). Indicate the type of relationship (total/partial). Try
to incorporate generalization, aggregation, specialization etc wherever required.

ER-Model:
Describes data as entities, relationships and attributes .The ER-Model is important
preliminary for its role in database design. ER Model is usually shown pictorially using entity
relationship diagrams.

Relationship: - it is defined as an association among two or more entities.

Entity diagram for BUS

Bus no

Name
Bus

Type

4
Entity diagram for Ticket

Jour_date
Tktno
Src

Busno
Ticket
Dest

Amt
Dept_tim
Reach_tim e
e

Entity diagram for Passenger

Pnrno

Tktno
Passenger Name

Gender
Age
Ppno

Entity diagram for Reservation

Pnrno

Status
Reservation Jour_date

contactno
Noofseats
Addres
s

5
Entity diagram for Cancellation

Pnrno

Status
Cancellation Jour_date

contactno
Noofseats
Addres
s

Entity diagram for Branch

Name
Location

Branch

The cardinality ratio: - specifies the number of entities to which another entity can be
associated via a relationship set.
For a binary relationship set R between entity sets A & B, the mapping cardinality must
be one of the following:

1. One-to-One: An entity in A is associated with at most one entity in B and vice versa.

Ex: “Issued to” relation between ticket and passenger entities.

tktno
Journe
tktno name
y Date
Pnrno
Src age
Ticket Passenger
Issued
to
Dest
Gen
ppno der
Reach_tim
Dept_tim e
e

2. One-to-many: An entity in A is associated with any no. of entities in B. An entity in B is

at most associated with at most one entity in A.

6
Ex: “contains” relation between bus and passenger entities.

name
Busno Pnrno

Age
Name Bus Contain Passenger
s

Ppno Gender
Type

tktno

3. Many-to-One: An entity in A is associated with at most one entity in B. However, an

entity in B can be associated with any no. of entities in A.

Ex: “Travels in” relation between passenger and bus entities.

pnrno tktno Busno

name

Name Passenger Travels Passenger

in

Age Ppno
Type
Gender

4. Many-to-many: An entity in A is associated with an no. of entities in B and an entity in

B can be associated with any no. of entities in A.

7
Types of entities :-
Weak and strong entity: - an entity set may not have sufficient attributes to form a primary
key. Such an entity set is termed a weak entity set. An entity set that has primary key is termed
a strong entity set.

Entity Relationship diagram consisting of Bus, ticket, Passenger and Branch entites :

Name
Location
Bus no
Branch
Deals
with Name
Bus

Type

Consist
s of

tktno
Journe
name
y Date
Pnrno
Src age
Ticket Passenger
Issued
to
Dest
Gen
tktno der
Reach_tim ppno
Dept_tim e
e

8
1. Generalization: It consists of identifying some common characteristics of a collection
of entity set and creating new entity set that contains entities possessing these common
characteristics.

It is defined by using ‘ISA’ (Is a) relationship.

Ex:

2. Aggregation: It allows us to indicate that a relationship set participates in another

relationship set.
Ex:

9
3. Specialization: It is the process of identifying subsets of an entity set (the super set)
that share some distinguishing characteristics. This entity type is called the super class
of the specialization.
Ex:

10
Week-3
Relational model
Represent all entities (strong, week) in tabular fashion. Represent relationships in a
tabular fashion. There are different ways of representing as tables based on the cardinality.
Represent attributes as columns in the tables or as tables based on the requirement. Different
types of attributes (composite, multivalued and derived).

Schema : Databases change over time as information is inserted and deleted. The collection of
information stored in a database at a particular moment is called an instance of the database.

The overall design of the database is called the database schema.

Entity sets to tables:

Relational shema for Bus relation:

Bus(Busno:varchar(8), name:varchar(15), type:varchar(10))

Relational shema for Ticket relation:

Ticket(Tktno:numeric, jour_date:date, src:varchar(10), dest:varchar(10), dept_time:time,
reach_time:time, amt:varchar(4),busno:varchar(8))

Relational shema for Passenger relation:

Passenger(Pnrno:numeric(9),name:varchar(15),age:int(4),Gender:char(3),tktno:numeric(8),p
pno:varchar(15))

Relationship sets to tables:

Relational shema for reservation relation:

Reservation(pnrno:numeric(9),jour_date:date,noofseats:int(8),address:varchar(50),contactno
:numeric(10),status:char(3))

Relational shema for Cancellation relation:

Cancellations((pnrno:numeric(9),jour_date:date,noofseats:int(8),address:varchar(50),contact
no:numeric(10),status:char(3))

SAMPLE DATA IN TABLES:

Bus:
Busno name type
100 Xyz a/c
101 Pop Non a/c
102 Xxx a/c

Ticket:
Tktno Jour_date Src Dest amt busno Dept_time Reach_time
10001 20-07-10 Hyd Delhi 800 100 06:00 22:00
10002 21-07-10 Hyd Chennai 700 101 08:00 23:00
10003 05-08-10 Delhi Hyd 800 102 06:00 22:00

11
Passenger:
Pnrno name tktno age Gender ppno
1001 Alekhya 10001 25 F ff11112
1002 Krupani 10002 27 F ff22332
1003 Prathima 10003 28 F F234444
1004 Prem 10004 30 M Ff202020

Reservation:
Pnrno Jour_date Noofseats Address Contactno Status
1001 20-07-10 4 Hyd 9492500000 Yes
1002 21-07-10 5 Sec 9492511111 Yes
1003 05-08-10 10 hyd 9949022222 No

Cancels:
Pnrno Joudate Noofseats Address Contact_no Status
1001 20-07-10 4 Hyd 9492500000 Yes
1002 21-07-10 5 Sec 9492511111 Yes

12
Week: 4
Normalization:
Database normalization is a technique for designing relational database tables to
minimize duplication of information and, in doing so , to safeguard the database against certain
types of logical or structural problems namely data anomalies.

The normalization forms are:

1. First Normal Form: 1NF requires that the values in each column of a table are atomic.
By atomic we mean that there are no sets of values within a column.
2. Second Normal Form: where the 1NF deals with atomicity of data, the 2NF deals with
relationships between composite key columns and non-key columns. To achieve 2NF
the tables should be in 1NF. The 2NF any non-key columns must depend on the entire
primary key. In case of a composite primary key, this means that non-key column can’t
depend on only part of the composite key.
3. Third Normal Form: Any transitive dependencies have been removed.
4. Boyce/Codd normal Form: Any remaining anomalies that result from functional
dependencies have been removed.
5. Fourth Normal Form: Any multivalued dependencies have been removed.
6. Fifth Normal Form: Any remaining anomalies have been removed.

Applying Normalization to our Entities

Consider Passenger Entity
A Passenger may consist of two phone numbers, but atomic values should be there. so,
we normalize the relation as follows:

Passenger:

Pnrno pname age Gender ticketno address phno

2001 Alekhya 25 F 1111 H.no:101 9999900000
9999911111
2002 Krupani 26 F 2222 H.no:102 9999912345
2003 pratima 28 F 3333 H.no:103 9000000000

Pnrno pname age Gender ticketno address phno

2001 Alekhya 25 F 1111 H.no:101 9999900000
2001 Alekhya 25 F 1111 H.no:101 9999911111
2002 Krupani 26 F 2222 H.no:102 9999912345
2003 pratima 28 F 3333 H.no:103 9000000000

The above relation is now in 1NF and the relation is 2NF as there are no partial functional
dependencies and the relation is also in 3NF as there are no transitive dependencies.

Normalization of Bus entity:

Bus:
Busno serviceno source destination bustype Noofseats
1001 3300 Hyd Delhi A/c 20
1002 4400 Hyd Chennai A/c 28
1003 5500 Hyd Bglore Non a/c 30

In this relation the values in each column are atomic so it is already in 1NF.
In the Bus entity Busno+serviceno is the primary key.

13
There exists following partial dependencies.
Busno ----> Bustype,Noofseats
Serviceno---->Source,Dest

So the relation will be in 2NF as follows.

Busno serviceno
1001 3300
1002 4400
1003 5500

Busno bustype Noofseats

1001 A/c 20
1002 A/c 28
1003 Non a/c 30

serviceno source destination

3300 Hyd Delhi
4400 Hyd Chennai
5500 Hyd Bglore

The above relation is 2NF. And all columns directly depend on primary key. So there is no
transitive dependency and the relation is 3NF.

Normalization of Ticket entity:

Ticketno Joudate Source Destination Amount Catcard

1111 2010-10-08 Hyd Delhi 1200 No
2222 2010-10-08 Hyd Chennai 1000 Yes
3333 2010-08-08 Hyd Bglore 800 Yes

In this relation the values in each column are atomic so it is already in 1NF.
In the above relation there are no partial functional dependencies so the relation is in 2NF.
The ticket entity might face the following transitive dependency
Ticketno-------> catcard
Catcard--------->amount
So the relation is in 3NF.
Ticketno Joudate Source Destination Catcard
1111 2010-10-08 Hyd Delhi No
2222 2010-10-08 Hyd Chennai Yes
3333 2010-08-08 Hyd Bglore Yes

Put the catcard and amount attributes in a separate table. Then the relation should be in 3NF.

Catcard Amount
No 1200
Yes 1000
Yes 800
The above relation is 3NF as we have eliminated the transitive dependencies.

Finally all the tables are normalized and free from data redundancy, partial functional
dependencies and transitive dependencies.

14
Week 5:
Installation of Mysql and Practicing DDL commands: Installation of Mysql. In this week you
will learn creating databases, how to create tables, altering the tables, dropping tables and
databases if not required. You will also try truncate, rename commands etc.

Step: 1 download mysql essential from the website www.mysql.com/downloads and save the
.exe file.

Steps: 2&3double click on the mysql.exe file to start installation.

Steps: 4&5

Steps: 6&7

15
Steps: 8&9

Steps: 10&11

Step: 12&13

16
Steps: 14&15

Steps: 16&17

Steps: 18&19

17
1. Creation of databases:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.09 sec)

mysql> create database dblab

-> ;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| dblab |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use dblab;

Database changed

2. Creation of tables:
mysql> create table groupamem(rollno numeric(10),name varchar(15),phone numeric (10),
branch varchar(10));
Query OK, 0 rows affected (0.42 sec)

mysql> desc groupamem;

+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| rollno | decimal(10,0) | YES | | NULL | |
| name | varchar(15) | YES | | NULL | |
| phone | decimal(10,0) | YES | | NULL | |
| branch | varchar(10) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

3. Altering the table:

mysql> alter table groupamem add gender char(3);
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc groupamem;

18
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| rollno | decimal(10,0) | YES | | NULL | |
| name | varchar(15) | YES | | NULL | |
| phone | decimal(10,0) | YES | | NULL | |
| branch | varchar(10) | YES | | NULL | |
| gender | char(3) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

4. Dropping the table:

mysql> create table dd(name varchar(10));
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+------------------+
| Tables_in_groupa |
+------------------+
| dd |
| groupamem |
+------------------+
2 rows in set (0.00 sec)

mysql> drop table dd;

Query OK, 0 rows affected (0.06 sec)

5. Dropping the database:

mysql> create database dbl;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| dbl |
| dblab |
| mysql |
| test |
+--------------------+
6 rows in set (0.01 sec)

mysql> drop database dbl;

Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dblab |
| mysql |
| test |
+--------------------+

19
5 rows in set (0.00 sec)

6. Rename the tables:

mysql> rename table groupamem to ga;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;

+------------------+
| Tables_in_groupa |
+------------------+
| ga |
+------------------+
1 row in set (0.00 sec)

7. Truncate the table:

mysql> insert into ga values(1111,'ram',9885321456,'mbbs','m');
Query OK, 1 row affected (0.06 sec)

mysql> select * from ga;

+--------+------+------------+--------+--------+
| rollno | name | phone | branch | gender |
+--------+------+------------+--------+--------+
| 1111 | ram | 9885321456 | mbbs | m |
+--------+------+------------+--------+--------+
1 row in set (0.01 sec)

mysql> truncate table ga;

Query OK, 1 row affected (0.09 sec)
mysql> select * from ga;
Empty set (0.00 sec)

Creation of tables for Roadway Travels:

1. Bus
mysql> create table bus(busno varchar(8),primary key,name varchar(15),type varchar(10));
Query OK, 0 rows affected (0.17 sec)

2. Ticket
mysql> create table ticket(tkt_no numeric(8) primary key, jour_date date, src varchar(10),dest
varchar(10), busno varchar(8),amt varchar(4),dept_time time, reach_time time);
Query OK, 0 rows affected (0.08 sec)

mysql> alter table ticket add constraint tkt_fk foreign key(busno) references bus(busno);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

3. Passenger
mysql> create table passenger(pnr_no numeric(9),primary key,tktno numeric(8),name varc
har(15),age int(4),Gender char(10),ppno varchar(15));
Query OK, 0 rows affected (0.06 sec)

mysql> alter table passenger add constraint pas_fk foreign key(tktno) references ticket(tktno);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

20
4. Reservation
mysql> create table reservation(pnr_no numeric(9),noofseats int(8),jour_date datetime,
address varchar (50), phno numeric(10),status char(3));
Query OK, 0 rows affected (0.16 sec)

mysql> alter table reservation add constraint res_fk foreign key(pnr_no) references passenger
(pnr_no);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

5. Cancel
mysql> create table cancel(pnr_no numeric(9),noofseats int(4),address varchar(20), phno
numeric(10),status char(3));
Query OK, 0 rows affected (0.06 sec)

mysql> alter table cancel add constraint cancel_fk foreign key(pnr_no) references passenger
(pnr_no);
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

6. Branch
mysql>create table branch(name varchar(8),location varchar(20));
Query OK, 0 rows affected (0.06 sec)

21
Week 6:
Practicing DML commands:DML commands are used to for managing data within the schema
objects.

Use of insert command:

Inserting values into Bus table:

mysql> insert into bus values('1001',’Metro’,'ac');
Query OK, 1 row affected (0.03 sec)
mysql> insert into bus values('1002',’Metro Journam’,'ac');
Query OK, 1 row affected (0.03 sec)
mysql> insert into bus values('1003',’Express’,'nonac');
Query OK, 1 row affected (0.03 sec)
mysql> insert into bus values('1004',’Ordinary’,,'nonac');
Query OK, 1 row affected (0.03 sec)
mysql> insert into bus values('1005',’Ordinary’,'nonac');
Query OK, 1 row affected (0.03 sec)

Inserting values into Ticket table:

mysql> insert into ticket values(1111,'2010-10-08','hyd','delhi',1200,1001,'09:00:00','2
1:00:00');
Query OK, 1 row affected (0.03 sec)
mysql> insert into ticket values(2222,'2010-10-08','hyd', 'delhi',1200,1001,09:00:00','2
1:00:00');
Query OK, 1 row affected (0.03 sec)
mysql> insert into ticket values(3333,'2010-11-08','hyd','bglore',800,1002,'09:00:00','1
5:00:00');
Query OK, 1 row affected (0.03 sec)
mysql> insert into ticket values(4444,'2010-11-08','hyd','bglore ',800,1002,'09:00:00','1
5:00:00');
Query OK, 1 row affected (0.03 sec)

Inserting values into Passenger table:

mysql> insert into passenger values(1001,1111,'alekhya',25,'f','pp1111');
Query OK, 1 row affected (0.05 sec)
mysql> insert into passenger values(1002,2222,'Krupani',26,'f','pp2222');
Query OK, 1 row affected (0.03 sec)
mysql> insert into passenger values(1003,3333,'Pratima',28,'f','pp3333');
Query OK, 1 row affected (0.05 sec)
mysql> insert into passenger values(1004,4444,'prem',28,'m','pp4444');
Query OK, 1 row affected (0.01 sec)

Inserting values into reservation table:

mysql> insert into reservation values(2001,4,’2010-10-08’,'hno:101,msrd',9999911111,'yes');
Query OK, 1 row affected (0.05 sec)
mysql> insert into reservation values(2002,6, ’2010-10-08’,'hno:102,msrd',9999900000,'yes');
Query OK, 1 row affected (0.03 sec)
mysql> insert into reservation values(2003,5, ’2010-08-08’,'hno:101,chpy',9000011111,'yes');
Query OK, 1 row affected (0.01 sec)
mysql> insert into reservation values(2004,8, ’2010-08-08’,'hno: 102,chpy',9000000000,'yes');
Query OK, 1 row affected (0.03 sec)

22
Inserting values into cancellation table:
mysql> insert into cancel values(2003,5, ’2010-08-08’,'hno:101,chpy',9000011111,'yes');
Query OK, 1 row affected (0.05 sec)
mysql> insert into cancel values(2004,8, ’2010-08-08’,'hno:102,chpy',9000000000,'yes');
Query OK, 1 row affected (0.03 sec)
mysql> insert into cancel values(2002,2, ’2010-10-08’,'hno:102,msrd',9999900000,'yes');
Query OK, 1 row affected (0.05 sec)

Inserting values into Branch table:

mysql>insert into branch values(‘kesineni’,’sec’);
Query OK, 1 row affected (0.05 sec)
mysql>insert into branch values(‘punnami’,’hyd’);
Query OK, 1 row affected (0.05 sec)
mysql>insert into branch values(‘punnami’,’sec’);
Query OK, 1 row affected (0.03 sec)

Use of select command:

mysql> select *from bus;
+--------+---------+
| busno | bustype | Type|
+--------+---------+
| 1001 | Metro |ac|
| 1002 | Metro Journam |ac|
| 1003 | express |non ac|
| 1004| Ordinary |non ac|
| 1005 | Ordinary |non ac|
+--------+---------+
5 rows in set (0.00 sec)

mysql> select *from ticket;

+--------+------------+--------+------+----------+----------+-----------+--------+
| tkt_no | jour_date | src | dest | deptime | reachtime | busno |amt |
+--------+------------+--------+------+----------+----------+-----------+--------+
| 1111 | 2010-10-08 | hyd | delhi | 09:00:00 | 21:00:00 | 1001 |1200|
| 2222 | 2010-10-08| hyd | delhi | 09:00:00 | 21:00:00 | 1001 |1200|
| 3333 | 2010-11-08 | hyd | bglore | 09:00:00 | 15:00:00 | 1002 |800|
| 4444 | 2010-11-08 | hyd | bglore | 09:00:00 | 15:00:00 | 1002 |800|
+--------+------------+--------+------+----------+----------+-----------+
4 rows in set (0.00 sec)set (0.00 sec)

mysql> select *from passenger;

+-------+-----------+---------+------+------+--------+
| pnrno | ticnumber | pname | age | Gender | ppno |
+-------+-----------+---------+------+------+--------+
| 1001 | 1111 | Alekhya | 25 | f | pp111 |
| 1002 | 2222 | krupani | 26 | f | pp2222 |
| 1003 | 3333 | pratima | 28| f | pp3333 |
| 1004 | 4444 | prem | 28 | m | pp444 4 |
+-------+-----------+---------+------+------+--------+
4 rows in set (0.03 sec)

23
mysql> select *from reservation;
+-----------+-----------+--------------------+------------+--------+
| pnrnumber | noofseats | address | phno | status |jour_date|
+-----------+-----------+--------------------+------------+--------+----------+
| 2001 | 4 | hno:101,msrd | 9999911111 | yes |2010-10-08|
| 2001 | 6 | hno:102,msrd | 9999900000| yes |2010-10-08|
| 2002 | 5 | hno:101,chpy | 9000011111 | yes |2010-10-08|
| 2003 | 8 | hno:102,chpy | 9000000000 | no |2010-08-08|

+-----------+-----------+--------------------+------------+--------+----------+
4 rows in set (0.02 sec)

mysql> select *from cancel;

+-----------+-----------+--------------------+------------+--------+-----------+
| pnr_no | noofseats | address | phno | status |jour_date
+-----------+-----------+--------------------+------------+--------+-----------+
| 2003 | 5 | hno:101,chpy | 9000011111 | yes |2010-08-08|
| 2004 | 8 | hno:102,chpy | 9000000000| no |2010-08-08|
| 2002 | 2 | hno:102,msrd | | 9999900000 | yes |2010-10-08|
+-----------+-----------+--------------------+------------+--------+-----------+
3 rows in set (0.00 sec)

Use of update command:

mysql> update passenger set pnr_no=’2001’ where tktno=1111;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 changed: 1 warnings: 0
mysql> update passenger set pnr_no=’2002’ where tktno=2222;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 changed: 1 warnings: 0
mysql> update passenger set pnr_no=’2003’ where tktno=3333;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 changed: 1 warnings: 0
mysql> update passenger set pnr_no=’2004’ where tktno=4444;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 changed: 1 warnings: 0

mysql> select *from passenger;

+-------+-----------+---------+------+------+--------+
| pnrno | ticnumber | pname | age | Gender | ppno |
+-------+-----------+---------+------+------+--------+
| 2001 | 1111 | Alekhya | 25 | f | pp111 |
| 2002 | 2222 | krupani | 26 | f | pp2222 |
| 2003 | 3333 | pratima | 28| f | pp3333 |
| 2004 | 4444 | prem | 28 | m | pp444 4 |
+-------+-----------+---------+------+------+--------+
4 rows in set (0.03

24
Use of DELETE command:
mysql>delete from branch where location =’sec’;
Query OK, 1 row affected (0.03 sec)

mysql> select *from branch;

+-----------+-----------+
| name |location |
+-----------+-----------+
| kesineni | Hyd|
| punnami| Hyd|
+-----------+-----------+
2 rows in set (0.00 sec)

25
Week: 7
Querying: In this week you are going to practice the queries (along with sub queries)
using ANY, ALL, IN, EXISTS, NOT EXISTS, UNION, INTERSECT, Constraints etc.

Practice the following queries:

1. Display unique PNR_no of all passengers.

mysql> select distinct pnr_no from passenger;

+--------+
| pnr_no |
+--------+
| 2001 |
| 2002 |
| 2003 |
| 2004 |
+--------+
4 rows in set (0.00 sec)

2. Display all the names of male passengers.

mysql> select name from passenger where Gender='m';

+------+
| name |
+------+
| prem |
+------+
1 row in set (0.01 sec)

3. Display ticket numbers and names of all the passengers.

mysql> select tktno,name from passenger;

+-------+----------+
| tktno | name |
+-------+----------+
| 1111 | alekhya |
| 2222 | krupani |
| 3333 | prathima |
| 4444 | prem |
+-------+----------+
4 rows in set (0.00 sec)

4. Display the source and destination having journey time more than 10 hours.

mysql> select src,dest from ticket where hour(timediff(reach_time,dept_time))>10

;
+------+-------+
| src | dest |
+------+-------+
| hyd | delhi |
| hyd | delhi |
+------+-------+
2 rows in set (0.00 sec)

26
5. Find the ticket numbers of passengers whose name starts with ‘A’ and ends with ‘H’.

mysql> select tktno from ticket where tktno=any(select tktno from passenger wher
e name like 'a%h');
Empty set (0.03 sec)

mysql> update passenger set name="amith" where name="prem";

Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select tktno from ticket where tktno=any(select tktno from passenger wher
e name like 'a%h');
+-------+
| tktno |
+-------+
| 4444 |
+-------+
1 row in set (0.00 sec)

6. Find the name of passengers whose age is between 30 and 45.

mysql> select name from passenger where age between 30 and 45;
+-------+
| name |
+-------+
| amith |
+-------+
1 row in set (0.00 sec)

7. Display all the passengers names beginning with ‘A’.

mysql> select all name from passenger where name like 'a%';
+---------+
| name |
+---------+
| alekhya |
| amith |
+---------+
2 rows in set (0.00 sec)

8. Display the sorted list of passengers names.

mysql> select name from passenger order by name;

+----------+
| name |
+----------+
| alekhya |
| amith |
| krupani |
| prathima |
+----------+
4 rows in set (0.00 sec)

27
9. Display the Bus numbers that travel on Sunday and Wednesday.

mysql> select busno from bus where busno in(select busno from ticket where dayof
week(jour_date)=1 or dayofweek(jour_date)=4);
Empty set (0.05 sec)

10. Display the details of passengers who are traveling either in AC or NON_AC.

mysql> select pnr_no,name,age,Gender from passenger where tktno in(select tktno fro
m ticket where busno in(select busno from bus where type='a/c' or type='non a/c'
));
+---------+-----------+--------+---------+
| pnr_no | name | age | Gender |
+--------+----------+------+------+
| 2001 | alekhya | 25 | f |
| 2002 | krupani | 26 | f |
| 2003 | prathima | 28 | f |
| 2004 | amith | 35 | m |
+--------+----------+------+------+
4 rows in set (0.00 sec)

28
Week: 8 & 9
You are going to practice the queries using Aggregate functions (COUNT, SUM, AVG, MAX
and MIN), GROUP BY, HAVING AND Creation of Views.

1. Write a query to display the information present in the Passenger and Cancellation
tables.

mysql> select pnr_no from passenger union select pnr_no from cancel;
+--------+
| pnr_no |
+--------+
| 2001 |
| 2002 |
| 2003 |
| 2004 |
+--------+
4 rows in set (0.00 sec)

2. Write a query to display the busnumber with source and destination available in
Roadway Travels.
mysql> select busno,src,dest from bus b ,ticket t where b.busno=t.busno group by busno;
+--------+--------+------+
| busno | source | dest |
+--------+--------+------+
| 1001 | hyd | delhi |
| 1001 | hyd | delhi |
| 1002 | hyd | bglore |
| 1002 | hyd | bglore |
| +--------+--------+-----+
4 rows in set (0.00 sec)

3. Display the number of days in a week on which AP444 bus is available.

mysql> select count(jour_date) from ticket where busno in(select busno from bus
where busno='1111') and jour_date between '2010-10-04' and '2010-10-10';
+------------------+
| count(jour_date) |
+------------------+
| 0|
+------------------+
1 row in set (0.00 sec)

4. Find the ticket numbers booked for each PNR_no using Group By clause.

mysql> select noofseats,pnr_no from reservation where status='yes' group by pnr_no;

+-----------+--------+
| noofseats | pnr_no |
+-----------+--------+
| 4 | 2001 |
| 6 | 2002 |
| 5 | 2003 |
+-----------+--------+
3 rows in set (0.00 sec)

29
5. Find the distinct PNR numbers that are present.

mysql> select distinct pnr_no from reservation;

+--------+
| pnr_no |
+--------+
| 2001 |
| 2002 |
| 2003 |
| 2004 |
+--------+
4 rows in set (0.00 sec)

6. Find the number of tickets booked for each bus with bustype where the number of
seats is greater than 1.

mysql> select busno,type,noofseats from bus b,reservation r,ticket t,passenger p

where b.busno=t.busno and t.tktno=p.tktno and p.pnr_no=r.pnr_no and status='yes' group by
tktno having count(*)>=1;
+--------+---------+--------------+
| busno | type | booked_seats |
+--------+---------+--------------+
| 1001 | a/c | 4|
| 1002 | a/c | 6|
| 1003 | non a/c | 5|
| 1004 | non a/c | 8|
+--------+---------+--------------+
4 rows in set (0.00 sec)

7. Find the total number of cancelled seats.

mysql> select sum(noofseats) from cancel where status='yes';
+----------------+
| sum(noofseats) |
+----------------+
| 7|
+----------------+
1 row in set (0.00 sec)

8. Write a query to count the number of tickets for the buses which traveled after the
date ‘2010-08-06’.
mysql> select busno,type,noofseats from bus b,reservation r,ticket t,passenger p
where b.busno=t.busno and t.tktno=p.tktno and p.pnr_no=r.pnr_no and status='yes
' and jour_date>'2010-02-02' group by tktno having count(*)>=1;

+--------+---------+--------------+
| busno | bustype | booked_seats |
+--------+---------+--------------+
| ap444 | nonac | 6|
| ap891 | nonac | 8|
| ap8830 | metro | 5|
+--------+---------+--------------+
3 rows in set (0.01 sec)

30
Creation of Views:
mysql> create view takes as
-> select tktno,name from ticket t,passenger p where t.tktno=p.tktno;
Query OK, 0 rows affected (0.44 sec)

mysql> select tktno from takes;

+--------+
| tktno |
+--------+
| 1111 |
| 2222 |
| 3333 |
| 4444 |
+--------+
4 rows in set (0.05 sec)

Dropping of Views:

mysql> drop view takes;

Query OK, 0 rows affected (0.00 sec)

31
Week 10: TRIGGERS
In this week you are going to work on the triggers. Creation of insert trigger, delete
trigger, update trigger. Practice triggers using above database.

A Trigger is a named database object which defines some action that the database
should take when some databases related event occurs.
Triggers are executed when you issues a data manipulation command like INSERT,
DELETE, UPDATE on a table for which the trigger has been created. They are automatically
executed and also transparent to the user. But for creating the trigger the user must have the
CREATE TRIGGER privilege.
In this section we will describe you about the syntax to create and drop the triggers
and describe you some examples of how to use them.

CREATE TRIGGER:

        CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW

trigger_statement

By using above statement we can create the new trigger. The trigger can associate only
with the table name and that must be refer to a permanent table. Trigger_time means trigger
action time. It can be BEFORE or AFTER. It is used to define that the trigger fires before or
after the statement that executed it. Trigger_event specifies the statement that executes the
trigger. The trigger_event can be any of the DML Statement: INSERT, UPDATE, DELETE.
We can not have the two trigger for a given table, which have the same trigger action
time and event. For Instance : we cannot have two BEFORE INSERT triggers for same table.
But we can have a BEFORE INSERT and BEFORE UPDATE trigger for a same table.
Trigger_statement have the statement that executes when the trigger fires but if you
want to execute multiple statement the you have to use the BEGIN…END compound
statement. We can refer the columns of the table that associated with trigger by using the
OLD and NEW .keyword. OLD.column_name is used to refer the column of an existing row
before it is deleted or updated and NEW.column_name is used to refer the column of a new
row that is inserted or after updated existing row.
In INSERT trigger we can use only NEW.column_name because there is no old row and
in a DELETE trigger we can use only OLD.column_name because there is no new row. But in
UPDATE trigger we can use both, OLD.column_name is used to refer the columns of a row
before it is updated and NEW.Column_name is used to refer the column of the row after it is
updated.

1. Update Trigger:
mysql> create trigger tri1 before update on reservation
-> for each row
-> begin
-> if new.noofseats>20 then
-> set new.noofseats=old.noofseats;
-> else
-> set new.noofseats=new.noofseats;
-> end if;
-> end//
Query OK, 0 rows affected (0.11 sec)

mysql> update reservation set noofseats=10 where pnr_no=2001;

Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0

32
mysql> select *from reservation;
-> //
+--------+-----------+---------------------+--------------+------------+--------+
| pnr_no | noofseats | jour_date | address | phno | status|
+--------+-----------+---------------------+--------------+------------+--------+
| 2001 | 10 | 2010-10-08 00:00:00 | hno:101,msrd | 9999911111 | yes|
| 2002 | 6 | 2010-10-08 00:00:00 | hno:102,msrd | 9999900000 | yes|
| 2003 | 5 | 2010-08-08 00:00:00 | hno:101,chpy | 9000011111 | yes|
| 2004 | 8 | 2010-08-08 00:00:00 | hno:102,chpy | 9000000000 | no|
+--------+-----------+---------------------+--------------+------------+--------+
4 rows in set (0.00 sec)

mysql> update reservation set noofseats=25 where pnr_no=2003;

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> select *from reservation;

+--------+-----------+---------------------+--------------+------------+--------+
| pnr_no | noofseats | jour_date | address | phno | status|
+--------+-----------+---------------------+--------------+------------+--------+
| 2001 | 10 | 2010-10-08 00:00:00 | hno:101,msrd | 9999911111 | yes|
| 2002 | 6 | 2010-10-08 00:00:00 | hno:102,msrd | 9999900000 | yes|
| 2003 | 5 | 2010-08-08 00:00:00 | hno:101,chpy | 9000011111 | yes|
| 2004 | 8 | 2010-08-08 00:00:00 | hno:102,chpy | 9000000000 | no|
+--------+-----------+---------------------+--------------+------------+--------+
4 rows in set (0.00 sec)

2. Insert Trigger:
mysql> create trigger tri2 before insert on passenger
-> for each row
-> begin
-> if new.age>29 then
-> set new.ppno='pp4567';
-> else
-> set new.ppno=" ";
-> end if;
-> end//
Query OK, 0 rows affected (0.11 sec)

mysql> select *from passenger;

+--------+-------+----------+------+------+--------+
| pnr_no | tktno | name | age | Gender | ppno |
+--------+-------+----------+------+------+--------+
| 2001 | 1111 | alekhya | 25 | f | pp1111 |
| 2002 | 2222 | krupani | 26 | f | pp2222 |
| 2003 | 3333 | prathima | 28 | f | pp3333 |
| 2004 | 4444 | amith | 35 | m | pp4444 |
+--------+-------+----------+------+------+--------+
4 rows in set (0.00 sec)

mysql> insert into passenger values(2005,5555,'prem','27','m','99')//

Query OK, 1 row affected (0.07 sec)

33
mysql> select *from passenger;
+--------+-------+----------+------+------+--------+
| pnr_no | tktno | name | age | Gender | ppno |
+--------+-------+----------+------+------+--------+
| 2001 | 1111 | alekhya | 25 | f | pp1111 |
| 2002 | 2222 | krupani | 26 | f | pp2222 |
| 2003 | 3333 | prathima | 28 | f | pp3333 |
| 2004 | 4444 | amith | 35 | m | pp4444 |
| 2005 | 5555 | prem | 27 | m | |
+--------+-------+----------+------+------+--------+
5 rows in set (0.00 sec)

3. Delete Trigger:
mysql> create trigger tri3 before delete on cancel
-> for each row
-> begin
-> insert into reservation values(old.pnr_no,old.noofseats,old.jour_date,old
.address,old.phno,old.status);
-> end//
Query OK, 0 rows affected (0.11 sec)

mysql> select *from reservation;

+--------+-----------+---------------------+--------------+------------+--------+
| pnr_no | noofseats | jour_date | address | phno | status|
+--------+-----------+---------------------+--------------+------------+--------+
| 2001 | 10 | 2010-10-08 00:00:00 | hno:101,msrd | 9999911111 | yes|
| 2002 | 6 | 2010-10-08 00:00:00 | hno:102,msrd | 9999900000 | yes|
| 2003 | 5 | 2010-08-08 00:00:00 | hno:101,chpy | 9000011111 | yes|
| 2004 | 8 | 2010-08-08 00:00:00 | hno:102,chpy | 9000000000 | no|
+--------+-----------+---------------------+--------------+------------+--------+
4 rows in set (0.00 sec)

mysql> delete from cancel where pnr_no=2004//

Query OK, 1 row affected (0.06 sec)
mysql> select *from reservation;
+--------+-----------+---------------------+--------------+------------+--------+
| pnr_no | noofseats | jour_date | address | phno | status|
+--------+-----------+---------------------+--------------+------------+--------+
| 2001 | 10 | 2010-10-08 00:00:00 | hno:101,msrd | 9999911111 | yes|
| 2002 | 6 | 2010-10-08 00:00:00 | hno:102,msrd | 9999900000 | yes|
| 2003 | 5 | 2010-08-08 00:00:00 | hno:101,chpy | 9000011111 | yes|
| 2004 | 8 | 2010-08-08 00:00:00 | hno:102,chpy | 9000000000 | no|
| 2004 | 8 | 2010-08-08 00:00:00 | hno:102,chpy | 9000000000 | no|
+--------+-----------+---------------------+--------------+------------+--------+
5 rows in set (0.00 sec)

mysql> select *from cancel//

+--------+-----------+---------------------+--------------+------------+--------+
| pnr_no | noofseats | jour_date | address | phno | status|
+--------+-----------+---------------------+--------------+------------+--------+
| 2003 | 5 | 2010-08-08 00:00:00 | hno:101,chpy | 9000011111 | yes|
| 2002 | 2 | 2010-10-08 00:00:00 | hno:102,msrd | 9999900000 | yes|
+--------+-----------+---------------------+--------------+------------+--------+ 2 rows in set (0.00 sec)

34
Week 11: Procedures
In this session you are going to learn Creation of stored procedures, execution of
procedure and modification of procedures. Practice the procedures using above
database.

A stored procedure is a procedure (like a subprogram in a regular computing language)

that is stored (in the database). Correctly speaking, MySQL supports "routines" and there are
two kinds of routines: stored procedures which you call, or functions whose return values you
use in other SQL statements the same way that you use pre-installed MySQL functions like pi().

mysql> create procedure p1(p_age int)

-> begin
-> select tktno,name,Gender from passenger where age>p_age;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> call p1(27)

+-------+----------+------+
| tktno | name | Gender |
+-------+----------+------+
| 3333 | prathima | f |
| 4444 | amith | m |
+-------+----------+------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> create procedure p2(pnr_id int)

-> begin
-> select jour_date,address,phno from reservation where pnr_no=pnr_id;
-> end
-> //
Query OK, 0 rows affected (0.02 sec)

mysql> call p2(2001)

-> //
+---------------------+--------------+------------+
| jour_date | address | phno |
+---------------------+--------------+------------+
| 2010-10-08 00:00:00 | hno:101,msrd | 9999911111 |
+---------------------+--------------+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

35
Week 12: Cursors
In this week you need to do the following: Declare the cursor that defines the result set.
Open the cursor to establish the result set. Fetch the data into local variables as needed
from the cursor, one row at a time. Close the cursor when done.

Cursors are used when the SQL Select statement is expected to return more than one
row. Cursors are supported inside procedures and functions.
Cursors must be declared and its definition contains the query. The cursor must be
defined in the DECLARE section of the program. A cursor must be opened before processing
and closed after processing.
Syntax to declare the cursor:
  DECLARE <cursor_name> CURSOR FOR <select_statement>
Multiple cursors can be declared in the procedures and functions but each cursor must
have a unique name. And in defining the cursor the select_statement cannot have INTO
clause.

Syntax to open the cursor:

        OPEN <cursor_name>
By this statement we can open the previously declared cursor.

Syntax to store data in the cursor:

        FETCH <cursor_name> INTO <var1>,<var2>…….
The above statement is used to fetch the next row if a row exists by using the defined open
cursor.

Syntax to close the cursor:

        CLOSE <cursor_name>
By this statement we can close the previously opened cursor. If it is not closed explicitly then
a cursor is closed at the end of compound statement in which that was declared.

mysql> create procedure cursor_ticket(tktid int)

-> begin
-> declare tkt_no int;
-> declare tkt_src varchar(20);
-> declare tkt_dest varchar(20);
-> declare c1 cursor for select tktno,src,dest from ticket where tktno=tktid;
-> open c1;
-> fetch c1 into tkt_no
-> ,tkt_src,tkt_dest;
-> select tkt_no,tkt_src,tkt_dest;
-> close c1;
-> end
-> //
Query OK, 0 rows affected (0.03 sec)

mysql> call cursor_ticket(4444);

+--------+---------+----------+
| tkt_no | tkt_src | tkt_dest |
+--------+---------+----------+
| 4444 | hyd | bglore |
+--------+---------+----------+
1 row in set (0.00 sec)

36
Query OK, 0 rows affected (0.02 sec)

mysql> create procedure cursor_reserve(p_id int)

-> begin
-> declare res_id int;
-> declare res_addr varchar(20);
-> declare c2 cursor for select pnr_no,address from reservation where pnr_no=p_id;
-> open c2;
-> fetch c2 into res_id,res_addr;
-> select res_id,res_addr from reservation where pnr_no=p_id;
-> close c2;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)

mysql> call cursor_reserve(2002);

-> //
+--------+--------------+
| res_id | res_addr |
+--------+--------------+
| 2002 | hno:102,msrd |
+--------+--------------+
1 row in set (0.10 sec)

Query OK, 0 rows affected (0.10 sec)

37