Inheritance concept in ORDBMS

Now since we have understood what ORDBMS is all about and how it differs from RDBMS.

Let us understand inheritance concept in ordbms along with the code.

-- 'TYPE' creation (creating custom data types):

CREATE TYPE fullname AS(first_name text, middle_name text, last_name text);

-- Notice how we are using the 'text' datatype and not 'varchar()'.

CREATE TABLE staff(staff_id int primary key, staff_name fullname);

-- Now, just use the name of the data type we have made, in the same way as we would use any other built in data type.

INSERT INTO staff VALUES(100, ROW('Akhil', 'Samir', 'Killawala'));
INSERT INTO staff VALUES(101, ROW('Walter', 'Hartwell', 'White'));

-- We use the 'ROW()' function to insert values into the user defined data type.


SELECT *
FROM staff;

/*

Output:
staff_id staff_name
100 (Akhil,Samir,Killawala)
101 (Walter,Hartwell,White)

*/

-- Notice how the custom data type's values are all in a single column. Don't want that? Use the next query.

SELECT staff_id, (staff_name).first_name, (staff_name).middle_name, (staff_name).last_name
FROM staff;
/*

Output:
staff_id first_name  middle_name last_name
100 Akhil            Bharat         Sanghavi
101 Walter      Hartwell       White

*/
-- This will increase the complexity of the query though.
-- VERY IMPORTANT: Notice the parantheses '()' around 'staff_name'? They are important and cannot be skipped.

-- TYPE creation, completed!!! Have a cookie :)



-- INHERITANCE (how to create a new table, which is an extension of an already existing table):

CREATE TABLE teacher(department text) INHERITS (staff);

-- We are creating a new table having a 'department' column which will be appended to the end of the 'staff' table columns.

-- INHERITS will *not* copy the existing data of the old table: only the column names and types will be 'inherited'.

-- Again, notice 2 things: usage of 'text' data type over 'varchar()' and the use of parantheses '()' while mentioning the table to be inherited.

INSERT INTO teacher VALUES(100, ROW('Akhil', 'Samir', 'Killawala'), 'Computer');

SELECT *
FROM teacher;
/*

Output:
staff_id staff_name          department
100 (Akhil,Samir,Killawala)  Computer

*/

CREATE TABLE working_staff(salary int) INHERITS (staff);
INSERT INTO working_staff VALUES(101, ROW('Walter', 'Hartwell', 'White'), 5000);

SELECT *
FROM working_staff;

/*

Output:
staff_id staff_name salary
101 (Walter,Hartwell,White) 5000

*/

-- Notice how in both the newer tables, old values had to be reinserted....
-- The new tables contain the exact same column names and data types of the old table...
-- And the newer columns were appended to the older columns.


--INHERITANCE done!!!

This code is 100% working so try it on sql see how inheritance works in ORDBMS.

Enjoy!!