Relational Database Management Systems (RDBMS) are a category of databases that organise and store data following the principles of the relational model. The relational model was introduced by Edgar F. Codd in 1970 and is based on mathematical set theory.

Here are key characteristics and elements of RDBMS:

Tables (Relations)

Data in an RDBMS is organised into tables, also known as relations. Each table consists of rows and columns. Rows represent records, and columns represent attributes or fields.

Each row in a table is uniquely identified by a primary key, which is a specific column or combination of columns with unique values.

Columns (Attributes)

Each column in a table has a defined data type (e.g., integer, varchar, date) that specifies the kind of data it can store.

Columns can have constraints, such as NOT NULL, UNIQUE, and FOREIGN KEY, which enforce data integrity and relationships between tables.

Keys

A primary key uniquely identifies each record in a table. It must be unique for each row and cannot contain NULL values.

A foreign key is a column or set of columns in a table that refers to the primary key in another table. It establishes relationships between tables.

Normalisation

Normalisation is the process of organising data to minimise redundancy and dependency. It involves breaking down tables into smaller, related tables to avoid data duplication.

Tables are brought to certain normal forms (e.g., 1NF, 2NF, 3NF) to ensure data integrity and reduce anomalies.

SQL (Structured Query Language)

SQL is the standard language used to interact with RDBMS. It provides a set of commands for querying, updating, and managing relational databases.

Data Manipulation Language (DML): SQL includes commands like SELECT, INSERT, UPDATE, and DELETE for manipulating data.

Data Definition Language (DDL): SQL includes commands like CREATE, ALTER, and DROP for defining and modifying database structures.

ACID Properties

i. Atomicity: Transactions are treated as a single, indivisible unit of work. If any part of a transaction fails, the entire transaction is rolled back.

ii. Consistency: Transactions bring the database from one valid state to another, maintaining data integrity.

iii. Isolation: Transactions are executed independently of each other. The execution of one transaction does not affect the outcome of another.

iv. Durability: Once a transaction is committed, its changes are permanent and survive system failures.

Concurrency Control

RDBMS uses locking mechanisms to control concurrent access to data. This ensures that transactions are executed in a way that maintains consistency.

Transactions

A transaction is a sequence of one or more SQL statements treated as a single unit of work. It either completes successfully, making changes permanent, or is rolled back, leaving the database unchanged.

Commit and Rollback

Transactions can be committed to make changes permanent or rolled back to undo changes.

Indexes

Indexes are used to optimize query performance by providing a quick lookup of rows based on specific column values.

B-tree, hash, and bitmap indexes are Common types of indexes.

Relational databases have been widely adopted for various applications due to their well-defined structure, support for complex queries, and adherence to the relational model principles. They are used in business applications, e-commerce systems, content management systems, and many other scenarios where structured and relational data storage is essential. Popular RDBMS implementations include MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server.

Here are some SQL puzzles you can try solving to improve your SQL skills