3.9Databases (Relational Databases, SQL Basics, and Transactions)

Customer information, inventory, sales figures, and other data handled throughout business activities are, in most cases, stored in a database organized as tables. This section covers the basic structure of the most widely used kind, the relational database; normalization, which organizes data; SQL, used to query and update it; and the mechanism of transactions, which prevents inconsistency even when multiple people use the database at once.

3.9.1Relational databases, normalization, and SQL basics

• A relational database manages data in the form of tables, each made up of rows (records) and columns (fields). A primary key is a column that uniquely identifies each row (no duplicates, no NULL). A foreign key is a column that references another table's primary key, used to relate tables to one another.
• Normalization is a design technique that splits and organizes tables to reduce duplication and inconsistency. For example, instead of repeating a customer's name and address directly in an "orders" table, splitting out a separate "customers" table and referencing it via a foreign key means an address change only needs to be updated in one place.
• SQL is the language for operating a relational database. The SELECT statement retrieves data (SELECT name FROM customers), and the WHERE clause narrows results by a condition (SELECT name FROM customers WHERE city = 'Tokyo'). At the basic level, the priority is being able to read this combination.

3.9.2Transactions and data utilization

• A transaction treats multiple operations as one unit, ensuring the outcome is either "all succeed" or "all fail" (rolled back to the pre-execution state)—for example, a bank transfer where a debit and a credit must both succeed, or the whole operation is undone. The ACID properties are the four qualities a transaction must satisfy: atomicity (all-or-nothing execution), consistency (data integrity is preserved), isolation (concurrent transactions do not affect each other), and durability (a completed result is not lost even if a failure occurs).
• Concurrency control prevents inconsistency when multiple users try to write to the same data at the same time (by locking data, temporarily blocking changes from other operations). Commit is the operation that finalizes a transaction's changes. Rollback is the operation that undoes changes within a transaction and reverts to the pre-execution state.
• A data warehouse is a mechanism that accumulates large volumes of data, chronologically, for analysis, gathered from multiple operational systems. Data mining is a technique for discovering patterns and relationships in accumulated large-scale data using statistical and other methods (for example, discovering that "customers who buy product A also tend to buy product B").

Consider an online shopping site's order processing. Its database has a "customers" table (customer ID, name, address) and an "orders" table (order ID, customer ID, product, amount), where the orders table's customer ID column is a foreign key referencing the customers table's primary key (customer ID). If a customer's address were repeated directly in the orders table too, every past order row would need updating whenever the customer moved, creating a source of inconsistency—but normalization by splitting the tables avoids this. To look up a particular customer's orders, you narrow with SQL (SELECT plus WHERE), as in SELECT * FROM orders WHERE customer_id = 123. Checkout processing treats "decrement inventory by one," "create an order record," and "log the payment" as one bundled unit—a transaction—preventing a state where only some of these steps ran due to a network failure partway through. If all three steps succeed, the changes are finalized with commit; if one fails partway, rollback undoes all the inventory and order changes, reverting to the state before execution. When a sale causes a flood of orders for the same product, concurrency control manages simultaneous writes to the inventory data, preventing the stock count from being mistakenly decremented twice. Years of accumulated order data are then organized for analysis as a data warehouse, and data mining discovers patterns such as "customers who buy this product tend to also buy a related product," which feeds recommendation features.

3.9.3Section summary

• Primary key = uniquely identifies a row; foreign key = references another table's primary key. Normalization reduces duplication and inconsistency
• SELECT plus WHERE narrows retrieved data. A transaction satisfies ACID (atomicity/consistency/isolation/durability)
• Commit = finalize, rollback = undo. Concurrency control prevents conflicts from simultaneous writes. Data warehouse = accumulation, data mining = discovery