lOMoARcPSD|47775205

DBMS lab

Database management systems laboratory (Anna University)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

AD3381 Database design and Management

LABORATORY
II YEAR / III SEMESTER AI&DS

REGULATION – 2021

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA

SCIENCE

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

1
 lOMoARcPSD|47775205

LIST OF EXPERIMENTS

EXP.No Experiment Title

1
Data Definition Commands, Data Manipulation Commands for inserting, deleting, updating
and retrieving Tables and Transaction Control statements

2
DatabaseQuerying – Simplequeries, Nested queries, Sub queries and Joins

3
Creation of Views, Sequences and Synonyms

4
IMPLEMENTATION OF CURSORS

5A STUDY OF PL/SQL BLOCK

5B
Creation of Procedures

5C Creation of database functions

6
Creation of database triggers

7
PL/SQL block that handles all types of exceptions.

8
Database Design using ER modeling, normalization and Implementation

9
RAILWAY RESERVATION SYSTEM

10 INVENTORY CONTROL SYSTEM

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

2
 lOMoARcPSD|47775205

Ex.No:1

Data Definition Commands, Data Manipulation Commands for inserting, deleting, updating
and retrieving Tables and Transaction Control statements

AIM:
To create the database using Data Definition Language(DDL) Commands and to perform
Insert, Delete, Modify, Alter, Update and View the records using Data Manipulation Language
(DML) Commands and to control the tables using Transaction Control Statements.

Data Definition Language:

A data definition language or data description language (DDL) is a syntax similar to a
computer programming language for defining data structures, especially database schemas. Many
data description languages use a declarative syntax to define fields and data types.
The DDL commands in the SQL are
 CREATE
 DESC
 RENAME
 ALTER
 TRUNCATE
 DROP

CREATE
CREATE command is used to create tables in database.
Syntax
CREATE TABLE <table name> (column1 datatype1,…, column datatypen);

DESC : To view the table structure.

Syntax
Desc<table name>

RENAME
RENAME command is used to rename the tables.
Syntax
Rename <old table name> to <new table name>;

ALTER
The structure of a table can be changed using this command. Using this command we can do the
following.
i. Add a new column.
ii. Change the width of a data type
iii. Change the data type of a column
While altering the columns the values of the particular column should be empty.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

3
 lOMoARcPSD|47775205

Syntax
ALTER TABLE <table name> MODIFY (column datatype,…);
ALTER TABLE <table name> ADD (column datatype,…);

TRUNCATE
It is used to remove all the records in the table including the space allocated for the table.
But the structure of the table is retained.
TRUNCATE TABLE <table name>;

DROP
This command is used to remove a table from the database.
Syntax
DROP TABLE <table name>;

Output
CREATE
SQL> create table employee (Employee_namevarchar(10), employee_no number(8), dept_name
varchar(10),dept_no number (5),date_of_join date);
Table created.

CREATION OF TABLE WITH PRIMARY KEY

SQL> create table employee1 (Employee_namevarchar(10),employee_no number(8) primary key,

dept_name varchar(10), dept_no number (5),date_of_join date);

Table created.
DESC
SQL>desc employee1;
Name Null? Type

EMPLOYEE_NAME VARCHAR2(10)
EMPLOYEE_NO NOT NULL NUMBER(8)
DEPT_NAME VARCHAR2(10)
DEPT_NO NUMBER(5)
DATE_OF_JOIN DATE

RENAME
SQL> Rename employee to employee2;
Table renamed.

ALTER
SQL> alter table employee1 add ( salary number);
SQL> alter table employee1 modify ( salaryvarchar(10));
Table altered.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

DROPPING PRIMARY KEY USING ALTER COMMAND

SQL> alter table employee1 drop primary key;

Table altered.

CREATING PRIMARY KEY USING ALTER COMMAND

SQL> alter table employee1 add primary key (employee_no);
Table altered.

TRUNCATE
SQL> truncate table employee1;
Table truncated.

DROP
SQL> drop table employee1;
Table dropped.

Data Manipulation Language (DML)

Data Manipulation Language performs the following operations.
• Insert the Data
• Update the Data
• Delete the Data
• Retrieve the Data

The DML commands in the SQL are INSERT, UPDATE, DELETE, SELECT.
INSERT
It is used to insert the values into the table.
Syntax
INSERT INTO <table name> VALUES (value1, value2…);
SQL> INSERT INTO employee1 VALUES(‘&Employee_name’, ’&employee_no’,
‘&dept_name’, ‘&dept_no’,’&date_of_join’);
Using this we can insert ‘N’ no. of rows.

UPDATE
The Update command is used to update (changing values in one or two columns of a row) rows in a
table. Specific rows can also be updated based on some condition.
If the WHERE clause is omitted, then the changes take place in all rows of the table.

Syntax
UPDATE <table_name> SET column1=expression,column2=expression… WHERE
<search_condition>;

DELETE
The delete command is used to delete rows from a table.
Syntax
DELETE FROM<table_name> [WHERE <search_condition>];
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

Deletion of all rows

SQL>DELETE FROM <table_name>;
This causes the deletion of all rows in the table.

SELECT
The Select command is used to retrieve the stored data from a table.
Syntax
SELECT * FROM <table_name>;

OUTPUT:
SQL> create table employee (Employee_namevarchar(10), employee_no number(8), dept_name
varchar(10),dept_no number (5),date_of_join date);
Table created.

INSERT
SQL>insert into employee1 values ('Vijay',345,'CSE',21,'21-jun-2006');
1 row created.

UPDATE
SQL> update employee1 set employee_no=300 where dept_no=67;
1 row updated.

DELETE
SQL> delete from employee1 where employee_no>344;
SQL> delete from employee1 where employee_no =124;

SELECT
SQL> select * from employee1;
It selects all the columns from the table.

SQL>select Employee_name, employee_no from employee1;

It selects only the specified columns from the table.

TCL (TRANSACTION CONTROL LANGUAGES COMMANDS)

 COMMIT
 SAVEPOINT
 ROLLBACK
COMMIT
It is used to save the work done.
SQL> commit;
Commit complete.

SAVE POINT
This command is used to identify a point in a transaction in which it can be restored using Roll
back command.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

SQL> savepoint <save point Name>;

Savepoint created.

ROLLBACK
It is used to restore database to original since last commit.
SQL> rollback;
Rollback complete.
SQL> roll back <savepoint Name>
Rollback complete.

COMMIT & ROLLBACK COMMAND

SQL> select * from employee;

EMPLOYEE_N EMPLOYEE_NO DEPT_NO DEPT_NAME

Ganesh 234 45 CSE

Vijay 877 85 EEE
Vignesh 990 95 BME

SQL> commit;
Commit complete.

SQL> delete from employee where employee_no=990;

1 row deleted.

SQL> select * from employee;

EMPLOYEE_N EMPLOYEE_NO DEPT_NO DEPT_NAME

Ganesh 234 45 CSE

Vijay 877 85 EEE

SQL> rollback;
Rollback complete.

SQL> select * from employee;

EMPLOYEE_N EMPLOYEE_NO DEPT_NO DEPT_NAME

Ganesh 234 45 CSE

Vijay 877 85 EEE
Vignesh 990 95 BME

SAVEPOINT & ROLLBACK COMMAND

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

SQL> select * from employee;

EMPLOYEE_N EMPLOYEE_NO DEPT_NO DEPT_NAME

Ganesh 234 45 CSE

Vijay 877 85 EEE
Vignesh 990 95 BME
SQL> savepoint sp1;
Savepoint created.

SQL> update employee set dept_no=75 where employee_no=234;

1 row updated.
SQL> delete from employee where employee_no=990;
1 row deleted.
SQL> select * from employee;
EMPLOYEE_N EMPLOYEE_NO DEPT_NO DEPT_NAME

Ganesh 234 75 CSE

Vijay 877 85 EEE

