What Are Relational Databases

Relational databases represent data in a tabular form consisting of rows and columns. In this article, we'll learn the basics of relational database management systems (RDBMS) and how data is represented and stored in a relational database.

Relational database

 
A relational database is a DBMS that represents the data in a tabular form of rows and columns. A table is a representation of an entity. A table is a combination of columns and rows. Each column in a table represents an attribute of the entity, also known as fields or properties. Each row in a table represents a record, the data associated with an entity.
 
The following tabular data represents a Customer entity with attributes including ID, Company Name, Contact Name, Contact Title, Address, City, and so on. Each column of the table is an attribute (or property) of a Customer entity. Each row of the table represents a customer data.
 
RDBMS  
 
Each column of a table has a data type that represents the kind of data a column can store. For example, the company name or customer name is a varchar type that can store characters, but a zip code is a numeric field and can store numbers only.
History of relational databases
 
Relational database concept was introduced by E. F. Codd at IBM in 1970 via his research paper "A Relational Model of Data for Large Shared Data Banks". Later in 1974, IBM introduced System R, that was a prototype of RDBMS.
 
Oracle database was the first commercially RDBMS that was released in 1979 by Relational Software, now Oracle Corporation.
Today, RDBMS are the most popular database management systems in the world.
 

Relationships in RDBMS

 
Relational databases are relational because of their relationships feature. RDBMS supports relationships between their entities and their attributes. A column of a table can have a relationship with another column of another table.
 
Let's look at the following 4 tables of Northwind database, Customer. Orders, Order Details, and Products.
 
RDBMS Primary Key 
 
In the above diagram, a Customer can have multiple orders and each order can have multiple products. The relationship between two table columns are represented by
 

Primary keys and foreign keys

 
A table in RDBMS typically has a unique private key (PK) that uniquely identifies each row in a table. The private key can be a single column or a combination of multiple columns. A primary key ensures that each row in a table is unique. PK is also used as a constraint to force data integrity. A table can contain only one primary key.
 
A foreign key (FK) is a column or combination of columns, that is used to create a relationship between two tables. When a PK is referenced in another table, it is called a foreign key on the referenced table.
 

RDBMS operations

 
RDBMS allow Create, Read, Update, and Delete (CRUD) operations.
  • Create operation includes inserting new records in a table.
  • Read operation includes selecting and reading records from a table.
  • Update operation includes updating values of existing records in a table.
  • Delete operation includes deleting existing records from a table.
 

SQL and relational databases

 
Structured Query Language (SQL) is the language that is used to query, update, and delete data in relational database management systems (RDBMS). SQL is a standard query language. SQL language’s queries are also known as SQL commands or SQL statements.
SQL provides DML (Data Manipulation Language) commands that are used insert, update and delete data. Common DML commands are INSERT, UPDATE and DELETE. 
  • SQL DDL (Data Definition Language) commands are used to manipulate the database objects such as database itself, tables, and other objects. 
  • SQL DCL (Data Control Language) commands are used to control the access on a database includes privileges on database objects. Common DCL commands are GRANT and REVOKE. 
  • SQL DQL (Data Query Language) command allows us to fire queries to the database and get the data from the database. The DQL command is SELECT.
Learn more: What is SQL
 

Popular relational databases

 
There are hundreds of RDBMS. Here is a list of the most popular RDBMS. 
  1. Oracle
  2. MySQL
  3. SQL Server
  4. PostgreSQL
  5. IBM DB2
  6. Microsoft Access
  7. SQLite
  8. MariaDB
  9. Informix
  10. Azure SQL
 

Oracle

 
Oracle developed by Oracle Corporation is the most popular relational database system (RDBMS). Not only Oracle is an RDBMS, but also provides functionality for Cloud, Document Store, Graph DBMS, Key-value storage, BLOG, and PDF Storages. Recently. Oracle just announced an autonomous feature that allows the database to be intelligent and self-managed.
 
The current version of Oracle Database is 18c.
 
Oracle database is a relational database (RDBMS). Relational databases store data in a tabular form of rows and columns. The column of a database table represents the attributes of an entity and rows of a table stores records. An RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism is called an object-relational database management system (ORDBMS). Oracle Database has extended the relational model to an object-relational model, making it possible to store complex business models in a relational database.
 
One characteristic of an RDBMS is the independence of physical data storage from logical data structures.
 
In Oracle Database, a database schema is a collection of logical data structures or schema objects. A database user owns a database schema, which has the same name as the user name.
 
Schema objects are user-created structures that directly refer to the data in the database. The database supports many types of schema objects, the most important of which are tables and indexes.
 
A schema object is one type of database object. Some database objects, such as profiles and roles, do not reside in schemas.
 

MySQL

 
MySQL is the world’s most popular database that is open source and free. MySQL was acquired by Oracle as a part of Sun Microsystems acquisition in 2009.
 
