Identify Features of Relational Data

In the first section of this chapter, you will learn what relational data is and the features of relational databases.

It can be helpful to think of the library analogy for relational data, where each book has a unique identification number (e.g., ISBN) and belongs to a particular category (e.g., fiction versus non-fiction). The library tables can be arranged into shelves, each of which would be a table in the system. For each category of books, there will be a table showing all the books. Each shelf will have the books placed into rows and columns in the order in which each book is placed on the shelf. Each row and column on the shelf is associated with exactly one attribute, just like the attributes of a table that come together to relate one instance of the entity (e.g., the book) in the relational database. The library can have a catalog listing all the books and their data, as many tables contain columns listing data associated with specific books. The library can also have data-capturing relationships between books, such as those written by the same author, those in the same series, those about the same topic, in the same category, and so on. Each of these relationships can be seen in the relational database as a reference key embedded in a table that links data between different tables.

Relational data can be defined as data that is organized in such a way as to define the connections and associations between different data points or entities. This type of data structure is commonly used in relational databases to represent the relationships between various pieces of information. It allows for the efficient retrieval and manipulation of data based on these defined relationships and is a fundamental concept in data management and database design.

Some examples of relational data are as follows:

• Customer information: Each customer can have an ID number, name, address, phone number, email, and so on. These can be stored in a table called customer, where each row represents a customer, and each column represents an attribute.
• Employee records: Each employee can have an ID number, name, department, position, salary, and so on. These can be stored in a table called Employees, where each row represents an employee, and each column represents an attribute.

Data is held in tables, which comprise rows and columns. Figure 2.1 illustrates a table:

Figure 2.1 – Table example

The following are the components of tables:

• Each row is an instance of a data entity
• Each data entity can have several attributes assigned; each is defined in a separate column, named with attribute headers
• A datatype is assigned to each column
• Keys are used to uniquely identify a row in a database; they can link tables through a relationship

Figure 2.2 illustrates relational data components:

Figure 2.2 – Relational data components

There may be multiple tables for customers, orders, products, and so on, each with specific attributes for the data entity.

The customer table data entities may have attributes that include ID number, name, and orders; for the Orders table, the data entities may have order ID, order date, and customer ID attributes; for the Line Item table, the data entities may have order id, item id, and item qty; and for the product table, ID, name, and price. These tables have relationships that are based on related attributes that have keys that connect tables.

The Customer table may use the field for customer ID numbers, which would also appear in the Orders table to associate the sales records with the customers. The linking of the tables allows easy access and analysis of data from several tables and gives insight into how different data entities are related. Figure 2.3 illustrates the points that were covered:

Figure 2.3 – Database table relationships

Relational databases are popular in modern applications and can store different kinds of data, including financial data, customer details, product catalogs, and so on.

Relational databases enable organizations to organize their data consistently and clearly define relationships between different data entities. This helps make modern systems effective and allows data-driven decision-making.

You will now learn about the characteristics of relational databases.

Relational Database Characteristics

A relational database holds the structured relational data in a system accessed by transactional and analytical workloads.

Structured relational data is organized in tables with rows and columns. Each row represents an entity or an instance of data. Relational databases organize information based on structural relationships, so searching for clusters of related information is easy. Structured relational data supports the following two types of workloads:

• Transactional workload: The data is frequently added or changed, such as in online transactions, inventory management, or bank operations
• Analytical workload: The data is frequently queried or analyzed from large amounts of information, such as in business intelligence, reporting, or data mining

Relational databases have the following characteristics:

• Data entities are stored as structured data
• A fixed schema provides a structure that guarantees strong consistency
• Data entities are stored as data values in tables, a tabular format that consists of rows and columns
• Rows represent data entities; one row per data entity instance
• The same columns are used for each row in the table
• A datatype is assigned to each column in the table; entries can have a value of NULL
• Tables have no duplicate information
• Tables can reference other tables by using keys to provide the relationships

An example of a relational database would be Microsoft SQL; this can be implemented as an IaaS or PaaS service on Azure. You will discover the methods for implementing Microsoft SQL for Azure in Chapter 3, Describe Relational Azure Data Services.

Now that you understand the features of relational data, you will learn about data normalization.