What is a Relational Database (RDBMS)?

SQL and relational databases work together to store and manage structured data efficiently. A relational database organizes information in tables with rows and columns, while SQL (Structured Query Language) provides the commands to interact with this data.

Let’s start by understanding what a relational database is.

What is a relational database

A relational database is a type of database that can store data in tables similar to a spreadsheet. Such tables organize information in rows and columns:

• Every row is a record
• Each column represents an attribute of that record.

It is termed as “relational” because it links the tables to one another using keys.

For example, imagine a school system having a Students and Courses table. A third table named Enrollments could relate to them by showing which student is enrolled in which course.

Relational databases differ from regular databases in several key ways. The following table summarizes these differences:

In short, not all databases are relational.

What is a relational database management system (RDBMS)?

A relational database management system (RDMS) is software for creating, managing, and interacting with relational databases. It handles tasks such as organizing data into tables, linking tables, enforcing rules, and running SQL queries. Without an RDBMS, raw data would sit around with no structure or logic.

Think of the relational database as your data warehouse, and the RDBMS as the system that keeps it clean, searchable, and connected. Key features of RDBMS are:

• Data is organized with a defined schema.
• Uses keys to connect data across tables.
• Queries and data operations are performed using SQL.
• Supports ACID properties for consistent data changes.
• Manages permissions to keep data secure.

With the knowledge of RDBMS, let’s break down the core building blocks that make it all work.

Key components of RDBMS

We can understand relational databases by examining their fundamental elements:

Tables

A table is where data is stored. We can think of it as a spreadsheet where each table focuses on a specific entity. For example, a Students table might hold information about all enrolled students:

• Each row is a single record (e.g., one student).
• Each column represents an attribute (e.g., name, grade, date of birth).

Primary key

A primary key uniquely identifies each row in a table. It ensures that no two rows are the same and helps with faster lookup.

In our Students table, student_id is the primary key. Each student has a unique ID, and none is repeated.

Foreign key

A foreign key links one table to another, establishing a relationship. It references the primary key of another table.

For example, in an Enrollments table:

Here, student_id is a foreign key that refers to the Students table. It connects each enrollment to the student it belongs to.

Relationships

Foreign keys allow you to create relationships between tables. These can be:

• One-to-One: One student has one locker.
• One-to-Many: One student can enroll in many courses.
• Many-to-Many: Many students can take many courses (usually implemented using a third table like Enrollments).

These relationships help reduce redundancy and improve data consistency.

Constraints

Constraints are rules applied to table columns to maintain data quality.

Common constraints include:

• NOT NULL: A column should always have a value
• UNIQUE: No duplicate values are allowed
• CHECK: Sets custom rules
• DEFAULT: Sets a fallback value

These rules are enforced by the RDBMS whenever data is added or changed.

Indexes

An index is like a shortcut for your database. If you often search for students by name, creating an index on the name column will help the RDBMS find matching records faster.

Note: Indexes improve read performance but can slow down writes if overused.

Schema

A schema is the overall structure or blueprint of the database. It defines:

• What tables exist
• What columns do they have
• Data types, constraints, and relationships between tables

In short, it describes how your entire database is organized.

The following section explains how the RDBMS works at its core.

How does RDBMS work?

A relational database management system stores data in tables that can be manipulated using SQL. When a query is written, the system looks at the tables involved, finds the rows that match the conditions, and gives the results. It uses those relationships to pull the right data together if multiple tables are connected.

It also ensures consistency in the data. For example, it won’t let us add a value that doesn’t exist in a related table. It checks that each record is unique where needed and prevents changes that could break the links between tables.

Let’s work with SQL to manipulate data in the relational database.

Writing SQL query for RDBMS

Let’s start by creating a table. Here’s the syntax:

CREATE TABLE table_name ( 
column1_name datatype constraint, 
column2_name datatype constraint, 
); 

To create a named Students:

CREATE TABLE Students ( 
  StudentID INT PRIMARY KEY, 
  Name VARCHAR(50), 
  Age INT, 
  Grade INT 
); 

Copy to clipboard
Copy to clipboard

The Students has four columns: an integer ID, a name (text), an age, and a grade level.

Now, let’s add some data to this table. The general syntax for adding data to a table is:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); 

Let’s add some students to our table using the following code:

