MOOKAMBIGAI COLLEGE OF ENGINEERING

Srinivasa Nagar, Kalamavur-622 502.

CS8481 – DATABASE MANAGEMENT SYSTEMS

LABORATORY

2020-2021
IV Semester

DEPARTMENT
OF
COMPUTER SCIENCE AND ENGINEERING
 MOOKAMBIGAI COLLEGE OF ENGINEERING
(Approved by AICTE & Affiliated to Anna University, Chennai)
(ISO 9001:2008 Certified Institution)
Srinivasa Nagar, Kalamavur-622502.Pudukkottai District, Tamil Nadu.

Name……………………………………………………………………………………….

Year………II……………….Semester………04…………………Branch…..CSE…...

University Register No:

CERTIFICATE

Certified that this is the Bonafide record of work done by the above student in the

CS8481-Database Management Systems Laboratory during the year 2021-2021.

______________________________

Signature of Staff-Incharge

Submitted for the University practical Examination held on…………………………..

EXAMINERS

Date: ………………. Internal: ……………………...

External: ……………………..
 CONTENTS

Page Initials
S.No Date Name of the Experiment No.

1. DDL,DML,TCL COMMANDS

2. DATABASE QUERYING

3. VIEWS, SEQUENCES, SYNONYMS

DATABASE PROGRAMMING: IMPLICIT

4.
AND EXPLICIT CURSORS
PROCEDURES AND FUNCTIONS
5.

TRIGGERS
6.

EXCEPTION HANDLING
7.

DATABASE DESIGN USING ER MODELING,

8. NORMALIZATION AND IMPLEMENTATION
FOR ANY APPLICATION

CONNECTING TO ORACLE DATABASE

9.
USING VISUAL BASIC
CASE STUDY-REAL LIFE DATABASE
10.
APPLICATION
EX:NO:1 DDL,DML,TCL COMMANDS

Aim:

To create table and Execute Data Definition Commands, Create, Alter, Rename, Truncate, Drop,
Data Manipulation Commands for Inserting, Deleting, Updating And Retrieving Tables and
Transaction Control Statements for Rollback, Commit and Savepoint.

SQL: create command

Create is a DDL SQL command used to create a table or a database in relational database
management system.

Creating a Database
Create command can also be used to create tables. Now when we create a table, we have to
specify the details of the columns of the tables too. We can specify the names and data types of
various columns in the create command itself.

CREATE TABLE <TABLE_NAME> (column_name1 datatype1, column_name2 datatype2,

column_name3 datatype3, column_name4 datatype4 );

SQL: ALTER command

Alter command is used for altering the table structure, such as,

• To add a column to existing table

• To rename any existing column
• To change datatype of any column or to modify its size.
• To drop a column from the table.

ALTER TABLE table_name ADD( column_name datatype);

ALTER TABLE table_name ADD( column_name1 datatype1, column-name2 datatype2, );

Rename a Column

ALTER TABLE table_name RENAME old_column_name TO new_column_name;

ALTER Command: Drop a Column

ALTER TABLE table_name DROP( column_name);

TRUNCATE command

TRUNCATE command removes all the records from a table. But this command will not destroy the
table's structure. When we use TRUNCATE command on a table its (auto-increment) primary key is
also initialized.
Syntax,
TRUNCATE TABLE table_name;

DROP command

DROP command completely removes a table from the database. This command will also destroy the
table structure and the data stored in it. Following is its syntax,

DROP TABLE table_name;

RENAME TABLE

RENAME command is used to set a new name for any existing table. Following is the syntax,

RENAME TABLE old_table_name to new_table_name;

DML COMMANDS

INSERT command

Insert command is used to insert data into a table.

Syntax:

1.INSERT INTO table_name VALUES(data1, data2, ...)

2.INSERT INTO student(id, name) values(value, value);

3.INSERT INTO Student VALUES(102,'Alex', null);

DELETE command

DELETE command is used to delete data from a table.

DELETE FROM table_name;

Delete a particular Record from a Table

DELETE FROM student WHERE s_id=103;

TCL COMMANDS

COMMIT command

COMMIT command is used to permanently save any transaction into the database.

COMMIT;

ROLLBACK command
This command restores the database to last commited state. It is also used with SAVEPOINT
command to jump to a savepoint in an ongoing transaction.

ROLLBACK TO savepoint_name;

SAVEPOINT command

SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that
point whenever required.

SAVEPOINT savepoint_name;

COMMANDS

CREATE

SQL> create table employee1(eno number(15),ename varchar(15),salary number(16),dob date);

Table created.

SQL> desc employee1;

Name Null? Type

--------------------------------------------------
ENO NUMBER(15)
ENAME VARCHAR2(15)
SALARY des
DATE

ALTER

SQL> alter table employee1 modify(ename varchar(20));

Table altered.

SQL> desc employee1;

Name Null? Type

--------------------------------------------------
ENO NUMBER(15)
ENAME VARCHAR2(20)
SALARY NUMBER(16)
DOB DATE

SQL> alter table employee1 add designation varchar(20);

Table altered.

SQL> alter table employee1 drop column dob;

Table altered.
SQL> alter table employee1 rename column designation to desgn;

Table altered.

SQL> desc employee1;

Name Null? Type

-----------------------------------------------
ENO NUMBER(15)
ENAME VARCHAR2(20)
SALARY NUMBER(16)
DESGN VARCHAR2(20)

RENAME

SQL> rename employee1 to emp;

Table renamed.

TRUNCATE

SQL> truncate table emp;

Table truncated.

SQL> desc emp;

Name Null? Type

--- -----------------------------------------------
ENO NUMBER(15)
ENAME VARCHAR2(20)
SALARY NUMBER(16)
DESGN VARCHAR2(20)

DROP

SQL> drop table emp;

Table dropped.

SQL> desc emp;

Object does not exist.

INSERT

SQL> create table employee (eno number(15),ename varchar(15),salary number(16),dob date);

Table created.
SQL> desc employee;

Name Null? Type

--------------------------------------------------
ENO NUMBER(15)
ENAME VARCHAR2(15)
SALARY NUMBER(16)
DOB DATE

SQL> insert into employee values (&eno,'&ename',&salary,'&dob');

Enter value for eno: 1
Enter value for ename: kavi
Enter value for salary: 35000
Enter value for dob: 14-jul-85

old 1: insert into employee values(&eno,'&ename',&salary,'&dob')

new 1: insert into employee values(1,'kavi',35000,'14-jul-85')

1 row created.

SQL> /

Enter value for eno: 2

Enter value for ename: keerthi
Enter value for salary: 32000
Enter value for dob: 18-oct-87

old 1: insert into employee values(&eno,'&ename',&salary,'&dob')

