10.2 Relational Database Concepts & Schema Structure

Relational Database Concepts & Schema Structure

Core Foundation: While modern computing can store information in flat text files or spreadsheets, mission-critical business systems demand relational architectures. Relational Database Management Systems (RDBMS) provide structured schemas, reduce redundancy through sound design, enforce declared integrity rules, and support durable transaction recovery when the database and storage are configured correctly.

---

RDBMS Advantages Over Flat Files and Spreadsheets

Before the advent of database management systems, organizations stored records in flat files (comma-separated values [CSV], tab-delimited text, or single-file spreadsheets). In a flat-file system, data is stored in a single unlinked structure where every record contains all attributes relating to an entity.

FLAT FILE / SPREADSHEET STORAGE (Severe Data Redundancy):
Order_ID | Customer_Name | Customer_Address        | Product_Name | Unit_Price | Qty
---------+---------------+-------------------------+--------------+------------+----
1001     | Alice Chang   | 742 Evergreen Terr, IL  | USB-C Cable  | $12.00     | 2  
1002     | Alice Chang   | 742 Evergreen Terr, IL  | 4K Monitor   | $350.00    | 1  
1003     | Alice Chang   | 742 Evergreen Terr, IL  | Laptop Stand | $45.00     | 1  

Storing operational data in flat files or spreadsheets introduces critical technical failures as organizations grow:

1. Data Redundancy and Update Anomalies

In the flat file above, Alice Chang's name and full address must be duplicated on every individual purchase row. If Alice changes her address, an administrator must locate and update every single order row in the file. If an update misses one row, the system enters an update anomaly state where identical customers have conflicting addresses across different records.

2. Concurrency Conflicts and File Locking

Spreadsheets and flat files lack multi-user concurrency control. When User A opens a spreadsheet over a network share to modify a record, the operating system locks the entire file. User B is forced into read-only mode or faces catastrophic race conditions where User B's save operation silently overwrites User A's changes.

3. Lack of Referential Constraints and Data Corruption

Flat files cannot enforce logical boundaries between distinct business entities. A user typing directly into a spreadsheet cell can enter a negative price, enter text into a phone number field, or create an order for a customer ID that does not exist. Flat files provide no automated integrity validation.

4. Coarse Security and Access Granularity

Flat files provide an all-or-nothing security model at the file system level. A user either has read/write permission to the entire file or none at all. An RDBMS allows fine-grained, role-based access control (RBAC), permitting a clerk to view customer names while masking credit card numbers and restricting salary columns to human resources managers.

---

Why Use a Database?

A database supports controlled create, import/input, query, and report operations over persistent records. Compared with a flat file, a database engine can coordinate multiple concurrent users, enforce constraints, index fields for faster retrieval, and apply permissions at a finer level. It can scale beyond a single desktop file while preserving consistent records.

Data persistence means committed records remain stored after an application closes or a system restarts. Availability describes whether authorized users can reach those records when needed. A database may be local or cloud-hosted and online or offline: cloud/online access improves reach and collaboration but depends on connectivity, while local/offline storage can keep one site working without the internet but requires its own backup and synchronization plan.

Relational Architecture & Terminology

A Relational Database organizes data according to the mathematical relational model formulated by computer scientist Edgar F. Codd in 1970. Data is partitioned into discrete, interconnected logical structures.

+-------------------------------------------------------------------------+
|                        DATABASE INSTANCE                                |
|  +-------------------------------------------------------------------+  |
|  |                         SCHEMA                                    |
|  |  +-------------------------------------------------------------+  |  |
|  |  |                     TABLE: CUSTOMERS                        |  |  |
|  |  |  +---------------+--------------------+------------------+  |  |  |
|  |  |  | customer_id   | first_name         | email            |  |  |  |
|  |  |  | (PK, INT)     | (VARCHAR 50)       | (VARCHAR 100)    |  |  |  |
|  |  |  +---------------+--------------------+------------------+  |  |  |
|  |  |  | 101           | Sarah              | sarah@corp.com   |  |  |  |
|  |  |  | 102           | David              | david@corp.com   |  |  |  |
|  |  |  +---------------+--------------------+------------------+  |  |  |
|  |  +-------------------------------------------------------------+  |  |
|  +-------------------------------------------------------------------+  |
+-------------------------------------------------------------------------+

