What is a relational database?
It is a database that stores and provides a point of access to stored items of data. These items are then linked to one another via tables, and this is what is referred to as a relational model.
Relational database definition
The relational database management system (RDBMS) is a market-leading model. Many web services or business applications work with these types of databases, since that they can structure and extract essential information from their data. Some large cloud companies have developed their own models (e.g. Db2, which belongs to IBM, Oracle Database, and Microsoft SQL Server). However, many RDBMS projects are open source:
- MySQL is the most widely used system in the world. Although it was purchased by Oracle in 2009, it was a free and open-source project. In reaction to the acquisition, developers created its successor, which is still open source: MariaDB.
- PostgreSQL is also an open-access solution. It is developed by a team of independent developers. It works slightly differently from MySQL, because it has an object-oriented database model (ORDBMS).
- SQLite differs from other database engines because rather than working in client-server mode as a standalone app, it is embedded directly in a program or app.
The acronym SQL corresponds to the query language of the same name: Structured Query Language. This is the standard interface for a relational database. Its instructions are used to structure the data, and organise the information that users want to extract from it.
Relational database architecture
The organisation of these databases is based on the theorem demonstrated by Edgar F. Codd in 1971: the relational model. It states that any ‘natural’ query can be translated into relational algebra, and thus into a query language that is intelligible to a computer (via SQL). In this model, a relationship (or table) consists of multiple attributes organised into multiple rows and columns. These are called tuples. The entire table is seen as a set of tuples. Take the example below:
Each element in the relationship is associated with a data type (value) by a relational schema as follows:
Relation = (Attribute1: Type1, Attribute2: Type2, [...], AttributeX: TypeX)
We can then define a specific element and assign it the data of the value of a tuple to make up our table. This shows that a relational database is just a collection of different tables linked together.
How does a relational database work?
To administer their database, users communicate with the management interface via a query language: SQL. SQL is based on relational algebra. All of the commands are explained in SQL, and detail each step of database development. This language is used to select the targeted information, specify its location in the database, make it interact, etc.
However, there are some specific details to remember.
It is a set of attributes used to identify a specific data set. For example, if we go back to our previous table: A1 + A2 can define customers who bought a new car in the second half of the year, with A1 defining the purchase of the vehicle and A2 defining the purchase period. However, none of the selected attributes can be null. Otherwise, the set does not really exist. Each table has a primary key that can be exported to another table (as a foreign key) to link data sets.
However, when two tables are linked by foreign keys, they can only respond to one query. To determine information that involves multiple tables, you will need to use a JOIN function.
This is an operation that allows multiple tables to be queried simultaneously. The selected data is assembled and filtered according to user-defined conditions.
The SQL JOIN function is a key element in multi-table databases to generate actionable, qualitative information.
There are three important types of JOIN function:
- INNER JOIN
- OUTER JOIN
- SELF JOIN
Data tables are rarely reviewed separately. Generally, we are interested in a data set with a precise meaning, distributed in different tables. To do this, the data must be standardised beforehand.
Querying multiple tables at once would logically require sending multiple requests to different tables to extract the data from them. However, an operation like this is time-consuming, impractical and can create duplicates. This can affect data processing — and this is where standardisation comes in. You can perform a single request by processing data by relation. This is done by querying the data sets linked by the foreign keys.
How they differ from other database models
Not all databases rely on this table model. They may be inspired by it, but they have specific characteristics. In the 1980s, the “object-oriented” model emerged, and reinvented the way data is stored. In the 2000s, the “NoSQL” model appeared, challenging the relational model of databases. The aim was to develop more practical databases for in-depth analyses, such as big data. All of these models have their features and advantages.
It allows data to be stored as objects. ‘Object’ means a dataset in which the interface is defined to access this information.
The ODBMS system automatically assigns an identifier (ID) to each object, allowing access to it through different methods. The main difference with relational databases is the ability to assign a different ID to two separate objects that have the same values. This is unlike a tuple, which can only be identified by a value.
They also have their own language: OQL (Object Query Language).
Storage in these databases is completely different to the relational model. It is based on heterogeneous data from documents processed individually (and no longer in tables). This can range from text documents in JSON, YAML, or XML to unstructured data such as image, video, or audio files.
These documents are stored in key/value combinations. Please note that here, the key term is identical to that of an attribute — so it has nothing to do with the keys of the relational database.
Advantages and disadvantages of relational databases
While relational databases have become very popular, they also have their limitations. Their strength can also be their weakness with certain types of data (e.g. high volumes, multimedia, objects).
- The table model: It is fairly simple to set up. It allows customer data or inventory to be identified easily and quickly.
- Low data redundancy: With standardisation, you avoid distorting the information you use on your database, and gain consistency.
- Quantitative data processing: Enables values to be linked to attributes.
- Standardised Query Language (SQL): Applications can be developed and run within various web projects, with better compatibility.
- Not all data can be displayed in tables: Abstract and unstructured data cannot work with a relational database.
- Data cannot be tiered: Unlike object-oriented databases. You cannot specify your analyses with sub-tuples or tuple classes, for example.
- Data segmentation: Resulting from standardisation, it scatters the data storage spaces. This results in complex queries across multiple tables at the application level. This high number of simultaneous queries across multiple tables decreases performance during scans, and increases compute time.
Overall, these are the most popular databases in the world. However, they are not well-suited to heterogeneous data, or certain use cases, such as big data.
OVHcloud relational databases
We offer a wide range of databases. You can choose between two relational models:
- MySQL: The market leader for relational databases. You can buy it in ‘managed’ mode, which saves you from monitoring and managing your database.
- PostgreSQL: A relational database model that can handle large workloads. It is also available in “managed” mode.
- We also offer Private SQL databases like MariaDB for your web hosting plans.
You can access all of our managed databases, designed to help you manage your data securely. All of these databases are hosted in our datacentres, and have strict security standards. This means we can guarantee sovereignty and confidentiality for your data.