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;

Domain Relational Calculus

• Domain Relational Calculus uses domain variables that take on values from an attributes domain, rather than for an entire tuple. ​
• Formal Definition​
• An expression in the domain relational calculus is of the form :​
• {<x1, x2, …..., xn> | P(x1, x2, …..., xn)}​
• Where ​
• x1, x2, …..., xn represent domain variables.​
• P formula composed of atoms. ​
• An atom in the domain relational calculus has one of the following forms:​
• <x1, x2, …..., xn> ϵ r, where r is a relation on n attributes and x1, x2, …..., xn are domain variables or domain constants.​
• X Θ y, where x and y are domain variables and Θ is a comparison operator (<, ≤, >, ≥, = , ≠) Attributes x and y have domains that can be compared by  Θ ​
• x Θ c, where x is a domain variable, Θ  is a comparison operator, and c is a constant in the domain of the attribute for which x and a domain variables. ​

Examples based on following relation.

Example1: Find the loan number, branch name and amount for loans of over 100000

Answer: =>      { < l, b, a > | < l, b, a > ϵ loan ᴧ a > 100000 }​

Example2: Find the name of all customer who have a loan from the Mumbai branch

Answer: =>  { < c, a > ∃ l ((<c, l > ϵ borrower ᴧ ∃ b(<l, b, a >  ϵ loan ᴧ b = "Mumbai") }​

 

Tuple Relational Calculus

• The tuple relational algebra is a nonprocedural query language. It describes the desired information without giving a specific procedure or obtaining that information.​
• A query in the tuple relational calculus is expressed as ​
• { t | P(t) }​
• That is, it is the set of all tuples t such that predicate P is true for t.​
• In tuple relational calculus ​
• t[A] - denotes the value of tuple on attribute A.​
• t ε  r –denotes tuple t in relation r. ​

Examples based on the following table

Example 1: Find all tuples from the depositor relation

Answer: { t | t ϵ depositor }​

Example 2: Find the loan_no, branch_name and amount for loans of over 100000.

Answer: { t | t ϵ loan ᴧ [amount] > 100000 }​

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){
}
}
}

Modification of the Database in Relational Algebra

• Different operations that modify the contents of the database are​
• Delete​
• Insert​
• Update​
• We express database modification by using the assignment operation.​

Deletion

• The deletion operation removes the selected tuples from the database using delete values of any particular attribute.​
• In relational algebra a deletion is expressed by​
•  r ← r - E​
•  r is a relation​
• E is a relational algebra query​

Example​

Delete 'Ramesh' Record from the database​

• employees ← employees - σemp_name='Ramesh'(employees)

• Delete all employee working in IT department​
• employees ← employees - σdepartment='IT'(employees)

Insertion

• The relational algebra expresses an insertion by ​
• r ← r Ս E​
• r is a relation​
• E is relational algebra expression​

Examples​

• Insert Ramesh Record in employee relation​
• employees ← employees Ս {('E004','Ramesh', 12000)}​

Updating

• Sometimes, we wish to change a value in a tuple without changing all values in the tuple. We can use generalized projection operation to do this:​

• r  ← ∏f1,f2,f3...fn(r)

• Where fi the ith attribute to be updated has expression involving constants and attributes of r, that gives new value for the attribute. ​

• Example​
• ∏emp_id, salary*1.05(employees)

Union, Intersection and Difference Cartesian Product and Additional Operations (Natural Join, Division, Assignment, Generalized Projection, Aggregate Functions, Outer Join ) ​ in Relational Algebra

• The Union, Intersection and Difference operations requires following condition ​
• The two relation/table must contain the same number of columns ​
• Each column of the first relation/table must be either the same data type as the corresponding column of the second relation/table or convertible to the same data type as corresponding column of the second. ​

Union

The result of operation is denoted by depositor Ս borrower, is the relation that includes all tuples that are either in depositor or borrower or both. Duplicates are eliminated. ​

Intersection

The Result of intersection operation is a relation that includes all tuples that are in both depositor and borrower. It is denoted by depositor ∩ borrower.​

Difference

The difference operation is denoted by depositor – borrower. ​

The result of the difference operator is the relation that contain all tuples in depositor but not in borrower. 

• Both Union and intersection operations are commutative and associative ​
• AUB =BUA  and  A∩B=B∩A​
• AU(BUC)=(AUB)UC  and A∩(B∩C)=(A∩B)∩C​

• The difference operation is not commutative.​
• A-B≠B-A​

Cartesian Product

• It is known as CROSS PRODUCT or CROSS JOINS.​
• It is denoted by 'x'​
• The Cartesian product of two relations A and B is denoted by AxB​
• The result of cartesian product of two relation which have X and Y columns is relation that has X+Y Columns. The resulting relation will have one tuple or each combination of tuples from each participating relation. ​
• If a relation have m and n tuples, then the CARTESIAN PRODUCT will have m x n tuples.​

Additional Operations

We define additional operations that do not add any power to the relational algebra, but that simplify common queries.​

• Set intersection​
• Natural join​
• Assignment​
• Outer join ​

Natural Join

The natural join is a binary operation.​