• Database Instance: The overarching software environment and physical storage files managed by the database engine (e.g., a PostgreSQL or Microsoft SQL Server installation).
• Schema: The formal structural blueprint representing the logical design of the database. The schema defines tables, column names, data types, indexes, default values, and relational constraints.
• Table (Relation): A two-dimensional collection of related data organized into horizontal rows and vertical columns. A well-designed database contains separate tables for separate real-world entities (e.g., customers, orders, products, employees).
• Record / Row (Tuple): A single horizontal entry within a table. A row represents one specific, unique instance of an entity (e.g., a single customer account or a specific order).
• Field / Column (Attribute): A single vertical component within a table representing a specific attribute common to all records (e.g., phone_number, hire_date, account_balance).
• Column Data Types: Every column must be assigned an explicit data type that dictates memory allocation and permissible operations:
  • INT / BIGINT: Whole numbers used for counters, quantities, and foreign keys.
  • VARCHAR(n) / TEXT: Variable-length alphanumeric character strings up to length $n$.
  • DECIMAL(p, s) / NUMERIC: Exact fixed-point numbers with precision $p$ and scale $s$, required for monetary currency calculations.
  • BOOLEAN: Binary logical truth values (TRUE, FALSE).
  • DATE / TIMESTAMP: Standardized calendar dates and epoch microsecond timestamps.

---

Keys & Constraints: Enforcing Structural Validity

Relational databases rely on keys and constraints to establish relationships and maintain data accuracy across tables.

        [CUSTOMERS TABLE]                                  [ORDERS TABLE]
  ┌─────────────────────────┐                        ┌─────────────────────────┐
  │ customer_id (PK) ◄──────┼────────────────────────┼─── customer_id (FK)     │
  │ first_name              │      Referential       │    order_id (PK)        │
  │ last_name               │      Integrity Link    │    order_date           │
  │ email (UNIQUE)          │                        │    order_total          │
  └─────────────────────────┘                        └─────────────────────────┘

1. Primary Key (PK)

A Primary Key is a designated column (or combination of columns) that uniquely identifies every individual row in a table.

• Mandatory Rules:
  1. Uniqueness: No two rows in the same table can possess identical primary key values.
  2. Non-Nullability: A primary key column cannot contain NULL values under any circumstance.
• Surrogate Key vs. Natural Key:
  • Natural Key: A real-world attribute that is inherently unique (such as a vehicle VIN, national tax identifier, or email address). Natural keys are vulnerable to real-world changes and privacy leakage.
  • Surrogate Key: An artificially generated, meaningless identifier assigned by the database (such as an auto-incrementing integer 1, 2, 3... or a 128-bit Universally Unique Identifier [UUID]). Surrogate keys are standard best practice because they remain immutable over the lifetime of a record.

2. Foreign Key (FK)

A Foreign Key is a column (or group of columns) in a child table whose values must match the Primary Key of a related parent table (or contain NULL).

• The foreign key serves as the logical connector establishing the relational link between tables.
• Example: In an orders table, the column customer_id is a foreign key pointing back to customer_id in the customers table. This identifies which customer placed the order without duplicating the customer's personal details inside the order record.

3. Core Database Constraints

Constraints are automated rules defined in the database schema that the RDBMS enforces on every write operation (INSERT, UPDATE):

• NOT NULL: Guarantees that a column cannot store missing, unassigned, or null values. Critical for required attributes like last_name or order_date.
• UNIQUE: Ensures that all non-null values in a column are distinct across all rows. Often applied to natural identifiers like email or username.
• CHECK: Validates that data entered into a column satisfies a specific boolean condition. For example, CHECK (unit_price > 0.00) prevents negative prices, and CHECK (age >= 18) enforces age thresholds.
• DEFAULT: Automatically inserts a predefined value into a column if the user does not supply one during an INSERT statement (e.g., status VARCHAR(20) DEFAULT 'Pending').