new 1: insert into employee values(2,'keerthi',32000,'18-oct-87')

1 row created.

SQL> /

Enter value for eno: 3

Enter value for ename: geetha
Enter value for salary: 28000
Enter value for dob: 4-jun-86

old 1: insert into employee values(&eno,'&ename',&salary,'&dob')

new 1: insert into employee values(3,'geetha',28000,'4-jun-86')

1 row created.

SQL> /
Enter value for eno: 4
Enter value for ename: ganesh
Enter value for salary: 35000
Enter value for dob: 9-jan-83
old 1: insert into employee values(&eno,'&ename',&salary,'&dob')
new 1: insert into employee values(4,'ganesh',35000,'9-jan-83')
1 row created.

SQL> /
Enter value for eno: 5
Enter value for ename: dinesh
Enter value for salary: 46000
Enter value for dob: 26-feb-84
old 1: insert into employee values(&eno,'&ename',&salary,'&dob')
new 1: insert into employee values(5,'dinesh',46000,'26-feb-84')
1 row created.

SQL> select * from employee;

ENO ENAME SALARY DOB
-------------------------------------------------------------
1 kavi 35000 14-JUL-85
2 keerthi 32000 18-OCT-87
3 geetha 28000 04-JUN-86
4 ganesh 35000 09-JAN-83
5 dinesh 46000 26-FEB-84
------------------------------------------------------------------

UPDATE

SQL> update employee set salary=40000 where eno=2;

1 row updated.

SQL> select * from employee;

ENO ENAME SALARY DOB

------------------------------------------------------------------
1 kavi 35000 14-JUL-85
2 keerthi 40000 18-OCT-87
3 geetha 28000 04-JUN-86
4 ganesh 35000 09-JAN-83
5 dinesh 46000 26-FEB-84
------------------------------------------------------------------

DELETION

SQL> delete from employee where eno=4;

1 row deleted.

SQL> select * from employee;

ENO ENAME SALARY DOB
------------------------------------------------------------------
1 kavi 35000 14-JUL-85
 2 keerthi 40000 18-OCT-87
3 geetha 28000 04-JUN-86
5 dinesh 46000 26-FEB-84
SELECT
SQL> select * from employee;

ENO ENAME SALARY DOB

------------------------------------------------------------------
1 kavi 35000 14-JUL-85
2 keerthi 32000 18-OCT-87
3 geetha 28000 04-JUN-86
4 ganesh 35000 09-JAN-83
5 dinesh 46000 26-FEB-84

SQL> select * from employee where eno=1;

ENO ENAME SALARY DOB

------------------------------------------------------------------
1 kavi 35000 14-JUL-85

SQL> select ename from employee;

ENAME
---------------
kavi
keerthi
geetha
ganesh
dinesh

SAVEPOINT

SQL> savepoint sa;

Savepoint created.

SQL> delete from employee where eno=4;

1 row deleted.

SQL> select * from employee;

ENO ENAME SALARY

-------------------------------------------------
1 kavitha 25000
2 prabu 26000
3 geethu 38000
5 kani 29000
6 janani 24000
7 hari 25000
6 rows selected.
ROLLBACK

SQL> rollback to sa;

Rollback complete.

SQL> select * from employee;

ENO ENAME SALARY

----------------------------------------------------
1 kavitha 25000
2 prabu 26000
3 geethu 38000
4 nive 25000
5 kani 29000
6 janani 24000
7 hari 25000

7 rows selected.

Result : Thus the table created successfully , and applied all the DDL, DML, TCL commands.
 EX.NO.2 DATABASE QUERYING

Aim:
To Create a table and apply Simple Queries Nested Queries, Sub Queries and Joins.
SQL - SIMPLE QUERIES

The SQL SELECT statement is used to fetch the data from a database table which returns this data in
the form of a result table. These result tables are called result-sets.

SELECT column1, column2, columnN FROM table_name;

SELECT * FROM table_name;
SELECT * FROM table_name WHERE colname=value(condition);

SUB QUERY
If a Query that contains another Query, then the Query inside the main Query is called a Sub
Query and the main Query is known as the parent Query.

Syntax

SELECT <column,...> FROM <table> WHERE expression operator (SELECT column, >FROM <table>
WHERE <condition> );

• Sub Query using FROM Clause

SELECT a.course_name, b.Avg_Age FROM subject a, (SELECT course_id, Avg(Age) as avg_Age FROM
student GROUP BY course_id) WHERE b.course_id = a.course_id;
• Single Row Sub Query using HAVING Clause
SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary)
> ( SELECT MIN(salary) FROM employees WHERE department_id = 50);
• Multiple Row Sub Query
SELECT e.first_name, e.salary FROM employees e WHERE salary IN ( SELECT
MIN(e.salary) FROM employees e GROUP BY e.department_id);
• Multiple Column Sub Query
SELECT e.department_id, e.job_id,e.salary FROM employees e WHERE (e.job_id, e.salary) IN ( SELECT
e.job_id, e.salary FROM employees e WHERE e.department_id = 50) ;

Nested Sub Query

When we write a Sub Query in a WHERE and HAVING clause of another Sub Query then it is
called a nested Sub Query.
SELECT e.first_name,e.salary FROM employees e WHERE e.manager_id in ( SELECT e.manager_id FROM
employees e WHERE department_id in (select d.department_id FROM departments d WHERE
d.department_name='Purchasing' ));

Correlated Sub Query

A Correlated Sub Query contains a reference to a table that appears in the outer query. It is used for
row by row processing, in other words the Sub Query will execute row by row for the parent query.

SELECT a.first_name||' '||a.last_name, a.department_id, (SELECT b.first_name||' ||b.last_name FROM

employees b WHERE b.employee_id in (SELECT d.manager_id FROM departments d WHERE
d.department_name='IT' ) ) as MANAGER FROM employees a ;

JOINS

A join is the most powerful operation for merging information from multiple tables based on a
common field. There are various types of joins but an INNER JOIN is the common of them.

Syntax

SELECT col1, col2, col3... FROM table_name1, table_name2 WHERE table_name1.col2 =

table_name2.col1;
SELECT Cust_id, Cust_name, Country, item_Ordered, Order_date FROM Customer C JOIN
Orders O USING (Order_id);

Equi Join

An Equi join is used to get the data from multiple tables where the names are common and the
columns are specified. It includes the equal ("=") operator.

SELECT Cust_id, Cust_name, item_Ordered, Order_date FROM Customer C, Orders O

WHERE C.Order_id = O.Order_id;

Inner Join

An Inner Join retrieves the matching records, in other words it retrieves all the rows where there is at
least one match in the tables.

