112717104037

CS8481-DATABASE MANAGEMENT SYSTEMS LABORATORY

LIST OF EXPERIMENTS

1. Data Definition Commands, Data Manipulation Commands for inserting, deleting, updating

and retrieving Tables and Transaction Control statements

2. Database Querying – Simple queries, Nested queries, Sub queries and Joins

3. Views, Sequences, Synonyms

4. Database Programming: Implicit and Explicit Cursors

5. Procedures and Functions

6. Triggers

7. Exception Handling

8. Database Design using ER modeling, normalization and Implementation for any application

9. Database Connectivity with Front End Tools

10. Case Study using real life database applications

1
 112717104037

EX.NO: 1 DATA DEFINITION COMMANDS, DATA MANIPULATION COMMANDS

FOR INSERTING, DELETING, UPDATING AND RETRIEVING TABLES
AND TRANSACTION CONTROL STATEMENTS
DATE:

AIM:
To create a database and to write SQL queries to retrieve information from the database and
executing transaction control statements in RDBMS.

DESCRIPTION:
DEFINITIONS AND SYNTAX:
SQL:
SQL stands for Structured Query Language. SQL is composed of commands that enable users to
create database and table structures, perform various types of data manipulation and data
administration, and query the database to extract useful information.
DDL COMMANDS:
The language used to define the database schema is called Data Definition language. DDL is
used to create, update and drop the database views. The Commands used in DDL are
 CREATE
 ALTER
 DROP
CREATE TABLE:
This command is used to create database tables in RDBMS.
Syntax
CREATE TABLE table_name (colname1 datatype, colname2 datatype, colname3
datatype, …..…colnamen datatype);
ALTER TABLE:
This command is used to add or drop or modify the attributes from the existing table.
Syntax
Adding an attribute:
ALTER TABLE <table_name> ADD column_name datatype;
2
 112717104037

Dropping an attribute:
ALTER TABLE <table_name> DROP COLUMN column_name;
Modifying an attribute:
ALTER TABLE <table_name> MODIFY column_name newdatatype;
DROP TABLE:
This command is used to remove a relation from an SQL database. This command deletes not
only the records of the table but also the entire schema of the database.
Syntax:
DROP TABLE <table_name>;
DML COMMANDS:
 Insert
 Select
 Update
 Delete
INSERT:
Insert command is used to insert the values into the table. There are three ways to insert a record
into the database.
1. Inserting Data’s in specified columns:
Syntax
INSERT INTO table_name(col1,col2,…….,coln) VALUES(val1,val2,……,valn);
2. Inserting Values:
Syntax
INSERT INTO table_name VALUES(val1,val2,……...,valn);
3. Inserting Multiple Rows:
Syntax
INSERT INTO tablename VALUES(&col1,’&col2’,’&col3’,…….,&coln);
SELECT:
This statement is used to retrieve the information from the database. The SELECT statement can
be used in many ways. They are:
1. Selecting some columns:
To select specified number of columns from the table the following command is used.
3
 112717104037

Syntax
SELECT column name FROM table_name;
2. Query All Columns:
To select all columns from the table * is used instead of column names.
Syntax
SELECT * FROM table_name;
UPDATE:
UPDATE command is used to change a value of a record in the database.
Syntax:
UPDATE tablename SET column name =new value WHERE columnname=some value;
DELETE:
The DELETE statement is used to delete rows in a table.
Syntax:
DELETE FROM table_name WHERE column name=some value;
TCL COMMANDS:
SAVE POINT
As changes are made in a transaction, we can create SAVEPOINTs to mark different points
within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way
back to the beginning of the transaction.
Create a Save point:
Syntax
Savepoint <Save point name>;
PARTIAL ROLLBACK:
Syntax
Rollback to < Save point name>;
EXERCISES USING DDL COMMANDS
1. Create the following table with the given specifications.
a. Table name: Department
NAME TYPE
DNO NUMBER(4)
DNAME VARCHAR2(20)
Command for creating Table:

4
 112717104037

SQL> create table department(dno number(4),dname varchar2(20) );

SQL> desc department;
OUTPUT:

b. Table name: EMPLOYEE

Command for creating the table:
SQL> create table employee(eid number(4), name varchar2(20), dob date, addr varchar2(50),
pin number(10), sex char, desig varchar2(15), deptno number(4), salary number(8,2));
SQL> desc employee;
OUTPUT:

2. Add one additional field called manager to the department table

Command for adding additional field:
SQL> alter table department add manager varchar2(25);
SQL> desc department;
NAME NULL? TYPE
----------------------------------------- -------- ----------------------------
DNO NUMBER(4)
DNAME VARCHAR2(20)

5
 112717104037

MANAGER VARCHAR2(25)

OUTPUT:

3. Command for changing the data type of an attribute:

SQL> alter table employee modify PIN NUMBER(6);
SQL> desc employee;
OUTPUT:

4. Delete the field PIN from the EMPLOYEE table.

