Inheritance Hierarchies in DBMS

Inheritance Hierarchies are crucial to building a structured and well-organized database. It is comparable to the idea of inheritance found in object-oriented programming languages. The main ideas of inheritance hierarchies in database management systems (DBMS) will be covered in this article, along with definitions, procedures, and in-depth examples to aid in understanding.

What are Inheritance Hierarchies in DBMS?

In database management systems, inheritance hierarchies help create linkages between tables or entities, much like inheritance helps create links between classes in object-oriented programming. It explains the process by which a newly created table, often known as a child table, can inherit specific attributes and capabilities from an existing parent table. This lowers redundancy and improves data integrity by creating a hierarchical structure in the database.

Key Terminologies

• Superclass: The class or table whose methods and attributes are inherited is called the superclass or base class. Another name for it is the parent class.
• Subclass/Derived Class: In an inheritance structure, a subclass is a class or table that receives some methods and attributes from another class. Another name for it is the child class.

Types of Inheritance Hierarchies

There are mainly three types of inheritance hierarchies in DBMS as follows:

• Single Table Inheritance
• Class Table Inheritance
• Concrete Table Inheritance

Single Table Inheritance

Single Table Inheritance, also known as Table per Hierarchy (TPH), stores all entities—superclass and subclass—in a single table. Every record in the table has a unique marker, known as a discriminator, that designates the particular kind of entity it represents. Because fewer tables and intricate connections are required, this method makes data storage and retrieval easier. But it could lead to sparse tables with a lot of null values for characteristics that don’t apply to some kinds of entities.

Example

Let us take a situation when we have two categories of workers: full-time and part-time.

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

name VARCHAR(100),

type VARCHAR(20), — Discriminator column

salary DECIMAL,

hourly_rate DECIMAL

)

Let’s insert some sample data

— Full-time employee

INSERT INTO employees (name, type, salary) VALUES (‘Ravi Kumar’, ‘full-time’, 50000.00);

— Part-time employee

INSERT INTO employees (name, type, hourly_rate) VALUES (‘Rishi Patel’, ‘part-time’, 20.00);

Now, let’s query the employees table

SELECT * FROM employees;

The above query will return the following table as output:

id	name	type	salary	hourly_rate
1	Ravi Kumar	full-time	50000.00	NULL
2	Rishi Patel	part-time	NULL	20.00

In this example, full-time and part-time employees are stored in a single table called employees. The employee type is ascertained by using the type field as a discriminator. This method makes it simpler to manage and maintain the database schema and queries. However, maintaining data integrity and appropriately handling NULL values are crucial.

Class Table Inheritance

Table per Type (TPT), another name for Class Table Inheritance, is the process of making a separate table for every entity type, including the superclass and all of its subclasses. Only the properties pertinent to that particular entity type are contained in each table, and foreign keys are used to form associations between the tables. By cutting down on redundancy, this method preserves data integrity while providing improved data organization. To extract data from many tables, however, more intricate queries requiring joins can be needed.

Example:

Let’s understand class table inheritance with the help of animal table.

Parent Table (animal): This table acts as the foundation, holding shared characteristics amongst all animals. It contains data such as the species, which denotes the kind of animal, and the id, which uniquely identifies every animal.

CREATE TABLE animals (

id SERIAL PRIMARY KEY,

species VARCHAR(50)

);

Child Tables (mammals and birds): Each child table depicts a certain kind of animal (mammal or bird). They might have extra properties unique to the subtype in addition to those inherited from the parent table (animals). Every child table has an id column that functions as both the child table’s primary key and a foreign key that references the id of the parent table.

CREATE TABLE mammals (

id SERIAL PRIMARY KEY,

name VARCHAR(100),

num_legs INTEGER,

FOREIGN KEY (id) REFERENCES animals(id)

);

CREATE TABLE birds (

id SERIAL PRIMARY KEY,

name VARCHAR(100),

wingspan DECIMAL,

FOREIGN KEY (id) REFERENCES animals(id)

);

