Database Design
Terms
Table
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:
SGD is deducted from A's account
Bank converts A's SGD into MYR
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!
Last updated