SQL> roll back sp1

Rollback complete.

SQL> select * from employee;

EMPLOYEE_N EMPLOYEE_NO DEPT_NO DEPT_NAME

Ganesh 234 45 CSE

Vijay 877 85 EEE
Vignesh 990 95 BME

RESULT
Thus the Database creation using Data Definition Language(DDL) commands, the Insert, Delete,
Modify, Alter, Update and View the records using Data Manipulation Language (DML)
Commands and to control tables using Transaction Control statement was executed successfully.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Ex. No 2: DatabaseQuerying – Simplequeries, Nested queries, Sub queries

and Joins
AIM:
To create a table and execute Simple queries, Nested queries, Sub queries and Joins.

DESCRIPTION:

JOIN OPERATIONS

 INNER JOIN/ NATURAL JOIN/ JOIN: It is a binary operation that allows us to combine
certain selections and a Cartesian product into one operation.
 OUTER JOIN: It is an extension of join operation to deal with missing information.

Left Outer Join: It takes tuples in the left relation that did not match with any tuple in
the right relation, pads the tuples with null values for all other attributes from the right
relation and adds them to the result of the natural join.

Right Outer Join: It takes tuples in the right relation that did not match with any tuple
in the left relation, pads the tuples with null values for all other attributes from the left
relation and adds them to the result of the natural join.

Full
tuplesOuter
withJoin:
null Itvalues
combines tuples
for the from attributes
missing both the left
andand thetoright
hem the relation
result ofand
thepads the
natural
join.

CREATION OF TABLE

SQL>create table stud (sname varchar2(30), sid varchar2(10), sage number(10), sarea varchar2(20),
sdept varchar2(20));

Table created.

INSERTION OF VALUES INTO THE TABLE

SQL> insert into stud values ('ashwin',101,19,'anna nagar','aeronautical');
1 row created.

SQL> insert into stud values ('bhavesh',102,18,'nungambakkam','marine');

1 row created.

SQL> insert into stud values ('pruthvik',103,20,'annanagar','aerospace');

1 row created.

SQL> insert into stud values ('charith',104,20,'kilpauk','mechanical');

1 row created.
SQL> select * from stud;
SNAME SID SAGE SAREA SDEPT

ashwin 101 19 annanagar aeronautical

bhavesh 102 18 nungambakkam marine

pruthvik 103 20 annanagar aerospace

charith 104 20 kilpauk mechanical

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

RENAMING THE TABLE ‘STUD’

SQL> rename stud to studs;

Table renamed.

ARITHMETIC OPERATION

SQL> select sname, sid+100 "stid" from studs;

SNAME stid

ashwin 201
bhavesh 202
pruthvik 203
charith 204

DISPLAY ONLY DISTINCT VALUES

SQL> select distinct sarea from studs;

SAREA
annanagar
kilpauk
nungambakkam

USING THE WHERE CLAUSE

SQL> select sname,sage from studs where sage<=19;

SNAME SAGE

ashwin 19
bhavesh 18

BETWEEN OPERATOR
SQL> select sname,sarea, sid from studs where sid between 102 and 104;
SNAME SAREA SID

bhavesh nungambakkam 102

pruthvik annanagar 103

charith kilpauk 104

PATTERN MATCHING
SQL> select sname, sarea from studs where sarea like '%g%';
SNAME SAREA

ashwin annanagar

bhavesh nungambakkam

pruthvik Annanagar
LOGICAL AND OPERATOR

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

SQL> select sname ,sid from studs where sid>102 and sarea='annanagar';

SNAME SID

pruthvik 103

LOGICAL OR OPERATOR
SQL> select sname ,sid from studs where sid>102 or sarea='annanagar';
SNAME SID

ashwin 101
pruthvik 103
charith 104

UPDATING THE TABLE

SQL> alter table studs add ( spocket varchar2(20) );

Table altered.

SQL> update studs set spocket=750 where sid=101;

1 row updated.

SQL> update studs set spocket=500 where sid=102;

1 row updated.

SQL> update studs set spocket=250 where sid=103;

1 row updated.

SQL> update studs set spocket=100 where sid=104;

1 row updated.

SQL> select * from studs;

SNAME SID SAGE SAREA SDEPT

SPOCKET

ashwin 101 19 annanagar aeronautical

750

bhavesh 102 18 nungambakkam marine

500

pruthvik 103 20 annanagar aerospace

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

250

charith 104 20 kilpauk mechanical

100

AGGREGATE FUNCTIONS

SQL> select avg( spocket ) result from studs;

400

SQL> select min(spocket) result from studs;

100

SQL> select count(spocket) result from studs;

4
SQL> select max(spocket) result from studs;
750
SQL> select sum(spocket) result from studs;
1600

CREATING TABLES FOR DOING JOIN AND NESTED QUERY OPERATIONS

Creating Dept table:

SQL> create table dept(dno number(10),dname varchar(10),loc varchar(10));

Table created.

SQL> insert into dept values(10,'inventory','hyd');

1 row created.

SQL> insert into dept values(20,'finance','bglr');

1 row created.

SQL> insert into dept values(30,'HR','mumbai');

1 row created.

SQL> select * from dept;

DNO DNAME LOC
10 inventory hyd
20 finance bglr
30 HR mumbai

10
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

Creating emp2 table:

SQL> create table emp2(enonumber(10),ename varchar(10),job varchar(10),Mgr(10),dno number(10));

Table created.

SQL> insert into emp2 values(111,'saketh','analyst',444,10);

1 row created.

SQL> insert into emp2 values(222,'sandeep','clerk',333,20);

1 row created.

SQL> insert into emp2

values(333,'jagan','manager',111,10); 1 row created.

SQL> insert into emp2

values(444,'madhu','engineer',222,40); 1 row created.

SQL> select * from emp2;

ENO ENAMEJOB MGR DNO

111 saketh analyst 444 10

222 sandeep clerk 333 20

333 jagan manager 111 10

444 madhu engineer 222 40

1. Equijoin:
A join which contains an equal to ‘=’ operator in this joins condition

Using Clause:
SQL> select eno,ename,job,dname,loc from emp2 e join dept d using(dno);

ENO ENAMEJOB DNAMELOC

111 saketh analyst inventory hyd

222 sandeep clerk finance bglr
333 jagan manager inventory hyd

On Clause:
SQL> select eno,ename,job,dname,loc from emp2 e join dept d on(e.dno=d.dno);
ENO ENAMEJOB DNAMELOC

111 saketh analyst inventory hyd

222 sandeep clerk finance bglr

11
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

333 jagan manager inventory hyd

2. Non-Equijoin:
A join which contains an operator other than equal to ‘=’ in the join condition.
SQL> select eno,ename,job,dname,loc from emp2 e,dept d where e.dno>d.dno;
ENO ENAME JOB DNAMELOC

222 sandeep clerk inventory hyd

444 madhu engineer inventory hyd
444 madhu engineer finance bglr
444 madhu engineer HR Mumbai

3. Self Join:
Joining the table itself is called self join.
SQL> select e1.eno,e2.ename,e1.job,e2.dno from emp2 e1,emp2 e2 where
e1.eno=e2.mgr;
ENO ENAME JOB DNO

444 saketh engineer 10

333 sandeep manager 20
111 jagan analyst 10
222 madhu clerk 40

4. Natural Join:
It compares all the common columns.
SQL> select eno,ename,job,dname,loc from emp2 natural join dept;
ENO ENAMEJOB DNAMELOC

111 saketh analyst inventory hyd

222 sandeep clerk finance bglr
333 jagan manager inventory hyd

5. Cross Join:
This will give the cross product.
SQL> select eno,ename,job,dname,loc from emp2 cross join dept;
ENO ENAME JOB DNAME LOC

111 saketh analyst inventory hyd