Query for inserting values:

— Inserting a record for a mammal

INSERT INTO animals (species) VALUES (‘Mammal’);

INSERT INTO mammals (id, name, num_legs) VALUES (1, ‘Dog’, 4);

— Inserting a record for a bird

INSERT INTO animals (species) VALUES (‘Bird’);

INSERT INTO birds (id, name, wingspan) VALUES (2, ‘Eagle’, 2.5);

The output table for the above is:

id	species	name	num_legs	wingspan
1	Mammal	Dog	4	NULL
2	Bird	Eagle	NULL	2.5

Concrete Table Inheritance

Concrete Table Inheritance (CTI) assigns a unique table to every subclass or derived entity in an inheritance hierarchy. In CTI, every table denotes a certain class of object or entity, complete with both class-specific and parent-class-inherited properties.

Example:

let us consider a base class called “Vehicle” with two subclasses, “Car” and “Truck.” .

CREATE TABLE vehicles (

id SERIAL PRIMARY KEY,

make VARCHAR(50),

model VARCHAR(50),

vehicle_type VARCHAR(20) — discriminator column

);

CREATE TABLE cars (

id SERIAL PRIMARY KEY,

num_doors INTEGER,

FOREIGN KEY (id) REFERENCES vehicles(id)

);

CREATE TABLE trucks (

id SERIAL PRIMARY KEY,

load_capacity DECIMAL,

FOREIGN KEY (id) REFERENCES vehicles(id)

);

Inserting data into the tables:

— Inserting data for cars

INSERT INTO vehicles (make, model, vehicle_type) VALUES (‘Toyota’, ‘Camry’, ‘car’);

INSERT INTO cars (id, num_doors) VALUES (1, 4);

— Inserting data for trucks

INSERT INTO vehicles (make, model, vehicle_type) VALUES (‘Ford’, ‘F150’, ‘truck’);

INSERT INTO trucks (id, load_capacity) VALUES (2, 2000.0);

Output query:

SELECT * FROM vehicles;

Output:

id	make	model	vehicle_type	num_doors	load_capacity
1	Toyota	Camry	car	4	NULL
2	Ford	F150	truck	NULL	2000.0

The base table for common attributes like make, model, and vehicle_type is the vehicles table. Vehicle type distinguishes between a car and a truck. There is a table for each subclass (trucks and cars) with characteristics unique to that kind of vehicle.

Specific attributes for vehicles and trucks are shown in the inserted data, with NULL values for attributes that are not applicable. The output illustrates the concrete table inheritance approach by combining data from all tables.

Characteristics of Inheritance Hierarchies

• Code Reusability: We may reduce redundancy in the database schema by reusing the attributes and functions from the superclass .
• Hierarchy Structure: The generalization-specialization relationship between entities is demonstrated by the hierarchical structure formed by the interaction between a superclass and its subclasses.
• Flexibility: Any modifications we make to the superclass will also affect the subclasses. This guarantees the database design’s coherence. Subclasses, however, may have special qualities that offer adaptability.

Frequently Asked Questions on Inheritance Hierarchies – FAQs

Is it possible to modify a subclass’s properties without having an impact on the superclass?

Yes, we can modify a subclass’s attributes without affecting the superclass. One of the main benefits of inheritance hierarchy is its adaptability.

What are the advantages of inheritance hierarchies in DBMS?

It promotes code reusability, reduces redundancy, and allows for a more organized database design.

Can changes made to the superclass affect the subclasses in an inheritance hierarchy?

Yes, changes made to the superclass affect the subclasses in an inheritance hierarchy. This promotes consistency besides providing flexibility to the subclass to add their unique attributes.

Does the number of layers in inheritance hierarchies have a limit?

The number of levels in inheritance hierarchies in database management systems (DBMS) is not strictly limited; still, it is crucial to preserve equilibrium. While too few levels might not be sufficient to capture the specificity of the database design, too many levels could increase complexity.