What Are Relational Databases?


The main characteristics of these sets of information that favor organization, management and analysis. The fundamental concepts, the advantages of the system and examples.


Main characteristics of relational databases


Relational databases are sets of information organized based on predefined relationships, that is, the logical connections established from the interaction of the contents.


In these databases (also known by their acronym in English RDB) data is stored in tables with columns and rows. This simplifies the understanding of the information, the structures and the link between the information; and it enables the obtaining of relevant statistics.


The model was developed by the computer scientist Edgar Frank Codd during the 70s while working for the company IBM. His reference work is A Relational Model of Data for Large Shared Data Banks, published in 1970.





In summary, the following are the main characteristics of relational databases.


They don't use a hierarchy structure: they're based on the relational model.


In that sense, they differ from non-relational databases (toward the end of this review we'll talk in detail about this).


They use sets of tables to represent the data and the relationships between them.


They don't have information repeated unnecessarily.


They allow any table to relate to another through a common property.


Important aspects of relational databases


RDBs contain a series of important elements, whose particularities we review below.  


Tables: Used to store information about the objects represented in the database.


Columns (attributes): Used to store a certain type of data, with a field that stores the value of an attribute.


Rows (records): Represent a collection of related values of an entity or object. Rows are also known as “tuples.”


Primary key or PK: It's a unique identifier to mark each row of a table; it makes the record mandatorily non-null. It's also known in English as “primary key.”


Foreign, secondary keys or FK: Allows relating rows from various tables (reference to a primary key of another existing table). It's the linking point with another table. It's also known by the acronym in English “foreign key.”


Index key: It's a field that simplifies the search within a table. Usually, they're PK fields.


Connected key or CK: Allows finding uniqueness in a table by combining two fields, since there's no primary key.


Candidate keys: When there's more than one primary key within the table, for example, ID and file number.


In summary, in the columns a type of data is specified and in each row there's a specific value.


The following are some of the relevant concepts in relational databases.





What is SQL?


The acronym SQL refers to “structured query language.” It's the main interface used for RDBs and since 1986 it's a standard of ANSI, the American National Standards Institute.SQL is supported by the most used relational database engines.


This language is used to add, delete or update rows of data, and to manage the different aspects of the database.


What is RDBMS?


It's an RDB management system. In other words, it's the software that lets you work with those databases.


What is data integrity?


It refers to the completeness, coherence and precision of the information. In practice, it's a series of constraints that guarantee the reliability of the data. Most relational databases also let you integrate custom code into triggers.


What is the E-R data model?


The entity-relationship data model is based on a perception supported by the “real world,” which consists of a series of basic objects (they're called “entities”) and the relationships between them.


What are transactions in RDB?


It's one or more SQL instructions that are executed as a sequence of operations. That way, they generate a logical unit of work.


When working with relational databases, a transaction generates a COMMIT or a ROLLBACK. Each instruction is treated in a coherent and reliable way, independently of other transactions.


What is ACID compliance?


ACID is an acronym of the following terms, desirable for database transactions.


Atomic: Requires correctness in the execution of the transaction as a whole. If one part fails, the whole transaction is invalidated.


Consistent: Implies that the data written to the database as part of a transaction complies with the established rules. This includes cascades, constraints and triggers.


Isolated: Allows controlling concurrency and ensuring that each transaction is independent.


Durable: The changes made to the database must be permanent, once the transaction completes correctly.


Finally, among the fundamental concepts in RDB it's necessary to know four shapes used in relational databases.


The rectangles represent entities.


The ellipses represent attributes (usually the candidate key).


The diamonds represent actions that allow relationships between the tables.


The lines represent the union between actions and entities.


Advantages of the relational model


The main advantage of the relational model is the possibility of organizing, relating and managing data.


Other benefits of relational databases are:


They provide an intuitive way to represent data and easy access to the relationships between it.


That's why they're useful for organizations that need to manage large amounts of information in a structured way.


They let you create one or several relationships.


They help avoid the duplication of records: in technical terms, this benefit is known as redundancy reduction.


They ensure referential integrity: when a record is related to another table, its deletion isn't allowed.


It's a flexible method that lets you add, update and delete information (tables and relationships) without changing the primary structure of the database.


It lets you perform complex queries with SQL in a simple way.


In that sense, it's not complex to learn to use RDB: users without deep technical knowledge quickly learn to interact with these databases. 


They support collaboration: different people can access the data and operate simultaneously.


To avoid conflicts, “integrated locking” is used, which prevents access to the data at the same time when it's being updated.





Examples of relational databasesIn practice, relational databases are useful in cases like application logging, transaction processing or inventory tracking, to mention some cases.  


To work with relational databases the aforementioned RDBMS are used. The following are some examples.


MySQL


Microsoft SQL Server


PostgreSQL


MariaDB


Oracle Database


Amazon Aurora


These examples of RDBMS are programs used to create, manage and update relational databases.


There are also cloud-based RDBs like Cloud Spanner, Cloud SQL and Alloy DB. They're platforms that offer services to maintain data; they include management, patching and support capabilities.


Relational and non-relational databases


What is the difference between relational and non-relational databases, also known as NoSQL?


The key is to understand how data is stored and organized in each case.


Unlike what we've reviewed so far, non-relational databases don't store information in a tabular way (that is, in tables) nor are they based on rules.


Instead, data is stored as individual files. In addition, there's no connection between them.


As Google explains in an entry dedicated to RDBs, NoSQL databases follow a flexible model. In that case, they're suitable for storing data that changes frequently, or for applications that use diverse types of data.


Based on that difference, the main value of the relational model is understood: the understanding of the information contained and especially of the relationships between the data.

If you'd like to keep exploring this topic, you can also read the right learning path to become a data analyst.

Recommended Coderhouse courses

If you want to go deeper into data analysis and applied artificial intelligence, Coderhouse has programs for all levels:

• Data Analytics Course: to learn to analyze data, build dashboards and make decisions based on real information.

• AI Engineering Course: to incorporate AI and machine learning models into your data projects.

• Introduction to Artificial Intelligence Course: to understand the AI ecosystem and complement your data profile with knowledge about language models.