SELECT Cust_id, Cust_name, Country, item_ordered, Order_date FROM Customer INNER

JOIN Orders USING (Order_id);

Outer Join

The records that don't match will be retrieved by the Outer join. It is of the following three types:
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join
1. Left Outer Join

A Left outer join retrieves all records from the left hand side of the table with all the matched records.
This query can be written in one of the following two ways.

SELECT Cust_id, Cust_name, Country, item_ordered, Order_date FROM customer C, LEFT OUTER
JOIN Orders O ON (C. Order_id = O.Order_id);

2. Outer Join

A Right Outer Join retrieves the records from the right hand side columns.

SELECT Cust_id, Cust_name, Country, item_ordered, Order_date FROM customer C, RIGHT

OUTER JOIN Orders O ON (C. Order_id = O.Order_id);
3.Full Outer Join

To retrieve all the records, both matching and unmatched from all the tables then use the FULL
OUTER JOIN.

SELECT Cust_id, Cust_name, Country, item_ordered, Order_date

FROM customer C, FULL OUTER JOIN Orders OON (C. Order_id = O.Order_id);

2. Non-Equi Join

A Non-Equi join is based on a condition using an operator other than equal to "=".

SELECT Cust_id, Cust_name, Country, Item_ordered, Order_date FROM Customer C, Oredrs O WHERE C.
Order_id > O.Order_id;

3. Self-join

When a table is joined to itself only then that condition is called a self-join.

SELECT C1.Cust_id, C2.Cust_name, C1.Country, C2.Order_id FROM Customer C1,

Customer C2 WHERE C. Cust_id > O.Order_id;
4. Natural Join

A natural join is just like an equi-join since it compares the common columns of both tables.

SELECT Cust_id, Cust_name, Country, Item_ordered, Order_date

FROM Customer, NATURAL JOIN Orders;
5. Cross Join

This join is a little bit different from the other joins since it generates the Cartesian product of two
tables as in the following:

Syntax
SELECT * FROM table_name1 CROSS JOIN table_name2;
COMMANDS-SIMPLE QUERIES

SQL> select * from employee where eno=1 and salary=40000;

no rows selected

SQL> select * from employee where eno=1 and salary=35000;

ENO ENAME SALARY DOB

------------------------------------------------------
1 kavi 35000 14-JUL-85

SQL> select * from employee where eno=1 or salary=40000;

ENO ENAME SALARY DOB

-------------------------------------------------------------
1 kavi 35000 14-JUL-85
2 keerthi 40000 18-OCT-87

SQL> select * from employee order by ename;

ENO ENAME SALARY DOB

------------------------------------------------------
5 dinesh 46000 26-FEB-84
4 ganesh 35000 09-JAN-83
3 geetha 28000 04-JUN-86
1 kavi 35000 14-JUL-85
2 keerthi 40000 18-OCT-87

SQL> select * from employee order by ename desc;

ENO ENAME SALARY DOB

------------------------------------------------------
2 keerthi 40000 18-OCT-87
1 kavi 35000 14-JUL-85
3 geetha 28000 04-JUN-86
4 ganesh 35000 09-JAN-83
5 dinesh 46000 26-FEB-84

SQL> select * from employee where ename like'k%';

ENO ENAME SALARY DOB

------------------------------------------------------

1 kavi 35000 14-JUL-85

2 keerthi 40000 18-OCT-87
SQL> select * from employee where ename like'%a%';

ENO ENAME SALARY DOB

------------------------------------------------------
1 kavi 35000 14-JUL-85
3 geetha 28000 04-JUN-86
4 ganesh 35000 09-JAN-83

SQL> select * from employee where ename like'%a';

ENO ENAME SALARY DOB

--------------- --------------- --------------- ---------
3 geetha 28000 04-JUN-86

SQL> select * from employee where ename in('kavi','ganesh');

ENO ENAME SALARY DOB

------------------------------------------------------------
1 kavi 35000 14-JUL-85
4 ganesh 35000 09-JAN-83

SQL> select distinct salary from employee;

SALARY
---------------
28000
35000
40000
46000

AGGREGATE FUNCTION

SQL> select * from employee;

ENO ENAME SALARY

------------------------------------------------------
1 kavitha 20000
2 prabu 40000
3 geetha 35000
4 bala 28000
5 sundari 15000

.
SQL> select sum(salary) from employee;

SUM(SALARY)
-----------------
138000
SQL> select avg(salary) from employee;

AVG(SALARY)
----------------------
27600

SQL> select min(salary) from employee;

MIN(SALARY)
--------------------
15000

SQL> select max(salary) from employee;

MAX(SALARY)
-------------------
40000

SQL> select count(salary) from employee;

COUNT(SALARY)
-------------------------
5
SQL> select count(*) from employee;

COUNT(*)
-----------------
5
.
SQL> select distinct count(eno) from employee;
COUNT(ENO)
--------------------
5

JOINS

SQL> select * from orderitems;

OID PID TOTALUNITS CUSTOMER

----------------------------------------------------------------
5100 104 30 infosys
5101 102 5 satyam
5102 103 25 wipro
101 101 10 tcs
SQL> select * from product;

PID PNAME SUPPLIERNAME UNITPRICE

--------------------------------------------------------------------------
100 camere nikon 300
101 television onida 100
102 ipod apple 75

EQUI JOIN

SQL> select oid,customer,suppliername from orderitems,product where orderitems.pid=product.pid;

OID CUSTOMER SUPPLIERNAME

------------------------------------------------------------
101 tcs onida
5101 satyam apple

LEFTOUTER JOIN

SQL> select oid,customer,suppliername from orderitems,product where orderitems.pid=product.pid(+);

OID CUSTOMER SUPPLIERNAME

-------------------------------------------------------------
101 tcs onida
5102 wipro
5100 infosys

RIGHTOUTER JOIN

SQL> select oid,customer,suppliernamå from orderitems, product where orderitems.pid(+)=product.pid;

OID CUSTOMER SUPPLIERNAME

----------------------------------------------------------------
100 camere nikon
101 tcs onida
5101 satyam apple

SELF JOIN

SQL> select * from product where product.pid>product.unitprice;

PID PNAME SUPPLIERNAME UNITPRICE

--------------------------------------------------------------------------
101 television onida 100
102 ipod apple 75

NESTED QUERIES
SQL> select * from studentdetail;

ID FIRSTNAME LASTNAME AGE SUBJECT GAMES

----------------------------------------------------------------------------------------------
100 ragul sharma 10 science cricket
101 anjali bhaguat 12 maths football
102 stephen flaming 9 science cricket
103 shekar gowda 18 maths batmitan
104 priya chandra 15 economics chess

SQL>select id,firstname from studentdetail where firstname in(select firstname from studentdetail where

subject='science');

