> For the complete documentation index, see [llms.txt](https://ankit-apdc.gitbook.io/system-design/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ankit-apdc.gitbook.io/system-design/building-blocks/database/sql.md).

# SQL

Structured Query Language, is also known as Relational Database

## Keywords

### Why SQL is called Relational Database?

> A relational database is one that stores data in tables. The relationship between each data point is clear and searching through those relationships is relatively easy. 
>
> The relationship between tables and field types is called a **schema**. For relational databases, the schema must be clearly defined
>
> Relationships are established through Primary and Foreign keys

###

### What is normalisation?

> **D**atabase design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. 
>
> Normalisation rules divides larger tables into smaller tables and links them using relationships. 
>
> The purpose of Normalisation in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.

###

### What is a key?

> A value used to identify a record in a table uniquely. 
>
> A KEY could be a single column or combination of multiple columns
>
> * **Primary Key:** 
>
>   A primary key is a single column value used to identify a database record uniquely
>
>   * A primary key cannot be NULL
>
>   * A primary key value must be unique
>
>   * The primary key values should rarely be changed
>
>   * The primary key must be given a value when a new record is inserted
>
> * **Composite Key:** is a primary key composed of multiple columns used to identify a record uniquely
>
> * **Foreign Key**: 
>
>   A foreign key is references the primary key of another Table
>
>   * A foreign key can have a different name from its primary key
>   * It ensures rows in one table have corresponding rows in another
>   * Unlike the Primary key, they do not have to be unique. Most often they aren't
>   * Foreign keys can be null even though primary keys can not 

### Schema and Queries Use Case?

> * How normalisation is used? 
> * How multiple tables are used to store data?
> * How concepts of primary and foreign keys are used to retrieve data and presented as per requirement?
>
> All this concepts can be understood by looking at one use case of Stack Overflow data, go through the "Use Cases" page link as attached, to understand it better

{% content-ref url="/pages/-MaF6BFspblokAAh8Qc-" %}
[Use Cases](/system-design/building-blocks/database/use-cases.md)
{% endcontent-ref %}

## SQL Database Properties

{% hint style="success" %}

#### ACID Property

**Atomicity**: Atomicity means that you guarantee that either all of the transaction succeeds or none of it does. You don’t get part of it succeeding and part of it not. 

If one part of the transaction fails, the whole transaction fails. With atomicity, it’s either “all or nothing”.

**Consistency**: This ensures that you guarantee that all data will be consistent. 

All data will be valid according to all defined rules, including any constraints, cascades, and triggers that have been applied to the database.

**Isolation**: Guarantees that all transactions will occur in isolation. 

No transaction will be affected by any other transaction. So a transaction cannot read data from any other transaction that has not yet completed.

**Durability**: Durability means that, once a transaction is committed, it will remain in the system – even if there’s a system crash immediately following the transaction. 

Any changes from the transaction must be stored permanently. If the system tells the user that the transaction has succeeded, the transaction must have, in fact, succeeded
{% endhint %}

## References for Further Reading

{% embed url="<https://www.guru99.com/what-is-dbms.html>" %}
There are multiple lectures, go through all the topics; very informative
{% endembed %}