Table and Type Inheritance in DBMS (Oracle)

 

What is inheritance?

Inheritance enables you to share attributes between objects such that a subclass inherits attributes from its parent class. 

Inheritance is the process that allows a type or a table to acquire the properties of another type or table. The type or table that inherits the properties is called the subtype or subtable. The type or table whose properties are inherited is called the supertype or supertable. Inheritance allows for incremental modification so that a type or table can inherit a general set of properties and add specific properties. You can use inheritance to make modifications only to the extent that the modifications do not alter the inherited supertypes or supertables.

Type inheritance

Type inheritance applies to named row types only. You can use inheritance to a group named row types into a type hierarchy in which each subtype inherits the representation (data fields) and the behaviour of the supertype under which it is defined. A type hierarchy provides the following advantages:

• It encourages modular implementation of your data model.
• It ensures consistent reuse of schema components.
• It ensures that no data fields are accidentally left out.

Table inheritance

Only tables that are defined on named row types support table inheritance. Table inheritance is the property that allows a table to inherit the behaviour (constraints, storage options, triggers) from the supertable above it in the table hierarchy. A table hierarchy is a relationship that you can define among tables in which subtables inherit the behavior of supertables. A table inheritance provides the following advantages:

• It encourages modular implementation of your data model.
• It ensures consistent reuse of schema components.
• It allows you to construct queries whose scope can be some or all of the tables in the table hierarchy.

In a table hierarchy, a subtable automatically inherits the following properties from its supertable:

All constraint definitions (primary key, unique, and referential constraints)

• Storage option
• All triggers
• Indexes
• Access method

Demonstration of Type and Table Inheritance in Oracle

--Creating Type 

CREATE TYPE flName AS OBJECT (

          first_name VARCHAR2(50),

           last_name VARCHAR2(50)

        )NOT FINAL;

CREATE TYPE Address AS OBJECT (

          street VARCHAR2(50),

          city    VARCHAR2(50),

          state  VARCHAR2(50),

          country VARCHAR2(50)         

       ) NOT FINAL;

CREATE TYPE person AS OBJECT (

          p_name flName,

          p_address  Address,

          tel_number NUMBER(15)

         )NOT FINAL;

CREATE TYPE student UNDER person (

    student_id NUMBER(10),

    year NUMBER(1)

);

CREATE TYPE employee UNDER person (

    emp_id NUMBER(10),

    Salary NUMBER(10)  

);

--Creating Table

CREATE TABLE person_t OF person;

CREATE TABLE  student_t OF student;

CREATE TABLE employee_t OF employee;

--Describing the Structure of student_t table

DESC student_t;

--Inserting Values in student_t table

INSERT INTO student_t VALUES(FLNAME('Antosh','Dyade'),Address('Aurad','Bidar','Kar','India'),9999999,101,1);

--Structured Select Query on student_t table

SELECT t.p_name.first_name,t.p_name.last_name,

t.p_address.street,t.p_address.city,t.p_address.state,t.p_address.country,

t.tel_number,t.year FROM student_t t;

--Update Query on student_table 

update student_t t set t.p_address.state='Karnataka' where t.p_name.first_name='Antosh';

--Delete Query on student_table

DELETE from student_t t where t.p_name.first_name='Antosh';

--Dropping all the types and tables created above.
--First, drop the tables
--Then drop the type and make sure that while dropping the TYPE it should not hold/opened by any other TYPE or TABLE. 

DROP TABLE employee_t;

DROP TABLE student_t;

DROP TABLE person_t;

DROP type employee;

DROP TYPE student;

DROP TYPE person;

DROP type Address;

Drop type flName;