ID FIRSTNAME
---------------------------------
100 ragul
102 stephen

SQL> select id,firstname from studentdetail where firstname not in(select firstname from studentdetail where

subject='science');

ID FIRSTNAME
---------------------------------------
101 anjali
103 shekar
104 priya
.
SQL> select * from studentdetail where age>=(selece avg(age) from studentdetail);

ID FIRSTNAME LASTNAME AGE SUBJECT GAMES

---------------------------------------------------------------------------------------------
103 shekar gowda 18 maths batmitan
104 priya chandra 15 economics chess

SQL> create table mathsgroup(id number(10),name varchar(15));

Table created.

SQL> insert into mathsgroup(id,name( select id,firstname ||'`|| lastname from studentdetail where

subject='maths';

2 rows created.

SQL> select * from mathsgroup;

 ID NAME
-----------------------
101 anjali bhaguat
103 shekar gowda

SQL> select id,(select name from mathsgroup where id=101 as name,age,subject,games from

studentdetail where id=101;

ID NAME AGE SUBJECT GAMES
-----------------------------------------------------------------------------------
101 anjali bhaguat 12 maths football

SQL> create table emp4 as select * from mathsgroup;

Table created.

SQL> desc emp4;

Name Null? Type

------------------------------- -------- ----
ID NUMBER(10)
NAME VARCHAR2(15)

Result:
The table was create successfully, Simple Queries Nested Queries, Sub Queries and Joins are applied.
EX:NO:3 VIEWS, SEQUENCES, SYNONYMS

Aim:
To create database and apply Views, Sequences, Synonyms.

SQL - Using Views

A view is nothing more than a SQL statement that is stored in the database with an associated name.
A view is actually a composition of a table in the form of a predefined SQL query.

Creating Views

The basic CREATE VIEW syntax is as follows −

CREATE VIEW view_name AS SELECT column1, column2... FROM table_name WHERE

[condition];

Sql > create view customers_view as select name, age from customers;

WITH CHECK OPTION

Create view customers_view as select name, age from customers Where age is not null with check
option;

Updating a View

SQL > UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh';

Deleting Rows into a View

Sql > delete from customers_view where age = 22;

Dropping Views

drop view view_name;

drop view customers_view;

SQL| SEQUENCES

Sequence is a set of integers 1, 2, 3, … that are generated and supported by some database systems to
produce unique values on demand.

Syntax:

CREATE SEQUENCE sequence_name START WITH initial_value INCREMENT BY

increment_value MINVALUE minimum value MAXVALUE maximum value CYCLE|NOCYCLE ;
Example

CREATE SEQUENCE sequence_2 start with 100 increment by -1 minvalue 1 maxvalue 100 cycle;

CREATE TABLE students( ID number(10),NAME char(20));

SQL: Synonyms

A synonym is an alternative name for objects such as tables, views, sequences, stored
procedures, and other database objects.

Create Synonym (or Replace)

Syntax

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name FOR [schema .]

object_name [@ dblink];

Example

CREATE OR REPLACE PUBLIC SYNONYM suppliers FOR app.suppliers;

Drop synonym

Syntax

DROP [PUBLIC] SYNONYM [schema .] synonym_name [force];

Example

drop public synonym suppliers;

COMMANDS

VIEWS
SQL> create view employeeview as select * from employee;

View created.

SQL> select * from employeeview;

ENO ENAME SALARY

--------------------------------------------------------------
1 kavitha 20000
2 prabu 40000
3 geetha 35000
4 bala 28000
5 sundari 15000
SQL> insert into employeeview values(&eno,'&ename',&salary);

Enter value for eno: 6

Enter value for ename: naveena
Enter value for salary: 27000
old 1: insert into employeeview values(&eno,'&ename',&salary)
new 1: insert into employeeview values(6,'naveena',27000)
1 row created.
SQL> /
Enter value for eno: 7
Enter value for ename: suriya
Enter value for salary: 32000
old 1: insert into employeeview values(&eno,'&ename',&salary)
new 1: insert into employeeview values(7,'suriya',32000)
1 row created.

SQL> select * from employeeview;

ENO ENAME SALARY

-------------------------------------------------------
1 kavitha 20000
2 prabu 40000
3 geetha 35000
4 bala 28000
5 sundari 15000
6 naveena 27000
7 suriya 32000

7 rows selected.

SQL> select * from employee;

ENO ENAME SALARY

-----------------------------------------------------
1 kavitha 20000
2 prabu 40000
3 geetha 35000
4 bala 28000
5 sundari 15000
6 naveena 27000
7 suriya 32000

7 rows selected.

SQL> update employeeview set ename='kayal' where eno=6;

1 row updated.
SQL> select * from employeeview;

ENO ENAME SALARY

-------------------------------------------------
1 kavitha 20000
2 prabu 40000
3 geetha 35000
4 bala 28000
5 sundari 15000
6 kayal 27000
7 suriya 32000
7 rows selected.

SQL> select * from employee;

ENO ENAME SALARY

-----------------------------------------------------
1 kavitha 20000
2 prabu 40000
3 geetha 35000
4 bala 28000
5 sundari 15000
6 kayal 27000
7 suriya 32000

7 rows selected.

SQL> delete from employeeview where eno=7;

1 row deleted.

SQL> select * from employeeview;

ENO ENAME SALARY

-------------------------------------------------
1 kavitha 20000
2 prabu 40000
3 geetha 35000
4 bala 28000
5 sundari 15000
6 kayal 27000

6 rows selected.
SQL> select * from employee;

ENO ENAME SALARY

-------------------------------------------------
1 kavitha 20000
2 prabu 40000
3 geetha 35000
4 bala 28000
5 sundari 15000
6 kayal 27000

6 rows selected.

SQL> create view empview as select eno,ename from employee with read only;
View created.

SQL> select * from empview;

ENO ENAME
----------------------------
1 kavitha
2 prabu
3 geetha
4 bala
5 sundari
6 kayal

6 rows selected.

SQL> insert into empview values(&eno,'&ename');

Enter value for eno: 7

Enter value for ename: suriya
old 1: insert into empview values(&eno,'&ename')
new 1: insert into empview values(7,'suriya')

insert into empview values(7,'suriya')

*
ERROR at line 1:

ORA-01733: virtual column not allowed here

SYNONYM

SQL> select * from employee;

ENO ENAME SALARY
-------------------------------------------------
1 kavitha 25000
2 prabu 26000
3 geethu 38000
4 nive 25000
5 kani 29000
6 janani 24000
7 hari 25000

7 rows selected.

SQL> create sequence seq;

Sequence created.

SQL> select seq.nextval from dual;

NEXTVAL
----------------
1
SQL> /

NEXTVAL
---------------
2
SQL> /

NEXTVAL
----------------
3

SQL> /

NEXTVAL
------------------
4
SQL> /

NEXTVAL
----------------
5

SQL> create sequence seq1 maxvalue 3 cycle nocache;

Sequence created.
SQL> select seq1.nextval from dual;

NEXTVAL
----------------
1
SQL> /

NEXTVAL
----------------
2
SQL> /

NEXTVAL
-----------------
3

SQL> /

NEXTVAL
-----------------
1

SQL> /

NEXTVAL
----------------
2

SYNONYMS

SQL> create synonym mysyn1 for employee;

Synonym created.

SQL> select * from mysyn1;

ENO ENAME SALARY

-------------------------------------------------
1 kavitha 25000
2 prabu 26000
3 geethu 38000
4 nive 25000
5 kani 29000
6 janani 24000
7 hari 25000

7 rows selected.
INDEX

SQL> create index a_index on employee(ename);

Index created.

Result:

The database created and applied Views, Sequences, Synonyms in a database.

EX:NO:4 DATABASE PROGRAMMING: IMPLICIT AND EXPLICIT CURSORS

Aim:

To create a database and apply Implicit and Explicit Cursors

PL/SQL - Cursors

A cursor is a pointer to this context area. PL/SQL controls the context area through a cursor. A
cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is
referred to as the active set.

• Implicit cursors
• Explicit cursors

Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when
there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the
information in it.
DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with
this statement.

For INSERT operations, the cursor holds the data that needs to be inserted.

For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

Attributes used in implicit cursor as the SQL cursor, such as %FOUND, %ISOPEN, %NOTFOUND,
and %ROWCOUNT.

DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/
Explicit Cursors

Explicit cursors are programmer-defined cursors for gaining more control over the context area. An
explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a
 SELECT Statement which returns more than one row.
General syntax for creating a cursor:
CURSOR cursor_name IS select_statement;

cursor_name – A suitable name for the cursor.

select_statement – A select query which returns multiple rows

There are four steps in using an Explicit Cursor.

DECLARE the cursor in the Declaration section.
OPEN the cursor in the Execution Section.
FETCH the data from the cursor into PL/SQL variables or records in the Execution Section.
CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

Declaring the Cursor

CURSOR c_customers IS SELECT id, name, address FROM customers;

Opening the Cursor

OPEN c_customers;

Fetching the Cursor

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

CLOSE c_customers;

Example

DECLARE
c_id customers.id%type;
c_name customerS.No.ame%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
 dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/

PROGRAM

Implicit cursor

SQL> set serveroutput on;

SQL> select * from employ;

EMPID EMPNAME PLACE

-------------------------------------------------
101 parkavi namakkal
102 meena trichy
103 ramya chennai
104 sugi gova

SQL> declare
2 id employ.empid%type;
3 begin
4 select empid into id from employ
5 where place='&place';
6 dbms_output.put_line('rollno'||id);
7 exception
8 when no_data_found then
9 dbms_output.put_line('not exists');
10 end;
11 /

Enter value for place: trichy

old 5: where place='&place';
new 5: where place='trichy';
rollno102

PL/SQL procedure successfully completed.

Explicit cursor

1 declare
2 ename employ.empname%type;
3 epl employ.place%type;
 4 cursor c1 is select empname,place from employ;
5 begin
6 open c1;
7 fetch c1 into ename,epl;
8 dbms_output.put_line(ename||'place is'||epl);
9 fetch c1 into ename,epl;
10 dbms_output.put_line(ename||'place is'||epl);
11 fetch c1 into ename,epl;
12 dbms_output.put_line(ename||'place is'||epl);
13 fetch c1 into ename,epl;
14 dbms_output.put_line(ename||'place is'||epl);
15 close c1;
16* end;
SQL> /
parkavi place is namakkal
meena place is trichy
ramya place is chennai
sugi place is gova

PL/SQL procedure successfully completed.

Result:

Thus the Data base was created and applied Implicit and Explicit Cursors in a database.
EX:NO:5 PROCEDURES AND FUNCTIONS

Aim:
To create a database and apply Procedures and Functions.

PL/SQL - Procedures

A subprogram is a program unit/module that performs a particular task. These subprograms are
combined to form larger programs. This is basically called the 'Modular design'. A subprogram can
be invoked by another subprogram or program which is called the calling program.

• Functions − These subprograms return a single value; mainly used to compute and return a
value.
• Procedures − These subprograms do not return a value directly; mainly used to perform an
action.

S.No Parts & Description

Declarative Part
It is an optional part. It contains declarations of types, cursors, constants, variables, exceptions,
1 and nested subprograms. These items are local to the subprogram and cease to exist when the
subprogram completes execution.
Executable Part
2 This is a mandatory part and contains statements that perform the designated action.
Exception-handling
3 This is again an optional part. It contains the code that handles run-time errors.

Creating a Procedure

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;

PL/SQL - Functions

A function is same as a procedure except that it returns a value. Therefore, all the discussions of
the previous chapter are true for functions too.

Creating a Function

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])] RETURN
return_datatype
{IS | AS}
BEGIN
 < function_body >