222 sandeep clerk inventory hyd
333 jagan manager inventory hyd
444 madhu engineer inventory hyd
111 saketh analyst finance bglr
222 sandeep clerk finance bglr
333 jagan manager finance bglr
444 madhu engineer finance bglr
111 saketh analyst HR mumbai
222 sandeep clerk HR mumbai
333 jagan manager HR mumbai
444 madhu engineer HR mumbai

12 rows selected.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

6. Outer Join:
It gives the non-matching records along with matching records.
Left Outer Join:
This will display the all matching records and the records which are in left hand side table those
that are in right hand side table.
SQL> select eno,ename,job,dname,loc from emp2 e left outer join dept d
on(e.dno= d.dno);
(OR)
SQL> select eno,ename,job,dname,loc from emp2 e,dept d where e.dno=d.dno(+);
ENO ENAMEJOB DNAMELOC

333 jagan manager inventory hyd

111 saketh Analyst inventory hyd
222 sandeep Clerk finance bglr
444 madhu Engineer

Right Outer Join:

This will display the all matching records and the records which are in right hand side table
those that are not in left hand side table.

SQL> select eno,ename,job,dname,loc from emp2 e right outer join dept d on(e.dno =d.dno);
(OR)
SQL> select eno,ename,job,dname,loc from emp2 e,dept d where e.dno(+)=d.dno;

ENO ENAMEJOB DNAMELOC

111 saketh Analyst inventory hyd

222 sandeep Clerk finance bglr
333 jagan Manager inventory hyd
HR mumbai
Full Outer Join:
This will display the all matching records and the non matching records from both
tables.
SQL> select eno,ename,job,dname,loc from emp2 e full outer join dept d on(e.dno=
d.dno);

ENO ENAMEJOB DNAMELOC

333 jagan manager inventory hyd

111 saketh analyst inventory hyd
222 sandeep clerk finance bglr
444 madhu engineer
HR Mumbai

RESULT
Thus the relationship between databases has been implemented using join operation.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Ex. No 3: Creation of Views, Sequences and Synonyms

AIM:
To create views, sequences and synonyms using DDL, and DML statements

DESCRIPTION:
Views

 A database view is a logical or virtual table based on a query. It is useful to think of a

view as a stored query. Views are queried just like tables.
 A DBA or view owner can drop a view with the DROP VIEW command.

Sequences

 Oracle provides the capability to generate sequences of unique numbers, and they are
called sequences.
 Just like tables, views and synonyms, a sequence is a type of database object.
 Sequences are used to generate unique, sequential integer values that are used as primary
key values in database tables.
 The sequence of numbers can be generated in either ascending or descending order.

Synonyms

 A synonym is an alias, that is, a form of shorthand used to simplify the task of
referencing a database object.
 There are two categories of synonyms, public and private.
 A public synonym can be accessed by any system user.
 Private synonyms, on the other hand, belong to the system user that creates them and
reside in that user's schema.
 A system user can grant the privilege to use private synonyms that they own to other
system users.
 In order to create synonyms, we will need to have the CREATE SYNONYM privilege.
 This privilege will be granted to us by the DBA.
 We must have the CREATE PUBLIC SYNONYM privilege in order to create public
synonyms.
 If we own a synonym, we have the right to drop (delete) the synonym. The DROP
SYNONYM command is quite simple.
 DROP SYNONYM synonym_name;
 In order to drop a public synonym we must include the PUBLIC keyword in the DROP
SYNONYM command.
 In order to drop a public synonym, we must have the DROP PUBLIC SYNONYM
privilege.
 DROP PUBLIC SYNONYM synonym_name;

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

OUTPUT:
TO CREATE THE TABLE ‘FVIEWS’
SQL> create table fviews( name varchar2(20),no number(5), sal number(5), dno number(5));
Table created.

SQL> insert into fviews values('xxx',1,19000,11);

1 row created.

SQL> insert into fviews values('aaa',2,19000,12);

1 row created.

SQL> insert into fviews values('yyy',3,40000,13);

1 row created.

SQL> select * from fviews;

NAME NOSAL DNO

Xxx 1 19000 11

Aaa 2 19000 12

Yyy 3 40000 13

CREATE A NEW VIEW ‘SSVIEW’ AND DROP THE VIEW

SQL> create view ssview( cusname,id) as select name, no from fviews where dno=12;

View created.

SQL> select * from ssview;

CUSNAME ID

aaa 2

SQL> drop view ssview;

View dropped.

Sequences
Creation of table:
SQL> create table class(name varchar(10),id number(10));
Table created.

Insert values into table:

SQL> insert into class values('&name',&id);
Enter value for name: anu

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Enter value for id: 1

old 1: insert into class values('&name',&id)
new 1: insert into class values('anu',1)
1 row created.

SQL> /
Enter value for name: brindha
Enter value for id: 02
old 1: insert into class values('&name',&id)
new 1: insert into class values('brindha',02)
1 row created.

SQL> /
Enter value for name: chinthiya
Enter value for id: 03
old 1: insert into class values('&name',&id)
new 1: insert into class values('chinthiya',03)
1 row created.
SQL> select * from class;
NAME ID
anu 1
brindha 2
chinthiya 3

Create Sequence:
SQL> create sequence s_1
2 start with 4
3 increment by 1
4 maxvalue 100
5 cycle;
Sequence created.

SQL> insert into class values('divya',s_1.nextval)

1 row created.
SQL> select * from class;
NAME ID

anu 1
brindha 2
chinthiya 3
divya 4

Drop Sequence:
SQL> drop sequence s_1;
Sequence dropped.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Synonym:
SQL> select * from class;
NAME ID

Anu 1
Brindha 2
Chinthiya 3
Divya 4
ezhil 5
fairoz 7
hema 9

7 rows selected.

Create synonym:
SQL> create synonym c1 for class;
Synonym created.
SQL> insert into c1 values('kalai',20);
1 row created.
SQL> select * from class;
NAME ID

anu 1
brindha 2
chinthiya 3
divya 4
ezhil 5
fairoz 7
hema 9
kalai 20
8 rows selected.

SQL> select * from c1;

NAME ID

anu 1
brindha 2
chinthiya 3
divya 4
Ezhil 5
fairoz 7
hema 9
kalai 20
8 rows selected.

SQL> insert into class values('Manu',21);

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

1 row created.

SQL> select * from c1;

NAME ID

anu 1
brindha 2
chinthiya 3
divya 4
ezhil 5
fairoz 7
hema 9
kalai 20
Manu 21

9 rows selected.
Drop Synonym:
SQL> drop synonym c1;
Synonym dropped.

RESULT:

Thus the creation of Views, Sequences andSynonyms has been executed successfully.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

EX.NO:4 IMPLEMENTATION OF CURSORS

Aim:
To Create Cursor for Electricity Bill Calculation.

Procedure :
Implicit Cursor
Whenever any DML operations occur in the database, an implicit cursor iscreated that holds the
rows affected, in that particular operation. These cursorscannot be named and, hence they cannot be
controlled or referred from another placeof the code. We can refer only to the most recent cursor
through the cursor attributes.
Explicit Cursor
Programmers are allowed to create named context area to execute their DMLoperations to get more
control over it. The explicit cursor should be defined in thedeclaration section of the PL/SQL block,
and it is created for the 'SELECT' statementthat needs to be used in the code.

Below are steps that involved in working with explicit cursors.