Command to delete the field PIN
SQL> alter table employee drop column pin;
SQL> desc employee;

6
 112717104037

OUTPUT:

5. Delete the department table

Command to drop the department table
SQL> drop table department;
OUTPUT:

EXERCISES USING DML COMMANDS:

SQL> create table student (stud_id number (3), stud_name varchar2(15), stud_dept varchar2(5));
SQL> desc student;
OUTPUT:

7
 112717104037

1. INSERTING VALUES INTO THE TABLE:

SQL> insert into studentlists values (001,’ ABHI’,’ CSE’);
SQL> insert into studentlists values (002,’ ARUN’,’ ECE’);
SQL> insert into studentlists values (002,’ ARUN’,’ ECE’);
OUTPUT:

2. SELECTING THE TABLE

SQL> select * from studentlists;
OUTPUT:

3. UPDATE VALUES IN THE TABLE:

SQL> update studentlists set stud_dept='mech' where stud_id=3;
SQL> select * from studentlists;
OUTPUT:

8
 112717104037

4. DELETE ONE OR MORE ROWS FROM A TABLE:

SQL> delete from studentlists where stud_id=1;
SQL> select * from studentlists;
OUTPUT:

EXERCISES USING TCL COMMANDS:

SAVE POINT:
SQL> insert into studentlists values(1,'XXX','MCA');
SQL>savepoint S1;
SQL> select * from studentlists;
OUTPUT:

SQL> insert into studentlists values(4,'YYY','MBA');

SQL>savepoint S2;
SQL> select * from studentlists;
SQL> rollback to S1;
SQL> select * from studentlists;

9
 112717104037

OUTPUT:

RESULT:
Thus the database is created and the information is well retrieved using SQL in RDBMS.

10
 112717104037

EX.NO:2 DATABASE QUERYING – SIMPLE QUERIES, NESTED QUERIES, SUB

QUERIES AND JOIN QUERIES
DATE:

AIM:
To create database using Simple queries, Nested Queries, Sub queries and Join Queries
in RDBMS.

DESCRIPTION:
NESTED QUERIES:
Nesting of queries one within another is known as a nested queries.
Example:
SQL> select emp_no,e_name, job from empp where sal>(select sal from empp where e_name=
'ravi');
OUTPUT:

SUBQUERIES
The query within another is known as a subquery. A statement containing subquery is called
parent statement. The rows returned by subquery are used by the parent statement.
OUTPUT:

Correlated subquery
A subquery is evaluated once for the entire parent statement whereas a correlated subquery is
evaluated once per row processed by the parent statement.
11
 112717104037

Example:
Select * from empp x where x.salary > (select avg(salary) from empp where empno
=x.empno);

TYPES OF JOINS
1. Simple Join
2. Self Join
3. Outer Join
1. SIMPLE JOIN
It is the most common type of join. It retrieves the rows from 2 tables having a common
Column and is further classified into

SQL> select * from emp;

OUTPUT:

SQL> select * from dept;

OUTPUT:

A) EQUI-JOIN
A join, which is based on equalities, is called equi-join.
Example:

12
 112717104037

select * from item, cust where item.id=cust.id;

In the above statement, item-id = cust-id performs the join statement. It retrieves rows from both
the tables provided they both have the same id as specified by the where clause. Since the where
clause uses the comparison operator (=) to perform a join, it is said to be equijoin. It combines
the matched rows of tables.

SQL> select * from empl, dept where empl.deptno=dept.deptno;

OUTPUT:

B) NON EQUI-JOIN
It specifies the relationship between columns belonging to different tables by making use of
relational operators other than’=’.

SQL> select * from empl, dept where empl.deptno!=dept.deptno;

OUTPUT:

13
 112717104037

2. SELF JOIN
Joining of a table to itself is known as self-join. It joins one row in a table to another. It can
compare each row of the table to itself and also with other rows of the same table.

SQL>Select x.deptno, y.deptno from empl x, dept y where x.deptno=y.deptno;

OUTPUT:

3.OUTER JOIN
It extends the result of a simple join. An outer join returns all the rows returned by simple join as
well as those rows from one table that do not match any row from the table. The symbol(+)
represents outer join.

SQL>select ename, job, dname from empl, dept where empl.deptno (+) =dept.deptno;
OUTPUT:

Different SQL JOINs

 INNER JOIN: Returns all rows when there is at least one match in BOTH tables
 LEFT JOIN: Return all rows from the left table, and the matched rows from the right
table

14
 112717104037

 RIGHT JOIN: Return all rows from the right table, and the matched rows from the left
table
 FULL JOIN: Return all rows when there is a match in ONE of the tables.
SQL INNER JOIN
The INNER JOIN keyword selects all rows from both tables as long as there is a match between
the columns in both tables.
SQL> select * from product;
OUTPUT:

SQL>Select * from order_items;

OUTPUT:

SQL> select Order_id,Product_name,Unit_price,Supplier_name,Total_units from