END [function_name];

PROGRAMS

1.PROCEDURE

A) Addition of two numbers

1 create or replace procedure addition(a number,b number) is

2 c number;
3 begin
4 c:=a+b;
5 dbms_output.put_line('the addition is'||c);
6* end;
SQL> /
Procedure created.
SQL> exec addition(50,51);
the addition is101

B)To display a string using procedure

SQL> create or replace procedure hello(name varchar)

2 is
3 begin
4 dbms_output.put_line('hi hello'||name);
5 end;
6 /
SQL> exec hello('nivetha');
hi hello nivetha
PL/SQL procedure successfully completed.

SQL> exec hello(' nivetha');

hi hello nivetha

SQL> create or replace procedure hello

2 is
3 begin
4 dbms_output.put_line('Hi HELLO');
5 end;
SQL> /
Procedure created.
SQL> exec hello
Hi HELLO

FUNCTIONS

Factorial

SQL> create or replace function fact(f number) return number is

2 a number;
3 fact1 number:=1;
4 begin
5 for a in 1..f
6 loop
7 fact1:=fact1*a;
8 end loop;
9 return fact1;
10 end;
SQL> /
Function created.
SQL> declare
2 n number:=&n;
3 begin
4 dbms_output.put_line(fact(n));
5 end;
6 /
Enter value for n: 5
old 2: n number:=&n;
new 2: n number:=5;
120
PL/SQL procedure successfully completed.
SQL> /
Enter value for n: 6
old 2: n number:=&n;
new 2: n number:=6;
PL/SQL procedure successfully completed.
Largest of three numbers