Declaring the cursor
Declaring the cursor simply means to create one named context area for the'SELECT' statement
that is defined in the declaration part. The name of this contextarea is same as the cursor name.
Opening Cursor
Opening the cursor will instruct the PL/SQL to allocate the memory for this cursor. It will make the
cursor ready to fetch the records.
Fetching Data from the Cursor
In this process, the 'SELECT' statement is executed and the rows fetched arestored in the allocated
memory. These are now called as active sets. Fetching datafrom the cursor is a record-level activity
that means we can access the data in arecord-by-record way.
Each fetch statement will fetch one active set and holds the information of thatparticular record.
This statement is same as 'SELECT' statement that fetches therecord and assigns to the variable in
the 'INTO' clause, but it will not throw anyexceptions.
Closing the Cursor
Once all the record is fetched now, we need to close the cursor so that thememory allocated to this
context area will be released

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Syntax
DECLARE
CURSOR <cursor_name> IS <SELECT statement><cursor_variable declaration>
BEGIN
OPEN <cursor_name>;
FETCH <cursor_name> INTO <cursor_variable>;
.
.
CLOSE <cursor_name>;

END;

SQL>createtablebill(namevarchar2(10),addressvarchar2(20),cityvarchar2(20),unitnumber(10));

Tablecreated.

SQL>insertintobillvalues('&name','&addess','&city','&unit');Entervalueforname:yuva
Entervalueforaddess:sriviEntervalueforcity:srivilliputurEntervalueforunit:100
old 1:insertintobillvalues('&name','&addess','&city','&unit')new
1:insertintobillvalues('yuva','srivi','srivilliputur','100')
1rowcreated.

SQL>/
Entervalueforname:nithya
Entervalueforaddess:LakshminagarEntervalueforcity:sivakasi
Entervalueforunit:200
old 1:insertintobillvalues('&name','&addess','&city','&unit')
new 1:insertintobillvalues('nithya','Lakshminagar','sivakasi','200')

1rowcreated.

SQL>/
Entervalueforname:maya
Entervalueforaddess:housingboardEntervalueforcity:sivakasi
Entervalueforunit:300
old 1:insertintobillvalues('&name','&addess','&city','&unit')
new 1:insertintobillvalues('maya','housingboard','sivakasi','300')

1rowcreated.

SQL>/
Entervalueforname:jeeva
Entervalueforaddess:RRRnagarEntervalueforcity:sivaganagaiEntervalueforunit:400
old 1:insertintobillvalues('&name','&addess','&city','&unit')
20
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

new 1:insertintobillvalues('jeeva','RRRnagar','sivaganagai','400')

1rowcreated.

SQL>select*frombill;

NAME ADDRESS CITY UNIT

yuva srivi srivilliputur 100

nithya Lakshminagar sivakasi 200

mayahousingboard sivakasi 300

jeeva RRRnagar sivaganagai 400

SQL>declare

2 cursorcisselect*frombill;

3 bbill%ROWTYPE;

4 begin

5 openc;

6 dbms_output.put_line('Name Address cityUnit Amount');

7 Loop

8 fetchcintob;

9 if(c%notfound)then

10 exit;

11 Else