product,order_items where order_items.Product_id=product. Product_id;
OUTPUT:

OUTER JOIN
15
 112717104037

It extends the result of a simple join. An outer join returns all the rows returned by simple join as
well as those rows from one table that do not match any row from the table.
The symbol(+) represents outer join.

LEFT OUTER JOIN

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no
matches in the right table (table_name2)

SYNTAX:
SQL> SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON
table_name1.column_name=table_name2.column_name

SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o LEFT

JOIN product p ON o.Product_id=p. Product_id;
(OR)
SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o,product
p where o.Product_id(+)=p. Product_id;

OUTPUT:

RIGHT OUTER JOIN

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are
no matches in the left table (table_name1).

SYNTAX:

16
 112717104037

SQL> SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o RIGHT
JOIN product p ON o.Product_id=p. Product_id;
(OR)
SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o,product
p where o.Product_id=p. Product_id(+);
OUTPUT:

FULL JOIN
The FULL JOIN keyword return rows when there is a match in one of the tables.
SYNTAX:
SQL>SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON
table_name1.column_name=table_name2.column_name

SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o FULL

OUTER JOIN product p ON o.Product_id=p. Product_id;
OUTPUT:

17
 112717104037

RESULT:
Thus the database table using simple queries, Nested and Join query have been written
and executed successfully.
EX.NO :3 CREATION OF VIEWS, SYNONYMS AND SEQUENCES
18
 112717104037

DATE:

AIM:
To create Views , Synonyms ,Sequences, commands in database using SQL queries in
RDBMS.

DESCRIPTION:
CREATING VIEWS:
A view is a virtual table based on the result-set of an SQL statement. A view contains rows and
columns, just like a real table. The fields in a view are fields from one or more real tables in the
database.
Syntax
create view view_name as select column_name(s) from table_name where condition;
Updating a View
View can be updated. If view is already created with same name it will be replaced by create or
replace statement (or) if view already doesn’t exist, it will be created.
Syntax
SQL>create or replace view view_name as select column_name(s) from table_name where
condition;
Dropping a View
Deleting a view can be done with the DROP VIEW command.
Syntax :SQL>drop view view_name;

CREATING SYNONYMS
A synonym is an alternative name for objects such as tables, views, sequences, stored
procedures, and other database objects.
Syntax :
SQL>create or replace synonym synonym_name for table_name;
Drop synonym
Used to drop the synonym that has been created .
Syntax :
19
 112717104037

SQL>Drop public synonym synonym_name;

SEQUENCES
In Oracle, you can create an auto number field by using sequences. A sequence is an
object in Oracle that is used to generate a number sequence.

CREATING SEQUENCES
Syntax
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
Drop Sequence
Once you have created your sequence in Oracle, you might find that you need to remove it from
the database.
Syntax
SQL>DROP SEQUENCE sequence_name;
sequence_name is the name of the sequence that you wish to drop.

EXERCISES:
1. CREATING VIEWS
SQL> create view emp1 as select * from emp where salary>10000;
SQL> select * form emp1;
OUTPUT:

2. DROP VIEWS
20
 112717104037

SQL>drop view emp1;

OUTPUT:

3. CREATE SYNONYM:
SQL>create synonym synonym1 for emp1;
SQL>drop public synonym employee;

4. CREATE SEQUENCES
SQL>create sequence emp_seq
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;
OUTPUT:

5. DROP SEQUENCES
21
 112717104037

SQL>drop sequence emp_seq;

OUTPUT:

RESULT:
Thus Views, Synonyms, Sequence have been created and updated in RDBMS.

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

22
 112717104037

DATE:

AIM:
To create a database using Implicit and Explicit Cursors.

DESCRIPTION:
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.
There are two types of cursors
 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.
 Whenever a 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.
 In PL/SQL, we can refer to the most recent implicit cursor as the SQL cursor, which
always has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and
%ROWCOUNT.
 The SQL cursor has additional attributes, %BULK_ROWCOUNT and
%BULK_EXCEPTIONS, designed for use with the FORALL statement.

EXPLICIT CURSORS
 Explicit cursors are programmer-defined cursors for gaining more control over the
context area.
23
 112717104037

 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.
The syntax for creating an explicit cursor is
CURSOR cursor_name IS select_statement;

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement.
For example
CURSOR c_customers IS
SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows
returned by the SQL statement into it.
For example, we will open the above defined cursor as follows −
OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time.
For example, we will fetch rows from the above-opened cursor as follows −
FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory.
For example, we will close the above-opened cursor as follows −
CLOSE c_customers;

EXERCISES:
SQL>create table customers(id varchar(10), name varchar(10),age varchar(10),address
varchar(10),salary varchar(10));

24
 112717104037

SQL>insert into customers values(&id,’&name’,&age,’&address’,&salary);

OUTPUT:

IMPLICIT CURSORS
To update the table and increase the salary of each customer by 500 and use the SQL
%ROWCOUNT attribute to determine the number of rows affected :