INSERT INTO Students (StudentID, Name, Age, Grade) 
VALUES 
  (1, 'Aanya', 14, 9), 
  (2, 'Rohan', 15, 10), 
  (3, 'Meera', 13, 8); 

Copy to clipboard
Copy to clipboard

To view the data, we use SELECT. Here’s how it works:

SELECT * FROM Students; 

Copy to clipboard
Copy to clipboard

This returns all rows and columns from the table.

But what if we want to see only students in Grade 10? Use the following:

SELECT Name FROM Students WHERE Grade = 10; 

Copy to clipboard
Copy to clipboard

If some data needs to be updated, we can also use UPDATE as follows:

UPDATE Students SET Grade = 10 WHERE StudentID = 3; 

Copy to clipboard
Copy to clipboard

Or, if some data is to be deleted, use DELETE:

DELETE FROM Students WHERE StudentID = 1; 

Copy to clipboard
Copy to clipboard

This is how SQL lets us control and interact with data inside a relational database. Let’s look at the tools that run these commands behind the scenes.

Popular RDBMS software

Several relational database systems are used across industries. Even though each one follows the same core principles, they differ in performance, features, and use cases:

• MySQL: It is one of the most popular open-source RDBMS tools and is known for speed. Commonly used in web development (WordPress, Facebook).

• PostgreSQL: Open-source with a strong focus on standards and complex queries. It supports features like custom data types and full-text search.

• SQLite: A lightweight RDBMS that runs inside applications, not on a server. It’s used in mobile apps, embedded systems, and small desktop programs.

• Oracle Database: A commercial RDBMS used in large enterprises. It supports massive data volumes and comes with extensive features for security, scalability, and performance.

• Microsoft SQL Server: A commercial option by Microsoft, often used in Windows-based environments. It’s tightly integrated with other Microsoft tools like Excel and Power BI.

Why do so many systems rely on RDBMS?

Advantages of RDBMS

RDBMSs are popular as they solve real problems when managing large, structured data sets. Here’s what they bring to the table:

• Data integrity: RDBMSs keep data accurate and consistent with structured rules. You won’t accidentally end up with duplicate or mismatched entries.

• Easy relationships: Tables can be linked using keys, which means you can connect data across multiple tables without duplicating it.

• Scalability: Most RDBMS software handles growing data smoothly.

• Security: Using built-in permission systems, you can control who sees or edits what, right down to individual tables or rows.

• Standardized language (SQL): SQL is supported across all major RDBMS tools, so once you learn it, you can apply it almost anywhere.

• Backup and recovery: These systems usually include built-in features to back up data regularly and restore it in case of failure.

Conclusion

In this article, we explored how relational databases work, what makes an RDBMS, and how it stores and organizes data in structured tables using rows and columns. We also walked through key SQL operations like creating tables, inserting data, and running queries, while highlighting the core benefits of using an RDBMS.

If you’re ready to start working hands-on with SQL and relational databases, explore Codecademy’s Learn SQL course.

Frequently asked questions

1. What are the four types of DBMS?

The four main types of DBMS (Database Management Systems) are:

• Hierarchical DBMS: Organizes data in a tree-like structure.
• Network DBMS: Uses a graph structure to allow multiple relationships.
• Relational DBMS (RDBMS): Stores data in tables related by keys.
• Object-oriented DBMS: Stores data in objects, like in object-oriented programming.

2. What is DBMS and RDBMS with example?

A DBMS is software that allows you to create, manage, and retrieve data from databases. For example, file-based systems that store information in flat files.

An RDBMS is a type of DBMS that organizes data into related tables. For instance, MySQL or PostgreSQL lets you create tables like Students and Courses, which are linked using keys.

3. What is the difference between SQL and database?

A database is where data is stored. It’s the actual system that holds your information.

SQL (Structured Query Language) is the language you use to interact with that database—like retrieving, inserting, updating, or deleting data.

4. What is the role of SQL in relational databases?

SQL acts as the communication layer between users and databases. In relational databases, SQL lets you create tables, define relationships, insert and modify data, and run queries to extract insights.

5. What is an example of a relational database?

MySQL is an example of a relational database. In MySQL, you can create a Customers table and an Orders table and relate them using a customer_id to see which customer placed which order.