Summary - Schema Design and Transaction Management

Indexes in SQL

Indexing is a database technique used to speed up data retrieval. It allows the database management system to quickly locate specific data without scanning every row in a table.

Key Points about Indexing

Analogy: Indexing can be compared to an index in a textbook, which helps readers quickly find topics without reading the entire book.

Data Structure: Indexes are typically stored in a B-tree structure, which allows for faster searches with logarithmic time complexity (O(log N)).

Importance:

Faster Queries: Reduces the time taken to find specific data by enabling quick lookups.

Efficiency: Particularly beneficial in large databases, where searching through every row would be inefficient.

Automatic Indexing: Primary keys are automatically indexed when created, simplifying data management.

Creating an Index

The basic syntax for creating an index in SQL is:

CREATE INDEX index_name ON table_name (column_name);

⁠

Handling Transactions and Rollbacks

Transactions in databases are essential for maintaining data integrity and ensuring that operations involving multiple steps are completed reliably. A transaction can be defined as a sequence of operations that are treated as a single unit of work.

In SQL, a transaction is a sequence of operations performed as a single logical unit of work. A transaction follows four key properties, often referred to as ACID:

Atomicity: All operations in a transaction are treated as a single unit. If any part fails, the entire transaction fails.

Consistency: A transaction must bring the database from one valid state to another valid state.

Isolation: Transactions are isolated from each other. Concurrent transactions should not interfere with each other.

Durability: Once a transaction is committed, its changes are permanent, even in the event of a system failure.

How to Handle Transactions in SQL

Let’s look at how we can use transactions in SQL. In PostgreSQL, we can manage transactions using the following commands:

BEGIN: Starts a new transaction.

COMMIT: Saves all changes made in the transaction.

ROLLBACK: Reverses all changes made in the transaction if something goes wrong.

Example of Using Transactions

Let’s walk through a practical example. Suppose we want to transfer $100 from Alice’s account to Bob’s account.

First, we’ll start a transaction:

BEGIN;

Next, we’ll update Alice’s account to subtract $100:

UPDATE accounts SET balance = balance - 100 WHERE account_holder = 'Alice';

Now, we’ll add $100 to Bob’s account:

UPDATE accounts SET balance = balance + 100 WHERE account_holder = 'Bob';

At this point, everything seems good. But what if the second update fails due to some reason (for instance, insufficient funds)? We wouldn’t want to leave Alice’s account in an inconsistent state. Here’s where the ROLLBACK command comes in.

Handling Errors and Rollbacks

If something goes wrong during the transaction, we can roll back:

ROLLBACK;

This command undoes all the changes made in the current transaction, restoring the database to its previous state.

On the other hand, if everything is successful, we can commit the transaction:

COMMIT;

Putting It All Together

Here’s the complete sequence for our money transfer operation:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_holder = 'Alice';

-- If an error occurs during the second update, roll back

-- if no error occurs, proceed to commit

UPDATE accounts SET balance = balance + 100 WHERE account_holder = 'Bob';

-- If everything went well

COMMIT;

Mastering Schema Design and Transaction Management is essential for building scalable databases and ensuring data integrity. For more detailed information, refer to the Notion link:

Schema Design and Transaction Management⁠

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.