Inheritance | Database Management System (DBMS) - Software Development PDF Download

Introduction

In the world of databases, inheritance is a powerful concept that allows us to organize and model our data more effectively. Just as inheritance plays a role in object-oriented programming, it also has its place in Database Management Systems (DBMS). In this article, we will explore the concept of inheritance in DBMS, its benefits, and how it can be implemented. We will use simple examples and provide code snippets with explanations to help you grasp the concepts easily.

What is Inheritance in DBMS?

Inheritance in DBMS allows us to create a relationship between tables, similar to how classes inherit properties and methods in object-oriented programming. It enables us to establish a hierarchical structure among tables, where child tables inherit attributes and behaviors from their parent table. This helps to reduce redundancy, improve data integrity, and simplify queries.

Types of Inheritance in DBMS

In DBMS, there are three common types of inheritance:

(i) Single Table Inheritance: Single Table Inheritance (also known as Table Per Hierarchy) involves storing all the attributes of different entity types in a single table. It uses a discriminator column to determine the type of entity each record represents. Let's see an example:

-- Parent table

CREATE TABLE vehicles (

    id INT PRIMARY KEY,

    type VARCHAR(20),  -- Discriminator column

    make VARCHAR(50),

    model VARCHAR(50)

);

-- Child tables

CREATE TABLE cars (

    id INT PRIMARY KEY REFERENCES vehicles(id),

    num_doors INT,

    fuel_type VARCHAR(20)

);

CREATE TABLE bikes (

    id INT PRIMARY KEY REFERENCES vehicles(id),

    num_gears INT

);

Explanation: In the above example, the vehicles table acts as the parent table, and the cars and bikes tables are the child tables inheriting from it. The type column in the vehicles table acts as a discriminator column, which differentiates between the types of vehicles. The child tables contain additional attributes specific to each vehicle type.

(ii) Class Table Inheritance: Class Table Inheritance (also known as Table Per Type) involves creating a separate table for each entity type. Each table represents a specific class and contains attributes specific to that class. Let's see an example:

-- Parent table

CREATE TABLE vehicles (

    id INT PRIMARY KEY,

    make VARCHAR(50),

    model VARCHAR(50)

);

-- Child tables

CREATE TABLE cars (

    id INT PRIMARY KEY REFERENCES vehicles(id),

    num_doors INT,

    fuel_type VARCHAR(20)

);

CREATE TABLE bikes (

    id INT PRIMARY KEY REFERENCES vehicles(id),

    num_gears INT

);

Explanation: In this example, the vehicles table acts as the parent table, while the cars and bikes tables are child tables representing different entity types. Each child table has a foreign key reference to the parent table, allowing them to inherit the common attributes from the vehicles table.

(iii) Concrete Table Inheritance: Concrete Table Inheritance (also known as Table Per Concrete Class) involves creating a separate table for each entity type, including both the common attributes and the specific attributes. Let's see an example:

-- Parent table

CREATE TABLE vehicles (

    id INT PRIMARY KEY,

    make VARCHAR(50),

    model VARCHAR(50)

);

-- Child tables

CREATE TABLE cars (

    id INT PRIMARY KEY REFERENCES vehicles(id),

    num_doors INT,

    fuel_type VARCHAR(20)

);

CREATE TABLE bikes (

    id INT PRIMARY KEY REFERENCES vehicles(id),

    num_gears INT

);

Explanation: In this example, the vehicles table acts as the parent table, similar to class table inheritance. However, the child tables (cars and bikes) contain both the common attributes inherited from the parent table and the specific attributes unique to each entity type.

Example Scenarios

Let's dive deeper into each type of inheritance with some concrete examples.

(i) Single Table Inheritance Example: Consider a scenario where we have a table named employees that stores information about employees, including their roles and departments. We can use single table inheritance to handle different types of employees, such as managers and regular employees, with their specific attributes.

-- Parent table

CREATE TABLE employees (

    id INT PRIMARY KEY,

    type VARCHAR(20),  -- Discriminator column

    name VARCHAR(50),

    department VARCHAR(50)

);

-- Child tables

CREATE TABLE managers (

    id INT PRIMARY KEY REFERENCES employees(id),

    team_size INT

);