In MySQL, the SQL part of “MySQL” stands for “Structured Query Language”. SQL is the most common standardized language used to access databases. Depending on your programming environment, you might enter SQL directly (for example, to generate reports), embed SQL statements into code written in another language, or use a language-specific API that hides the SQL syntax.
 
Key properties of MySQL: 
  • MySQL is a database management system.
  • MySQL databases are relational.
  • MySQL software is Open Source.
  • The MySQL Database Server is very fast, reliable, scalable, and easy to use.
  • MySQL Server works in client/server or embedded systems. 

SQL Server

 
SQL Server database developed by Microsoft is one of the most popular databases in the world. Initially launched in 1989 and written in C, C++, SQL Server is now widely used among major companies. SQL Server is also a part of Microsoft’s Azure cloud as Azure SQL Server. The current version of SQL Server is SQL Server 2019.
 
Similar to Oracle and MySQL, SQL Server is also a relational database management system (RDBMS).
 
Some popular SQL Server editions are:
 
Azure SQL Database is the cloud-based version of Microsoft SQL Server, presented as a platform as a service offering on Microsoft Azure.
 
Compact (SQL CE), the compact edition is an embedded database engine. Unlike the other editions of SQL Server, the SQL CE engine is based on SQL Mobile (initially designed for use with hand-held devices) and does not share the same binaries. Due to its small size (1 MB DLL footprint), it has a markedly reduced feature set compared to the other editions. For example, it supports a subset of the standard data types, does not support stored procedures or Views or multiple-statement batches (among other limitations). It is limited to 4 GB maximum database size and cannot be run as a Windows service, Compact Edition must be hosted by the application using it. The 3.5 version includes support for ADO.NET Synchronization Services. SQL CE does not support ODBC connectivity, unlike SQL Server proper.
SQL Server Enterprise Edition is the main database most companies buy that comes with every product feature.
 
SQL Server Developer Edition includes the same features as SQL Server Enterprise Edition but is limited to one license only that is used for development purposes by software developers.
 

PostgreSQL

 
PostgreSQL is a powerful, open-source, object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform. The current version of PostgreSQL is 11.4 that was released on 20 June 2019. PostgreSQL is written in C language and managed by the PostgreSQL Global Development Group.
 
PostgreSQL is known for its architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open-source community behind the software to consistently deliver performant and innovative solutions.
 

IBM DB2

 
IBM Db2 database is a relational database that delivers advanced data management and analytics capabilities for your transactional and warehousing workloads. This operational database is designed to deliver high performance, actionable insights, data availability, and reliability, and it is supported across Linux, Unix and Windows operating systems.
 
The Db2 database software includes advanced features such as in-memory technology, advanced management and development tools, storage optimization, workload management, actionable compression, and continuous data availability.
 
With version 11.5, Db2 includes added AI functionality so that businesses can embrace forward-looking data science and AI technologies to retain competitive differentiation. Now you can leverage one platform that is both powered by AI and built for AI to optimize performance and availability while empowering your data scientists to find deeper insights.
 

SQLite

 
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. 
 
The SQLite file format is stable, cross-platform, and backward compatible and the developers pledge to keep it that way through at least the year 2050. SQLite database files are commonly used as containers to transfer rich content between systems and as a long-term archival format for data. There are over 1 trillion SQLite databases in active use.
 

MariaDB

 
MariaDB Server is one of the most popular database servers in the world. It’s made by the original developers of MySQL and guaranteed to stay open source. Notable users include Wikipedia, WordPress.com, and Google.
 
MariaDB turns data into structured information in a wide array of applications, ranging from banking to websites. It is an enhanced, drop-in replacement for MySQL. MariaDB is used because it is fast, scalable and robust, with a rich ecosystem of storage engines, plugins, and many other tools make it very versatile for a wide variety of use cases.
 
MariaDB is developed as open-source software and as a relational database, it provides an SQL interface for accessing data. The latest versions of MariaDB also include GIS and JSON features.
 

Informix

 
IBM Informix® is a fast and flexible database with the ability to seamlessly integrate SQL, NoSQL/JSON, and time series and spatial data. Key features of Informix includes real-time analytics, always-on transactions, and simplicity.
 

Azure SQL

 
Azure SQL Database is a general-purpose relational database-as-a-service (DBaaS) based on the latest stable version of Microsoft SQL Server Database Engine. SQL Database is a high-performance, reliable, and secure cloud database that you can use to build data-driven applications and websites in the programming language of your choice, without needing to manage infrastructure.
 
Azure SQL is fully managed, and fees are pay-per-use. Azure SQL provides advanced security with Azure AD authentication, Virtual Networks, Firewalls, Always Encrypted connections. Identify threats and vulnerabilities with built-in security.
 

More related articles

Here are some more articles that you may be interested in:

References

 
https://en.wikipedia.org/wiki/Relational_database
https://db-engines.com/en/ranking/relational+dbms