# Database Design ## Terms ### Table ![credit: https://www.geeksforgeeks.org/components-of-table-in-database/](/files/X1gx5LJJHMuhFBHaL0Yd) 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! --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://wiki.nushackers.org/orbital/relational-database/index-1.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.