---

Referential Integrity & Preventing Orphan Records

Referential Integrity is a fundamental relational rule dictating that every foreign key value in a child table must correspond to an existing, valid primary key in the referenced parent table.

The Danger of Orphan Records

An orphan record occurs when a row in a child table references a parent entity that does not exist. For example, if Customer #42 is deleted from the database, but Customer #42's existing order rows remain in the orders table, those order rows become orphans. Billing software attempting to generate invoices for those orders will crash or misattribute financial data.

Referential Actions on DELETE and UPDATE

To preserve referential integrity, database designers configure explicit foreign key policies that execute whenever a parent row is deleted or modified:

• ON DELETE RESTRICT / NO ACTION (The Default): The database engine immediately blocks and rolls back the deletion of a parent record as long as child records reference its primary key. An administrator cannot delete Customer #42 until all associated orders are resolved.
• ON DELETE CASCADE: The database engine automatically deletes all associated child records whenever the parent record is deleted. Deleting Customer #42 instantly purges all of Alice's orders from the orders table. (Useful for parent-child dependencies like an order and its line items, but hazardous if applied carelessly to critical customer accounts).
• ON DELETE SET NULL: The database engine deletes the parent record and automatically sets the foreign key column in all associated child records to NULL. The order records remain preserved in history, but they are no longer tied to an active customer account.

---

Cardinality and Relationship Modeling

Cardinality defines the numerical relationship between occurrences of entities in two related tables.

1. ONE-TO-ONE (1:1)       [EMPLOYEES] 1 ─────────── 1 [PARKING_PASSES]
   (Rare, specialized)

2. ONE-TO-MANY (1:N)      [CUSTOMERS] 1 ─────────── N [ORDERS]
   (Most Common)                                        │ Foreign Key resides on Many side
                                                        ▼
3. MANY-TO-MANY (M:N)     [ORDERS]    M ─────────── N [PRODUCTS]
   (Requires Junction Table)           │               │
                                       ▼               ▼
                          [ORDERS] 1 ───── N [ORDER_ITEMS] N ───── 1 [PRODUCTS]
                                            (Junction Table)

1. One-to-One (1:1)

A single record in Table A is associated with exactly one record in Table B, and vice-versa.

• Real-World Example: An employees table linked to a security_clearances table. Each employee has at most one security clearance record, and each clearance record belongs to one employee.
• Implementation: Often combined into a single table unless partitioned for sensitive security clearance permissions or performance optimization.

2. One-to-Many (1:N)

A single record in Table A is associated with zero, one, or many records in Table B, but each record in Table B is associated with exactly one record in Table A. This is the most common relationship in relational databases.

• Real-World Example: One Customer places Many Orders; One Department employs Many Staff Members; One Classroom hosts Many Students.
• Implementation Rule: The Foreign Key always resides on the "Many" side of the relationship. The orders table holds the customer_id foreign key pointing back to customers.

3. Many-to-Many (M:N) and Junction Tables

Multiple records in Table A are associated with multiple records in Table B. In the real world, Many-to-Many relationships are ubiquitous:

• An Order contains many Products, and a Product is included across many customer Orders.
• A Student enrolls in many Courses, and a Course contains many enrolled Students.
• A Doctor treats many Patients, and a Patient consults many specialized Doctors.

The Necessity of the Junction Table

Relational tables cannot directly represent an M:N relationship. If you attempted to place product_id inside the orders table, an order containing five products would require storing a comma-separated list in a single field (violating data atomicity) or creating duplicate order rows (violating primary key uniqueness).

To resolve an M:N relationship, database architects decompose it into two separate 1:N relationships using an intermediate table known as a Junction Table (also called a Bridge Table, Associative Entity, or Link Table).

• The junction table contains two foreign keys: one pointing to Table A and one pointing to Table B.
• These two foreign keys are frequently combined to form a Composite Primary Key (a primary key made up of two or more columns).
• The junction table can also hold relationship-specific attributes (such as quantity_purchased or grade_received).

