What is a relational database and how an RDBMS works?

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.