PL/SQL PROGRAM :
SQL> 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;
/

OUTPUT:

25
 112717104037

EXPLICIT CURSORS
SQL> DECLARE
c_id customers.id%type;
c_name customers.name%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;
/
OUTPUT:

26
 112717104037

RESULT:
Thus the database have been created and executed successfully by using Implicit and
Explicit Cursors.

EX.NO :5 CREATION OF PROCEDURES AND FUNCTIONS

27
 112717104037

DATE:

AIM:
To study and execute the procedures and functions using control structures in PL/SQL.

DESCRIPTION:
BASIC TERMINOLOGIES:
PL/SQL
PL/SQL stands for Procedural Language extension of SQL.PL/SQL is a combination
of SQL along with the procedural features of programming languages. It was developed by
Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

PL/SQL Engine
Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be
stored in the client system (client-side) or in the database (server-side).

PL/SQL Block consists of three sections:

 The Declaration section (optional).
 The Execution section (mandatory).
 The Exception (or Error) Handling section (optional).

Syntax PL/SQL Block

DECLARE
     Variable declaration
BEGIN
     Program Execution
EXCEPTION
     Exception handling
END;

KEYWORDS AND THEIR PURPOSES

 REPLACE: It recreates the procedure if it already exists.
 ARGUMENT: It is the name of the argument to the procedure. Parenthesis can be omitted
if no arguments are present.
28
 112717104037

 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 its 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 its calling
environment after execution.
 RETURN: It is the data type of the function’s return value because every function must
return a value, this clause is required.

PROCEDURES
A procedure is a subprogram that performs a specific action.
Syntax for creating a procedure:
create or replace procedure <Proc_name> [parameter list] is
<local declarations>;
begin
(executable statements)
[exception] (exception handlers)
end;

FUNCTIONS
A function is a subprogram that computes a value.
SYNTAX
create or replace function <function_name> [argument]
return datatype is
(local declaration)
begin
(exectable statements)
[Exception]
(exception handlers)
end;
29
 112717104037

where arguments can be in, out or inout.

EXERCISES:
1) To create a procedure to display the salary of the specified employee from employee table

PROCEDURE FOR IN PARAMETER – CREATION, EXECUTION

SQL> create procedure proc13(A in number)is
sal number(7,2);
begin
select salary into sal from emp where id=A;
dbms_output.put_line('Salary is'||sal);
end;
/
SQL> set serveroutput on;
SQL> exec proc13(11);

OUTPUT:

2) To create a PL/SQL program which calls a procedure to print 0 if salary < 15000 else
print 1.

PROCEDURE FOR IN-OUT PARAMETER – CREATION, EXECUTION

SQL> select * from emp;
OUTPUT:

30
 112717104037

SQL> create or replace procedure proc11(A in number,B out number) is

sal number;
begin
select salary into sal from emp where id=A;
if(sal<15000)then
B:=0;
else
B:=1;
end if;
end proc11;
/

OUTPUT:

SQL> declare
A number;
B number;
begin
A:=&A;
31
 112717104037

proc11(A,B);
dbms_output.put_line('B value is:'||B);
end;
/
OUTPUT:

3) To create and execute a function to multiply the given number by 1000

SQL> create or replace function f1(n number)return
number is
k number;
begin
k:=n*1000;
return k;
end;
/
SQL> select f1(5) from dual;
SQL> select * from dual;

32
 112717104037

OUTPUT:

4) To create and execute a factorial program using function

SQL> create or replace function f1(n number)return
number is
f number;
begin
f:=1;
for i in 1..n loop
f:=f*i;
end loop;
return f;
end;
/
SQL> select f1(5) from dual;

33
 112717104037

OUTPUT:

RESULT:
Thus the creation of various events such as insertion, updation, etc., and procedures for
functions was created, performed and executed successfully

EX.NO: 06 TRIGGERS
34
 112717104037

DATE:

AIM:
To study and execute Triggers in RDBMS.

DESCRIPTION:
BASIC TERMINOLOGIES:
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 (CREATES, ALTER, or DROP).
 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or
SHUTDOWN).
Triggers could be defined on the table, view, schema, or database with which the event is
associated.

PARTS OF A TRIGGER
A database trigger has three parts, namely, a trigger statement, a trigger body and a
trigger restriction.
Trigger Statement: -
A trigger statement specifies the DML statements like update, delete and insert and it
fires the trigger body. It also specifies the table to which the trigger is associated.
Trigger Body: -
Trigger body is a PL/SQL block that is executed when a triggering statement is issued.
Trigger Restriction: -
Restrictions on a triggers can be achieved using the WHEN clause as shown in the syntax
for creating triggers. They can be included in the definition of a row trigger, where in, the
condition in the WHEN clause is evaluated for each row that is affected by the trigger.
SYNTAX FOR CREATING A TRIGGER
Create or Replace trigger <trigger_name>
35
 112717104037

