What is a relational database and how does it work?

What is a relational database? 

A relational database is a type of database in which data points are connected to each other. Inside a relational database there are rows set up in tables. Each row in the table is a record with an ID (the “key”) and each column in the table holds data attributes. Structured Query Language (SQL) is used to interact with relational databases. SQL and Oracle are just two of the many Relational Database Management Systems (RDMS).

What does a relational database look and act like? 

Designing a relational database happens in five stages -  

1. Define relationships and attributes 
   1. Here, a user decides what they want the database to “do” - how it functions. They define their company’s objectives and gather any materials needed, digital or physical 
2. Define primary keys 
   1. The user gathers data, divides it into tables, and chooses columns as they primary key, which will identify each of the rows 
3. Define relationships 
   1. One to one – this type of “product sales” database has two tables to store optional and non-optional, or sensitive and non-sensitive data. Having two tables creates a one to one relationship 
   2. One to many – a “class roster” database has two tables because there are multiple primary keys needed in the data 
   3. Many to many – this type of “product sales” database needs a third table – a junction table – where each row represents a specific order 
4. Choose column data type -  
   1. The user chooses a data type such as integers, date/time, binary, text, etc 
5. Choose normalization type -  
   1. First normal form – in these tables, every cell contains a single value 
   2. Second normal form – in these tables, the data is 1NF and every non-key column is fully dependent on the primary key 
   3. Third normal form – in these tables, the data is 2NF and the non-key columns are independent of each other 

Benefits of a relational database 

Why use a relational database? It provides insight into your data’s relationships. The database can pull information from different tables without bringing in anything irrelevant. Relational databases are also simple – even though they can handle large sizes and scopes, the information is all centralized. The user can switch back and forth between different tables and navigate efficiently using the unique IDs.  

Example of a relational database 

Let’s say you have an Etsy store that sells cat furniture. Your business takes off and you need a place to manage all your data – you choose to use a relational database. You have to keep track of shipping, costs, online traffic, customer information, inventory, etc. Since your store is digital, your unique key should relate to shipping.  

Inside your relational database you can track how all the pieces of your business relate to shipping. How has your inventory changed since you shipped out 50 new cat couches last month? Who are your top 10 customers, and what does that say about your target demographic? Was Karen really charged three times for her cat hammock? Everything is localized and relevant, and there’s no room for redundancy.  

ACID compliance 

This acronym is pretty common in the data world. Four main properties define relational database transactions: atomicity, consistency, isolation, and durability. These standard properties guarantee a database’s reliability. 

• Atomicity  
  • If the transaction has all information required, it can be completed. If it’s incomplete, the user should wait until all information is collected 
  • This is an “all or nothing” feature – one piece of information can’t occur without its counterpart(s) 
• Consistency 
  • Ensures that any changes to values in an instance are consistent with changes to other values in the same instance 
• Isolation  
  • When there’s multiple users, each user’s transactions are invisible to others until committed, to avoid confusion 
  • There can be different degrees, or types, of isolation 
• Durability  
  • Refers to the ability of the system to recover committed transaction updates if either the system or the storage media fails 
  • Ensures that data changes become permanent once the transaction is committed 

Relational vs nonrelational databases 

Non-relational databases have different ways of storing information. Instead of rows and columns, non-relational databases function in unique and specific ways that assist the needs of their particular data.  

Non-relational databases can hold unstructured data – videos, emails, pictures, etc – in a way that relational databases cannot. Non-relational databases are often referred to as NoSQL. Much like the “is a rectangle a square” phenomenon, a NoSQL database can support a relational query, but a relational database can’t hold unstructured or nonrelational data.  

No type of database is better or worse – it depends on what you need your database to do, what kind of data you have, how much data you have, and what expertise your team has.  

People that use relational databases 

1. Data engineer: designs and builds systems for collecting and analyzing data
2. Database administrator: acts as technical support for databases, optimizing performance through maintenance and tuning
3. Data architect: analyzes the data infrastructure of an organization to improve efficiency
4. Data analyst: interprets data sets from relational databases to solve business problems
5. Data scientist: takes data sets to find patterns and trends, then creates algorithms and data models to forecast outcomes

Needing help troubleshooting your company’s relational database issues, or looking for an upgrade? Let us know!