12 if(b.unit<=100)then
13 dbms_output.put_line(b.name||''||b.address||' '||b.city||' '||b.unit||' '||b.unit*1);
14 elsif(b.unit>100andb.unit<=200)then
15 dbms_output.put_line(b.name||' '||b.address||' '||b.city||' '||b.unit||'
'||b.unit*2);
16 elsif(b.unit>200andb.unit<=300)then
17 dbms_output.put_line(b.name||' '||b.address||' '||b.city||' '||b.unit||'
'||b.unit*3);
18 elsif(b.unit>300andb.unit<=400)then
19 dbms_output.put_line(b.name||''||b.address||''||b.city||' '||b.unit||' '||b.unit*4);
20 else
21 dbms_output.put_line(b.name||''||b.address||''||b.city||' '||b.unit||' '||b.unit*5);

21
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

22 endif;
23 endif;
24 endloop;
25 closec;
26 end;
27 /

NameAddress cityUnit Amount

yuva srivi srivilliputur 100 100
NithyaLakshminagar sivakasi 200 400
Maya housingboard sivakasi 300 900
jeeva RRRnagar sivaganagai 400 1600

PL/SQLproceduresuccessfullycompleted.

RESULT:

Thus the creation of cursor has been executed successfully.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Ex. No: 5a Study of PL/SQL block.

AIM

To study about PL/SQL block in database management systems

DESCRIPTION

PL/SQL PROGRAMMING

Procedural Language/Structured Query Language (PL/SQL) is an extension of SQL.

Basic Syntax of PL/SQL

DECLARE
/* Variables can be declared here */
BEGIN
/* Executable statements can be written here */
EXCEPTION
/* Error handlers can be written here. */
END;

Steps to Write & Execute PL/SQL

 As we want output of PL/SQL Program on screen, before Starting writing anything type (Only)
 To write program, use Notepad through Oracle using ED command.
SQL> ED ProName
Type the program Save & Exit.
 To Run the program
SQL> @ProName

Decision making with IF statement :-

The general syntax for the using IF—ELSE statement is
IF(TEST_CONDITION) THEN
SET OF STATEMENTS
ELSE
SET OF STATEMENTS
END IF;

For Nested IF—ELSE Statement we can use IF--ELSIF—ELSE as follows

IF(TEST_CONDITION) THEN
SET OF STATEMENTS
ELSIF (CONDITION)
SET OF STATEMENTS
END IF;

LOOPING STATEMENTS:-
For executing the set of statements repeatedly we can use loops. The oracle supports number of
looping statements like GOTO, FOR, WHILE & LOOP.
Here is the syntax of these all the types of looping statements.

GOTO STATEMENTS
<<LABEL>>
SET OF STATEMENTS

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

GOTO LABEL;

FOR LOOP
FOR <VAR> IN [REVERSE] <INI_VALUE>..<END_VALUE>
SET OF STATEMENTS
END LOOP;

WHILE LOOP
WHILE (CONDITION) LOOP
SET OF STATEMENTS
END LOOP;

LOOP STATEMENT
LOOP
SET OF STATEMENTS
IF (CONDITION) THEN
EXIT
SET OF STATEMENTS
END LOOP;
While using LOOP statement, we have take care of EXIT condition, otherwise it may go
into infinite loop.

1. TO DISPLAY HELLO MESSAGE

SQL> set serveroutput on;

SQL> declare
2 a varchar2(20);
3 begin
4 a:='Hello';
5 dbms_output.put_line(a);
6 end;
7/

Hello

PL/SQL procedure successfully completed.

2. Insert the record into Sailors table by reading the values from the Keyboard.
SQL> create table sailors(sid number(10),sname varchar(10),rating number(10),age
number(10));

Table created.

SQL> set serveroutput on

SQL> declare
2 sidnumber(10):=&sid;
3 snamevarchar(10):='&sname';
4 rating number(10):=&rating;
5 age number(10):=&age;
6 begin
7 insert into sailors values(sid,sname,rating,age);
8 end;
9/

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Enter value for sid: 02

old 2: sidnumber(10):=&sid;
new 2: sidnumber(10):=02;
Enter value for sname: lavanya
old 3: snamevarchar(10):='&sname';
new 3: snamevarchar(10):='lavanya';
Enter value for rating: 01
old 4: rating number(10):=&rating;
new 4: rating number(10):=01;
Enter value for age: 25
old 5: age number(10):=&age;
new 5: age number(10):=25;

PL/SQL procedure successfully completed.

SQL> /
Enter value for sid: 03
old 2: sidnumber(10):=&sid;
new 2: sidnumber(10):=03;
Enter value for sname: vani
old 3: snamevarchar(10):='&sname';
new 3: snamevarchar(10):='vani';
Enter value for rating: 02
old 4: rating number(10):=&rating;
new 4: rating number(10):=02;
Enter value for age: 25
old 5: age number(10):=&age;
new 5: age number(10):=25;

PL/SQL procedure successfully completed.

SQL> select * from sailors; AGE

SID SNAME RATING

2 lavanya 1 25
3 vani 2 25
RESULT

Thus the PL/SQL block has been studied and implemented.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Ex. No: 5b Creation of Procedures.

AIM

To write PL/SQL programs that executes the concept of procedures.

DEFINITION

A procedure or function is a logically grouped set of SQL and PL/SQL statements that perform a
specific task. They are essentially sub-programs. Procedures and functions are made up of,

• Declarative part

• Executable part

• Optional exception handling part

These procedures and functions do not show the errors.

KEYWORDS AND THEIR PURPOSES

REPLACE: It recreates the procedure if it already exists.

PROCEDURE: It is the name of the procedure to be created.

ARGUMENT: It is the name of the argument to the procedure. Paranthesis can be omitted if no
arguments are present.

IN: Specifies that a value for the argument must be specified when calling the procedure ie. used to
pass values to a sub-program. This is the default parameter.

OUT: Specifies that the procedure passes a value for this argument back to it’s calling environment after
execution ie. used to return values to a caller of the sub-program.

INOUT: Specifies that a value for the argument must be specified when calling the procedure and that
procedure passes a value for this argument back to it’s calling environment after execution.

RETURN: It is the datatype of the function’s return value because every function must return a value, this
clause is required.

PROCEDURES – SYNTAX

create or replace procedure <procedure name> (argument {in,out,inout} datatype ) {is,as}

variable declaration;

constant declaration;

begin

PL/SQL subprogram body;

exception

exception PL/SQL block; end;

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

CREATING THE TABLE ‘ITITEMS’ AND DISPLAYING THE CONTENTS

SQL> create table ititems(itemid number(3), actualprice number(5), ordid number(4), prodid number(4));

Table created.

SQL> insert into ititemsvalues(101, 2000, 500, 201);

1 row created.

SQL> insert into ititemsvalues(102, 3000, 1600, 202);

1 row created.

SQL> insert into ititemsvalues(103, 4000, 600, 202);

1 row created.

SQL> select * from ititems;

ITEMID ACTUALPRICE ORDID PRODID

101 2000 500 201

102 3000 1600 202

103 4000 600 202

PROGRAM FOR GENERAL PROCEDURE – SELECTED RECORD’S PRICE IS INCREMENTED BY

500, EXECUTING THE PROCEDURE CREATED AND DISPLAYING THE UPDATED TABLE

SQL> create procedure itsum(identity number, total number) is price number;

2 null_price exception;
3 begin
4 select actualprice into price from ititems where itemid=identity;
5 if price is null then
6 raise null_price;
7 else
8 update ititems set actualprice=actualprice+total where itemid=identity;
9 end if;
10 exception
11 when null_price then
12 dbms_output.put_line('price is null');
13 end;
14 /

Procedure created.

SQL> exec itsum(101, 500);

PL/SQL procedure successfully completed.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

SQL> select * from ititems;

ITEMID ACTUALPRICE ORDID PRODID

101 2500 500 201

102 3000 1600 202

103 4000 600 202

PROCEDURE FOR ‘IN’ PARAMETER – CREATION, EXECUTION

SQL> set serveroutput on;

SQL> create procedure yyy (a IN number) is price number;

2 begin

3 select actualprice into price from ititems where itemid=a;

4 dbms_output.put_line('Actual price is ' || price);

5 if price is null then

6 dbms_output.put_line('price is null');

7 end if;

8 end;

9/

Procedure created.

SQL> exec yyy(103);

Actual price is 4000

PL/SQL procedure successfully completed.

PROCEDURE FOR ‘OUT’ PARAMETER – CREATION, EXECUTION

SQL> set serveroutput on;

SQL> create procedure zzz (a in number, b out number) is identity number;

2 begin
3 select ordid into identity from ititems where itemid=a;
4 if identity<1000 then
5 b:=100;
6 end if;
7 end;
8/

Procedure created.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

SQL> declare

2 a number;

3 b number;

4 begin

5 zzz(101,b);

6 dbms_output.put_line('The value of b is '|| b);

7 end;

8/

The value of b is 100

PL/SQL procedure successfully completed.

PROCEDURE FOR ‘INOUT’ PARAMETER – CREATION, EXECUTION

SQL> create procedure itit( ainout number) is

2 begin

3 a:=a+1;

4 end;

5/

Procedure created.

SQL> declare
2 a number:=7;
3 begin
4 itit(a);
5 dbms_output.put_line(‘The updated value is ‘||a);
6 end;
7/

The updated value is 8

PL/SQL procedure successfully completed.

RESULT

The PL/SQL programs were executed and their respective outputs were verified.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Ex. No: 5C Creation of database functions

AIM

To study and implement the concepts of functions.

FUNCTIONS – SYNTAX
create or replace function <function name> (argument in datatype,……) return datatype {is,as}
variable declaration;
constant declaration;
begin
PL/SQL subprogram body;
exception
exception PL/SQL block;
end;

CREATE THE TABLE ‘ITTRAIN’ TO BE USED FOR FUNCTIONS

SQL>create table ittrain( tno number(10), tfare number(10));

Table created.

SQL>insert into ittrain values (1001, 550);

1 row created.

SQL>insert into ittrain values (1002, 600);

1 row created.

SQL>select * from ittrain;

TNO TFARE

1001 550

1002 600

TO CREATE THE TABLE ‘ITEMPLS’

SQL> create table itempls (ename varchar2(10), eidnumber(5), salary number(10));

Table created.

SQL> insert into itempls values('xxx',11,10000);

1 row created.

SQL> insert into itempls values('yyy',12,10500);

1 row created.

SQL> insert into itempls values('zzz',13,15500);

1 row created.

SQL> select * from itempls;

ENAME EID SALARY

Xxx 11 10000
30
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

Yyy 12 10500

Zzz 13 15500

PROGRAM FOR FUNCTION AND IT’S EXECUTION

SQL> create function aaa (trainnumber number) return number is

2 trainfunctionittrain.tfare % type;

3 begin
4 select tfare into trainfunction from ittrain where tno=trainnumber;
5 return(trainfunction);
6 end;
7/

2 total number;
3 begin
4 total:=aaa (1001);
5 dbms_output.put_line('Train fare is Rs. '||total);
6 end;
7/

Train fare is Rs.550

PL/SQL procedure successfully completed.

FACTORIAL OF A NUMBER USING FUNCTION — PROGRAM AND EXECUTION

SQL> create function itfact (a number) return number is
2 fact number:=1;
3 b number;
4 begin
5 b:=a;
6 while b>0
7 loop
8 fact:=fact*b;
9 b:=b-1;
10 end loop;
11 return(fact);
12 end;
13 /

2 a number:=7;
3 f number(10);
4 begin
5 f:=itfact(a);
6 dbms_output.put_line(‘The factorial of the given number is’||f);
7 end;
8/
The factorial of the given number is 5040
PL/SQL procedure successfully completed.

RESULT
Thus the functions were created, executed and their respective outputs were verified.

30
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

Ex. No: 6 Creation of database triggers

AIM

To study and implement the concepts of triggers.

DEFINITION
 A trigger is a statement that is executed automatically by the system as a side effect of
a modification to the database. The parts of a trigger are,
 Trigger statement: Specifies the DML statements and fires the trigger body. It also specifies the
table to which the trigger is associated.
 Trigger body or trigger action: It is a PL/SQL block that is executed when the
triggering statement is used.
 Trigger restriction: Restrictions on the trigger can be achieved
The different uses of triggers are as follows,
• To generate data automatically
• To enforce complex integrity constraints
• To customize complex securing authorizations
• To maintain the replicate table
• To audit data modifications

TYPES OF TRIGGERS

The various types of triggers are as follows,

• Before: It fires the trigger before executing the trigger statement.

• After: It fires the trigger after executing the trigger statement.

• For each row: It specifies that the trigger fires once per row.

• For each statement: This is the default trigger that is invoked. It specifies that the trigger fires
once per statement.

VARIABLES USED IN TRIGGERS

• :new

• :old

These two variables retain the new and old values of the column updated in the database.
The values in these variables can be used in the database triggers for data manipulation

TRIGGERS - SYNTAX
create or replace trigger triggername [before/after] {DML statements}

on [tablename] [for each row/statement] begin

exception
end;

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

TO CREATE A SIMPLE TRIGGER THAT DOES NOT ALLOW INSERT UPDATE

AND DELETE OPERATIONS ON THE TABLE

SQL> create trigger ittrigg before insert or update or delete on itempls for each row

2 begin

3 raise_application_error(-20010,'You cannot do manipulation');

4 end;

6/

Trigger created.

SQL> insert into itempls values('aaa',14,34000);

insert into itempls values('aaa',14,34000)

* ERROR

at line 1:

ORA-20010: You cannot do manipulation

ORA-06512: at "STUDENT.ITTRIGG", line 2

ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'

SQL> delete from itempls where ename='xxx';

delete from itempls where ename='xxx'

* ERROR

at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'
SQL> update itempls set eid=15 where ename='yyy';
update itempls set eid=15 where ename='yyy'
*
ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'

TO DROP THE CREATED TRIGGER

SQL> drop trigger ittrigg;

Trigger dropped.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

TO CREATE A TRIGGER THAT RAISES AN USER DEFINED ERROR MESSAGE AND DOSE
NOT ALLOW UPDATION AND INSERTION

SQL> create trigger ittriggs before insert or update of salary on itempls for each row
2 declare
3 triggsalitempls.salary%type;
4 begin
5 select salary into triggsal from itempls where eid=12;
6 if(:new.salary>triggsal or :new.salary<triggsal) then
7 raise_application_error(-20100,'Salary has not been changed');
8 end if;
9 end;
10 /

Trigger created.

SQL> insert into itempls values ('bbb',16,45000);

insert into itempls values ('bbb',16,45000)
* ERROR
at line 1:
ORA-04098: trigger 'STUDENT.ITTRIGGS' is invalid and failed re-validation
SQL> update itempls set eid=18 where ename='zzz';
update itempls set eid=18 where ename='zzz'
*
ERROR at line 1:
ORA-04298: trigger 'STUDENT.ITTRIGGS' is invalid and failed re-validation

RESULT

Thus the Triggers were created, executed and their respective outputs were verified.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Ex. No: 7 PL/SQL block that handles all types of exceptions.

AIM:

To write a PL/SQL program with exception handling mechanisms.

DESCRIPTION:

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known
as exception Handling. Using Exception Handling we can test the code and avoid it from
exiting abruptly.

When an exception occurs amessages which explains its cause is recieved.

PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message

General Syntax for coding the exception section

DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;

Program with user defined exception:

SQL> DECLARE
2 N INTEGER:=&N;
3 A EXCEPTION;
4 B EXCEPTION;
5 BEGIN
6 IF MOD(N,2)=0 THEN
7 RAISE A;
8 ELSE
9 RAISE B;
10 END IF;
11 EXCEPTION
12 WHEN A THEN
13 DBMS_OUTPUT.PUT_LINE('THE INPUT IS EVEN.....')
14 WHEN B THEN
15 DBMS_OUTPUT.PUT_LINE('THE INPUT IS ODD.....');
16 END;
17 /

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Enter value for n: 20

old 2: N INTEGER:=&N;
new 2: N INTEGER:=20;
THE INPUT IS EVEN.....

PL/SQL procedure successfully completed.

SQL> /
Enter value for n: 21
old 2: N INTEGER:=&N;
new 2: N INTEGER:=21;
THE INPUT IS ODD.....

PL/SQL procedure successfully completed.

Program with system defined exception:

Divide by zero exception:

SQL> DECLARE

2 L_NUM1 NUMBER;

3 L_NUM2 NUMBER;

4 BEGIN

5 L_NUM1 := 10;

6 L_NUM2 := 0;

7 DBMS_OUTPUT.PUT_LINE('RESULT:'||L_NUM1/L_NUM2);

8 EXCEPTION
9 WHEN ZERO_DIVIDE THEN

10 DBMS_OUTPUT.PUT_LINE(SQLCODE);

11 DBMS_OUTPUT.PUT_LINE(SQLERRM);

12 END;

13 /

-1476

ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

RESULT

Thus the PL/SQL program that handles exception has been implemented and output was verified.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Ex. No: 8 Database Design using ER modeling, normalization and Implementation

AIM: To Create the Database Design Using ER Modeling, Normalization and Implementation.

NORMALIZATION

DEFINITION
Normalization is the analysis of functional dependencies between attributes/data
items of user views. It reduces a complex user view to a set of small and stable subgroups of the
fields and relations. This process helps to design a logical data model known as conceptual data
model.

First Normal Form(1NF)

1NF states that the domain of an attribute must include only atomic (simple,
indivisible) values and that value of any attribute in a tuple must be a single value from the domain
of that attribute.
Attributes must be atomic:
– they can be chars, ints, strings
– they can’t be
1. _ tuples
2. _ sets
3. _ relations
4. _ composite
5. _ multivalued

E-R DIAGRAM

First Normal Form

Epnorm 1

Eno Eadd Sal

Ename

Country
Sno
Snamer City

ALGORITHM:

FIRST NORMAL FORM:

1. Create a type address for the composite address attribute.

SQL>create or replace type address as object
(sno number (3),sname varchar2(30),city varchar2(20),country varchar2(20));

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

2. Create a employee table with the following fields eno,ename,eadd,sal and having the
eno as the primary key.

SQL>Create table emp(eno number(3) primary key,ename varchar2(20),eadd

address,sal number(7,2));
SQL> desc employees
Name Null? Type
ENO NOT NULL NUMBER(3)
ENAME VARCHAR2(20)
EADD ADDR
SAL NUMBER(7,2)

3. Insert values in the emp table

insert into emp values(&eno,'&ename',address
(&sno,'&sname','&city','&country'),&sal);
SQL> insert into employees
values(&eno,'&enmae',addr(&sno,'&sname','&city','&state'),&sal);
Enter value for eno: 001
Enter value for enmae: anbu
Enter value for sno: 12
Enter value for sname: Ist street
Enter value for city: chennai
Enter value for state: tamilnadu
Enter value for sal: 10000
old 1: insert into employees
values(&eno,'&enmae',addr(&sno,'&sname','&city','&state'),&sal)
new 1: insert into employees values(001,'anbu',addr(12,'Ist
street','chennai','tamilnadu'),10000)

1 row created.
4. Emp table is not in the first normal form since it has a composite attribute.So it has been
normalized to first normal form.

BEFORE NORMALIZATION :

Eno Ename Eadd Sal

Normalization To First Normal Form:

1. creating the en11 table with eno,ename and esal from emp;
SQL>create table en11 as select eno,ename,sal from emp;
2. creating the table en12 with eno and eadd from emp
SQL>create table en12 as select eno,eadd from emp;
3. altering the table en11 with primary key on eno
SQL>alter table en11 add constraint k1 primary key(eno);
4. altering the table en12 with foreign key on eno with reference from en11
SQL>alter table en12 add constraint c1 foreign key(eno) references en11(eno)

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

After Normalization:

en11 en12
Eno Enam Sal Eno Ead d
e Functional Dependencies (FDs)
Functional dependency describes the relationship between attributes in a relation.

For example, if A and B are attributes of relation R, and B is functionally dependent on A (denoted
A B), if each value of A is associated with exactly one value of B. (A and B may each consist of
one or more attributes.)

Trivial functional dependency means that the right-hand side is a subset ( not necessarily a proper
subset) of the left- hand side.

Second Normal Form(2NF)

A relation is said to be in 2NF if it is already in 1NF and it has no partial dependency.2NF

is based on the concept of full functional dependency.

A functional dependency(FD) XY is full functional dependency if(X-(A))Y does not

hold dependency any more if AX.

A functional dependency XY is partial dependency if A can be removed which does not
affect the dependency i.e. (X-(A))Y holds.

Second Normal Form

Epnorm2

Pname Hours
Eno Ename Pno

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

SECOND NORMAL FORM:

1. Creating the emp project table

SQL> create table epnorm2(eno number(3) primary key,pno number(3) unique,pname

varchar2(20),hours number(3),ename varchar2(20))
2. checking the table

SQL> desc epnorm2

Name Null? Type

ENO NOT NULL NUMBER(3)

PNO NUMBER(3)
PNAME VARCHAR2(20)
HOURS NUMBER(3)
ENAME VARCHAR2(20)

3. inserting the values in the table;

insert into epnorm2 values(&eno,&pno,'&pname',&hours,'&ename')
example of insertion

SQL> insert into epnorm2 values(&eno,&pno,'&pname',&hours,'&ename')

Enter value for eno: 1

Enter value for pno: 101
Enter value for pname: Sharma
Enter value for hours: 75
Enter value for ename: Aravind
old 1: insert into epnorm2 values(&eno,&pno,'&pname',&hours,'&ename')
new 1: insert into epnorm2 values(1,101,'Sharma',75,'Aravind')
1 row created.

4. To normalize the above table to second normal form.

Before Normalization

En Enam pn pnam hour

o e o e s

Normalization To Second Normal Form

a) create the table en21 with eno,ename from the table epnorm2

SQL> create table en21 as select eno,ename from epnorm2;

Table created.

40
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

b) Create the table en22 with pno,pname from table epnorm2

SQL> create table en22 as select pno,pname from epnorm2;

Table created.

c) Alter table en21 with a primary key constraint on eno.

SQL> alter table en21 add constraint en21 primary key(eno);

Table altered.

d) Alter table en22 with a primary key constraint on pno.