[before/after] [insert/update/delete] on <table_name>

[for each statement/for each row]
[when <condition>]
PL/SQL block;

EXERCISES:
1) PL/SQL program to create a trigger before the user inserts the data into the table.
SQL> create or replace trigger trig1 before insert on emp
begin
raise_application_error(-20001,'you cant insert a row');
end;
/
SQL> insert into emp values(44,'ashu','cfo','male',30000);

OUTPUT:

2) Write a PL/SQL program to create a trigger before the user deletes the data from the
table.
SQL> create or replace trigger del2 before delete on emp
begin
36
 112717104037

raise_application_error(-20001,'you cant delete a row');

end;
/
SQL> delete from emp where id=22;

OUTPUT:

3) Write a PL/SQL program to create a trigger before the user changes the value of the
salary of an employee

SQL> create trigger upd1 before update on emp for each row
begin
if:new.salary<20000 then
raise_application_error(-20001,'salary cant be lower than this');
end if;
end;
/
SQL> update emp set salary=3000 where id=11;

OUTPUT:

37
 112717104037

RESULT:
Thus the usage of triggers were studied and executed successfully in RDBMS.

EX. NO: 7 EXCEPTION HANDLING

DATE:

38
 112717104037

AIM:
To write a PL/SQL block to handle all types of exceptions.

DESCRIPTION:
EXCEPTION HANDLING

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 messages which explains its cause is received.

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;

TYPES OF EXCEPTION

39
 112717104037

There are 3 types of Exceptions.

a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

A) NAMED SYSTEM EXCEPTIONS

System exceptions are automatically raised by Oracle, when a program violates a
RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-
defined and given a name in Oracle which are known as Named System Exceptions.
For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.
Named system exceptions are:
1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.

B) UNNAMED SYSTEM EXCEPTIONS

Those system exception for which oracle does not provide a name is known as unamed
system exception. These exception do not occur frequently. These Exceptions have a code and an
associated message.

There are two ways to handle unnamed system exceptions:

1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

Steps to be followed to use unnamed system exceptions are

• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicity.
• To handle the exception explicity, they must be declared using Pragma EXCEPTION_INIT as
given above and handled referecing the user-defined exception name in the exception section.

C) USER-DEFINED EXCEPTIONS
40
 112717104037

Apart from sytem exceptions we can explicity define exceptions based on business rules.
These are known as user-defined exceptions.
Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception
section.

RAISE_APPLICATION_ERROR ( )
RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to
display the user-defined error messages along with the error number whose range is in between -
20000 and -20999.
RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.

PRE-DEFINED EXCEPTIONS
PL/SQL provides many pre-defined exceptions, which are executed when any database
rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is
raised when a SELECT INTO statement returns no rows. The following table lists few of the
important pre-defined exceptions.

EXERCISES:
1) PL/SQL program to implement Check Exception Type
SQL> DECLARE
  num_a NUMBER := 6;
           num_b NUMBER;
    BEGIN
num_b := 0;
 num_a := num_a / num_b;
          
41
 112717104037

            dbms_output.put_line(' Value of num_b ' || num_b);
EXCEPTION
           WHEN ZERO_DIVIDE
   THEN
                dbms_output.put_line('Trying to divide by zero');
dbms_output.put_line(' Value of num_a ' || num_a);
              dbms_output.put_line(' Value of num_b ' || num_b);
END;
/
OUTPUT:

2) PL/SQL program to implement When Other Exceptions Then

SQL>  set serveroutput  on;
SQL>  DECLARE        PARENT_ERROR exception;
BEGIN
DECLARE
CHILD_ERROR exception;
BEGIN
raise CHILD_ERROR;
EXCEPTION
WHEN CHILD_ERROR THEN
dbms_output.put_line('nested block exception handler');
42
 112717104037

raise;
END;
EXCEPTION
WHEN PARENT_ERROR THEN
dbms_output.put_line('parent block exception handler');
      WHEN OTHERS THEN
dbms_output.put_line('Caught the OTHERS exception');
     raise;
END;
/
OUTPUT:

3)PL/SQL program to implement Declaration Exception

SQL>  DECLARE
myNumber number default 'MY NUMBER';
 BEGIN
NULL;
 EXCEPTION
WHEN OTHERS THEN
 dbms_output.put_line('Exception caught');
raise;
43
 112717104037

END;
/
OUTPUT:

4) PL/SQL program to display the details of customer using Exception Handling

SQL> select * from customer;
OUTPUT:

SQL> declare
cid customer.c_id%type;
cname customer.c_name%type;
caddr customer.c_addr%type;

44
 112717104037

begin
cid:=&cid;
select c_name,C_addr into cname,caddr
from customer
where c_id=cid;
dbms_output.put_line('Name:'||cname);
dbms_output.put_line('Address:'||caddr);
exception
when no_data_found then
dbms_output.put_line('No such customer!');
when others THEN
dbms_output.put_line('Error!');
end;
/

