For the best web experience, please use IE11+, Chrome, Firefox, or Safari

What is a relational database and how an RDBMS works?

What is a relational database?

A relational database is a structured data storage system that uses a specific model known as the relational model. This model is based on the principles of data organization into tables with rows and columns, where each piece of data is related to other data through keys, creating a network of interconnected information. The primary purpose of these databases is to effectively store and organize structured data.


What is a structure and model of a relational database?

To truly understand how a relational database works, it is essential to dig into its structure and model. The relational model is based on the data being organized into tables (rows and columns), the keys involved, and the relationships associated between tables.

Let’s talk about them one by one.

Tables: Tables, also known as relations, are the fundamental building blocks and structure of a relational database. Every table has rows and columns, each row contains a single record corresponding to each column. Columns in a table represent attributes of the data stored in each row.

Keys: Keys play a crucial role in organizing and establishing relationships between tables. They ensure data integrity and data accuracy. There are many keys in a relational database. Two examples include a primary key, which is a unique identifier for each record in a table that cannot have NULL values and a foreign key, which is a primary key in another table that establishes a link between the data in two tables.

Relationships: In a relational database, relationships help create connections between tables based on common columns. They are implemented using primary and foreign keys which ensures data in related tables is synchronized. Below are several types of relationships in a relational database.

  1. One-to-One (1:1): In a one-to-one relationship, each record in one table is associated with a single record in another table. For instance, a customer and their passport information can be modeled as a one-to-one relationship.
  2. One-to-Many (1:N): In a one-to-many relationship, a record in one table can be related to multiple records in another table. An example is the relationship between a customer and their orders. One customer can have multiple orders.
  3. Many-to-Many (N:N): Many-to-many relationships involve multiple records in one table being related to multiple records in another table. To represent this, an intermediary table is used. For instance, students and courses relationship, where a student can enroll in multiple courses, and a course can have multiple students.

A quick example of the relational database structure with tables, “Students, Enrollment and Courses” with Many-to-Many relationships is shown below. StudentId is a primary key, which means it uniquely defines each record in the table Students. Foreign keys (StudentId, CourseId) in the Enrollment table establish a link between the data in the Students and Enrollment tables. Here, a bridge or junction table, Enrollment is created between Students and Courses table to record valid relationships between the students and their courses. Each row in the Enrollment table corresponds to a single enrollment, thereby enforcing referential integrity through foreign keys to the Students and Courses tables.


Why use a relational database?

Relational databases hold an important place in the world of data science. Data is like trash if not organized and handled carefully. Relational databases help collect data from multiple tables to derive useful information. The primary purpose of using relational databases is to remove redundancy by eliminating duplicate data, thus maintaining the integrity of data as well as ensuring efficient data storage.


What are the benefits of relational databases?

There are several advantages of relational databases:

  1. They enforce data integrity through constraints leading to data accuracy and consistency.
  2. They maintain ACID (Atomicity, Consistency, Isolation and Durability) properties to ensure reliable transactions.
  3. Relational databases support high concurrency which means multiple users can access the data at the same time while protecting data integrity.
  4. Relational databases make use of SQL (Structured Query Language) for querying and manipulating data. SQL is widely used across different relational database management systems (RDBMS) and is an easy to learn and use language.


What is an example of a relational database?

Consider a simple example of an online store. You might have two tables, one for Customers and another for Orders. The "Customers" table would contain information like CustomerId, Name, and Address, with CustomerId as the primary key. The "Orders" table would include information on OrderNumber, Date, CustomerId (as a foreign key), and Product details. This relational structure allows you to link orders to specific customers efficiently.

Another quick example could be of an Airport database, with tables like:

  • Airlines: AirlineCode PK, AirlineName, ConnectNo, etc.
  • Flights: FlightNo PK, AirlineCode FK, AirportInfo, TimeInfo, etc.
  • Passengers: PassengerId PK, FirstName, LastName, DOB, PhoneNo, etc.
  • Tickets: TicketNo PK, PassengerId FK, FlightNo FK, TicketPrice, DateOfPurchase, etc.

Here, each airline can operate multiple flights, but each flight is associated with only one airline. Each flight can have multiple tickets issued, but each ticket corresponds to only one flight. Each passenger can have multiple tickets, but each ticket is associated with only one passenger. Thus, having One-to-Many relationships between the tables.


What is an RDBMS?

The software used to store, manage, query, and retrieve data stored in a relational database is called a relational database management system (RDBMS). The RDBMS provides an interface between users and applications and the database, as well as administrative functions for managing data storage, access, and performance. It stores data in tables where each table consists of rows and columns. Some of the most well-known RDBMSs include MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, and Oracle Database.


Where can a relational database be used?

Relational databases have been extensively used in various industries and applications where structured data management is required. From financial services, banking, healthcare, and education to e-commerce, telecommunications, billing systems and so on. The choice to make use of a relational database depends on several requirements of an application like data consistency, integrity and the need for complex queries.


What is the difference between a relational database vs NoSQL?

Relational and NoSQL databases are two different kinds of systems designed to manage and store data. It is important to note that the choice to make use of relational vs non-relational databases depends on various factors like the project’s specific requirements, nature of the data, scalability needs, etc.

The below table highlights the major differences between them:


When to use relational vs non-relational databases

It is imperative to carefully evaluate data characteristics, your project’s specific requirements and other important needs before choosing between relational databases and NoSQL databases.

Relational databases are a solid choice when:

  • Data structure is well-defined: Your data has a clearly defined and unchanging structure.
  • ACID compliance is critical: You require transactional consistency and data integrity.
  • Complex queries: Your application demands complex queries and joins.

On the other hand, NoSQL databases are more suitable when:

  • Data is unstructured or evolving: Your data doesn't fit neatly into tables and rows.
  • Scalability is essential: You need to handle high volumes of data with distributed architectures.
  • Fast writes and reads: Your application requires high-speed data insertion and retrieval.



Whether you're working with widely used systems like SQL Server, MySQL, PostgreSQL, or Oracle, or exploring newer NoSQL alternatives, the principles of the RDBMS and the relational model provide a solid foundation for managing and harnessing the power of data.

The structured approach to data management, support for ACID transactions, and robust query capabilities make relational databases indispensable for applications where data integrity and complex querying are paramount.


Foglight Cloud Demo

See how Foglight Cloud provides deep performance monitoring, can help reduce TCO, and innovate & add value to your core business.