It is denoted by the join symbol ⨝​

The natural join operation forms a cartesian product of its two arguments, performs a selection forcing equality on those attributes that appear in both relation schemes and finally removes duplicate attributes.​

The difference between natural join and cartesian product is : the query involving cartesian product includes a selection operation on the result of the cartesian product. ​

Consider following schema​

• employee(emp_id, emp_name)​
• salary(emp_id,salary)​

• Πemp_name,salary(σemployee.emp_id=salary.emp_id(employee x salary)) = Πemp_name,salary(employee  ⨝ salary)

• The query using natural-join is simpler than cartesian product query. ​

Πemp_name,salary(employee  ⨝ salary)​

Division Operator​

• The division operation is denoted by '÷' it is used for the queries include the phrase 'for all' / 'for every'.​
• A(X,Y)÷B(Y) will results X values for that there should be tuple <x,y> for every Y value of relation B.

Division Example

List the E_id  of works relation worked for all /every projects  of Projects relation 

The Assignment Operator

• The assignment operation is denoted by '←'. ​
• It works like assignment in programming language​
• Example t1←σroll_no=1(students)
• The result of right side expression is assigned to the relation variable on the left. ​
• With assignment operation, a query can be written as a sequential program consisting of a series of assignment followed by an expression whose value is displayed as the result of the query. ​
  

Extended Relational Algebra Operations

• The basic relational-algebra operations have been extended in several ways​
• A simple extension allow arithmetic operations as part of project.​
• An important extension is, allow aggregate operations such as computing the sum of set, or their average.​
• Another important extension is outer join operation, which allow relational algebra expression to deal with null values. ​

Generalized Projection

• The generalized projection operation extends the projection operation by allowing arithmetic function to be used in the projection list. ​
• It is in the form of ∏f1,f2,f3,…,fn(E) where E: relation algebra expression and f1,f2,f3... are an arithmetic expression involving constants and attributes of E. ​
• Example  :  ∏emp_id, salary*1.05(employees)

Aggregate Functions

• Aggregate Functions take a collection of values and return a single value as a result. ​
• It is represented 'calligraphic G'  '𝒢'​
• 𝒢sum(salary)(employees)

• Display the sum of salary of employees​

• 𝒢count(emp_id)(employees)

• Count the number of employees in employees relation​
• The 𝒢 signifies that aggregate function is to be applied and subscript specifies the aggregate operation to be applied.​

Outer Join

It is extension of the join operation to deal with missing information. ​

In the below example the record of Raju was missing in the outcome of natural join ​

• To avoid the loss of information, we can use outer join operation.​
• There are three forms of outer-join​

1. Left Outer-join​
2. Right Outer-join​
3. Full Outer-join​

Left Outer-join

• It is denoted by ⟕​
• It take all tuples in the left relation and matching records of right relation, pads the tuples with null values for all other attributes from the right relation. ​

Right Outer-join

• It is denoted by ⟖​
• It take all tuples in the right relation and matching records of left relation, pads the tuples with null values for all other attributes from the left relation. ​

Full Outer-join

• It is denoted by ⟗​
• It take all tuples in the right relation and all the tuples from left relation, pads the tuples with null values for non-matching attributes from the left and right relation. ​

Joins Summary 

The Select, Project & Rename Operation​ in Relational Algebra

The Select Operation​

• The select operation select tuples that satisfy a given predicate.​
• The small Greek letter sigma(σ) is use to denote select operation​
• The predicate appears as a subscript to σ​
• The Comparison operator =,≠,<,>,≤,≥​
• The Connectives and(∧ ), or( v), not(¬)​

  

Examples

• To Select the tuples from employee relations those employees belong to CSE Department
• σdept_name='CSE'(employees)​

• The employees whose salary greater than 12000/-​
•  σemp_salary>12000(employees)

• Department is CSE and Salary is greater than 12000/- ​
• σdept_name='CSE' ∧ emp_salary>12000(employees)​

Project Operation

• It is Unary operation​
• It selects certain columns from a table while discarding others. ​
• It removes any duplicated rows from the result relation.​
• Denoted by the uppercase Greek letter pi (Π)​
• The attribute list appears in the subscript to  Π  with argument relation in parentheses. ​

Examples:

• ΠId,name,dept,salary(employees)
• Selects only id, name, dept_salary Form employees relation.​

• Πtitle,Author(Book)
• Display all title and author from book relation

Composition of Relation Operations

• The relational-algebra operations can be composed together into a relational-algebra expression it is just like composing arithmetic operations into athematic expression. ​

• Example​
• Πname(σdept_name='CSE'(employees))

Rename Operation

• In Relational algebra, we can rename either the relation or the attributes or both.​
• It is denoted by lowercase Greek letter rho (ρ)​
• ρs(new attribute name)(R)
• ρs(R)
• ρ(new attribute name)(R)

Examples:

• ρt1(fn,ln,sal)(R)

• Rename relation R with new relation t1 with  new name for the listed attributes ​

• ρ(fn,ln,sal)(R)

• Rename only attributes listed in the brackets.​

• ρt1(R)

• Rename only relation R with new name t1.​