OUTPUT:

/* for a valid customer id */

45
 112717104037

/* for an Invalid customer id */

RESULT:
Thus the PL/SQL block is created to handle all types of exceptions and are well executed
successfully.

46
 112717104037

EX. NO: 8 DATABASE DESIGN USING ER MODELING, NORMALIZATION AND

IMPLEMENTATION FOR ANY APPLICATION
DATE:

AIM:
To design database using normalization and ER-Diagram.

DESCRIPTION:
NORMALIZATION
 Normalization of data is a process of analyzing the given relation schemas based on their
functional dependencies and primary keys to achieve the desirable properties of
 minimizing redundancy
 minimizing the insertion,deletion and update anomalies.
 A normal form is a state of a table that results from applying simple rules regarding
functional dependencies to that table.The various types of normal form are:
 First Normal Form(1NF): The multi-valued should be removed i.e elimination of
redundant groups. 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.
 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.The
partial functional dependencies have to be removed i.e elimination of redundant data.
 Third Normal Form(3NF): A relation is said to be in 3NF if it is already in 2NF and it
has no transitive dependency. The transitive dependencies have to be removed i.e
elimination of columns not dependent on the key.
 Boyce-Codd Normal Form(BCNF): A relation is said to be in BCNF if it is already in
3NF and every determinant is a candidate key
 Fourth Normal Form(4NF): Multi-valued dependencies are removed i.e isolation of
independent multiple relationships.
 Fifth Normal Form(5NF): Any remaining anomalies are removed. Here we isolate
semantically related multiple relationships.
47
 112717104037

PROCEDURE:
1.Create a type ‘Address’ to represent composite attribute.
2. Create the table
3. Insert the values in the table.
4. Draw the E-R diagram for the table.
5. Convert the given table to the normalized form.

 Converting a table to 1NF:

To convert a table to 1NF removes all the multi valued & composite attributes from the
table.
 Converting a Table to 2NF:
Find and remove attributes that are functionally dependent on only a part of the key and not
on the whole key. Place them in a different table.Group the remaining attributes.

FIRST NORMAL FORM:

SQL>create type address as object(sno number(5),
sname varchar2(20),
city varchar2(20),
state varchar2(20));
/
SQL>create table Employees(eno number(3) primary key,
enmae varchar2(20),
eadd address,
sal number(7,2))
/
SQL> desc employees;

48
 112717104037

OUTPUT:

SQL> insert into employees

values(&eno,'&enmae',address(&sno,'&sname','&city','&state'),&sal);
SQL> select * from employees;

OUTPUT:

Normalizing the table to 1NF:

SQL> create table en1 as select eno, ename ,sal from employees;
SQL> alter table en1 add primary key(eno);

49
 112717104037

SQL> desc en1;

OUTPUT:

SQL> create table en2 as select eno,eadd from employees;

SQL> alter table en2 add foreign key(eno) references en1(eno);
SQL> desc en2;

OUTPUT:

50
 112717104037

Normalizing to 1NF:
employee

Eno Ename Eadd Sal

1NF
en1 en2

Eno Ename Sal Eno eadd

SECOND NORMAL FORM:

SQL> create table empproject(eno number(3) primary key,
ename varchar2(20),
pno number(3) unique,
pname varchar2(20),
hours number(3));
SQL> desc empproject;

OUTPUT:

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

SQL> select * from empproject;

51
 112717104037

Normalizing the table to 2NF:

SQL>create table ep1 as select eno,ename from empproject;
SQL>alter table ep1 add primary key(eno);
SQL> desc ep1;

OUTPUT:

SQL>create table ep4 as select pno,pname from empproject;

SQL>alter table ep4 add primary key(pno);
SQL> desc ep4;

52
 112717104037

OUTPUT:

SQL>create table ep3 as select eno,pno,hours from empproject;

SQL>alter table ep3 add primary key(eno);
SQL>alter table ep3 add unique(pno);
SQL> desc ep3;
OUTPUT:

Normalizing to 2NF:
Eno Ename pno pname hours

 2NF

Ep1 Ep4
eno Ename pno Pname
Ep3
eno pno Hours

53
 112717104037

THIRD NORMAL FORM:

SQL>create table empdept (eno number(3) primary key,
ename varchar2(20),
sal number(7),
dno number(3),
dname varchar2(20));
SQL> desc empdept;

OUTPUT:

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

SQL> select * FROM EMPDEPT;
OUTPUT:

54
 112717104037

Normalizing the table to 3NF:

SQL>create table ed1 as select eno,ename,sal,dno from empdept;
SQL>alter table ed1 add primary key(eno);
SQL> desc ed1;

OUTPUT:

SQL>create table ed2 as select dno,dname from empdept;

SQL>alter table ed2 add primary key(dno);
SQL> desc ed2;
SQL>alter table ed1 add foreign key(dno) references ed2(dno);

OUTPUT:

55
 112717104037

