Ex.No.03 PL/SQL FOR CURSOR
Aim:-
Introduction:-
Cursor is a memory area created by oracle which is used to store the table data temporarily while we manipulate them in Oracle.
Description:-
- Create a table for student and insert the appropriate values in the table database.
- Then create a cursor with cursor name, and this cursor name refers all the fields of the student table.
- Then declare the required variables.
- Now start the definition of the cursor using begin statement and open the cursor.
- Check whether the cursor opened or not. If it is opened display the message “Cursor Opened…..”
- Define the loop for encountered the student table and fetch the required output value into the table.
- Then count the fetched values in the student table and print the output in the display monitor.
- Finally close the cursor.
Before Creating PL/SQL:-
Oracle Table:-
SQL> create table student(regno number(7),dbms number(3),ethics number(3),dpsd number(3),total number(3),average number(3));
SQL> insert into student(regno,dbms,ethics,dpsd) values (®no,&dbms,ðics,&dpsd);
SQL> update student set total=0;
SQL> update student set average=0;
Default Table Contents:-
SQL> select * from student;
REGNO DBMS ETHICS DPSD TOTAL AVERAGE
————— ————- ————- ————- ———— ————–
404072 90 90 90 0 0
404071 95 95 95 0 0
404073 85 85 85 0 0
404075 88 88 88 0 0
404074 92 92 92 0 0
PL/SQL:-
SQL> set serveroutput on
SQL> declare
2 cursor stu is select regno,dbms,ethics,dpsd from student;
3 rno student.regno%type;
4 m1 student.dbms%type;
5 m2 student.ethics%type;
6 m3 student.dpsd%type;
7 tot student.total%type;
8 av student.average%type;
9 begin
10 open stu;
11 if stu%ISOPEN then
12 dbms_output.put_line(‘Cursor Opened…’);
13 loop
14 fetch stu into rno,m1,m2,m3;
15 exit when stu%NOTFOUND;
16 tot:=(m1+m2+m3);
17 av:=tot/3;
18 update student set average=av where regno=rno;
19 update student set total=tot where regno=rno;
20 end loop;
21 dbms_output.put_line(‘Total Records :’ ||stu%ROWCOUNT);
22 close stu;
23 dbms_output.put_line(‘Cursor Closed…’);
24 end if;
25 end;
26 /
Cursor Opened…
Total Records : 5
Cursor Closed…
SQL>
Output:-
SQL> select * from student;
REGNO DBMS ETHICS DPSD TOTAL AVERAGE
————— ————— ————— —————- ————— —————-
404072 90 90 90 270 90
404071 95 95 95 285 95
404073 85 85 85 255 85
404075 88 88 88 264 88
404074 92 92 92 276 92
SQL>
Result:-
Thus the above cursor in PL/SQL program was performed and verified successfully.
Ex.No.04 PL/SQL FOR TRIGGERS
Aim:-
Introduction:-
A database triggers is a stored procedure that is implicitly executed when an insert, update or delete statement is issued against the associated table. Database triggers can be used for the following purposes.
- To generate data automatically
- To prevent invalid transaction
- To enforce complex security authorizations
- To enforce referential integrity
- To maintain synchronous table replicates
- To gather statistics on table access
Description:-
- Create two tables named voters_master and voters_passed with appropriate data types to illustrate the functioning of the triggers.
- Insert the required values in the voters_master table.
- Write a proper PL/SQL block to delete the details of the voters whenever a row is inserted in the table voters_passed.
- Select the table and see now, the particular row has been deleted.
Before Creating PL/SQL:-
Oracle Table:-
SQL> create table voters_master(voter_id number(5),name varchar2(30), ward_no number(4),dob date,address varchar2(30), primary key(voter_id, ward_no));
SQL> create table voters_passed(voter_id number(5),ward_no number(4), primary key(voter_id,ward_no));
Default Table Contents:-
SQL> select * from voters_master;
VOTER_ID NAME WARD_NO DOB ADDRESS
————— —————– —————- ————— ——————-
1 saradha p 1 12-MAR-67 nagarcoil-4
2 janarthan m 5 01-FEB-78 kattur
3 vasu l 5 31-DEC-79 vadasery
SQL> select * from voters_passed;
no rows selected.
Trigger Function:-
SQL> create or replace trigger vote_trig after insert on voters_passed for each row
2 declare
3 v_id number(5);
4 w_id number(4);
5 begin
6 v_id:=&voter_id;
7 w_id:=&ward_no;
8 delete from voters_master where voter_id=v_id and ward_no=w_id;
9 end;
10 /
Trigger created.
Output:-
Insert value into voters_passed:-
SQL> insert into voters_passed values(1,1);
After Executing Trigger:-
SQL> select * from voters_master;
VOTER_ID NAME WARD_NO DOB ADDRESS
————— —————- —————– —————– ——————-
2 janarthan m 5 01-FEB-78 kattur
3 vasu l 5 31-DEC-79 vadasery
SQL> select * from voters_passed;
VOTER_ID WARD_NO
————— —————
- 1
Result:-
Thus the above triggers in PL/SQL program was performed and verified successfully.
Ex.No.05(A) PL/SQL FOR PROCEDURE
Aim:-
Introduction:-
A procedure is a subprogram the performs a specific action and accepts more than one argument and returns more than one value.
Description:-
- Create a table named order_master and insert the appropriate values in the database table.
- Now create the procedure named by proc with the required arguments
- Select the item code, ordered quantity and delivered quantity from the table and store them in the declared variables.
- Check if delivered quantity is less than the ordered quantity.
- If delivered quantity < ordered quantity, return item code to the called procedure.
- In the calling procedure declare the two variables and get the input for one, then call the procedure with the arguments.
- Finally print the output statement in the display monitor.
Before Creating PL/SQL:-
Oracle Table:-
SQL> create table order_master(qty_ord number(5),qty_del number(5),itemcode number(3),ordno number(2));
Default Table Contents:-
SQL> select * from order_master;
QTY_ORD QTY_DEL ITEMCODE ORDNO
————- ————- ————— ————
100 75 101 1
70 70 102 2
Create Procedure:-
SQL> create procedure proc(or_no in number,b in out number) is
2 qtyord number;
3 qtydel number;
4 code number;
5 begin
6 select qty_ord,qty_del,itemcode into qtyord,qtydel,code from order_master where ordno=or_no;
7 if qtydel<qtyord then
8 b:=code;
9 end if;
10 end;
11 /
Procedure created.
Calling Procedure:-
SQL> declare
2 a number;
3 b number;
4 begin
5 a:=&enter_ordno;
6 proc(a,b);
7 if b>0 then
8 dbms_output.put_line(‘The item code ‘||to_char(b)||’ has to be delivered’);
9 else
10 dbms_output.put_line(‘The item has been delivered’);
11 end if;
12 end;
13 /
Output:-
SQL> set serveroutput on
SQL> /
Enter value for enter_ordno: 2
old 5: a:=&enter_ordno;
new 5: a:=2;
The item has been delivered.
PL/SQL procedure successfully completed.
SQL> /
Enter value for enter_ordno: 1
old 5: a:=&enter_ordno;
new 5: a:=1;
The item code 101 has to be delivered.
PL/SQL procedure successfully completed.
Result:-
Thus the above procedure in PL/SQL program was performed and verified successfully.
Ex.No.05(B) PL/SQL FOR FUNCTIONS
Aim:-
Introduction:-
A function is a subprogram that accepts more than one argument and returns only one value. In function, RETURN keyword is used to the give a value to the PL/SQL program. Function can be called as many times as we need.
Description:-
- Create a table named order_master and insert the appropriate values in the database table.
- Now create the function named by items with the required arguments
- Select the ordered quantity and delivered quantity from the table and store them in the declared variables.
- Check if delivered quantity is less than the ordered quantity.
- If delivered quantity < ordered quantity, return 0, else return 1 to the called procedure.
- In the calling function declare the two variables and get the input for one, then call the function with the arguments.
- Finally print the output statement in the display monitor.
Before Creating PL/SQL:-
Oracle Table:-
SQL> create table order_master(qty_ord number(5),qty_del number(5),itemcode number(3),ordno number(2));
Default Table Contents:-
SQL> select * from order_master;
QTY_ORD QTY_DEL ITEMCODE ORDNO
————- ————- ————— ————
100 75 101 1
70 70 102 2
Create Function:-
SQL> create function items(it number)return number is args number;
2 qtyord number;
3 qtydel number;
4 begin
5 select qty_ord,qty_del into qtyord,qtydel from order_master where ordno=it;
6 if qtydel<qtyord then
7 args:=0;
8 return args;
9 else
10 args:=1;
11 return args;
12 end if;
13 end;
14 /
Function created.
Calling Function:-
SQL> declare
2 a number;
3 b number;
4 begin
5 a:=&enter_ordno;
6 b:=items(a);
7 if b=0 then
8 dbms_output.put_line(‘The item has to be delivered.’);
9 else
10 dbms_output.put_line(‘The item has been delivered.’);
11 end if;
12 end;
13 /
Output:-
SQL> set serveroutput on
SQL> /
Enter value for enter_ordno: 1
old 5: a:=&enter_ordno;
new 5: a:=1;
The item has to be delivered.
PL/SQL procedure successfully completed.
SQL> /
Enter value for enter_ordno: 2
old 5: a:=&enter_ordno;
new 5: a:=2;
The item has been delivered.
PL/SQL procedure successfully completed.
Result:-
Thus the above function in PL/SQL program was performed and verified successfully.
Ex.No.06 EMBEDDED SQL
Aim:-
To write a program in java to retrieve the data from the database.
Hardware Requirements:-
- Pentium III 600 MHz.
- 256 Mb RAM
- 14 inch color monitor.
- 101 keys keyboard
- 20 GB hard disk
- 3 button mouse
Software Requirements:-
- JDK 1.3.
- MS-ACCESS.
Concept:-
Definition:-
The SQL structures permitted in the host language (i.e. The language in which the SQL queries can be embedded) are called as EMBEDDED SQL.
Features:-
- Not all Queries can be expressed in SQL. These Queries can be embedded in languages like C, JAVA, or COBOL that cannot be resolved in SQL.
- SQL does not support actions like printing a report, interacting with a user, or sending the results of a query to a graphical user interface. By Embedding the SQL in the host language these actions can be performed.
Algorithm:-
- Start.
- Create a Student table in MS-ACCESS.
- Create a Data Source Name using MS-ACCESS Driver.
- Establish a connection from Java MS-ACCESS Database using JDBC:ODBC Driver
- Embed the SQL statements like Insert and Select statements in the JAVA program.
- Compile and Run the JAVA program.
- Stop.
Program:-
import java.io.*;
import java.sql.*;
public class jdeg
{
public static void main(String args[])throws IOException
{
BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
String rno,name,dept,mark;
System.out.println(“enter the value(rno,name,dept,mark)to be inserted”);
rno=br.readLine();
name=br.readLine();
mark=br.readLine();
dept=br.readLine();
try
{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con=DriverManager.getConnection(“jdbc:odbc:student”);
Statement st=con.createStatement();
st.executeUpdate(“insert into student values(‘”+rno+”‘,'”+name+”‘,'”+dept+”‘,'”+mark+”‘)”);
ResultSet rs=st.executeQuery(“select * from student”);
while(rs.next())
{
System.out.println(rs.getString(“rno”));
System.out.println(rs.getString(“name”));
System.out.println(rs.getString(“dept”));
System.out.println(rs.getString(“mark”));
}
}
catch(Exception e)
{
System.out.println(e);
}
}
}
Output:-
D:\jdk1.3\bin>javac jdeg.java
D:\jdk1.3\bin>java jdeg
enter the values(rno,name,dept,marks) to insert into the table
3
aravind
cse
90
RNO NAME DEPT MARKS
1 Achelal cse 90
2 Ahamath cse 100
3 aravind cse 90
IN MS-ACCESS:
Result:-
Thus a program is written to retrieve the data from the database.
Ex.No.07 NORMALIZATION
Aim:-
To design a database using E-R diagram and normalization.
Hardware Requirements:-
- Pentium III 600 MHz.
- 256 Mb RAM
- 14 inch color monitor.
- 101 keys keyboard
- 20 GB hard disk
- 3 button mouse
Software Requirements:-
- Oracle 8i server.
CONCEPT AND DEFINITION
Normalization:-
Normalization is the analysis of functional dependencies between attributes/data itemsa of user views. It reduces a complex user view to a set of small and stable subgroups of the fields amd relations. This process helps to design a logical data model known as conceptual data model.
There are different normal forms
- First normal form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
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.Hence 1NF disallows multi-valued attributes,composite attributes.It disallows “relations within relations”.
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 AeX.
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.
A relation is in 2NF if it is in 1NF and every non-primary key attribute is fully and functionally dependent on primary key.
A relation in the 1NF will be in the 2NF if one of the following conditions is satisfied:
The primary key consist of only one attribute.
No non-key attribute exist in relation i.e. all the attributes in the relation are components of the primary key.
Every non-key attribute is FD on full set of primary key attributes.
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.
E-R DIAGRAMS
First Normal Form
| Epnorm 1 | |||
 |
Second Normal Form
| Epnorm2 |
Third Normal Form
 |
ALGorithm
FIRST NORMAL FORM
- Create a type address for the composite address attribute.
create or replace type address as object
(sno number(3),sname varchar2(30),city varchar2(20),country varchar2(20));
- Create a employee table with the following fields eno,ename,eadd,sal and having the eno as the primary key.
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)
ENMAE VARCHAR2(20)
EADD ADDR
SAL NUMBER(7,2)
- 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.
5. 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
- creating the en11 table with eno,ename and esal from emp;
create table en11 as select eno,ename,sal from emp;
- creating the table en12 with eno and eadd from emp
create table en12 as select eno,eadd from emp;
- altering the table en11 with primary key on eno
alter table en11 add constraint k1 primary key(eno);
- altering the table en12 with foreign key on eno with reference from en11
alter table en12 add constraint c1 foreign key(eno) references en11(eno)
After Normalization
| Eno | Eadd |
En11 en12
| Eno | Ename | Sal |
second normal form
- 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))
- 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)
- 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.
- To normalize the above table to second normal form.
Before Normalization
| Eno | Ename | pno | pname | hours |
 |
