Oracle 11g :Inheritance in Oralce PLSQL

Get real time news update from your favorite websites.
Don't miss any news about your favorite topic.
Personalize your app.

Check out NTyles.

Get it on....

In my previous post I discussed a little about object oriented programming in oracle plsql. In previous post I gave a little example using OOP in oracle 11g.

In this post I will show you and give some example of one of the feature of the OOP, i.e Object Oriented Programming. The feature that I am going to show about is inheritance. I know all of you who worked with Java and .Net and other OOP languages must be familiar with Inheritance. And for those who don't know inheritance let me write something about it for you :

"Inheritance is that concept in OOP in oracle plsql, where a object or a relation inherits the specs i.e attributes (variables and methods) of another object. In oracle, inheritance relationship is established using primary-key and foriegn-key relationships(shared ID) in order to simulate the relationship between derived class and inherited class."

And if you guys are still wondering what an inheritance is , isn't there any one line sentence that I can understand whole thing, then I have one sentence for you :

We, humans are inherited from apes.

I now think you get the whole picture.


Let me take an opportunity to design a database for eBay,hehe, using Inheritance feature in oracle. Along the design process I will explain different types of inheritance features available in Oracle 11g. What I think about eBay is Users logs in, a logged in user may be buyer or bidder. Buyer is one who creates bid and Bidders are those who bids on the bids created by Buyer. (Everybody knows I am lazy and I am only talking this much for eBay. There are many other features too in eBay, but I think I will be able to describe inheritance in oracle plsql using user, buyer and bidder).

User logged in may be buyer and also a bidder. In inheritance this type of condition is know as
Union Inheritance.

1. Implementing Union Inheritance in Oracle 11g.

This is first design. All bidders and buyers are users and a user can be bidder or a buyer.

Now let me show you the script in oracle plsql using OOP implementing UNION INHERITANCE:

CREATE OR REPLACE TYPE users_obj AS OBJECT  --super object
(
    id       VARCHAR2(10),
    userName VARCHAR2(20),
    email    VARCHAR2(100),
    address  VARCHAR2(35)
) NOT FINAL
/  

CREATE TABLE users OF users_obj--table for users of eBay
(
    id NOT NULL,
    PRIMARY KEY (id)
); 

CREATE OR REPLACE TYPE buyers_obj UNDER users_obj  --inherited object for buyers
(
    bidscreated number
)
/

CREATE TABLE buyers OF buyers_obj  --table of buyers
(
    id NOT NULL,
    PRIMARY KEY (id)
);

CREATE OR REPLACE TYPE bidders_obj UNDER users_obj  --inherited object for bidders
(
    bidsapplied number
)
/

CREATE TABLE bidders OF bidders_obj  --table of bidders
(
    id NOT NULL,
    PRIMARY KEY (id)
);  

There may be users who just creates account and leave them without using them, being static. So, in this case there should be table to store these static users so as to inform them for the news like 'Hey, staic user you won a 1 month vacation to los angeles.'.

Now if the database was designed in classic way think of the problems that one has to face. One of the main advantage of Object Oriented Programming (OOP) is its reuseability. There's a requirement change that you have to face it and solve it.

 In inheritance terms this condition is known as Mutual Exclusion Inheritance.

2. Implementing Mutual Exclusion Inheritance in Oracle 11g.

Now let's move to second design to add table for static users. As we have created table Users I am use this to store information for static users who are neither buyer nor bidder.

Now there will be little change in script for creating Users table.Here's the script:

CREATE OR REPLACE TYPE users_obj AS OBJECT  --super object
(
    id        VARCHAR2(10),
    userName  VARCHAR2(20),
    email     VARCHAR2(100),
    address   VARCHAR2(35),
    user_type VARCHAR2(10)
) NOT FINAL
/  

CREATE TABLE users OF users_obj--table for users of eBay
(
    id NOT NULL,
    user_type CHECK (user_type in ('bidders', 'buyers', 'NULL')), --handling static users
    PRIMARY KEY (id)
); 

CREATE OR REPLACE TYPE buyers_obj UNDER users_obj  --inherited object for buyers
(
    bidscreated number
)
/

CREATE TABLE buyers OF buyers_obj  --table of buyers
(
    id NOT NULL,
    PRIMARY KEY (id)
);

CREATE OR REPLACE TYPE bidders_obj UNDER users_obj  --inherited object for bidders
(
    bidsapplied number
)
/

CREATE TABLE bidders OF bidders_obj  --table of bidders
(
    id NOT NULL,
    PRIMARY KEY (id)
);

Also, if eBay decides to make a new policy that every user must full into any of the role of the bidder, buyer or watchers then this situation is know as Partition Inheritance.

3. Implementing Partition Inheritance in Oracle 11g.

Third design includes the new table for watchers.

CREATE OR REPLACE TYPE users_obj AS OBJECT  --super object
(
    id        VARCHAR2(10),
    userName  VARCHAR2(20),
    email     VARCHAR2(100),
    address   VARCHAR2(35),
    user_type VARCHAR2(10)
) NOT FINAL
/  

CREATE TABLE users OF users_obj--table for users of eBay
(
    id NOT NULL,
    user_type CHECK (user_type in ('bidders', 'buyers', 'watchers')), --handelling watcher users
    PRIMARY KEY (id)
); 

CREATE OR REPLACE TYPE buyers_obj UNDER users_obj  --inherited object for buyers
(
    bidscreated number
)
/

CREATE TABLE buyers OF buyers_obj  --table of buyers
(
    id NOT NULL,
    PRIMARY KEY (id)
);

CREATE OR REPLACE TYPE bidders_obj UNDER users_obj  --inherited object for bidders

(
    bidsapplied number
)
/

CREATE TABLE bidders OF bidders_obj  --table of bidders
(
    id NOT NULL,
    PRIMARY KEY (id)
);

CREATE OR REPLACE TYPE watchers_obj UNDER users_obj  --inherited object for bidders
(
    favItems number
)
/

CREATE TABLE watchers OF watchers_obj  --table of bidders
(
    id NOT NULL,
    PRIMARY KEY (id)
);

Designing database using Object Oriented Features needs a bit high level thinking and needs model thinking. Designers should try to make database model according to the real world model and tables and design should encompass with the requirement change and changes in surrounding.

Well I finished some level of eBay database design , hehe.

 Happy Objecting In Oracle!!