What are SQL (relational) databases?

Basic concepts of the relational model

• Tables: A basic structure for storing data, consisting of rows and columns.
• Rows (Records): Represent individual instances of objects or events (e.g., a specific customer, an order).
• Columns (Attributes): Define characteristics or properties of objects stored in a table (e.g. customer name, order number, date). Each column has a specific data type (e.g. text, number, date).
• Primary Key (Primary Key): One or more columns whose values uniquely identify each row in a table.
• Foreign Key (Foreign Key): A column (or set of columns) in one table that references a primary key in another table, thus creating a relationship between the tables.
• Schema: A formal definition of the database structure – tables, columns, data types, keys and relationships. In relational databases, the schema is usually rigidly defined before the data is entered.

SQL (Structured Query Language)

SQL is a standard language used to interact with relational databases. It allows you to perform a wide range of operations, including:

• Defining data (DDL – Data Definition Language): Creating, modifying and deleting tables and other database objects (e.g. CREATE TABLE, ALTER TABLE, DROP TABLE).
• Data manipulation (DML – Data Manipulation Language): Inserting, updating and deleting data in tables (e.g. INSERT, UPDATE, DELETE).
• Execution of queries (DQL – Data Query Language): Retrieving data from a database according to specified criteria (mainly using SELECT command with various clauses like WHERE, GROUP BY, ORDER BY, JOIN).
• Data access control (DCL – Data Control Language): User Privilege Management (e.g. GRANT, REVOKE).

Properties of ACID

One of the key features of transactions in relational databases is the ACID properties that guarantee their reliability:

• Atomicity (Atomicity): A transaction is treated as an indivisible whole – either all of its operations will be executed correctly, or none (in case of an error, rollback of changes occurs).
• Consistency (Consistency): A transaction moves a database from one consistent state to another consistent state while maintaining data integrity (e.g., compliance with foreign key constraints).
• Isolation (Isolation): Simultaneously executed transactions are isolated from each other, as if they were executed sequentially. This ensures that conflicts are avoided when concurrently accessing data.
• Durability: Once a transaction is successfully approved (commit), its results are permanently stored in the database and resistant to system failures.

Popular RDBMS systems

There are many popular relational databases on the market, both commercial and open-source. Among the best known are: Oracle Database, Microsoft SQL Server, MySQL, PostgreSQL, IBM Db2, SQLite.

Advantages and disadvantages of relational databases (SQL)

The advantages are mainly: maturity of the technology, wide availability of tools and specialists, standardized SQL language, guarantee of data consistency (ACID), well-defined data model making it easier to understand the structure. The main disadvantages are less schema flexibility compared to NoSQL, and difficulty in horizontal scaling for very large workloads.

When to use relational databases?

Relational databases are still an excellent choice for many applications, especially where data integrity is crucial (e.g., financial, transactional systems), data is structured, and relationships are important. They are the basis of most traditional business systems.