Introduction
There are dozens of types of databases, and one of the most popular database types is relational. 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. SQL is used to access and manipulate RDBMS.
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 customer data.
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
The 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, a prototype of RDBMS.
Oracle database was the first commercial RDBMS released in 1979 by Relational Software, now Oracle Corporation.
Today, RDBMS is the most popular database management system in the world.
Relationships in RDBMS
Relational databases are relational because of their relationship 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.
Look at the following four tables of the Northwind database, Customer. Orders, Order Details, and Products.
In the above diagram, a Customer can have multiple orders, and each order can have multiple products. The relationship between two table columns is 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 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. Learn more about the Difference between Primary Keys and Foreign Keys.
RDBMS operations
RDBMS allows 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 queries are also known as SQL commands or SQL statements.
SQL provides DML (Data Manipulation Language) commands to 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 the database itself, tables, and other objects.
- SQL DCL (Data Control Language) commands control access to a database, including 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.
- Oracle
- MySQL
- SQL Server
- PostgreSQL
- IBM DB2
- Microsoft Access
- SQLite
- MariaDB
- Informix
- Azure SQL
Oracle
Oracle, developed by Oracle Corporation, is the most popular relational database system (RDBMS). Oracle is an RDBMS and provides functionality for Cloud, Document Store, Graph DBMS, Key-value storage, BLOG, and PDF Storage. 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 the rows of a table store 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 with 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 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 open-source and free database. Oracle acquired MySQL 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 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 and C++, SQL Server is now widely used among significant 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.
Like Oracle and MySQL, SQL Server is 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.
SQL Server Enterprise Edition is the main database most companies buy with every product feature.
SQL Server Developer Edition includes the same features as SQL Server Enterprise Edition but is limited to only one license 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, which was released on 20 June 2019. PostgreSQL is written in C 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 deliver performant and innovative solutions consistently.
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 provide 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 businesses can embrace forward-looking data science and AI technologies to retain competitive differentiation. Now you can leverage one platform 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 with 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 is guaranteed to stay open source. Notable users include Wikipedia, WordPress.com, and Google.
MariaDB turns data into structured information in various applications, 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 that make it very versatile for various use cases.
MariaDB is developed as open-source software and 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 that seamlessly integrates SQL, NoSQL/JSON, and time series and spatial data. Key features of Informix include 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, and Always Encrypted connections. Identify threats and vulnerabilities with built-in security.
Here is a detailed article, What is Azure SQL Database.
Do you know what RDBMS is the most popular in the world? Is it SQL Server? Oracle or MySQL? Check out here: The Most Popular RDBMS in the World.
Join the conversation! Your thoughts help the community grow.