Normalisation

NORMAL FORM - 1

SQL> create table em1(ename varchar(20),eid number(38),phone number(38));
Table created.

SQL> select * from em1;

ENAME
EID
PHONE
-------------------- ---------- ----------
A
1 9008763452
B
2 9000783456
C
3 9012376541

SQL> create table em2(ename varchar(20),eid number(38),phone1 number(38),
phone2 number(38));
Table created.

SQL> create table em4(ename varchar(20),eid number(38),phone number(38));
Table created.

SQL> declare
2 cursor c is select * from em1;
3 r1 em1%rowtype;
4 r2 em1%rowtype;
5 r3 em1%rowtype;
6 p1 em1.phone%type;
7 p2 em1.phone%type;
8 begin
9 for r1 in c
10 loop
11 p1:=substr(r1.phone,1,5);
12 p2:=substr(r1.phone,6,10);
13 insert into em2 values(r1.ename,r1.eid,p1,p2);
14 dbms_output.put_line(r1.ename||' '||r1.eid||' '||p1||' '||p2);
15 end loop;
16 dbms_output.put_line('DUPLICATE : ');
17 dbms_output.put_line(r1.ename||' '||r1.eid||' '||r1.phone);
18 for r2 in c
19 loop
20 p1:=substr(r2.phone,1,5);
21 p2:=substr(r2.phone,6,10);
22 if(p1<>0) then
23 dbms_output.put_line(r2.ename||' '||r2.eid||' '||p1);

insert into em4 values(r2.ename,r2.eid,p1);
end if;
if(p2<>0) then
dbms_output.put_line(r2.ename||' '||r2.eid||' '||p2);
insert into em4 values(r2.ename,r2.eid,p2);
end if;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from em2;

ENAME
EID PHONE1 PHONE2
-------------------- ---------- ---------- ----------
A
1
90087
63452
B
2
90007
83456
C
3
90123
76541

SQL> select * from em4;

ENAME
EID
PHONE
-------------------- ---------- ----------
A
1
90087
A
1
63452
B
2
90007
B
2
83456
C
3
90123
C
3
76541

NORMAL FORM - 2

SQL> create table stu(stuid int,stuname varchar(15),profid int,profname
varchar(20),grade varchar(3));
Table created.

SQL>select * from stu;

STUID STUNAME PROFID
PROFNAME GRA
------ --------------- ---------- -------------------- -----------------------
1
A
123
Z
a1
2
B
456
Y
b2
3
C
678
X
c1

SQL> create table stu1(stuid int,stuname varchar(20));
Table created.

SQL> create table stu2(profid int,profname varchar(20));
Table created.

SQL> create table stu3(profname varchar(20),stuname varchar(20),grade
varchar(3));
Table created.

SQL> declare
2 cursor c is select * from stu;
3 r1 stu%rowtype;
4 r2 stu%rowtype;
5 r3 stu%rowtype;
6 begin
7 for r1 in c
8 loop
9 insert into stu1 values(r1.stuid,r1.stuname);
10 dbms_output.put_line(r1.stuid||' '||r1.stuname);
11 end loop;
12 for r2 in c
13 loop
14 dbms_output.put_line(r2.profid||' '||r2.profname);
15 insert into stu2 values(r2.profid,r2.profname);
16 end loop;
17 for r3 in c
18 loop
19 dbms_output.put_line(r3.profname||' '||r3.stuname||' '||r3.grade);

20 insert into stu3 values(r3.profname,r3.stuname,r3.grade);
21 end loop;
22 end;
23 /

PL/SQL procedure successfully completed.

SQL> select * from stu1;

STUID STUNAME
---------- --------------------
1
A
2
B
3
C

SQL> select * from stu2;

PROFID PROFNAME
---------- --------------------
123
Z
456
Y
678
X

SQL> select * from stu3;

PROFNAME
STUNAME GRA
-------------------- -------------------- ---
Z
A
a1
Y
B
b2
X
C
c1

NORMAL FORM - 3

SQL>create table bank(bankname varchar(20),ename varchar(20),address
varchar(20),amount number(38));
Table created.

SQL> select * from bank;

BANKNAME ENAME ADDRESS AMOUNT
-------------------- -------------------- -------------------- ----------
KVB
A
Salem
40000
ICICI
B
Madurai
38000
IOB
C
Erode
50000

SQL>create table detail1(bankname varchar(20),ename varchar(20),amount
number(38));
Table created.

SQL>create table bank(bankname varchar(20),address varchar(20),amount
number(38));
Table created.

SQL> declare
2 cursor c is select * from bank;
3 r1 bank%rowtype;
4 r2 bank%rowtype;
5 p2 bank.amount%type;
6 p1 bank.ename%type;
7 begin
8 for r1 in c
9 loop
10 p1:=r1.ename;
11 insert into detail1 values(r1.bankname,p1,r1.amount);
12 dbms_output.put_line(r1.bankname||' '||p1||' ' ||r1.amount);
13 end loop;
14 for r2 in c
15 loop
16 p2:=r2.amount;
17 if(p2<>0)then
18 dbms_output.put_line(r2.bankname||' ' ||r2.address||' '||p2);
19 insert into detail2 values(r2.bankname,r2.address,p2);
20 end if;
21 end loop;

PL/SQL procedure successfully completed.

SQL> select * from detail1;

BANKNAME ENAME
AMOUNT
-------------------- -------------------- ----------
KVB
A
40000
ICICI
B
38000
IOB
C
50000

SQL> select * from detail2;

BANKNAME ADDRESS
AMOUNT
-------------------- -------------------- ----------
KVB
Salem
40000
ICICI
Madurai
38000
IOB
Erode
50000