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;

Distributed Transaction

A distributed transaction is a set of operations on data that is performed across two or more data repositories (especially databases). It is typically coordinated across separate nodes connected by a network, but may also span multiple databases on a single server.

Distributed Transaction Demo Code (Java + MySQL)

// Demo Implementation of Distributed Transaction
importjava.sql.*;
importjava.sql.DriverManager;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.Savepoint;
importjava.sql.Statement;
importjava.util.Scanner;
importjava.sql.Connection;

publicclass Distributed {
/**
*@paramargs
*/
publicstaticvoid main(String[] args) {
// TODO Auto-generated method stub
Scanner sc=newScanner(System.in);
System.out.println("Enter Your Account Number:");
intfromAccountNo=sc.nextInt();
System.out.println("Enter Reciever Account Number:");
inttoAccountNo=sc.nextInt();
System.out.println("Enter Amount to be Transferred:");
int amount=sc.nextInt();
String url = "jdbc:mysql://localhost:3306/transaction";
String url1 = "jdbc:mysql://localhost:3306/tran";
String driver = "com.mysql.jdbc.Driver";
String user = "root";
String pass = "root";
PreparedStatementpst=null;
ResultSet res=null;
ResultSet r1=null;
Connection con=null;
Statement st=null;
Statement st1=null;
intfromBalance=0;
inttoBalance=0;

try{
Class.forName(driver);
con=DriverManager.getConnection(url, user, pass);
con.setAutoCommit(false);
Connection con1 = DriverManager.getConnection(url1, user, pass);
Savepoint save1=con.setSavepoint();
st =con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.TYPE_FORWARD_ONLY);
String str="SELECT BALANCE from SBH where ACCOUNT_NUMBER="+fromAccountNo+"";
res = st.executeQuery(str);
while(res.next()){
System.out.println(str);
fromBalance= res.getInt(1);
System.out.println(+fromBalance);
}
fromBalance=fromBalance-amount;
pst=con.prepareStatement("UPDATE SBH SET BALANCE=? WHERE ACCOUNT_NUMBER=?");
res.first();
pst.setInt(1, fromBalance);
pst.setInt(2, fromAccountNo);
int re= pst.executeUpdate();
System.out.println("hii");
if(re!=0){
st1= con1.createStatement();
String strnew="SELECT BALANCE from BOI where ACCOUNT_NUMBER="+toAccountNo+"";
System.out.println(+toAccountNo);
st1 = con1.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.TYPE_FORWARD_ONLY);
//System.out.println("hiivvhh11");
r1= st1.executeQuery(strnew);
//System.out.println("hiivvhh");
while (r1.next())
{
toBalance = r1.getInt(1);
System.out.println("hiiii second");
}
toBalance=amount+toBalance;
PreparedStatement pst1 = con1.prepareStatement("UPDATE BOI SET BALANCE=? WHERE ACCOUNT_NUMBER=?");
pst1.setInt(1, toBalance);
pst1.setInt(2, toAccountNo);
int re1= pst1.executeUpdate();
if(re1!=0){
System.out.println("Amount Transferred Successfully");
con.commit();
}else{
System.out.println("Unable to transfer");
con.rollback(save1);
}
}
}
catch(Exception e){
}
}
}

Java Database Connectivity with MySQL

To connect Java application with the MySQL database, we need to follow 5 following steps.

In this example we are using MySQL as the database. So we need to know following information for the MySQL database:

Driver class: The driver class for the MySQL database is com.mysql.cj.jdbc.Driver.

Connection URL: The connection URL for the MySQL database is jdbc:mysql://localhost:3306/dbname where jdbc is the API, mysql is the database management system, localhost is the server name on which mysql is running, we may also use IP address, 3306 is the port number and dbname is the database name. We may use any database, in such case, we need to replace the dbname with your database name.

Username: The default username for the MySQL database is root.

Password: It is the password given by the user at the time of installing the mysql database. In this example, we are going to use root as the password.

Let's first create a table in the mysql database, 

create database dbname;

use dbname;

create table students(stu_id int,stu_name varchar(100));

Sample Java Code : MyDBExample.java

import java.sql.*;  

class MyDBExample{  

public static void main(String args[]){  

try{  

Class.forName("com.mysql.jdbc.Driver");  

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname","root","root");  

//here dbname is database name, root is username and password  

Statement stmt=con.createStatement();  

ResultSet rs=stmt.executeQuery("select * from students");  

while(rs.next())  

System.out.println(rs.getInt(1)+"  "+rs.getString(2));  

con.close();  

}catch(Exception e){ System.out.println(e);}  

}  

}  

To connect java application with the MySQL database, mysql-connector-j-x.x.xx.jar file is required to be loaded.

Download the mysql-connector-j-x.x.xx.jar file from Here 

Select operating system : Platform Independent  and download zip file and extract in c/d/e.. drive. Here I am demonstrating extraction in c drive. 

Two ways to load the jar file:

1. Paste the mysqlconnector.jar file in jre/lib/ext folder
2. Set classpath

1) Paste the mysql-connector-j-x.x.xx.jar file in JRE/lib/ext folder:

Download the mysql-connector-j-x.x.xx.jar file. Go to jre/lib/ext folder and paste the jar file here.

2) Set classpath:

There are two ways to set the CLASSPATH:

1. Temporary
2. Permanent

How to set the temporary CLASSPATH

open command prompt and write:  set CLASSPATH=c:\folder\mysql-connector-j-x.x.xx.jar;.;  

How to set the permanent CLASSPATH

Go to environment variable then click on new tab.

In variable name write CLASSPATH and in variable value paste the path to the mysql-connector-j-x.x.xx.jar file by appending ;.; as c:\folder\mysql-connector-j-x.x.xx.jar;.;

Here . indicates the current directory where your class files creates after compilation of java program.