1. Database Structure
I have considered a database for a simple ecommerce site. The
database will consist of different type of products having a bunch of
attributes inherited from Products table whereas each table will have
some other attributes of its own. So here I have a product object and
a Clothes table that inherits the product object. Each clothe is
available in multiple sizes. Further, each clothe tuple is available
in multiple colors represented by a collection array in this example.
For simplicity, I have included only one such table that inherits
from products table where there could be other tables like Jewellery,
Bags, Cosmetics and so on. Further, each product has multiple
categories which is implemented by a nested category table. In our
example, we charger 10% extra on the price of each product. Hence I
have used a member function that returns 10% extra of the price.
2. Object Relational Structure
Product is an object, not the table, from which another object or
type ClotheType is inherited. Product also contains a nested table
Category so that each product row contains a reference to an instance
of the table Category. Finally we create a table Clothes of the type
ClotheType. ClotheType consists of two attributes: clothesize and
clothecolor where clothesize is another Type and clothecolor is a
collection array.
The object oriented concept of function has been included in
ClotheType object which we call member function in Oracle.
3. DDL Script for the Database Objects
Create or replace type CategoryType as object( categoryid int, categoryname varchar2(50) ) not final
Create a table of CategoryType
Create or replace type ProductCategory as Table of CategoryType
Create an inheritable (specified by "not final" construct) object Product
Create or replace type Product as object( productid int, productname varchar2(100), price number(7,2) ) not final
Create type ProductSize
Create or replace type ProductSize as object( sizecode varchar2(20), sizevalue varchar2(20) )
Create collection array for colors
create or replace type ColorArray as varray(20) of varchar2(20)
Create a type ClotheType that inherits from Product
Create or replace type ClotheType under Products(
clothesize productsize,
clothecolor colorarray,
clothecategory productcategory,
member function total_price return number
)
--define the body for the member function total_price
create or replace type body ClotheType as
member function total_price return number is
begin
return (price + price * 0.1);
end;
end;
Finally create a table of the type ClotheType
create or replace table Clothes of clothetype nested table clothecategory store as prod_cat
4. Inserting rows into the Clothes table
Now we insert rows into the Clothes table.
insert into clothes
values(3,'V-neck tshirts with full sleeve',250,
productsize('M','Medium'),
colorarray('White','Blue','Black'),
productcategory(categorytype('1','T-Shirts'),
categorytype('2','Men''s Wears')
)
);
insert into clothes
values(2,'Round-neck tshirts',450,
productsize('L','Large'),colorarray('Red','Black','Green'),
productcategory(categorytype('1','T-Shirts'),
categorytype('2','Men''s Wears')
)
);
insert into clothes
values(1,'Both side design tshirts',600,
productsize('S','Small'),colorarray('Red','Blue','Black'),
productcategory(categorytype('1','T-Shirts'),
categorytype('2','Men''s Wears')
)
);
5. Querying the table Clothesa. Normal query to select the columns productid, productname, pirce and productsize's columns sizecode and sizevalue
select c.productid,c.productname,c.price, c.clothesize.sizecode,c.clothesize.sizevalue from clothes c
b. Querying the categories in a product from the nested table clothecategory
select c.productid,c.productname,p.categoryid,p.categoryname from clothes c,table(c.clothecategory) p where c.productid=1; select c.productid,c.productname,p.categoryid,p.categoryname from clothes c,table(c.clothecategory) p where c.productid=2;
c. Querying the colors associated with a product
select c.productid, c.productname,p.* from clothes c, table(c.clothecolor) p where c.productid=2;
d. Querying the total price by calling the member function total_price
select p.productid, p.productname,p.price,p.total_price() "Total Price" from clothes p;
6.Conclusion
we
realized and implemented a bunch of object relational features of
Oracle database. Some of the features we worked with were type
inheritance, type as a column, datatype collection and nested table,
as well as member function. We developed DDL script to create such
objects and performed select queries. All in all, we got the good
glimspe of object oriented programming in Oralce database.