SQL> alter table en22 add constraint en22 primary key(pno);

Table altered.

e) Create table en23 with eno,pno and hours from the table epnorm2.

SQL> create table en23 as select eno,pno,hours from epnorm2;

Table created.

f) Alter table en23 with a foreign key on eno with references on eno from en21

SQL> alter table en23 add constraint en231 foreign key(eno) references en21(eno);

Table altered.

g) Alter table en23 with foreign key on pno with references on pno from en22

SQL> alter table en23 add constraint en232 foreign key(pno) references en22(pno);

Table altered.

After Normalization

en21 En
o ENAM E

en22
pnPnam
o e

en23
en Pn Hour
o o s

41
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

Third Normal Form(3NF)

A relation is said to be in 3NF if it is already in 2NF and it has no transitive

dependency.

A FD XY in a relation schema R is a transitive dependency if there is a set of

attributes Z that is neither a candidate key nor a subset of any key of the relation and both XZ
and ZY hold.

Third Normal Form

Sal
Epname Dno

Eno
Dname

Epnorm3

THIRD NORMAL FORM

1. create the table emp_dept with eno,ename,sal,dno and dname as attributes.

SQL> create table emp_dept(eno number(3) primary key,ename varchar2(20),sal

number(7,2),dno number(3),dname varchar2(20));

