You’re reading the free preview.Unlock every conversation for €2.99
FreeFree previewChapter 7
Chapter 7 · Free preview
Databases: The Persistent Memory
Where Does the Data Actually Live?
What you will understand
- Write SQL queries with SELECT, INSERT, UPDATE, DELETE and JOINs
- Design relations with primary keys, foreign keys and constraints
- Explain the four ACID properties with a real-world example
- Recognise and fix the N+1 problem
- Choose between SQL and NoSQL based on the use case
Written by EduVerse for this preview; the slides below are the book’s own words.
Slides from the chapter
Each slide is a passage from the book with the figure or listing it talks about. Swipe, use the arrow keys or the buttons.
Slide 1 of 4
Relationships: Connecting Tables
7.1.5 · p. 152
Conversation from The Software Realm, Decoded
Chapter 7 · 7.1.5 Relationships: Connecting Tables · p. 152
Database responds:
ERROR: foreign key constraint violated
Peter
User 999 doesn’t exist. Database caught that!
Cascading Operations: What Happens When You Delete?
Peter’s next question
What happens if I delete a user who has orders?
Senior developer
Good question. You need to decide: cascade or restrict?
What the book shows with it
Example from The Software Realm, Decoded
Chapter 7 · 7.1.5 Relationships: Connecting Tables · p. 152
Users table:
| id | username |
|----|----------|
| 1 | alice |
| 2 | bob |Orders table:
| id | user_id | total |
|-----|---------|---------|
| 101 | 1 | $50.00 |
| 102 | 1 | $75.00 |
| 103 | 2 | $30.00 |Explore this text diagram
Pick one to highlight it and read what the book says about it.
Other terms (2)
When Queries Get Slow: The Need for Speed
7.1.10 · pp. 159–160
Conversation from The Software Realm, Decoded
Chapter 7 · 7.1.10 When Queries Get Slow: The Need for Speed · pp. 159–160
Peter investigates
What’s happening? Same query. More data. Much slower.
He asks his senior developer: "Why is this so slow now?"
Senior developer
Run this:
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';Result shows:
Seq Scan on users (cost=0.00..1808.00)Senior developer
See that? Sequential scan. The database is checking every single row. With 100,000 users, that’s 100,000 checks. Every time.
Peter
That’s... inefficient. There must be a better way.
Senior developer
There is. Think about how you find someone’s phone number. Do you read every page of the phone book?
Peter
No, I... oh. The phone book is alphabetized. I jump to the right section.
Senior developer
Exactly. Phone books are indexed. Databases can do the same thing.
What the book shows with it
Example from The Software Realm, Decoded
Chapter 7 · 7.1.11 Indexes: The Secret to Speed · p. 160
Peter tests his query after adding index:
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';New result:
Index Scan using idx_users_email on users (cost=0.29..8.31)Normalization: Avoiding Redundancy
7.3 · p. 164
Conversation from The Software Realm, Decoded
Chapter 7 · 7.3 Normalization: Avoiding Redundancy · p. 164
Peter
Done! All order information in one place. Easy to query.
Senior developer reviews
Alice placed a new order. You added her third row. With her email.
Peter
Yeah, so?
Senior developer
What happens when Alice changes her email?
Peter
I update... oh no. I have to update three rows. If I miss one, she has two different emails in the system.
Senior developer
Exactly. You’re violating normalization. You’re storing the same fact, Alice’s email, in multiple places.
What the book shows with it
Example from The Software Realm, Decoded
Chapter 7 · 7.3.2 The Solution: Normalized Tables · p. 165
Users table:
| id | name | email |
|----|-------|-------------------|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |Orders table:
| id | user_id | product | price |
|----|---------|----------|-------|
| 1 | 1 | Laptop | $999 |
| 2 | 1 | Mouse | $25 |
| 3 | 2 | Keyboard | $75 |Explore this text diagram
Pick one to highlight it and read what the book says about it.
Other terms (1)
SQL vs NoSQL: When to Use What
7.4.4 · p. 170
Example from The Software Realm, Decoded
Chapter 7 · 7.4.4 SQL vs NoSQL: When to Use What · p. 170
SQL (PostgreSQL/MySQL) vs NoSQL
--------------------------- --------------------------
Clear structure/relationships Schema changes frequently
Need ACID transactions Need horizontal scaling
Complex queries with JOINs Data is naturally nested
Data integrity is critical Speed > consistency
Financial, healthcare, orders Prototyping, real-time apps
Cassandra: Massive scale
MongoDB: Flexible documentsExplore this text diagram
Pick one to highlight it and read what the book says about it.
Other terms (8)
Slide 1 of 4
Try it yourself
A simulation built by EduVerse around this chapter. It runs in your browser; nothing is sent anywhere.
The full chapter
This preview shows 7 of the chapter’s 53 passages. The full chapter has:
- 8 sections
- 25 conversations
- 0 figures and tables
- 4 What They Say boxes
- 7 knowledge-check questions
Sections in this chapter
- 7.1Relational Databases: Tables and Relationships
- 7.2SQL Database Landscape: Choosing Your Database
- 7.3Normalization: Avoiding Redundancy
- 7.4NoSQL: Beyond Tables
- 7.5The Rise of Distributed Databases
- 7.6Database Performance
- 7.7Beyond Operational Databases: Analytics and Scale
- 7.8Peter's Takeaways