SQL> create or replace function large(a number, b number,c number) return •

number
2 is
3 begin
4 if a>b and a>c then
5 return a;
6 elsif b>c then
7 return b;
8 else
9 return c;
10 end if;
11 end;
SQL> /
Function created.
SQL> select large(5,9,4) from dual;
LARGE(5,9,4)
--------------------
9
Swapping of two numbers
declare
a number(10);
b number(12);
c number(12);
begin
dbms_output.put_line('prev value of a and b');
a:=&a;
b:=&b;
dbms_output.put_line(a);
dbms_output.put_line(b);
c:=a;
a:=b;
b:=c;
dbms_output.put_line('the values of a and b are');
dbms_output.put_line(a);
dbms_output.put_line(b);
end;

output
Enter value for a: 5
old 7: a:=&a;
new 7: a:=5;
Enter value for b: 3
old 8: b:=&b;
new 8: b:=3;
prev value of a and b
5
3
the values of a and b are
3
5

PL/SQL procedure successfully completed

sum of digits of a given number

1 declare
2 a number;
3 d number:=0;
4 sum1 number:=0;
5 begin
6 a:=&a;
7 while a>0
8 loop
9 d:=mod(a,10);
10 sum1:=sum1+d;
11 a:=trunc(a/10);
12 end loop;
13 dbms_output.put_line('sum is'||sum1);
14 end;
SQL> /

Enter value for a: 5895

old 6: a:=&a;
new 6: a:=5895;
sum is 27

PL/SQL procedure successfully completed.

Result:

Database created successfully. Procedures and Functions are executed successfully

EX:NO:6 TRIGGERS

Aim:
To create database and apply triggers.

PL/SQL - Triggers

Triggers are stored programs, which are automatically executed or fired when some events
occur. Triggers are, in fact, written to be executed in response to any of the following events −

• A database manipulation (DML) statement. (DELETE, INSERT, or UPDATE)