Normalization To Second Normal Form
- create the table en21 with eno,ename from the table epnorm2
SQL> create table en21 as select eno,ename from epnorm2;
Table created.
- Create the table en22 with pno,pname from table epnorm2
SQL> create table en22 as select pno,pname from epnorm2;
Table created.
- Alter table en21 with a primary key constraint on eno.
SQL> alter table en21 add constraint en21 primary key(eno);
Table altered.
- Alter table en22 with a primary key constraint on pno.
SQL> alter table en22 add constraint en22 primary key(pno);
Table altered.
- 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.
- 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.
- 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
| Eno | ename |
En22
| pno | Pname |
En23
| eno | Pno | Hours |
third normal form
- 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.
- 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.
- 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.
Before Normalization
Empdept
 Eno | ename | sal |  dno | dname |
Normalization To Third Normal Form
- 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.
- Create table en32 with dno,dname from the table emp_dept.
SQL> create table en32 as select dno,dname from emp_dept;
Table created.
- Alter the table en31 with the constraint primary key on eno.
SQL> alter table en31 add constraint en31 primary key(eno);
Table altered.
- Alter table en32 with the constraint primary key on dno;
SQL> alter table en32 add constraint en32 primary key(dno);
Table altered.
- 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
| Eno | ename | sal | dno |
En32
| Dno | Dname |
INPUT / OUTPUT
FIRST NORMAL FORM
SQL> select * from emp;
ENO ENAME EADD(SNO, SNAME, CITY, COUNTRY) SAL
1 Ahamath ADDRESS(45, ‘cross street’, ‘Chennai’, ‘India’) 50000
2 Bhaylool ADDRESS(46, ‘Cross Street’, ‘Chennai’, ‘India’) 90000
SQL> select * from en11;
ENO ENAME SAL
1 Ahamath 50000
2 Bhaylool 90000
SQL> select * from en12;
ENO EADD(SNO, SNAME, CITY, COUNTRY)
1 ADDRESS(45, ‘cross street’, ‘Chennai’, ‘India’)
- ADDRESS(46, ‘Cross Street’, ‘Chennai’, ‘India’)
second normal form
SQL> select * from epnorm2;
ENO ENAME PNO PNAME HOURS
1 Achelal 5 axe 8
2 Sharma 3 SETWET 8
SQL> select * from en21;
ENO ENAME
1 Achelal
- Sharma
SQL> select * from en22;
PNO PNAME
5 AXE
- SETWET
SQL> select * from en23;
ENO PNO HOURS
1 5 8
2 3 8
third normal form
SQL> select * from epnorm3;
ENO ENAME SAL DNO DNAME
1 Ahamath 50000 2 HR
2 Bhaylool 90000 5 R&D
SQL> select * from en31
ENO ENAME SAL DNO
1 Ahamath 50000 2
2 Bhaylool 90000 5
SQL> select * from ed2;
DNO DNAME
2 HR
- R&D
Result:-
Thus the database was designed using E-R diagrams and Normalizations.
There is no universally accepted definition of a mountain. Elevation, volume, relief, steepness, spacing and continuity have been used as criteria for defining a mountain.