CREATE TABLE regular_employees (

    id INT PRIMARY KEY REFERENCES employees(id),

    project_name VARCHAR(50)

);

Explanation: In this example, the employees table acts as the parent table, storing common attributes like name and department. The type column acts as a discriminator, differentiating between managers and regular_employees. The child tables (managers and regular_employees) contain additional attributes specific to each employee type.

(ii) Class Table Inheritance Example: Consider a scenario where we have a table named products that stores information about different types of products, including electronics and books. We can use class table inheritance to handle different product types with their specific attributes.

-- Parent table

CREATE TABLE products (

    id INT PRIMARY KEY,

    name VARCHAR(50),

    price DECIMAL(10,2)

);

-- Child tables

CREATE TABLE electronics (

    id INT PRIMARY KEY REFERENCES products(id),

    brand VARCHAR(50),

    model VARCHAR(50)

);

CREATE TABLE books (

    id INT PRIMARY KEY REFERENCES products(id),

    author VARCHAR(50),

    genre VARCHAR(50)

);

Explanation: In this example, the products table acts as the parent table, storing common attributes like name and price. The child tables (electronics and books) represent different product types and contain additional attributes specific to each type.

(iii) Concrete Table Inheritance Example: Consider a scenario where we have a table named animals that stores information about different types of animals, including mammals and birds. We can use concrete table inheritance to handle different animal types with their specific attributes.

-- Parent table

CREATE TABLE animals (

    id INT PRIMARY KEY,

    name VARCHAR(50),

    category VARCHAR(50)

);

-- Child tables

CREATE TABLE mammals (

    id INT PRIMARY KEY REFERENCES animals(id),

    lifespan INT,

    habitat VARCHAR(50)

);

CREATE TABLE birds (

    id INT PRIMARY KEY REFERENCES animals(id),

    wingspan INT,

    migration BOOLEAN

);

Explanation: In this example, the animals table acts as the parent table, storing common attributes like name and category. The child tables (mammals and birds) represent different animal types and contain both the common attributes and the specific attributes unique to each type.

Sample Problems with Solutions

Here are a few sample problems related to inheritance in DBMS along with their solutions:

Problem 1: Consider the following scenario: You are designing a database for an e-commerce platform that sells different types of products, including electronics and clothing. The electronics category includes products like smartphones and laptops, while the clothing category includes products like shirts and pants. Design the database schema using the appropriate type of inheritance.

For this scenario, we can use class table inheritance. Here's the schema:

-- Parent table

CREATE TABLE products (

    id INT PRIMARY KEY,

    name VARCHAR(50),

    price DECIMAL(10,2)

);

-- Child tables

CREATE TABLE electronics (

    id INT PRIMARY KEY REFERENCES products(id),

    brand VARCHAR(50),

    model VARCHAR(50)

);

CREATE TABLE clothing (

    id INT PRIMARY KEY REFERENCES products(id),

    size VARCHAR(10),

    color VARCHAR(20)

);

Problem 2: Consider the following scenario: You are working on a database for a university. The database needs to store information about different types of students, including undergraduate students and graduate students. Design the database schema using the appropriate type of inheritance.

For this scenario, we can use single table inheritance. Here's the schema:

-- Parent table

CREATE TABLE students (

    id INT PRIMARY KEY,

    type VARCHAR(20),  -- Discriminator column

    name VARCHAR(50),

    department VARCHAR(50)

);

-- Child tables

CREATE TABLE undergraduates (

    id INT PRIMARY KEY REFERENCES students(id),

    year INT,

    program VARCHAR(50)

);

CREATE TABLE graduates (

    id INT PRIMARY KEY REFERENCES students(id),

    specialization VARCHAR(50),

    advisor VARCHAR(50)

);

Conclusion

Inheritance in DBMS is a powerful concept that allows us to organize and model data effectively, reducing redundancy and improving data integrity. By establishing relationships between tables and allowing them to inherit attributes and behaviors, we can create a more structured and efficient database design. Understanding the types of inheritance and their implementation can greatly enhance your database management skills and make your data modeling more robust.
In this article, we covered the basics of inheritance in DBMS, including single table inheritance, class table inheritance, and concrete table inheritance. We provided simple examples and code snippets to illustrate each concept. Remember to choose the appropriate type of inheritance based on your specific requirements and data model.