• A database definition (DDL) statement.(CREATE, ALTER, or DROP).
• A database operation. (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Creating Triggers

Syntax :

CREATE [OR REPLACE ] TRIGGER trigger_name{ BEFORE | AFTER | INSTEAD OF }

{ INSERT [OR] | UPDATE [OR] | DELETE}[ OF col_name] ON table_name [REFERENCING OLD
AS o NEW AS n] [FOR EACH ROW]

WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

PROGRAMS
A.CREATE A TRIGGER TO CONVERT LOWERCASE TO UPPERCASE
SQL> create or replace trigger uppercase
2 before insert or update on employee
3 referencing new as n for each row
4 begin
5 :n.ename:=upper(:n.ename);
 6 end;
7 /
Trigger created.
SQL> insert into employee values(11,'ravi',20000);
1 row created.
SQL> select * from employee;
ENO ENAME SALARY
-------------------------------------------------
11 RAVI 20000

B.CREATE A TRIGGER TO CHECK THE SALARY IS ABOVE 1000

SQL> create or replace trigger salcondition

2 before insert or update on employee
3 referencing new as n for each row
4 begin
5 if(:n.salary < 1000) then
6 raise_application_error (-20001,'salary must be greater than 1000');
7 end if;
8 end;
9 /
Trigger created.
SQL> insert into employee values(12,'ram',999);
insert into employee values(12,'ram',999)
*
ERROR at line 1:
ORA-20001: salary must be greater than 1000
ORA-06512: at "SHAR.SALCONDITION", line 3
ORA-04088: error during execution of trigger 'SHAR.SALCONDITION'
SQL> insert into employee values(12,'ram',1001);
1 row created.
SQL> select * from employee;
ENO ENAME SALARY
-------------------------------------------------
11 RAVI 20000
 12 RAM 1001

C.CREATE A TRIGGER TO AVOID DELETION ON WEDNESDAY

SQL> create or replace trigger datedelete

2 before delete on employee
3 declare
4 Date1 char(5);
5 begin
6 Date1:=to_char(sysdate,'dy');
7 if date1 in ('wed','WED')then
8 raise_application_error(-20002,'records cannot be deleted');
9 end if;
10 end;
11 /
Trigger created.
SQL> delete from employee where eno=11;
delete from employee where eno=11
ERROR at line 1:
ORA-20002: records cannot be deleted
ORA-06512: at "SHAR.DATEDELETE", line 6
ORA-04088: error during execution of trigger 'SHAR.DATEDELETE'

D.CREATE A TRIGGER TO AVOID DELETION OF PARTICULAR DATE

SQL> create or replace trigger namedelete

2 before delete on employee
3 referencing new as n for each row
4 begin
5 if rtrim(:n.ename)in('ravi','RAVI')then
6 raise_application_error(-20003,'record cannot be deleted for ravi');
7 end if;
8 end;
9 /
Trigger created.
SQL> delete from employee where ename='RAVI';
delete from employee where ename='RAVI'
*
ERROR at line 1:
ORA-20002: records cannot be deleted for ravi
ORA-06512: at "SHAR.DATEDELETE", line 6
ORA-04088: error during execution of trigger 'SHAR.DATEDELETE'

E.CREATE A TRIGGER TO AVOID INSERTION OF PARTICULAR DATA

SQL> create or replace trigger inserteno

2 before insert on employee
3 referencing new as n for each row
4 begin
5 if(:n.eno=14)then
6 raise_application_error(-20003,'cannot insert this eno');
7 end if;
8* end;
SQL> /
Trigger created.
SQL> insert into employee values(14,'seetha',20000);
insert into employee values(14,'seetha',20000)
*
ERROR at line 1:
ORA-20003: cannot insert this eno
ORA-06512: at "SHAR.INSERTENO", line 3
ORA-04088: error during execution of trigger 'SHAR.INSERTENO'

Result:

Thus the Trigger program was executed successfully.

EX:NO:7 EXCEPTION HANDLING

Aim:

To construct database and apply Exception Handling in PL/SQL Program.

Exception Handling

An error condition during a program execution is called an exception and the mechanism for
resolving such an exception is known as an exception handler. SQL Server provides TRY,
CATCH, THROW blocks for exception handling.

Syntax of Exception Handling

BEGIN TRY

/* T-SQL Statements */

END TRY

BEGIN CATCH

- Print Error OR
- Rollback Transaction

END CATCH

PL/SQL - Exceptions

An exception is an error condition during a program execution. PL/SQL supports programmers to

catch such conditions using EXCEPTION.

• System-defined exceptions
• User-defined exceptions
Syntax

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
........
WHEN others THEN
exception3-handling-statements
 END;

User-defined Exceptions

PL/SQL allows to define our own exceptions according to the need of our program.

Syntax

DECLARE
my-exception EXCEPTION;

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions, which are executed when any database rule is
violated by a program.

• NO_DATA_FOUND
• ZERO_DIVIDE

PROGRAM

PL/SQL BLOCK EXCEPTION HANDLING

A. NO_DATA_FOUND EXCEPTION

SQL>create table phonebook(phone_no number(10) primary key, username varchar(14), doorno

varchar(8),street varchar(34),place varchar(30), pincode number(8));

Table created.

SQL> insert into phonebook values(2767375,'akshay','101/9a','southstreet','trichy',620003);

1 row created.

SQL> insert into phonebook values(20314, 'vijay', '130/6D' ,'nortè street', 'Thanjavur', 632005);

1 row created.

SQL> select * from phonebook;

PHONE_NO USERNAME DOORNO STREET PLACE PINCODE

-----------------------------------------------------------------------------------------------------------------
2767375 akshay 101/9a southstreet trichy 620003

20314 vijay 130/6D north street Thanjavur 632005

SQL>create or replace function findaddress(phone number return varchar as address varchar(100);
2 begin
3 select username ||', ' || doorno||', '||street||', '||place||','||pincode into address from phonebook
4 where phone_no=phone;
5 return address;
6 exception
7 when no_data_found then return 'address not found';
8 end;
9 /
Function created.

SQL> declare
2 address varchar(100);
3 begin
4 address:=findaddress(2767375);
5 dbms_output.put_line(address);
6 end;
7 /

akshay, 101/9a, southstreet, trichy, 620003

PL/SQL procedure successfully completed.

SQL> declare
2 address varchar(100);
3 begin
Š 4 address:=findaddress(230612);
5 dbms_output.put_line(address);
6 end;
7 /
address not found

PL/SQL procedure successfully completed.

B.DIVIDE_BY_ZERO EXCEPTION

SQL> create table stock1(name varchar(20), price number(10), earnings number(10));

Table created.

SQL> commit;
Commit complete.

SQL> select * from stock1;

NAME PRICE EARNINGS
--------------------------------------------------------
ram 500 600
ravi 200 100
raja 200 0

SQL> create table stats(name varchar2 (10),ratio number(10));

Table created.

SQL> declare
2 pe_ratio number(3,1);
3 begin
4 select price / earnings into pe_ratio from stock1 where name='ram';
5 exception
6 WHEN ZERO_DIVIDE THEN return ‘divide by zero’;
7 commit;
8 end;
SQL> /

PL/SQL procedure successfully completed.

SQL> insert into stats (name,ratio(select name,DECODE(earnings,0,NULL,price /earnings from

stock where name='ram'));

1 row created.

SQL> select * from stats;

NAME RATIO
-----------------------------
ram 1

SQL> declare
2 pe_ratio number(3,1);
3 begin
4 select price / earnings into pe_ratio from stock1 where name='raja';
5 exception
6 WHEN ZERO_DIVIDE THEN return ‘divide by zero’;
 7 commit;
8 end;
SQL> /

PL/SQL procedure successfully completed.

SQL> insert into stats (name, ratio(select name, DECODE (earnings, 0, NULL, price /earnings from

stock where name='raja'));

divide by zero

1 row created.

SQL> select * from stats;

NAME RATIO
----------------------------
ram 1
raja

Result :

Thus the Exception Handling programs was executed successfully.

 EX:NO : 8 DATABASE DESIGN USING ER MODELING, NORMALIZATION
AND IMPLEMENTATION FOR ANY APPLICATION

Aim:

To implement a database design using ER modeling, normalization for any real time application.

Entity Relationship Modeling (ER Modeling) is a graphical approach to database design. It uses
Entity/Relationship to represent real world objects.

An Entity is a thing or object in real world that is distinguishable from surrounding environment.

• An entity has a set of properties.

• Entity properties can have values.

Enhanced Entity Relationship (EER) Model

Enhanced Entity Relationship (EER) Model is a high level data model which provides extensions to
original Entity Relationship (ER) model.

1. Identify the entities and determine the relationships that exist among them.
2. Each entity, attribute and relationship, should have appropriate names that can be easily
understood by the non-technical people as well.
3. Relationships should not be connected directly to eachother. Relationships should connect
entities.
4. Each attribute in a given entity should have a unique name.

Defining the relationships among entities

Relational databases do not support many-to-many relationships. We need to introduce a junction

entity.

Attributes

• Membership number
• Full names
• Gender
• Date of birth
• Physical address
• Postal address

Let's now create the ER Diagram using EDRAW tool

1. Drag the table object from the tools panel

2. Drop it in the workspace area. An entity named table 1 appears

3.Double click on it.

 1. Change table 1 to Members
2. Edit the default idtable1 to membership_number
3. Click on the next line to add the next field
4. Do the same for all the attributes identified in members' entity.

What is Normalization? 1NF, 2NF, 3NF & BCNF with

Examples What is Normalization?

Normalization is a database design technique which organizes tables in a manner that

reduces redundancy and dependency of data.

Result

Thus the Database design using ER modeling, Normalization was implemented successfully
EX:NO:9 CONNECTING TO ORACLE DATABASE USING VISUAL BASIC

AIM:

To design a Form using Visual Basic and connect with oracle database.

PROCEDURE:

STEP 1: Start

STEP 2: Create the form with essential controls in tool box.

STEP 3: Drag the ADODC component into the form.

STEP 4: Right click the adodc component and change the properties of general,

authentication, Record source.

STEP 5:Click the TextField, change data source and data field.

STEP 6: Write the code for doing the appropriate functions.

STEP 7: Save the forms and project.

STEP 8: Execute the form.

Coding

Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim MENO As Integer

Private Sub Command1_Click()

RS.AddNew
Call CAL
Call ASSIGN
End Sub

Private Sub Command2_Click()

RS.MoveFirst
MENO = CInt(InputBox("ENTER EMPLOYEE NUMBER TO FIND"))
RS.FIND "regno = " & MENO
If RS.EOF = True Then
MsgBox "sorry no such record "
Else
Call DISPLAY
RS.Delete
MsgBox "RECORD GETS DELETED.."
Call CLEARALL
End If
End Sub

Private Sub Command3_Click()

RS.MoveFirst
MENO = CInt(InputBox("ENTER EMPLOYEE NUMBER TO FIND"))
RS.FIND "regno = " & MENO
If RS.EOF = True Then
MsgBox "sorry no such record "
Else
Call DISPLAY
End If
End Sub

Private Sub Form_Load()

Set CN = New ADODB.Connection
Set RS = New ADODB.Recordset
CN.ConnectionString = "Provider=MSDASQL.1;Password=pavithra;Persist Security Info=True;User
ID=pavithra;Data Source=pavi"
CN.Open
RS.Open "pavithra.studentdetail", CN, adOpenDynamic, adLockOptimistic
End Sub

Private Sub ASSIGN()

RS!regno = CInt(Text1.Text)
RS!Name = Text2.Text
RS!m1 = CInt(Text3.Text)
RS!m2 = CInt(Text4.Text)
RS!m3 = CInt(Text5.Text)
RS!m4 = CInt(Text6.Text)
RS!m5 = CInt(Text7.Text)
RS!total = CInt(Text8.Text)
RS!average = CInt(Text9.Text)
End Sub
Private Sub CLEARALL()
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
Text7.Text = ""
Text8.Text = ""
Text9.Text = ""
Text1.SetFocus
End Sub

Private Sub CAL()

Text8.Text = CDbl(Text3.Text) + CDbl(Text4.Text) + CDbl(Text5.Text) + CDbl(Text6.Text) +
CDbl(Text7.Text)
Text9.Text = CDbl(Text8.Text) / 5
End Sub

Private Sub DISPLAY()

Text1.Text = RS!regno
Text2.Text = RS!Name
Text3.Text = RS!m1
Text4.Text = RS!m2
Text5.Text = RS!m3
Text6.Text = RS!m4
Text7.Text = RS!m5
Text8.Text = RS!total
Text9.Text = RS!average
Text8.Text = CDbl(Text3.Text) + CDbl(Text4.Text) + CDbl(Text5.Text) + CDbl(Text6.Text) +
CDbl(Text7.Text)
Text9.Text = CDbl(Text8.Text) / 5
End Sub
OUTPUT
Result:

Thus the forms in Visual Basic created and connected with the database successfully.
Ex.No.10 Case Study on Real Life Database Applications (Railway Reservation)

Aim:
The aim of case study is to design and develop a database maintaining the records of different
trains, train status, and passengers.

Description:
The railway reservation system facilitates the passengers to enquire about the trains available on
the basis of source and destination, booking and cancellation of tickets, enquire about the status of the
booked ticket, etc.
The record of train includes its number, name, source, destination, and days on which it is
available, whereas record of train status includes dates for which tickets can be booked, total number of
seats available, and number of seats already booked. The database has been developed and tested on the
Oracle.
Passengers can book their tickets for the train in which seats are available. For this, passenger
has to provide the desired train number and the date for which ticket is to be booked. Before booking a
ticket for a passenger, the validity of train number and booking date is checked.
Once the train number and booking date are validated, it is checked whether the seat is available.
If yes, the ticket is booked with confirm status and corresponding ticket ID is generated which is stored
along with other details of the passenger.
After all the available tickets are booked, certain numbers of tickets are booked with waiting
status.
If waiting lot is also finished, then tickets are not booked and a message of nonavailability of
seats is displayed.
The ticket once booked can be cancelled at any time. For this, the passenger has to provide the
ticket ID (the unique key).
The ticket ID is searched and the corresponding record is deleted. With this, the first ticket with
waiting status also gets confirmed.

List of Assumptions
The reservation system is very large in reality, Therefore, a small sample case study has been
created to demonstrate the working of the reservation system. To implement this case study, some
assumptions have been made, which are as follows:

1. The number of trains has been restricted to 5.

2. The booking is open only for next seven days from the current date.
3. Only two categories of tickets can be booked, namely, AC and General.
4. The total number of tickets that can be booked in each category (AC and General) is 10.
5. The total number of tickets that can be given the status of waiting is 2.
6. The in between stoppage stations and their bookings are not considered.

Tables and Procedures

Tables and procedures that will be considered are as follows:

1. Train List: This table consists of details about all the available trains. The information stored in
this table includes train number, train name, source, destination, fare for AC ticket, fare for
 general ticket, and weekdays on which train is available.

Constraint: The train number is unique.

Train number Train name source destination Type of class Fare

16239 Pallavan Trichy chennai AC 1369
Express
16234 Howrah Trichy Vizhagapattinam AC 2456
Express
16223 Chennai Chennai Mumbai AC 3455
Express

2. Train_Status: This table consists of details about the dates on which ticket can be booked for a
train and the status of the availability of tickets. The information stored in this table includes
train number, train date, total number of AC seats, total number of general seats, number of AC
seats booked, and number of general seats booked.

Constraint: Train number should exist in TrainList table.

Train number Train date Seats in AC Seats Avail in Seats in Seats Avail
AC General in General
16239 12/02/19 45 21 123 120
16234 14/03/19 22 3 113 75
16223 11/02/19 44 12 110 90

3. Passenger: This table consists of details about the passenger that includes ID,name, age, sex and
address of the passenger, Proof.
Constraint: Passenger ID is unique.

Pas. ID Pas. name Age Address Proof

11 XXX 28 TRICHY AADHAR
12 YYY 47 CHENNAI VOTER
13 ZZZ 72 TRICHY VOTER

4.Ticket History: This table consists of details about the booked tickets. The information stored in this
table includes ticket ID, train number, date for which ticket is booked, name, age, sex and Proof of the
passenger, status of reservation (either confirmed or waiting), and category for which ticket is booked.

Constraint: Ticket ID is unique and the train number should exist in TrainList table.

Train number Train date Pas. ID Pas. name status of Proof

reservation
16239 12/02/19 11 XXX confirmed AADHAR
16234 14/03/19 12 YYY waiting list VOTER
16223 11/02/19 13 ZZZ RAC VOTER

4. Booking: In this procedure, the train number, train date, and category is read from the passenger.
On the basis of the values provided by the passenger, corresponding record is retrieved from the
 Train_Status table. If the desired category is AC, then total number of AC seats and number of
booked
AC seats are compared in order to find whether ticket can be booked or not. Similarly, it can be
checked for the general category. If ticket can be booked, then passenger details are read and
stored in the Passenger table.

Train number Train date Pas. ID Pas. name Transaction Fare

ID
16239 12/02/19 11 XXX 112456 1250
16234 14/03/19 12 YYY 125871 240
16223 11/02/19 13 ZZZ 254589 521

6. Cancel: In this procedure, ticket ID is read from the passenger and corresponding record is searched
in the Passenger table. If the record exists, it is deleted from the table. After deleting the record (if it is
confirmed), first record with waiting status for the same train and same category are searched from the
Passenger table and its status is changed to confirm.

Train Train date Pas. ID Pas. name Transaction Cancel Fare

number ID
16239 12/02/19 11 XXX 112456 cancelled 550
16234 14/03/19 12 YYY 125871 partially No refund
cancelled
16223 11/02/19 13 ZZZ 254589 cancelled 320

E‐Rdiagram