# Relational Databases (RDBMS) A Relational Database Management System (RDBMS) is a type of [[Database Management Systems (DBMS)]] that stores data in tables with rows and columns. Based on the relational model proposed by [[Edgar F. Codd]] in 1970, it uses [[SQL]] as its query language. The "relational" name comes from the mathematical concept of relations (sets of tuples), not from relationships between tables—though foreign key relationships are a key feature. ## Core Concepts ### Tables (Relations) Data is organized into tables, each representing an entity: ``` ┌─────┬──────────┬─────────────────────┐ │ id │ name │ email │ ├─────┼──────────┼─────────────────────┤ │ 1 │ Alice │ [email protected] │ │ 2 │ Bob │ [email protected] │ └─────┴──────────┴─────────────────────┘ ``` - **Row (Tuple)**: A single record in the table - **Column (Attribute)**: A field with a specific data type - **Schema**: The structure definition of a table ### Keys - **Primary Key**: Unique identifier for each row (e.g., `id`) - **Foreign Key**: Reference to a primary key in another table - **Composite Key**: Primary key made of multiple columns - **Candidate Key**: Column(s) that could serve as primary key ### Relationships Tables connect through foreign keys: ``` users orders ┌─────┬────────┐ ┌─────┬─────────┬────────┐ │ id │ name │ │ id │ user_id │ total │ ├─────┼────────┤ ├─────┼─────────┼────────┤ │ 1 │ Alice │◄─────────────│ 1 │ 1 │ 99.99 │ │ 2 │ Bob │ │ 2 │ 1 │ 49.99 │ └─────┴────────┘ │ 3 │ 2 │ 149.99 │ └─────┴─────────┴────────┘ ``` **Relationship Types:** - **One-to-One**: One record relates to exactly one other record - **One-to-Many**: One record relates to multiple records (most common) - **Many-to-Many**: Multiple records relate to multiple records (requires junction table) ## Normalization Process of organizing data to reduce redundancy and improve integrity: | Form | Rule | Example Fix | |------|------|-------------| | 1NF | No repeating groups, atomic values | Split comma-separated values into rows | | 2NF | No partial dependencies on composite keys | Move partially dependent columns to new table | | 3NF | No transitive dependencies | Remove columns dependent on non-key columns | | BCNF | Every determinant is a candidate key | Further decomposition for edge cases | **When to Denormalize:** - Read-heavy workloads where joins are expensive - Reporting and analytics queries - Caching frequently accessed aggregations ## ACID Properties RDBMS guarantee transactional integrity. See [[ACID]] for the full treatment. - **[[Atomicity]]**: All operations in a transaction succeed or all fail - **Consistency**: Database remains in a valid state after transactions - **Isolation**: Concurrent transactions don't interfere with each other - **Durability**: Committed transactions persist even after system failure (typically via [[Write-Ahead Logging (WAL)]]) ## Constraints Rules enforced by the database: ```sql CREATE TABLE users ( id SERIAL PRIMARY KEY, -- Primary key email VARCHAR(255) UNIQUE NOT NULL, -- Unique, required age INTEGER CHECK (age >= 0), -- Check constraint role_id INTEGER REFERENCES roles(id), -- Foreign key created_at TIMESTAMP DEFAULT NOW() -- Default value ); ``` ## Indexes Data structures that speed up queries: ```sql -- B-tree index (default, good for equality and range) CREATE INDEX idx_users_email ON users(email); -- Unique index CREATE UNIQUE INDEX idx_users_email ON users(email); -- Composite index CREATE INDEX idx_orders_user_date ON orders(user_id, created_at); -- Partial index (PostgreSQL) CREATE INDEX idx_active_users ON users(email) WHERE active = true; ``` ## Popular RDBMS | Database | Characteristics | |----------|----------------| | [[PostgreSQL]] | Feature-rich, standards-compliant, extensible | | [[SQLite]] | Embedded, serverless, zero-config | | MySQL | Popular for web apps, owned by Oracle | | MariaDB | MySQL fork, community-driven | | SQL Server | Microsoft ecosystem, enterprise features | | Oracle | Enterprise standard, expensive licensing | ## RDBMS vs NoSQL | Aspect | RDBMS | NoSQL | |--------|-------|-------| | Schema | Fixed, predefined | Flexible, dynamic | | Query language | [[SQL]] | Varies by database | | Scaling | Vertical (bigger server) | Horizontal (more servers) | | ACID | Full support | Often eventual consistency | | Relationships | Native support via joins | Application-level or embedded | | Best for | Complex queries, transactions | High volume, simple access patterns | ## When to Use RDBMS **Good for:** - Applications requiring complex queries and joins - Systems needing strong data integrity (financial, medical) - Well-defined, stable data structures - Reporting and analytics - Multi-table transactions **Consider alternatives for:** - Extremely high write throughput - Unstructured or rapidly changing schemas - Simple key-value access patterns - Horizontal scaling requirements ## References - https://en.wikipedia.org/wiki/Relational_database - Codd's original paper: "A Relational Model of Data for Large Shared Data Banks" (1970) ## Related - [[Database]] - [[Database Management Systems (DBMS)]] - [[SQL]] - [[SQLite]] - [[PostgreSQL]] - [[DuckDB]] - [[Dolt]] - [[ACID]] - [[Atomicity]] - [[Write-Ahead Logging (WAL)]] - [[Edgar F. Codd]]