Demystifying Relational Databases

Hello, dev!

Today, we’ll talk simply about what relational databases are, how they work, and why they’re so useful. We’ll also explore key concepts like DBMS and ACID, and at the end, we’ll share some valuable tips for those who want to dive deeper into the topic.

A relational database is a way to store data in tables with rows and columns. Each row represents a complete record, while each column holds specific details, like a customer’s name or a product’s price.

The unique advantage of relational databases is their ability to connect this information. This helps answer questions like: "Which customer bought a specific product?" Let’s imagine a small store that uses a database to manage its sales. It has three tables: one for customers, another for products, and a third for purchases. Here’s an example:

With these tables, we can see that Pedro (customer 2) bought rice (product 3) and Jorge (customer 1) bought milk (product 1). This kind of connection is essential for understanding purchase history and making informed business decisions. Without it, organizing such information would be much more complicated.

What are DBMSs?

DBMSs (Database Management Systems) are programs designed to manage databases. They help organize, access, and protect data. Additionally, they ensure information remains consistent and secure, even when multiple people use the system simultaneously. Popular examples of DBMSs include PostgreSQL, MySQL, and SQL Server. These systems also allow you to insert, update, delete, or retrieve information using SQL commands.

What Makes Relational Databases So Useful?

Rules for Consistent Data

Relational databases follow rules to ensure information is accurate and organized. These rules include:

• 
  Primary Key (PK): Uniquely identifies each record in a table, like a person’s ID number.


• 
  Foreign Key (FK): Links one table to another, enabling relationships between information. For example, the "foreign key" in the purchases table points to the corresponding customer.


• 
  UNIQUE: Ensures certain values, like an email, don’t repeat in the table.


• 
  NOT NULL: Requires a column to have a value. For instance, a customer must have a name.

These rules help keep data organized and reliable.

Security and Control

DBMSs allow administrators to control who can access or modify data. This protects sensitive information, like financial details or customer data, from unauthorized access.

Data Recovery

If something goes wrong, such as a system failure, DBMSs provide backup and recovery features. This ensures data can be safely restored, minimizing issues.

What is ACID?

Transactions in databases are operations that include multiple actions, such as recording a sale. For example, when buying something online, the system must log the customer, the order, and the payment. All this must happen in a coordinated way. These transactions follow ACID rules:

• 
  Atomicity (Atomic): Either everything works, or nothing happens. If one step fails, the system cancels the entire operation.


• 
  Consistency (Consistent): Ensures the database rules are followed. For instance, you can’t log a purchase for a non-existent customer.


• 
  Isolation (Isolated): Transactions occur independently, avoiding interference between them.


• 
  Durability (Durable): Once completed, the transaction is permanent, even in the event of system failures.

These features make transactions secure and reliable, essential for complex systems.

Conclusion

Relational databases are essential tools for organizing and managing information securely and efficiently. They are widely used across various industries, such as banking systems, which require reliable financial transactions, or e-commerce platforms that manage inventories, customers, and orders simultaneously.

With these technologies, you can trust that data will always be well-structured and accessible. This allows businesses to make decisions based on accurate information, improving efficiency and performance. In this text, we covered the fundamental concepts of relational databases in a basic way. To learn more, we recommend exploring the following resources:

• The book "Database Systems" by Elmasri and Navathe ().


• The W3Schools SQL guide () and LeetCode ().

Additionally, there are other types of databases, like non-relational ones, designed for specific needs—but that’s a topic for another day.