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;
/

Oracle for Beginner: ORDB with Oracle (II)

Continuing my last note, here we will try to use VARRAY() to make multiple value for a attribute of table. One of disadvantage of varray is we have to specify the maximum of element array that we want to save in it.

Here an example of how to create a varray type:

SQL> create type v_pengajar_t as varray(3) of pengajar_t;

Like previous example, we can create table which have one attribute with varray() type.

SQL> create table vkursus(
2 no_kursus char(5) primary key,
3 nama varchar(30),
4 pengajar v_pengajar_t
5 );

To insert new data, we can use statement below:

SQL> insert into vkursus values ('X01', 'Oracle', v_pengajar_t(pengajar_t('P01', 'Budi'), pengajar_t('P02', 'Wati')));

1 row created.

SQL> insert into vkursus values ('X02', 'ORDB with Oracle', v_pengajar_t(pengajar_t('P01', 'Budi'), pengajar_t('P03', 'Othie'), pengajar_t('P04','Indah')));

1 row created.

SQL> commit;


To make a query for varray, we can use table() function to create a virtual table from varray field. Here is an example:

SQL> select no_kursus, v.nama, p.nama
2 from vkursus v, table(v.pengajar) p;

NO_KU NAMA NAMA
----- ------------------------------ ------------------------------
X01 Oracle Budi
X01 Oracle Wati
X02 ORDB with Oracle Budi
X02 ORDB with Oracle Othie
X02 ORDB with Oracle Indah

Oracle for Beginner: ORDB with Oracle (I)

If you want to learn about object relational database in Oracle, the first thing that you have to understand is object oriented concept itself. By the way, in what level of your understanding, let we learn about how to implement a simple Oracle's ORBMS.
In Oracle, there are object schema, .i.e., User Define Type (UDT). With this object, we can create any customize type, specially object type. Here is a example:

create or replace type pengajar_t as object (
pengajar_id char(5),
nama varchar(30)
)
/

With customize object type, we can use that as a attribute type for a table.

create table kursus (
kursus_id char(5) primary key,
nama_kursus varchar(50),
pengajar pengajar_t
);

If you want to insert or update for that table, here are an example for how to do that:

insert into kursus values ('K01', 'Air Blender', pengajar_t('X-01','Avatar'));
insert into kursus values ('K02', 'Kamehameha', pengajar_t('X-02','Goku'));
update kursus k set k.pengajar.nama = 'Gohan' where k.pengajar.pengajar_id ='X-02';