# Database Design

## Terms

### Table

![credit: https://www.geeksforgeeks.org/components-of-table-in-database/](https://2807223923-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FTUqAJOgHs57S8lmqdxRV%2Fuploads%2Fgit-blob-74fc8e7ba0cf3b44c8096cf5bcc02c710670fbd6%2Ftable-labeled.png?alt=media)

In a relational database, a table is composed of several components:

* **Columns**: Each column has a name and a data type. Also called attributes/fields.
* **Rows**: Represent individual instances of data in a table. Also called records or tuples.

### Keys

* **Primary Key(PK)**: A primary key is a unique identifier for each row in a table.

  Eg. NRIC uniquely identify every Singapore citizen.
* **Foreign Key(FK)**: Foreign key establishes a relationship between two entities.

  Eg. One's NRIC is referred to in all tables (Hospital Records, Education Records etc.) keeping his/her identity information.

### Schema

A schema in a relational database defines the structure and organization of the database. It includes the tables, columns, relationships, and constraints.

## ACID Transactions in RDBMS

The ACID properties ensure the reliability and integrity of data in a relational database.

> **Example: Remittance in a banking**
>
> A single transaction (remittance from A to B) is broken down into several discrete steps:
>
> 1. SGD is deducted from A's account
> 2. Bank converts A's SGD into MYR
> 3. MYR is received by B's account

### Atomicity

Atomicity guarantees that a transaction is treated as a single, indivisible unit of work. Either all the changes made by the transaction are committed, or none of them are.

**In the above example, either all 3 steps are committed, or none. It is not possible to only do 1 or 2 steps.**

### Consistency

Consistency ensures that a transaction brings the database from one valid state to another. It enforces integrity constraints and rules defined in the database schema.

**In the above example, the money received by B will be the exact value *****(after exchange rate calculation wrt live bank rate)***** as the money sent by A. It is not possible that A sent 2000 and B only received 2.**

### Isolation

Isolation ensures that concurrent transactions do not interfere with each other. Each transaction is executed as if it is the only transaction running, preventing data inconsistencies.

**In the above example, the bank is processing many simultaneous transaction at the same time. However, they will not interfere with each other.**

### Durability

Durability guarantees that once a transaction is committed, its changes are permanent and will survive any subsequent failures, such as power outages or system crashes.

**In the above example, after the remittance is received by B, it will be permanently stored by the bank as a completed transaction. It is not possible that the banking system crashes and remittance is sent back to A.**

Most RDBMS satisfy these properties, so we don't have to worry about its implementaion!