Relational Database

A relational database is a collection of information that stores data in one or more tables (or "relations") with columns and rows. This makes it easy to see how different data structures relate to each other. Relationships are logical links between tables that are set up by how the tables interact with each other.

Relational database working model

Think of a relational database as a group of spreadsheet files that help businesses organise, manage, and link data. In the relational database model, each "spreadsheet" is a table that stores information in the form of columns (called "attributes") and rows (records or tuples).

Attributes (columns) tell us what kind of data we're dealing with, and each record (row) has the value of that type of data. In a relational database, each row has an attribute called the primary key, which is a unique identifier. A foreign key, which is a reference to the primary key of another table, can be used in a row to make a connection between two tables.

Let's see how the relational database model actually works:

Say you have two tables: one for Customers and one for Orders.

The Customer table contains data about the customer: 

• Customer ID (primary key)
• Customer name
• Billing address
• Shipping address 

In the Customer table, the customer ID is a primary key that uniquely identifies who the customer is in the relational database. No other customer would have the same Customer ID. 

The Order table contains transactional information about an order: 

• Order ID (primary key)
• Customer ID (foreign key)
• Order date 
• Shipping date
• Order status

Here, the Order ID is the primary way to find a specific order. You can link a customer to an order by linking the customer ID from the Customer table to a foreign key.

Based on the shared customer ID, the two tables are now linked. This means that you can query both tables to make formal reports or use the data for other applications. For example, a retail branch manager could make a report of all the customers who bought something on a certain date or find out which customers' orders were late in the last month.

The above explanation is meant to be easy to understand. But relational databases are also great at showing relationships between data that are very complicated. This lets you use the same data in more than one table as long as it fits the relational schema of your database.

Since the data is organised by relationships that have already been set up, you can query the data in a declarative way. A declarative query is a way to tell the system what you want to get out of it without telling it how to get the result. This is the main difference between a relational system and other systems.

SAMPLE

Relational databases: A few examples

Now that you know how relational databases work, you can start to learn about the many relational database management systems that use the relational database model. A relational database management system (RDBMS) is a program that is used to create, update, and manage relational databases. MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, and Oracle Database are just a few of the most well-known RDBMSs.

Advantages of Relational Databases

The main benefit of the relational database model is that it makes data easy to understand and makes it easy to get to data points that are related. Relational databases are most often used by organisations that need to manage a lot of structured data. This includes keeping track of inventory, processing transactional data, and logging application data.

Using relational databases to manage and store your data also has a lot of other benefits, such as:

Flexibility

It's easy to add, update, or delete tables, relationships, and other data at any time without changing the overall structure of the database or affecting applications that are already running.

ACID compliance

Relational databases use ACID (Atomicity, Consistency, Isolation, Durability) performance to make sure that data is valid even if there are mistakes, failures, or other problems.

Usage-friendly

SQL makes it easy to run complex queries, so even people who aren't tech-savvy can learn how to use the database.

Collaboration

Multiple people can operate and access data simultaneously. Data that is being changed can't be accessed at the same time because of built-in locking.

Built-in Security

Role-based security makes sure that only certain users can access data.

Database normalization

Relational databases use a method called "normalization" to make sure that data is correct and doesn't repeat itself.

How to configure in Quixy?

What is ER Diagram? How does it show cases relationship between data tables?

An ER (Entity-Relationship) diagram is a type of data modeling that illustrates the logical structure of databases. It shows the entities (things) in the database and the relationships between them. Here's how it works:

1. Entities: Entities are the things or objects in the database that are represented by data tables. For example, in a database for a university, entities could include Student, Course, and Instructor.
2. Attributes: Attributes are the properties or characteristics of entities. For example, a Student entity might have attributes like Student ID, Name, and Date of Birth.
3. Relationships: Relationships describe how entities are related to each other. There are different types of relationships:
   1. One-to-One: Each entity in the first set is related to exactly one entity in the second set.
   2. One-to-Many: Each entity in the first set is related to zero or more entities in the second set.
   3. Many-to-Many: Each entity in the first set is related to zero or more entities in the second set, and each entity in the second set is related to zero or more entities in the first set.