2.5. Inheritance

Inheritance is a concept from object-oriented databases. It opens up interesting new possibilities of database design.

Suppose we have employees in an organization, and then we have some directors who receive a special allowance. We need to create two tables. One for directors and one for employees who are not directors. Let us create a table director. Naturally, directors are also employees, so you want some way to show the directors implicitly when you list all employees. If you're really clever you might invent some scheme like this:

CREATE TABLE non_director (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_non_dir_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) 
);


CREATE TABLE director (
    empno          NUMBER(4) NOT NULL CONSTRAINT emp_dir_pk PRIMARY KEY,
    ename          VARCHAR2(10),
    job            VARCHAR2(9),
    mgr            NUMBER(4),
    hiredate        DATE,
    sal            NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk REFERENCES dept(deptno),
    director_allowance NUMBER (10)
);  

CREATE VIEW employees AS
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM director
    UNION
  SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM non_director;

This works OK as far as querying goes, but it gets ugly when you need to update several rows, for one thing.

A better solution is this. We already have an employee table. We will create a director table which will inherit the employee table emp:

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);



CREATE TABLE director (
   director_allowance NUMBER (10)
) INHERITS (emp);

INSERT INTO director VALUES (8002,'ALEX','DIRECTOR',7839,'23-DEC-81',3000,NULL,20,1000);
INSERT INTO director VALUES (8008,'KENNETH','DIRECTOR',7839,'01-AUG-81',3850,NULL,30,1500);
INSERT INTO director VALUES (7009,'RON','DIRECTOR',7839,'17-OCT-81',4000,NULL,10,2500);

In this case, a row of directors inherits all columns (empno, ename,job,mgr, hiredate,sal,comm, deptno) from its parent, emp. Director table has an extra column, director_allowance, that shows the extra allowance that they receive. In EnterpriseDB, a table can inherit from zero or more other tables.

For example, the following query finds the names of all employees, including directors, that receive a salary of over 1500:

SELECT ename, sal 
    FROM emp
    WHERE sal > 1500;

which returns:

  ename  |   sal
---------+---------
 ALLEN   | 1600.00
 JONES   | 2975.00
 BLAKE   | 2850.00
 CLARK   | 2450.00
 KING    | 5000.00
 FORD    | 3000.00
 SCOTT   | 3200.00
 ALEX    | 3000.00
 KENNETH | 3850.00
 RON     | 4000.00
(10 rows)

On the other hand, the following query finds all the employees that are not directors and receive a salary of over 1500:

SELECT ename, sal 
    FROM ONLY emp
    WHERE sal > 1500;

 ename |   sal
-------+---------
 ALLEN | 1600.00
 JONES | 2975.00
 BLAKE | 2850.00
 CLARK | 2450.00
 SCOTT | 3000.00
 KING  | 5000.00
 FORD  | 3000.00
(7 rows)

Here the ONLY before emp indicates that the query should be run over only the emp table, and not tables below emp in the inheritance hierarchy. Many of the commands that we have already discussed - SELECT, UPDATE, and DELETE - support this ONLY notation.