Table created.

2. insert the values in the table.

SQL> insert into emp_dept values(&eno,'&ename',&sal,&dno,'&dname');

Example record

SQL> insert into emp_dept values(&eno,'&ename',&sal,&dno,'&dname')

Enter value for eno: 1
Enter value for ename: Mr. Brown
Enter value for sal: 10000
Enter value for dno: 1
Enter value for dname: cse
old 1: insert into emp_dept values(&eno,'&ename',&sal,&dno,'&dname')
new 1: insert into emp_dept values(1,'Mr. Brown',10000,1,'cse')

1 row created.
3. The relation does not satisfy the 3rd normal form since dno is not a primary key. So
normalization is done for the third normal form.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Before Normalization

Empdept

Enenamsadndnam oeloe

Normalization To Third Normal Form

a) Create table en31 with eno,ename,sal,dno from the table emp_dept.

SQL> create table en31 as select eno,ename,sal,dno from emp_dept

Table created.

b) Create table en32 with dno,dname from the table emp_dept.

SQL> create table en32 as select dno,dname from emp_dept;

Table created.

c) Alter the table en31 with the constraint primary key on eno.

SQL> alter table en31 add constraint en31 primary key(eno);

Table altered.

d) Alter table en32 with the constraint primary key on dno;

SQL> alter table en32 add constraint en32 primary key(dno);

Table altered.

e) Alter table en31 with the constraint foreign key on dno with reference from dno
in en32
SQL> alter table en31 add constraint en311 foreign key(dno) references
en32(dno)
Table altered.

After Normalization

en31
En enam Sa dn
o e l o

en32
Dno dname

RESULT:

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Thus the study of Database Design Using ER Modeling and Normalization was successfully
implemented

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Ex.No :9 RAILWAY RESERVATION SYSTEM

(Database Connectivity with Front End Tools)
AIM:
To create a Database Connectivity with Front End Tools.

DESCRIPTION:
Railway Reservation System is used to book the train tickets.
It has one form
1. Reservation form
Reservation form:
It allows to book the train ticket by selecting the train id and specifying the travel
date and no. of seats to be booked. The information entered is stored in the database.
DATABASE TABLES
TRAIN TABLE
create table train
(
trainid INT PRIMARY KEY,
travels VARCHAR2(50),
departure VARCHAR2(20),
arrival VARCHAR2(20),
duration INT,
seats int,
fare int
);

INSERT INTO train(trainid,travels,departure,arrival,duration,seats,fare)

VALUES(1,'VOLVO','11AM','2PM',3,50,0;

INSERT INTO train(trainid,travels,departure,arrival,duration,seats,fare)

VALUES(2,'RATHIMEENA','9AM','1PM',4,48,350);

INSERT INTO train(trainid,travels,departure,arrival,duration,seats,fare)VALUES

(3,'PARVEEN','8 AM','1 PM',5,45,750);

TRAINBOOKING TABLE

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

46
 lOMoARcPSD|47775205

create table trainbooking

(
trainid INT PRIMARY KEY,
availableseats INT
);

insert into trainbooking(trainid,availableseats)values(1,50); insert

into trainbooking(trainid,availableseats)values(2,48); insert into
trainbooking(trainid,availableseats)values(3,45);

CODING :
MAIN FORM
package e.ticketing;
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt)
{
//TODO add your handling code here: this.setVisible(false);
new Reservation().setVisible(true)
}