Normalizing to 3NF:
Empdept
Eno ename Sal dno dname
    

 3NF
Ed1
eno ename sal dno

Ed2
Dno Dname

RESULT:

Thus the database was designed successfully using E-R diagram and Normalization.

56
 112717104037

EX. NO: 9 DATABASE CONNECTIVITY WITH FRONT END TOOLS

DATE:

AIM:
To develop a Data Report for the application, ‘Banking System’ using Oracle as a back
end (data base) and Microsoft Visual Basic 6.0 as a Front end.

DESCRIPTION:

HARDWARE REQUIREMENTS:
 Pentium IV Processor
 2 GB RAM

SOFTWARE REQUIREMENTS:
 Windows 7 or above
 Oracle
 Visual Basic 6.0

PROCEDURE:
Creating a Data Environment
1. Start a new Standard EXE project.
2. On the Project menu, click Add Data Environment. If this item is not on the menu, click
Components. Click the Designers tab, and choose Data Environment and click OK to add the

57
 112717104037

designer to your menu.

3. We need to point to our database. In the Data Environment window, right-click the
Connection1 tab and select Properties. In the Data Link Properties dialog box, choose
Microsoft Jet 3.51 OLE DB Provider. Click Next to get to the Connection tab. Click the
ellipsis button. Find your phone database (mdb) file. Click OK to close the dialog box.

58
 112717104037

4. We now tell the Data Environment what is in our database. Right-click the Connection1 tab
and click Rename. Change the name of the tab to “Banking”. Right-click this newly named tab
and click Add Command to create a Command1 tab. Right-click this tab and choose
Properties. Assign the following properties:

59
 112717104037

Command Name: qrybank

