Complex Data types | Database Management System (DBMS) - Software Development PDF Download

Introduction

In the world of databases, data comes in various forms and structures. While traditional data types like integers and strings are commonly used, there are situations where complex data needs to be stored and managed. This is where complex data types in Database Management Systems (DBMS) come into play. In this article, we will explore the concept of complex data types, their significance, and provide examples and simple code snippets to help you understand them better.

What are Complex Data Types?

Complex data types, also known as structured data types, allow us to store and manipulate data that has more intricate structures than simple scalar values (like integers or strings). These data types enable us to represent real-world objects and relationships in a more meaningful way. Some commonly used complex data types include arrays, lists, sets, and maps.

Arrays

An array is a collection of elements of the same data type. It allows us to store multiple values under a single variable name. Let's consider an example where we want to store the names of students in a class:

CREATE TABLE students (

    id INT,

    names TEXT[]

);

In this example, the names column is of the array data type (TEXT[]), allowing us to store multiple names for each student. Here's an example of how we can insert and retrieve values:

INSERT INTO students (id, names)

VALUES (1, ARRAY['John', 'Jane', 'Michael']);

SELECT names[1] FROM students;

Output

John

The SELECT statement retrieves the first element (names[1]) from the array, which is 'John'.

Lists
A list is an ordered collection of elements that can be of different data types. It allows for duplicate values and preserves the insertion order. Let's consider an example where we want to store a list of products purchased by a customer:

CREATE TABLE purchases (

    id INT,

    products LIST<TEXT>

);

In this example, the products column is of the list data type (LIST<TEXT>). Here's an example of how we can insert and retrieve values:

INSERT INTO purchases (id, products)

VALUES (1, ['apple', 'banana', 'orange']);

SELECT products[2] FROM purchases;

Output

banana

The SELECT statement retrieves the second element (products[2]) from the list, which is 'banana'.

Sets
A set is an unordered collection of unique elements. It does not allow duplicate values. Let's consider an example where we want to store a set of tags associated with an article:

CREATE TABLE articles (

    id INT,

    tags SET<TEXT>

);

In this example, the tags column is of the set data type (SET<TEXT>). Here's an example of how we can insert and retrieve values:

INSERT INTO articles (id, tags)

VALUES (1, {'technology', 'programming', 'database'});

SELECT tags FROM articles;

Output

{technology, programming, database}

The SELECT statement retrieves the entire set of tags associated with the article.

Maps
A map is a collection of key-value pairs, where each key is unique. It allows for efficient retrieval of values based on the key. Let's consider an example where we want to store the scores of students:

CREATE TABLE scores (

    id INT,

    student_scores MAP<TEXT, INT>

);

In this example, the student_scores column is of the map data type (MAP<TEXT, INT>). Here's an example of how we can insert and retrieve values:

INSERT INTO scores (id, student_scores)

VALUES (1, {'John': 95, 'Jane': 80, 'Michael': 90});

SELECT student_scores['John'] FROM scores;

Output

95

The SELECT statement retrieves the score of the student with the key 'John'.

Sample Problems

• Create a table to store employee information, including their names and contact numbers. Assume that each employee may have multiple contact numbers.
• Create a table to store customer orders, including the ordered products and their quantities. Assume that the same product can be ordered multiple times.
• Create a table to store books, including their titles and the authors. Assume that each book may have multiple authors.

1. 

CREATE TABLE employees (

    id INT,

    name TEXT,

    contact_numbers TEXT[]

);

2.

CREATE TABLE orders (

    id INT,

    products LIST<TEXT>,

    quantities LIST<INT>

);

3.

CREATE TABLE books (

    id INT,

    title TEXT,

    authors SET<TEXT>

);

Conclusion

Complex data types in DBMS provide a powerful way to store and manage data with more intricate structures. By understanding and utilizing complex data types like arrays, lists, sets, and maps, you can represent real-world objects and relationships more effectively in your database. With the help of the provided examples and code snippets, you can start incorporating complex data types into your database designs and queries, enabling you to build more robust and flexible systems.