Oracle for Beginner: ORDB with Oracle (III)

In this third part of my articles about ORDB with Oracle, I just want to share about a simple example of implementation ORDB in Oracle based on class diagram UML. I want to show you about how to implement aggregation, association and inheritance in Oracle. Here is the class diagram:


Segitiga and Lingkaran class are child class from Bidang class (as parent class). Bidang class has association relationship with Warna class. This association is one direction that means Bidang class has a Warna's type attribute. First, we have to create Warna class:

create type warna as object(
r number(3),
b number(3),
g number(3),
member function getWarna return varchar
);
/

create type body warna as
member function getWarna return varchar is
begin
return 'Red=' || to_char(self.r) || ', Blue=' || to_char(self.b) || ', Green=' || to_char(self.g);
end;
end;
/

Warna class has 3 attributes, i.e. r for Red, b for Blue and g for Green, and 1 operation that will return a varchar for the color. Now, we can create Bidang that will have an attribute as an object for Warna class.

create type Bidang as object(
x number(3),
y number(3),
color warna,
member function getLuas return number,
member function getKeliling return number
) not final;
/

create type body Bidang as
member function getLuas return number is
begin
return 0;
end;
member function getKeliling return number is
begin
return 0;
end;
end;
/


Bidang class has a coordinate attribute that is presented as x and y attribute. Because of Bidang class will become as parent class for Segitiga and Lingkaran, then in Bidang class must provide function getLuas and getKeliling. Why? We have to do this because in Bidang class could save either Bidang, Segitiga, or Lingkaran objects. So, we have to prepare those method in Bidang class. Next, we will create Segitiga class and Lingkaran class:

create or replace type Segitiga under Bidang (
alas number(10,3),
tinggi number(10,2),
overriding member function getLuas return number,
overriding member function getKeliling return number
);
/

create or replace type body Segitiga as
overriding member function getLuas return number is
begin
return 0.5 * self.alas * self.tinggi;
end;
overriding member function getKeliling return number is
begin
return 3 * self.alas;
end;
end;
/

create or replace type Lingkaran under Bidang (
r number(10,3),
overriding member function getLuas return number,
overriding member function getKeliling return number
);
/

create or replace type body Lingkaran as
overriding member function getLuas return number is
begin
return 3.14 * self.r * self.r;
end;
overriding member function getKeliling return number is
begin
return 2 * 3.14 * self.r;
end;
end;
/

As we can look for that command, the methods declaration in Segitiga and Lingkaran class must be declare as OVERRIDING MEMBER FUNCTION, because this method will override parent's methods. To implement 1..* composition relationship between Bidang class and Bangun class, we can create a new type as table of Bidang, and we will declare a new attribut in Bangun class that has type of table of Bidang:

create or replace type bidang_tab as table of bidang;
/

create or replace type Bangun as object(
nama varchar(10),
mybidang bidang_tab)
/

The final command is to create a nested table of Bangun. With this table, we can make CRUD (Create, Read, Update and Delete) for Bidang and Bangun data.

create table bangun_tab of bangun (
primary key (nama)
) nested table mybidang store as mybangun_tab;

Yes, now we can try to insert some records to our BANGUN_TAB nested table with simple INSERT command:

insert into bangun_tab values ('test1', bidang_tab(segitiga(0, 0, warna(0, 0, 0), 10.0, 20.0), segitiga(100, 100, warna(65, 0, 0), 20.0, 5.0), bidang(20.0, 20.0, warna(0, 0, 0))));

insert into bangun_tab values ('test2', bidang_tab(lingkaran(0, 0, warna(255, 255, 255), 20.0), segitiga(200, 200, warna(65, 0, 0), 20.0, 5.0)));

And of course we can query our above data:

SQL> col warna format a25
SQL> select nama, b.color.getWarna() "warna", b.getLuas() "Luas", b.getKeliling() "Keliling"
2 from bangun_tab, table(bangun_tab.mybidang) b;

NAMA warna Luas Keliling
---------- ------------------------- ---------- ----------
test1 Red=0, Blue=0, Green=0 100 30
test1 Red=65, Blue=0, Green=0 50 60
test1 Red=0, Blue=0, Green=0 0 0

I hope this sharing can help all of you to learn about how to implement ORDB in Oracle. It's looked like a simple commands, is it?