Connection: Banking
DataBase Object: Table
Object Name: Bank (object name is your table under banking1 database.

5. Click OK. All this was needed just to connect the environment to our database.

6. Display the properties window and give the data environment a name property of bank. Click
File and Save bank As. Save the environment in an appropriate folder. We will eventually add
this file to our bank database management system. At this point, my data environment window
looks like this (I expanded the qrybank list tab by clicking the + sign): shown below

60
 112717104037

Creating a Data Report

Once the Data Environment has been created, we can create a Data Report. We will drag things
out of the Data Environment onto a form created for the Data Report, so make sure your Data
Environment window is still available.
1. On the Project menu, click Add Data Report and one will be added to your project. If this item
is not on the menu, click Components. Click the Designers tab, and choose Data Report and click
OK to add the designer to your menu.
2. Set the following properties for the report:
Name: Banking
Caption: Banking Database

61
 112717104037

DataSource: Banking (Banking is your data environment name- choose, don’t type)
DataMember: qrybank (the table name - choose don’t type)
3. Right-click the Data Report and click Retrieve Structure. This establishes a report format
based on the Data Environment.
4. Note there are five sections to the data report: a Report Header, a Page Header, a Detail
section, a Page Footer, and a Report Footer. The headers and footers contain information you
want printed in the report and on each page. To place information in one of these regions, right-
click the selected region, click Add Control and then choose the control you wish to place. These
controls are called data report controls and properties are established just like you do for usual
controls. Try adding some headers.
5. The Detail section is used to layout the information you want printed for each record in your
database. We will place the field listings (accno, aname…etc) there. Click on the ‘accno’ tab in
the Data Environment window and drag it to the Detail section of the Data Report. Two items
should appear: a text box ‘accno’ and a text box Name (qrybank). The first text box is heading
information. Move this text box into the Page Header section. The second text box is the actual
value for Name from the PhoneList table. Line this text box up under the Name header. Now,
repeat the same steps to drag the other tab (aname, address, dob, nation, atype, balance) from the
Data Environment to the Data Report. Adjust the text boxes in the same manner. Our data report
will have page headers accno, anameetc. Under these headers, these fields for each record in our
database will be displayed. When done, the form should look something like this:

62
 112717104037

In this form, I’ve resized the labels a bit and added a Report Header. Also, make sure you close
up the Detail section to a single line. Any space left in this section will be inserted after each
entry.
6. Click File and Save bank As. Save the environment in an appropriate folder. We will now
reopen our phone database manager and attach this and the data environment to that project and
add capabilities to display the report.

Accessing the Data Report

1. Add a form to your project and use a command button named “REPORT” and command name
as cmdReport
3. Use this code in cmdReport_Click:

63
 112717104037

Private Sub cmdReport_Click()

bank1.Show ‘ where bank is the datareport name.
End Sub
4. This uses the Show method to display the data report.
5. Save the application and run it. Click the Show Report button and this should appear:

64
 112717104037

You now have a printable copy of the phone directory. Just click the Printer icon. Notice the
relationship with this displayed report and the sections available in the Data Report designer.

RESULT:
Thus the Report for the application, ‘Banking System’ is built by connecting with the
database using front end tools successfully.

65
 112717104037

EX. NO. 10 CASE STUDY USING REAL LIFE DATABASE APPLICATIONS

DATE:

AIM:
To design and implement the case study, ‘Banking System’ using Oracle as a back end
(data base) and Microsoft Visual Basic 6.0 as a Front end.

DESCRIPTION:
PROCEDURE:
1. Create an banking database with the following fields in Oracle namely
acno,name,address,balance and insert some record into the database.
2. Design the corresponding form with labels ,text boxes and command buttons.
Form1  Customer Details
Form2  Withdraw
Form3  Deposit
3. Connect the back end with the front end using DAO method by creating a dsn as follows
a .Select Administrative Tools option from Control Panel . Then click on Data
Sources (ODBC), which displays a dialog box named ODBC
DataSourceAdministrator in which click Add button.
b. In Create New Data Source dialog box, select “Microsoft ODBC for
ORACLE”and click finish button.
c. Give a suitable Data Source Name ,username and server name.
4. Perform the required operations likeext,find,deposit,withdraw,movelast,moveprevious,
searching and Exit.
5. Perform the transaction in banking system.
6. Execute the project.

66
 112717104037

TABLE DESIGN:
Table Name: Banking
Name Type
----------------------------------- ---------------------------
ACC_NO NUMBER(6)
NAME VARCHAR2(30)
ADDRESS VARCHAR2(20)
BALANCE NUMBER(8,2)

FORM 1:
Dim DB As Database
Dim RS As Recordset
Private Sub Form_Load()
Set DB = OpenDatabase("BANKDSN",FALSE,FALSE,”ODBC;UID= ;PWD= ;”)
Set RS = DB.OpenRecordset("SELECT * FROM BANK")
Text1.Text = RS(0)
Text2.Text = RS(1)
Text3.Text = RS(2)
Text4.Text = RS(3)
End Sub

Private Sub CLEAR_Click()

Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
End Sub

Private Sub DELETE_Click()

RS.DELETE
MsgBox "the record is deleted"
67
 112717104037

RS.MoveNext
If RS.EOF Then
MsgBox "no more records"
Else
Text1.Text = RS(0)
Text2.Text = RS(1)
Text3.Text = RS(2)
Text4.Text = RS(3)
End If
End Sub

Private Sub EXIT_Click()

End
End Sub

Private Sub FIRST_Click()

RS.MoveFirst
Text1.Text = RS(0)
Text2.Text = RS(1)
Text3.Text = RS(2)
Text4.Text = RS(3)
End Sub

Private Sub INSERT_Click()

RS.MoveLast
RS.AddNew
RS(0) = Text1.Text
RS(1) = Text2.Text
RS(2) = Text3.Text
RS(3) = Text4.Text
MsgBox "record is inserted"
68
 112717104037

RS.UPDATE
End Sub

Private Sub LAST_Click()

RS.MoveLast
Text1.Text = RS(0)
Text2.Text = RS(1)
Text3.Text = RS(2)
Text4.Text = RS(3)
End Sub

Private Sub NEXT_Click()

RS.MoveNext
If RS.EOF Then
MsgBox "no more records"
Else
Text1.Text = RS(0)
Text2.Text = RS(1)
Text3.Text = RS(2)
Text4.Text = RS(3)
End If
End Sub

Private Sub PREVIOUS_Click()

RS.MovePrevious
If RS.BOF Then
MsgBox "no more records"
Else
Text1.Text = RS(0)
Text2.Text = RS(1)
Text3.Text = RS(2)
69
 112717104037

Text4.Text = RS(3)
End If
End Sub

Private Sub UPDATE_Click()

RS.Edit
RS(0) = Text1.Text
RS(1) = Text2.Text
RS(2) = Text3.Text
RS(3) = Text4.Text
RS.UPDATE
MsgBox "the record is updated"
End Sub

Private Sub FIND_Click()

I = InputBox("ENTER THE ACNO", "FIND")
rs.FindFirst "[ACNO]=" & I
If rs.NoMatch Then
MsgBox "no such records"
Else
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
TEXT4.TEXT=RS(3)
End If
End Sub

Private Sub WITHDRAW_Click()

Form2.Show
End Sub

70
 112717104037

Private Sub DEPOSIT_Click()

Form3.Show
End Sub

FORM 2:
Private Sub HOMEPAGE_Click()
Form1.Show
Form2.Hide
End Sub

Private Sub WITHDRAW_Click()

Text2.Text = Val(Form1.Text4) - Text1.Text
Form1.Text4 = Text2.Text
End Sub

FORM 3:
Private Sub DEPOSIT_Click()
Text2.Text = Val(Form1.Text4) + Text1.Text
Form1.Text4 = Text2.Text
End Sub

Private Sub HOMEPAGE_Click()

Form1.Show
Form3.Hide
End Sub

71
 112717104037

OUTPUT:

72
 112717104037

73
 112717104037

RESULT:
Thus the case study for banking system has been designed and implemented successfully
in visual basic 6.0.
74