Introduction to databases and persistence

The Relational Model

SQL Basics, DDL, and DML

Querying Data

Advanced Concepts

Sqlite

Glossary of Database Terms

Practice

Assignment

• Teachers

Core program

Transactions

A Transaction is a group of database actions that are treated as one single job.

The Bank Transfer Example

Imagine you are transferring 00 from Alice to Bob. This requires two steps:

1. Subtract 100 from Alice's account.
2. Add 100 to Bob's account.

What happens if the power goes out after step 1 but before step 2? Alice loses money, and Bob gets nothing. The money vanishes.

To prevent this, databases use transactions. You wrap both steps in a transaction block. If any part fails, the database Rolls Back (undoes) everything to the start. If everything works, it Commits (saves) the changes.

ACID Properties

Relational databases follow 4 rules called ACID:

• **Atomicity:** All or nothing. The transaction happens completely or not at all.
• Consistency: The database must follow all rules (like constraints) before and after the transaction.
• Isolation: Transactions happening at the same time don't mess each other up.
• **Durability:** Once a transaction is saved, it stays saved forever, even if the power goes out immediately.

-- SQLite Transaction Syntax
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

COMMIT; -- Save changes
-- OR
-- ROLLBACK; -- Undo changes if something went wrong

Indexes: Speeding Up Queries

An Index is a tool that helps the database find data faster.

<aside> 💡

The Library Analogy

In an old library, if you wanted a book, you wouldn't walk shelf by shelf looking at every book spine. That is slow (a "Full Table Scan"). You would go to the Card Catalog. You look up the Author alphabetically, find the card, and it tells you exactly where the book is.

A database index works the same way. It creates a sorted list for a specific column (like last_name) so it can find the row instantly.

</aside>

The Trade-off

Why not index every column?

• Pro: FAST reading. Finding a user by email is instant.
• Con: SLOW writing. Every time you add a new row, the database has to update the table and the index.

Best Practice: Index columns that you use often in WHERE clauses (like id, email, or foreign keys).

-- Create an index on the email column
CREATE INDEX idx_students_email ON students(email);

Views

A View is like a "saved query". It looks like a table, but it doesn't store data itself.

If you often run a complex join to get a "Class Roster", you can save it as a View.

CREATE VIEW class_roster AS
SELECT students.first_name, classes.title
FROM students
JOIN enrollments ON students.id = enrollments.student_id
JOIN classes ON enrollments.class_id = classes.id;

-- Now you can query it like a simple table
SELECT * FROM class_roster;

Debugging and Performance

EXPLAIN QUERY PLAN

Sometimes a query is slow, and you don't know why. SQL databases allow you to ask "How are you going to run this?".

In SQLite, you put EXPLAIN QUERY PLAN before your query.

EXPLAIN QUERY PLAN SELECT * FROM students WHERE email = '[email protected]';

Output Meaning:

• SCAN TABLE students: It is reading every single row (Slow!).
• SEARCH TABLE students USING INDEX: It is using the index you created (Fast!).

Timing

In the SQLite CLI, you can turn on the timer to see exactly how long queries take.

.timer on
SELECT * FROM students;
-- Output: Run Time: real 0.000 user 0.000 sys 0.000

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0

*https://hackyourfuture.net/*

Found a mistake or have a suggestion? Let us know in the feedback form.