Relational Databases Overview

A Relational Database Management System (RDBMS) stores data in a structured format using rows and columns. In this article, we’ll elaborate on the features of RDBMS, SQL syntax, and some of its advantages and drawbacks.

What makes Relational Databases Stand Out?

Structured Data

Relational databases store data in tables with rows and columns. Each column has a data type (e.g., INT, VARCHAR, DATE), set of constraints (like mandatory or unique fields).

Relationships

The relationships between tables are the core concept of RDMBS.
There are three types of relationships:

• One to One
• One to Many
• Many to Many

Normalization

Normalization is a process of organizing data efficiently to reduce redundancy and improve integrity. This manifested through the First, Second, and Third Normal Forms.

ACID Compliance

Relational databases follow the ACID (Atomicity, Consistency, Isolation, Durability) principles.

With ACID, we can perform multiple database operations (like INSERT, UPDATE, DELETE) in a single scope. If just one of those fails, everything is rolled back. On the other hand, if the transaction is committed, it is permanently saved even after the system crashes.

SQL Syntax

The language used to script RDMBS is Structured Query Language (or simply SQL).

ETL

The ETL transformations are crucial for data migration across systems. It consists of three parts:

• Extract — pull data from different sources (tables, databases, files)
• Transform — modify the data if necessary
• Load — save data into the target system

Popular tools:

• SSIS (SQL Server Integration Services) — Used for Microsoft SQL Server ETL workflows.
• Snowflake — A cloud-based data warehouse optimized for ETL.

Data warehouses

Relational databases can be integrated with data warehouses for analytics and business intelligence. The table contents can be exported as RDL (Report Definition Language) files and imported into report-generating tools such as SSRS (SQL Server Reporting Services).

Integrations with External Systems

Relational databases can integrate with various platforms for hosting or automation. These include:

• Azure DevOps integration (for SQL Server)
• AWS RDS (Provides global hosting solutions for different SQL databases)
• Excel spreadsheets (SQL tables can easily be imported into Excel)

Documentation

Relational databases can be easily visualized using Entity-Relational Diagrams that show how tables are related using tools like DrawSQL, Lucidhart, or Draw.io.

Battle-tested

SQL has been around since the 1970s and is used in enterprise-scale projects (such as banks, healthcare, governments, and Fortune 500 companies) and startups.

Popular Relational Databases:

• Microsoft SQL Server
• Oracle DB
• MySQL
• MariaDB
• PostgreSQL, etc.

SQL Syntax

SQL is all about organizing data in any way you like.

Create Table

• Each table has a unique name
• Set of columns
• Data type associated with each column

-- SQL Server:
CREATE TABLE Users (
   ID INT IDENTITY(1,1), -- autoincrement ID BY 1 on each insert
   Name VARCHAR(50) NOT NULL, -- String text up to 50 characters
   Age INT, -- Whole number
   Home_town VARCHAR(100)
)

Projection

The SELECT statement is used to retrieve data from tables.

• The users can retrieve one or multiple items from the table:

SELECT * FROM Users
-- or
SELECT Name, Age FROM Users

• Limit the number of items returned:

-- SQL SERVER
SELECT TOP 100 Name, Age FROM Users

-- MYSQL
SELECT Name, Age FROM Users LIMIT 100

• Write specific conditions:

SELECT * FROM Users WHERE Age = 30
-- or
SELECT * FROM Users WHERE Age > 100
-- or
SELECT * FROM Users WHERE Age BETWEEN 10 AND 100
-- or
SELECT * FROM Users WHERE Name LIKE '%Batman%'

• Sort the results:

SELECT Age FROM Users ORDER BY Age DESC

• Or all at once:

SELECT TOP 100 Name, Age FROM Users
WHERE Age > 100 OR Name LIKE '%Batman%'
ORDER BY Age DESC

Data Manipulation

• Create new records using insert:

INSERT INTO Users (Name, Age)
VALUES
('Mirza', 30),
('Armin', 32),
('Amar', 31),
('Hamza', 24)

• Update existing records

UPDATE Users
SET Name = 'Mirzly'
WHERE ID = 5

• Delete records

DELETE FROM Users
Where Name = 'Mirzly'

• Add or remove columns and constraints. Change column data types:

ALTER TABLE Users
ADD Email varchar(255); -- adds email column

• Delete tables

DROP TABLE Users

Aggregations

• Count records:

SELECT COUNT(*) FROM USERS -- e.g. 141 Users

• Find minimum and maximum values:

SELECT MIN(Age) FROM Users -- e.g. 18

SELECT MAX(Age) FROM Users -- e.g. 106

• Aggregate all records:

SELECT SUM(Age) FROM Users -- e.g. 2054

• Group records

SELECT Name, COUNT(Age) FROM Users
GROUP BY Name

Joins

• Join two or multiple tables

-- Retrieves all records from users and countries they're from
SELECT U.Name, U.Age, C.Country FROM Users U
INNER JOIN Countries C
ON U.Home_town = C.City

-- Retrieves only users records employed at certain Company
SELECT U.Name, U.Age, comp.Name FROM Users U
LEFT JOIN Company comp
ON U.Name = comp.Employee

A better way to join data is to use Primary and Foreign Key relationships. This approach ensures referential integrity and cascade deleting.

The SQL syntax is mostly consistent across different RDBMS. There are some minor differences as well.

Advanced Features

Stored Procedures

A stored procedure is a prepared SQL code that you can save so that the code can be reused repeatedly. The stored procedure can accept parameters and retrieve values. It’s usually used to handle business logic.

Functions

Functions are reusable blocks of code that are callable and must return a value. A function can return a single value (Scalar) or whole tables (Table-Valued Functions).

Views

A view is a virtual table based on the result set of an SQL statement. It contains rows and columns, just like a real table. Views are used to save commonly used SQL queries and exclude certain columns from the original tables.

Jobs

SQL Jobs are automated tasks or processes scheduled to run at specific intervals or in response to certain events. The Jobs are used to run backups, migrations, monitoring, trigger specific actions, etc.

Triggers

SQL triggers automatically execute a set of SQL statements when specific database events occur, such as INSERT, UPDATE, or DELETE operations. Triggers are used to maintain data integrity, track changes, and enforce business rules automatically without manual input.

Indexing

Indexing makes columns faster to query by creating pointers to where data is stored within a database. The indexes are used on large databases (with 100K+ records) to retrieve items quickly.

Plain SQL vs Object-Relational Mapper

Typically, the ORMs (such as Entity Framework in .NET, or Sequalize in Node.js) are used to communicate with the database in backend projects. Here are a few pros and cons of either data access approach.

Benefits of plain SQL

• Speed & Performance
  The data is consumed directly on the source. The users have complete control over indexing, joins, and query execution.
• Portable Across Languages
  Learn the syntax once and apply it to any backend technology (.NET, Node.js, Java, etc.).
• You don’t need to be a developer to understand SQL
  SQL syntax is very close to plain English. There are no abstractions, no different paradigms, programming patterns, or similar overheads that exist in a standard programming language.
• Advanced Querying Capabilities
  The users can work with views, stored procedures, common table expressions, and more. They can also easily join data across multiple databases or even different database servers.

Drawbacks

• Security Concerns
  Developers have to handle SQL injection manually using parametrized queries.
• Manual Data Mapping
  Records must converted into objects manually, leading to repetitive boilerplate code.
• Integration with the backend technology
  Again, more manual work for the developer that would otherwise be easier to handle with ORM.
• Database-Specific Queries
  The queries users write in one RDMBS may not work in the other. The ORMs handle this out of the box.

Who is working with Databases?

Database Administrators (DBA)

The DBA is in charge of creating and maintaining databases. This includes installation, configuration, database design, migration, performance monitoring, security, troubleshooting, data recovery, etc.

Database Analysts

Data analysts oversee the development and modification of company databases, gather and interpret data, prepare reports, and more.

Data Scientists

The data scientist role combines elements of several traditional and technical jobs, including mathematician, analyst, and developer.
A data scientist might develop models, use machine learning, or incorporate advanced programming to find and analyze data.

Developers

Developers create and manipulate tables to store/retrieve data and write functions and stored procedures to handle business logic.

Alternatives to RDMBS:

• NoSQL databases
• Graph databases
• Excel spreadsheets, etc.

Wrapping up

Relational Databases and SQL have been around for a long time and are not going anywhere. Knowing SQL is a must for excelling in your IT career.