-- Resolving M:N with a Junction Table
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),              -- Composite Primary Key
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

---

ACID Transaction Properties

In database management, a transaction is a logical unit of work consisting of one or more database operations (INSERT, UPDATE, DELETE) executed against a database. For a database to be considered reliable, its transaction engine must conform to the ACID properties.

                                    [ACID TRANSACTION PROPERTIES]
                                                  │
     ┌────────────────────┬───────────────────────┴───────────────────────┬────────────────────┐
     ▼                    ▼                                               ▼                    ▼
 [ATOMICITY]        [CONSISTENCY]                                   [ISOLATION]          [DURABILITY]
 "All or Nothing"   "Valid to Valid State"                          "Concurrent Safety"  "Survives Crashes"
 If one step fails,  All schema constraints,                         Uncommitted changes  Committed data is
 the entire unit is  PKs, FKs, and check rules                       are hidden from      permanently written
 rolled back.        must remain valid.                              other transactions.  to non-volatile disk.

1. Atomicity ("All or Nothing")

Atomicity guarantees that a transaction is treated as an atomic, indivisible unit: either all operations within the transaction succeed completely, or the entire transaction is rolled back and none of the changes take effect.

• The Classic Banking Example: Moving $500 from Checking to Savings requires two operations: (1) deduct $500 from Checking, and (2) add $500 to Savings. If a power outage or network severance occurs after Step 1 but before Step 2, atomicity commands the database engine to rollback Step 1. The $500 does not vanish into thin air.

2. Consistency ("Preserving the Rules")

Consistency ensures that a transaction can only transition the database from one valid state to another valid state, maintaining all schema constraints, referential integrity rules, unique keys, and check rules.

• If a transaction attempts to insert a record with a negative price (violating a CHECK constraint) or references a non-existent foreign key, the database engine aborts the transaction, rolls back any partial changes, and leaves the database in its pre-transaction compliant state.

3. Isolation ("Concurrent Independence")

Isolation dictates that concurrently executing transactions execute without interfering with one another. The intermediate, uncommitted states of Transaction A are invisible to Transaction B until Transaction A is formally committed.

• Concurrency Hazard: If Isolation were absent, User B could read a temporary account balance modified by User A that is subsequently rolled back (known as a Dirty Read), leading to financial calculation errors.

4. Durability ("Permanent Commitment")

Durability guarantees that once a transaction has received a confirmation of commitment (COMMIT), its modifications remain permanently recorded in non-volatile storage and will survive any subsequent system crash, power outage, or operating system failure.

• Mechanism: Modern RDBMS engines write transactions to non-volatile Write-Ahead Logs (WAL) or redo logs on disk prior to updating memory buffers. If the server loses power instantly after a commit, the database engine reads the WAL upon reboot and replays any pending writes into the physical database tables.

---

Practical Diagnostic Scenarios & Exam Pitfalls

• Trap 1: Placing the Foreign Key on the "One" Side of a 1:N Relationship. Novice administrators often mistakenly place the customer foreign key in the wrong table. Remember: One Customer has Many Orders. Placing order_id in the customers table would prevent a customer from having more than one order. The foreign key customer_id must reside on the Many side (inside the orders table).
• Trap 2: Attempting to Model M:N Directly Without a Junction Table. Relational databases cannot directly implement a Many-to-Many relationship. Any attempt to store multiple values in a single cell violates the First Normal Form (1NF). An intermediate Junction Table with a composite key is mandatory.
• Trap 3: Confusing Primary Keys with Unique Constraints. While both enforce unique values, a table can possess only one Primary Key, and that Primary Key strictly forbids NULL values. Conversely, a table can have multiple UNIQUE constraints, and standard SQL permits NULL values within columns constrained only by UNIQUE.
• Trap 4: Confusing Atomicity with Consistency. Candidates often mix up these two ACID properties. Atomicity ensures that all steps in a transaction finish or none do ("all or nothing"). Consistency ensures that the data adheres to all schema rules, foreign keys, and validation checks throughout the transition.