What are relational databases and how they work?

As you know, databases are fundamental components of any computer system. In fact, each program uses data or generates information that must be stored reliably and permanently. It is done in structured databases (DBs) that are managed by so-called database management systems (DBMS), i.e., database management systems, software applications that interact with end-users or other programs, and provide them with a subset of data stored in the database.

To date, electronic data management is dominated by relational database models. This is an alphabetical list of the most widely used relational database management systems (RDBMS):

- Db2: Db2 is a relational database management system owned by IBM, available to the user with a commercial license.

 - Microsoft SQL Server: this Microsoft database management system is available with a Microsoft paid the end-user license. 

- MySQL: MySQL is one of the most used open-source RDBMSs in the world. Since it was acquired by Oracle, MySQL is on the market with a dual licensing system: the original developer community continues the project with the name MariaDB. 

- PostgreSQL: with PostgreSQL users have a free object-relational database management system (ORDBMS) available. The open-source community is also involved in continuing its development.

- Oracle Database: the relational database system of the homonymous company Oracle is marketed as paid proprietary software.

- SQLite: SQLite is a public domain library containing a relational database management system.

All named systems are based on a tabular information organization. But what does it mean? We present some basic principles of relational database design, introduce relational databases with examples, and explain the differences that exist with other models.

What are relational databases?

As can be expected, the central term in relational database models is that of relationship. According to British database mathematician and theorist F. Codd, a relation consists of a series of data (the so-called tuples) and represents a set of entities whose values ​​are associated with certain attributes.

It defines what the attributes are comprised of a relationship and which types of data correspond to the values ​​assigned to the attributes using a relationship scheme according to the following syntax:

R = (A 1 : Type 1 , A 2 : Type 2 ,…, A n : Type n )

The relation scheme (R) includes the attributes from A 1 to A n. Each attribute is assigned a data type (Type 1, Type 2, etc.). A concrete example can illustrate this outline.

The following scheme specifies the attributes of the "collaborator" relationship:

Collaborator = (c_id: integer,

Cognome : string,

nome : string,

cf : string,

ind : string,

cap : string,

luogo : string )

This example touches the attributes ID of the collaborator (c_id), surname, name, fiscal code (cf), address (ind), zip code (cap), and place and could, for example, be used for internal company management of personal data. Each attribute is assigned a type of data (for example, string or integer ), indicating that in this relationship, there are attributes that conceive values ​​as a string of signs, while others do not accept whole numbers.

A relationship with the newly defined schema could contain the following tuple:

(1, Bianchi, Maria, BNCMRA18E47R230W, via New York 1, 11111 New York)

As you can see, how a database table can represent the scheme of the collaborators illustrated above:

c_id

Surname

First name

cf.

Ind

POSTAL CODE

Place

1

Watson

Jenny

BNCMRA55F59F500S

Via Los Angeles

90001

Los Angeles

2

Robbins

Michael

RSSGDI97G54B243L

Via New York

10005

New York

3

Oliver

Tony

PDVGLC55M21L424T

Via South Dakota

57002

South Dakota

4

Hathaway

Jamie

TRRLBT32R56C352Z

Via Atlanta

30301

Atlanta

The example table serves to store personal data and consists of four records. Each of them contains information on a precise collaborator.

How do relational databases work?

The database of a relational database system is the structured tabular database. Each RDMS (relational database management system) supports at least one language that can be used to perform the following database operations:

• Define the data structure: in the data definition, a description of the data structure is stored through the metadata in the database system data dictionary. For example, if a user creates a new table, a corresponding report schema is stored in the data dictionary. The vocabulary of a database language used to define data is called Data Definition Language (DDL).
• Define permissions: each database language provides a syntax that allows permissions to be granted or revoked. In this case, we speak of Data Control Language (DCL), a part of the database language vocabulary.

• Define the constraints: the constraints are the requirements for the state of a database. Once the criteria for integrity have been defined, the database ensures that they are met at all times, and for this reason, we speak of a "coherent state." For example, a primary integrity constraint in relational database systems is that each record (tuple) can be uniquely identified.

• Define transactions: when a database is moved from one consistent state to another, it is called a transaction. The transactions contain a series of instructions and must always be completed because if a transaction is interrupted, the database is restored to the initial state (rollback). Each transaction begins with an indication to connect to the database and followed by commands that initiate the actual operation as well as a commitment that guarantees the integrity of the database. Dangerous operations for integrity are not performed. Finally, the connection to the database is closed. The vocabulary of the database language underlying data manipulation is called data Manipulation Language (DML).

Conclusions

The relational database model is clear, mathematically valid, and has over 40 years of practical use behind it. Yet the storage of data in structured tables does not meet all the requirements of modern information technology.The limits of classic relational systems are highlighted above all by the administration of big data analysis and the storage of abstract data types, where specialized systems such as object databases or concepts are distinguished developed within the NoSQL movement.

which still offers many advantages, especially in areas where the processing of transaction data is of primary importance.Data on customer actions or marketing measures can ideally be represented in tabular systems. Moreover, users benefit from a syntax that, despite its simplicity, allows complex queries.