RESERVATION FORM
package e.ticketing;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
public class Reservation extends javax.swing.JFrame { int ttrainid,fare,avail;
public Reservation() {
initComponents();
private void jrathimeenaActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:

try
{ Class.forName("oracle.jdbc.OracleDriver");
Connection
conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","heme
sh","123");
String sql="select * from train where trainid=2";
PreparedStatement pst=conn.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
if(rs.next())
{
ttrainid =rs.getInt("trainid");

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

fare =rs.getInt("fare");
}
String sql1="select * from trainbooking where trainid=2";
PreparedStatement pst1=conn.prepareStatement(sql1);
ResultSet rs1=pst1.executeQuery();
if(rs1.next())
{
avail =rs1.getInt("AVAILABLESEATS");
availseat.setText(String.valueOf(avail));
}
}
catch (Exception e)
{
JOptionPane.showMessageDialog(null, e);
}
}

private void jparveenActionPerformed(java.awt.event.ActionEvent evt) {

//TODO add your handling code here: Try
{
Class.forName("oracle.jdbc.OracleDriver");
Connectionconn=
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","hemesh","1
23");
String sql="select * from train where trainid=3";
PreparedStatement pst=conn.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
if(rs.next()){
ttrainid =rs.getInt("trainid"); fare
=rs.getInt("fare");
}
String sql1="select * from trainbooking where trainid=3";
PreparedStatement pst1=conn.prepareStatement(sql1);
ResultSet rs1=pst1.executeQuery();
if(rs1.next()){
avail =rs1.getInt("AVAILABLESEATS");
availseat.setText(String.valueOf(avail));
}
}
catch (Exception e)
{
JOptionPane.showMessageDialog(null, e);
}
}

private void jvolvoActionPerformed(java.awt.event.ActionEvent evt) {

// TODO add your handling code here: Try
{
Class.forName("oracle.jdbc.OracleDriver");
Connectionconn=
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","hemesh","1
23");
String sql="select * from train where trainid=1";

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

PreparedStatement pst=conn.prepareStatement(sql);
ResultSet rs=pst.executeQuery(); if(rs.next()){
ttrainid =rs.getInt("trainid");
fare =rs.getInt("fare");
}
String sql1="select * from trainbooking where trainid=1";
PreparedStatement pst1=conn.prepareStatement(sql1);
ResultSet rs1=pst1.executeQuery();
if(rs1.next())
{
avail =rs1.getInt("AVAILABLESEATS");
availseat.setText(String.valueOf(avail));
}
}catch (Exception e)
{
JOptionPane.showMessageDialog(null, e);
}
}

private void jconfirmseatsActionPerformed(java.awt.event.ActionEvent evt) {

//TODO add your handling code here:
try
{
int noseats=Integer.parseInt(jTextField1.getText()); int tfare=fare*noseats;
JOptionPane.showMessageDialog(null,"Your Booking Fare Amount: "+tfare);
if(avail>noseats)

{ Class.forName("oracle.jdbc.OracleDriver");
Connectionconn=
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
"hemesh", "123");
String sql="update trainbooking set availableseats=? where trainid=?";
PreparedStatement pst=conn.prepareStatement(sql); //create a statement
pst.setInt(1,avail-noseats); pst.setInt(2,ttrainid);
pst.executeUpdate();
JOptionPane.showMessageDialog(null,"Booking Confirmed");

}
else
{ JOptionPane.showMessageDialog(null,"Sorry Seats not Available");

}
}

catch (Exception e)
{
JOptionPane.showMessageDialog(null,e);
}
}
RESULT:
Thus the project was successfully created, executed and verified.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

Ex. No. 10 INVENTORY CONTROL SYSTEM

AIM:
To create a mini project named Inventory Control System.
DESCRIPTION:
Inventory Control System is a project which allows to maintain the stocks and
sell the products and update the stock.
It has three forms
• Main Menu form
• Stock Form.
• Sales Form
Main Menu Form :
It allows to choose the option whether stock entry or sales entry.
Stock Form:
It allows to enter the product id, product name, quantity, unit price and reorder
value.
Sales Form:
It allows to sell the product by choosing the product id and specifying the sales
quantity. It checks whether the sales quantity is less than or equal to available
quantity and also checks whether the remaining quantity after sales is lesser
than reorder level. If so, it disallows sales.
The information entered is stored in the database.
DATABASE TABLES:
STOCK TABLE
CREATE TABLE stock
(
prodid INT PRIMARY KEY,
prodname VARCHAR2(50),
quantity INT,
unitprice INT,
reorder int
);
SALES TABLE
CREATE TABLE sale
(
prodid INT REFERENCES stock(prodid),
prodname VARCHAR2(50),
unitprice INT,
salesqty INT,
datetime VARCHAR2(50)
);

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

50
 lOMoARcPSD|47775205

SAMPLE CODING:

STOCK ENTRY FORM

STOCK ENTRY:

package conn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import javax.swing.JOptionPane;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
public class stockentry extends javax.swing.JFrame {

Connection conn=null;
OraclePreparedStatement pst=null;
OracleResultSet rs=null;

private void btnInsert_clickActionPerformed(java.awt.event.ActionEvent evt) {

//TODO add your handling code here:
try
{
Class.forName("oracle.jdbc.OracleDriver"); Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
"hemesh", "123");
String sql=” Insert into
stock(prodid,prodname,quantity,unitprice,reorder)values(?,?,?,?,?)";

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

51
 lOMoARcPSD|47775205

PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1,txt_prodid.getText());
pst.setString(2,txt_prodname.getText());
pst.setString(3,txt_quantity.getText());
pst.setString(4,txt_unitprice.getText());
pst.setString(5,txt_reorder.getText());
pst.execute();
JOptionPane.showMessageDialog(null,"Successfully Inserted");
}
catch (Exception e)
{
JOptionPane.showMessageDialog(null, e);
}
}

private void btnUpdate_clickActionPerformed(java.awt.event.ActionEvent evt) {

//TODO add your handling code here: try
{
Class.forName("oracle.jdbc.OracleDriver"); Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
"hemesh", "123");
String sql="update stock set
prodname=?,quantity=?,unitprice=?,reorder=? where prodid=?";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1,txt_prodname.getText());
pst.setString(2, txt_quantity.getText()); pst.setString(3,
txt_unitprice.getText()); pst.setString(4, txt_reorder.getText());
pst.setString(5,txt_prodid.getText()); pst.executeUpdate();
JOptionPane.showMessageDialog(null,"Successfully Updated");

}
catch (Exception e)
{
JOptionPane.showMessageDialog(null,e);
}

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)

 lOMoARcPSD|47775205

STOCK SALES FORM

CODING: STOCK SALES

package stock;
import java.sql.Connection;
import java.util.Date;
import java.sql.DriverManager;
import java.text.SimpleDateFormat;
import javax.swing.JOptionPane;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class stocksale extends javax.swing.JFrame {
public stocksale() {
initComponents();
additems();
}
private void btnsell_clickActionPerformed(java.awt.event.ActionEvent evt) {
//TODO add your handling code here: Try
{
Date d = new Date();
SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("dd-MM-yyyy 'at'
HH:mm:ss a");
String date = DATE_FORMAT.format(d);
int i=Integer.parseInt(txt_salesqty.getText());
Class.forName("oracle.jdbc.OracleDriver") Connectionconn=
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hemesh",
"123");

String sql="update stock set quantity=quantity-'"+i+"' where prodid=?";

PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1,jComboBox1.getSelectedItem().toString()); pst.executeUpdate();
Stringsql1="Insertintosale(prodid,prodname,unitprice,salesqty,datetime)
values(?,?,?,?,?)";
PreparedStatement pst1=conn.prepareStatement(sql1);
pst1.setInt(1,Integer.parseInt(jComboBox1.getSelectedItem().toString()));
pst1.setString(2, txt_prodname.getText());

pst1.setInt(3,Integer.parseInt( txt_unitprice.getText()));
pst1.setInt(4, Integer.parseInt(txt_salesqty.getText()));
53
Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)
 lOMoARcPSD|47775205

pst1.setString(5,date);
pst1.execute();
JOptionPane.showMessageDialog(null, "Sucessfully Inserted");
}
catch (Exception e)
{
JOptionPane.showMessageDialog(null, e);
}
}

private void jComboBox1ItemStateChanged(java.awt.event.ItemEvent evt) {

//TODO add your handling code here:
try
{
Class.forName("oracle.jdbc.OracleDriver");
Connectionconn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521 :XE",
"hemesh", "123");
String sql="select * from stock where prodid=?";
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1, jComboBox1.getSelectedItem().toString());
ResultSet rs=pst.executeQuery();
if(rs.next())
{
txt_prodname.setText(rs.getString("prodname"));
txt_unitprice.setText(rs.getString("unitprice"));
txt_salesqty.setText(rs.getString("salesqty"));
}
}
}

public void additems()

{
tr
y Class.forName("oracle.jdbc.OracleDriver");
{ Connectionconn=
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
"hemesh", "123");
String sql="select prodid from stock";
PreparedStatement pst=conn.prepareStatement(sql);
ResultSet rs=pst.executeQuery();
while(rs.next())
{
jComboBox1.addItem(rs.getInt("prodid"));
}
}
}

RESULT
Thus the project was successfully created, executed and verified.

Downloaded by Shanmathi Balamurugan (shanmathi0905@gmail.com)