NUS Hackers Wiki
NUS Hackers Wiki
  • NUS Hackers Wiki
  • Hackerschool
    • Virtual Machines and Linux
    • Beginners' Guide to the Terminal
      • Introduction to the Terminal
      • Modern Shell Tools
      • Shell Scripting
      • Real World Scripting
      • Resources
    • Self-Hosting: Three Easy Pieces
      • 1. Setting up your server
      • 2. Running Services
      • 3. Monitoring your server
    • Vim
    • Introduction to Zig
      • Language Basics
      • Error Handling
      • Memory Management
      • Working with C
      • Exploring comptime
    • CI/CD with Github Actions
      • Background
      • Basics of Github Actions
        • Target workflow
        • Running unit tests
        • Linting code
        • Deploying to Github Pages
      • Advanced use cases
        • Pollers
        • Github script
        • Executing third-party scripts
        • Reusable workflows
      • Cookbook
    • Lightning Git
      • Git Concepts
      • Getting Started with Git
      • Making your first commit
      • Branching
      • Merge Conflicts
      • Integrating remote repositories
      • Collaborative Workflows
      • Commit Manipulation and Reflog
      • Interactive rebasing
      • filter-repo
  • Orbital
    • JavaScript
      • Browser Developer Tools
      • Getting Started
      • Datatypes
      • Operators and Operations
      • Loops and Conditions
      • Functions
      • Strings
      • Arrays
      • HTML
        • Getting Started
        • Tag Attributes
        • HTML Forms
        • Browser Inspector
      • CSS
        • Selectors
        • Colors in CSS
        • Measurements in CSS
        • The Box Model
        • Adding Styles - Part 1
        • Adding Styles - Part 2
      • Working with the DOM
        • Querying the DOM - Selectors
        • Querying the DOM - Element Attributes
        • Querying the DOM - Element Styles
        • Events with JS and HTML
        • Exercise: Click Counter
        • Editing the DOM
        • Fetch Requests
        • Exercise: The NUSMods API
    • React
      • Setup
      • State
    • React Native
      • Setup
      • Intro to JSX
      • Basic Syntax
      • Handling UI
      • Props
      • State Management
    • Git
      • Setup
      • Command Glossary
      • Fundamental Concepts
        • Getting Started
        • Integrating Remote Repositories
        • Branching
        • Merge Conflicts
      • Collaborative Workflows
        • Fork and PR Workflow
        • Branch and PR Workflow
      • Advanced Concepts
        • Ignoring Files
        • Commit Message Conventions
        • Github Collaborators
        • CI/CD with Github Actions
        • Advanced Git Commands
      • FAQ
    • Telegram Bot
      • Creating a TeleBot
      • API Calls
      • Telebot Basics
      • Integrating API's
    • Relational Database
      • Database Overview
      • Database Design
      • Entity Relationship Diagram
      • SQL Basics & PostgreSQL
    • TypeScript
      • Types and Interfaces
      • Utility Types
      • Typing Component Props, Events, and Hooks
      • Why You Should Avoid Using any (and What to Do Instead)
      • TypeScript Tricks You’ll Use All the Time in React
Powered by GitBook
On this page
  • Terms
  • Table
  • Keys
  • Schema
  • ACID Transactions in RDBMS
  • Atomicity
  • Consistency
  • Isolation
  • Durability
Edit on GitHub
Export as PDF
  1. Orbital
  2. Relational Database

Database Design

PreviousDatabase OverviewNextEntity Relationship Diagram

Last updated 11 months ago

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:

  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!

credit: https://www.geeksforgeeks.org/components-of-table-in-database/