These are the notes for System Design episode 9. For two episodes we protected the database with caches. Here we open the database itself. You invent tables, primary and foreign keys, JOIN, the schema and transactions (ACID) from scratch, then hit the wall that made people invent NoSQL: data whose shape keeps changing. Every demo uses Python's built-in sqlite3 module (SQLite is a real SQL database that ships with Python) or plain dictionaries, and runs as-is; outputs are real (Python 3.13, SQLite 3.53).
The six problems
| Problem | What it teaches |
|---|---|
| Same phone number, 3 copies | Update anomaly → tables and keys |
| Put it back together | JOIN |
| An order for nobody | Schema, foreign key, IntegrityError |
| ₹200 vanishes | Transactions, rollback, ACID |
| Every item is different | Documents (NoSQL) |
| Which one, when? | Choosing SQL or a NoSQL type per data |
First, two words we'll use all the time. A database is a program that stores data safely and answers questions about it. A query is one such question ("what's Priya's phone?").
Problem 1: three copies of a phone number (the update anomaly)
ChaiNow starts simple: every order is one line in a book with the customer's name, phone and item. Priya orders every day, so her phone is written hundreds of times. Then she changes her number.
Demo 1: copies drift apart
# every order repeats the customer's details
orders = [
{"id": 1, "customer": "Priya", "phone": "98100", "item": "chai"},
{"id": 2, "customer": "Priya", "phone": "98100", "item": "samosa"},
{"id": 3, "customer": "Priya", "phone": "98100", "item": "poha"},
]
# Priya changes her number... we fix the first order we find
for o in orders:
if o["customer"] == "Priya":
o["phone"] = "99999"
break
for o in orders:
print(o["id"], o["customer"], o["phone"])
Output:
1 Priya 99999
2 Priya 98100
3 Priya 98100
Order 1 says 99999; orders 2 and 3 still say 98100. The data now disagrees with itself. This is an update anomaly: one fact (Priya's phone) was stored in many places, and every copy is a chance to forget one.
Tables, primary keys, foreign keys
A school solved this long ago:
| At school | In a database |
|---|---|
| One student list: roll number, name, parent's phone | A table: rows and columns holding one kind of thing |
| Roll number 7, never repeated | A primary key: a unique id for each row |
| The marks sheet says "roll 7", not the whole address again | A foreign key: a column that points to another table's primary key |
Refer, don't copy. In 1970, Edgar F. Codd, working at IBM, published "A Relational Model of Data for Large Shared Data Banks" in Communications of the ACM. His model's terms were relations, tuples and attributes; in practice we say tables, rows and columns. Databases built on it are called relational databases, and SQL (Structured Query Language) is the language you use to ask them questions. Codd received the Turing Award in 1981.
customers orders
+----+-------+-------+ +----+--------+-------------+
| id | name | phone | | id | item | customer_id |
+----+-------+-------+ +----+--------+-------------+
| 7 | Priya | 99999 | ◄──────────| 1 | chai | 7 |
+----+-------+-------+ ├──────| 2 | samosa | 7 |
primary key └──────| 3 | poha | 7 |
+----+--------+-------------+
foreign key ──┘
Demo 2: tables, keys and JOIN
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, phone TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT,
customer_id INTEGER REFERENCES customers(id));
INSERT INTO customers VALUES (7, 'Priya', '98100');
INSERT INTO orders VALUES (1, 'chai', 7), (2, 'samosa', 7), (3, 'poha', 7);
UPDATE customers SET phone = '99999' WHERE id = 7; -- one place
""")
rows = db.execute("""SELECT orders.id, customers.name, customers.phone
FROM orders JOIN customers
ON orders.customer_id = customers.id""")
for row in rows:
print(row)
Output:
(1, 'Priya', '99999')
(2, 'Priya', '99999')
(3, 'Priya', '99999')
Priya is stored once, as customer 7, so updating her phone is one UPDATE on one row. To show each order with the customer's details, JOIN matches each order's customer_id with the customer's id. Splitting data into tables so each fact is stored once is called normalization.
The SQL in that demo, line by line:
| SQL | Meaning |
|---|---|
CREATE TABLE customers (id INTEGER PRIMARY KEY, ...) |
Make a table; id is the primary key |
customer_id INTEGER REFERENCES customers(id) |
A foreign key pointing at customers.id |
INSERT INTO ... VALUES (...) |
Add rows |
UPDATE customers SET phone = '99999' WHERE id = 7 |
Change a row; WHERE picks which rows |
SELECT ... FROM orders JOIN customers ON ... |
Read rows, combining two tables |
-- one place |
A comment in SQL |
Problem 2: an order for nobody (the schema)
A bug in the app tries to save an order for customer 99, who doesn't exist. This time the tables have rules: NOT NULL (the value can't be missing) and the foreign key. SQLite checks foreign keys only after PRAGMA foreign_keys = ON; most other SQL databases always check them.
Demo 3: the database refuses bad data
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON") # SQLite: turn the check on
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL,
customer_id INTEGER REFERENCES customers(id));
INSERT INTO customers VALUES (7, 'Priya');
""")
db.execute("INSERT INTO orders VALUES (1, 'chai', 7)")
print("order 1 saved")
db.execute("INSERT INTO orders VALUES (2, 'samosa', 99)") # no customer 99
print("order 2 saved")
Output:
order 1 saved
Traceback (most recent call last):
File "main.py", line 12, in <module>
db.execute("INSERT INTO orders VALUES (2, 'samosa', 99)") # no customer 99
~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.IntegrityError: FOREIGN KEY constraint failed
The database itself refused the bad order. The schema is the set of rules: the tables, their columns, types and constraints (PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, CHECK). It's decided before data goes in, and the database enforces it on every write, even when the app has a bug.
Problem 3: ₹200 vanishes (transactions)
Priya pays Ravi ₹200 by UPI. For the database that's two steps: take 200 from Priya, then add 200 to Ravi. The server loses power between the two.
Demo 4: two separate steps
isolation_level=None makes this connection save (commit) every statement on its own, the moment it runs.
import sqlite3
db = sqlite3.connect(":memory:", isolation_level=None) # autocommit
db.execute("CREATE TABLE acct (name TEXT, bal INTEGER)")
db.execute("INSERT INTO acct VALUES ('Priya', 500), ('Ravi', 0)")
def send_200(): # Priya pays Ravi 200 by UPI
db.execute("UPDATE acct SET bal = bal - 200 WHERE name = 'Priya'")
raise RuntimeError("server crashed!") # power cut, halfway
db.execute("UPDATE acct SET bal = bal + 200 WHERE name = 'Ravi'")
try:
send_200()
except RuntimeError as e:
print(e)
print(db.execute("SELECT * FROM acct").fetchall())
Output:
server crashed!
[('Priya', 300), ('Ravi', 0)]
Priya has 300, Ravi still has 0: ₹200 vanished. (The line after raise never runs; that's the "crash".)
Demo 5: one transaction
A transaction is a group of steps the database treats as a single unit. In Python's sqlite3, with db: runs the block as one transaction: it commits if the block finishes, and rolls back (undoes everything) if an exception escapes.
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE acct (name TEXT, bal INTEGER)")
db.execute("INSERT INTO acct VALUES ('Priya', 500), ('Ravi', 0)")
db.commit()
def send_200():
with db: # one transaction: all or nothing
db.execute("UPDATE acct SET bal = bal - 200 WHERE name = 'Priya'")
raise RuntimeError("server crashed!")
db.execute("UPDATE acct SET bal = bal + 200 WHERE name = 'Ravi'")
try:
send_200()
except RuntimeError as e:
print(e, "-> rolled back")
print(db.execute("SELECT * FROM acct").fetchall())
Output:
server crashed! -> rolled back
[('Priya', 500), ('Ravi', 0)]
As if the payment never started. And when nothing crashes, both steps are committed together:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE acct (name TEXT, bal INTEGER)")
db.execute("INSERT INTO acct VALUES ('Priya', 500), ('Ravi', 0)")
db.commit()
def send(amount):
with db: # commit if both succeed
db.execute("UPDATE acct SET bal = bal - ? WHERE name = 'Priya'", (amount,))
db.execute("UPDATE acct SET bal = bal + ? WHERE name = 'Ravi'", (amount,))
send(200)
print(db.execute("SELECT * FROM acct").fetchall())
Output:
[('Priya', 300), ('Ravi', 200)]
ACID
The guarantees of a transaction are known as ACID (the acronym was coined in 1983 by Andreas Reuter and Theo Härder, building on Jim Gray's work):
| Letter | Meaning (Wikipedia, "ACID") | In the UPI example |
|---|---|---|
| Atomicity | each transaction "either succeeds completely or fails completely" | Both balances change, or neither |
| Consistency | it "can only bring the database from one consistent state to another" | Rules (like "balance ≥ 0") hold before and after |
| Isolation | the result is as if transactions were "executed sequentially" | Two payments at once don't mix up the balances |
| Durability | "once a transaction has been committed, it will remain committed even in the case of a system failure" | A confirmed payment survives a power cut |
Consistency in action, with a CHECK constraint (note the order: Ravi is credited first, then Priya's debit breaks the rule, and the whole transaction is rolled back):
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE acct (name TEXT, bal INTEGER CHECK (bal >= 0))")
db.execute("INSERT INTO acct VALUES ('Priya', 500), ('Ravi', 0)")
db.commit()
def send(amount):
with db:
db.execute("UPDATE acct SET bal = bal + ? WHERE name = 'Ravi'", (amount,))
db.execute("UPDATE acct SET bal = bal - ? WHERE name = 'Priya'", (amount,))
try:
send(800) # Priya only has 500
except sqlite3.IntegrityError as e:
print("refused:", e)
print(db.execute("SELECT * FROM acct").fetchall())
Output:
refused: CHECK constraint failed: bal >= 0
[('Priya', 500), ('Ravi', 0)]
Problem 4: every item is different (the schema becomes a wall)
ChaiNow grows. Kulhad chai needs a sugar level, a birthday cake needs a weight, a breakfast combo is a list of other items.
Demo 6: no column named sugar
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE menu (name TEXT, price INTEGER)")
db.execute("INSERT INTO menu VALUES ('masala chai', 20)")
# new items need new fields: sugar level, weight, combo items...
db.execute("INSERT INTO menu (name, price, sugar) VALUES ('kulhad chai', 30, 'less')")
Traceback (most recent call last):
File "main.py", line 7, in <module>
db.execute("INSERT INTO menu (name, price, sugar) VALUES ('kulhad chai', 30, 'less')")
~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: table menu has no column named sugar
The schema that saved us from the bad order now blocks us. Every new kind of item means changing the table first (ALTER TABLE menu ADD COLUMN sugar TEXT), and most rows get empty (NULL) columns: a cake has no sugar level, chai has no weight. When the shape of your data keeps changing, a fixed schema becomes a wall.
Documents
| In an office | In a database |
|---|---|
| A printed form: the same boxes for everyone | A SQL table: fixed columns, a schema |
| A file folder per customer, with whatever papers they have | A document: a JSON-like record with its own shape |
| Open one folder to see everything about that customer | One lookup, no JOIN for that record |
Demo 7: a tiny document store
import json
menu = {} # a tiny document store: key -> document
menu["chai-01"] = {"name": "kulhad chai", "price": 30, "sugar": "less"}
menu["cake-07"] = {"name": "eggless cake", "price": 450, "kg": 0.5}
menu["combo-3"] = {"name": "breakfast combo", "price": 99,
"items": ["poha", "chai", "banana"]}
for key, doc in menu.items(): # each document has its own shape
print(key, json.dumps(doc))
print(menu["combo-3"]["items"]) # one lookup, no JOIN
Output:
chai-01 {"name": "kulhad chai", "price": 30, "sugar": "less"}
cake-07 {"name": "eggless cake", "price": 450, "kg": 0.5}
combo-3 {"name": "breakfast combo", "price": 99, "items": ["poha", "chai", "banana"]}
['poha', 'chai', 'banana']
Three documents, three shapes, no schema change, and the combo's items come back in one lookup. That's a document database, like MongoDB, which in MongoDB's words "stores data in documents similar to JSON (JavaScript Object Notation) objects". JSON is the text format for data like {"name": "chai", "price": 30}.
The catch: copies come back
If you put a copy of the customer inside every order document (so one lookup shows everything), the update anomaly from Problem 1 is back:
# orders as documents, each with a copy of the customer
orders = {
"o1": {"item": "chai", "customer": {"name": "Priya", "phone": "98100"}},
"o2": {"item": "samosa", "customer": {"name": "Priya", "phone": "98100"}},
}
orders["o1"]["customer"]["phone"] = "99999" # update one copy
for key, doc in orders.items():
print(key, doc["customer"]["phone"])
Output:
o1 99999
o2 98100
Document databases let you choose: embed copies (fast reads, careful updates) or store an id and look it up (like a foreign key, but your app does the join). Flexibility moves that job from the database to you.
The NoSQL family
NoSQL is usually read as "not only SQL": databases that don't use the relational table model. MongoDB's overview lists four main types:
| Type | How it stores data | Examples | Good for |
|---|---|---|---|
| Document | "documents similar to JSON objects" | MongoDB, Couchbase | Product catalogues, profiles, content with varying fields |
| Key-value | "each item contains keys and values" | Redis, Amazon DynamoDB | Caches, sessions, shopping carts: get by key |
| Wide-column | "tables, rows, and dynamic columns" | Apache Cassandra, HBase | Huge write volumes: messages, events, time series |
| Graph | "nodes and edges" | Neo4j, Amazon Neptune | Relationships: friends of friends, recommendations |
SQL vs NoSQL: the trade-offs
| SQL (relational) | NoSQL (e.g. documents) | |
|---|---|---|
| Shape | Fixed schema, checked on every write | Flexible: each record can differ |
| Relations | JOIN across tables | Usually embed data or join in the app |
| Transactions | ACID transactions built in | Varies by product; often focused on one record |
| Scaling | Usually scale up first; spreading across many machines is harder | Many are built to spread data across machines |
| Duplicates | Normalized: each fact once | Copies are common; keeping them in sync is your job |
| Best when | Correctness and relations matter: money, bookings, inventory | Shape varies a lot, or scale and write volume are huge |
Choosing, per data: UPI payments and seat bookings → SQL (transactions); a product catalogue → document; sessions and caches → key-value; chat messages at huge volume → wide-column; friend suggestions → graph. Big apps use several databases at once, each for the data it suits (sometimes called polyglot persistence). When unsure, start with SQL: it's the safe default and goes very far.
The whole chain
- A phone number copied three times drifted apart → update anomaly.
- Store each fact once → tables, primary keys, foreign keys, and JOIN to combine them (the relational model).
- A bad order got in → the schema and constraints refuse it (
IntegrityError). - ₹200 vanished in a crash → transactions: commit or roll back, ACID.
- Every menu item had a different shape → the schema became a wall → documents (NoSQL).
- Choose per data: SQL, document, key-value, wide-column or graph.
Common mistakes (and the real errors)
| Mistake | Code | Error | Fix |
|---|---|---|---|
| Foreign key points to nothing | INSERT INTO orders VALUES (2, 'samosa', 99) |
sqlite3.IntegrityError: FOREIGN KEY constraint failed |
Insert the customer first (SQLite: PRAGMA foreign_keys = ON) |
| Column not in the schema | INSERT INTO menu (name, price, sugar) ... |
sqlite3.OperationalError: table menu has no column named sugar |
ALTER TABLE menu ADD COLUMN sugar TEXT, or use documents |
| Duplicate primary key | INSERT INTO customers VALUES (7, 'Ravi') when 7 exists |
sqlite3.IntegrityError: UNIQUE constraint failed: customers.id |
Use a new id, or let the database pick one |
| Rule broken inside a transaction | UPDATE acct SET bal = bal - 800 ... with CHECK (bal >= 0) |
CHECK constraint failed: bal >= 0 |
Check the balance first; the transaction rolls back |
| Forgetting SQLite's foreign key switch | no PRAGMA foreign_keys = ON |
no error: the bad order is saved | Turn it on for every connection |
The duplicate primary key in full:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT)")
db.execute("INSERT INTO customers VALUES (7, 'Priya')")
db.execute("INSERT INTO customers VALUES (7, 'Ravi')")
Output:
Traceback (most recent call last):
File "main.py", line 5, in <module>
db.execute("INSERT INTO customers VALUES (7, 'Ravi')")
~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.IntegrityError: UNIQUE constraint failed: customers.id
Misconceptions
- "NoSQL means no schema." The database may not enforce one, but your code still expects fields to be there. The schema moves into your app.
- "NoSQL is faster than SQL." Each is fast for the access patterns it was designed for. A SQL database with the right indexes (next episode) is very fast.
- "SQL databases can't scale." They scale up a long way, and can be replicated and sharded (coming episodes). Spreading them out is just harder.
- "Pick one database for the whole app." Real systems often use several.
- "JOINs are bad." JOINs are how relational databases avoid duplicate data. They cost time on huge tables, which is one reason some systems store copies instead.
Mini project: ChaiNow orders, both ways
Customers, a menu with item-specific fields stored as JSON text, orders with foreign keys, and place_order as a transaction: every item in an order is saved, or none.
# ChaiNow orders, both ways: SQL tables + a transaction, and documents.
import sqlite3, json
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL,
phone TEXT UNIQUE);
CREATE TABLE menu (id INTEGER PRIMARY KEY, name TEXT NOT NULL,
price INTEGER NOT NULL CHECK (price > 0),
extra TEXT); -- JSON for item-specific fields
CREATE TABLE orders (id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
menu_id INTEGER NOT NULL REFERENCES menu(id), qty INTEGER);
INSERT INTO customers VALUES (7, 'Priya', '98100'), (8, 'Ravi', '98200');
INSERT INTO menu VALUES (1, 'kulhad chai', 30, '{"sugar": "less"}'),
(2, 'eggless cake', 450, '{"kg": 0.5}');
""")
def place_order(customer_id, items): # all items, or none
with db:
for menu_id, qty in items:
db.execute("INSERT INTO orders (customer_id, menu_id, qty) VALUES (?, ?, ?)",
(customer_id, menu_id, qty))
place_order(7, [(1, 2), (2, 1)])
try:
place_order(8, [(1, 1), (99, 1)]) # menu item 99 doesn't exist
except sqlite3.IntegrityError as e:
print("order refused:", e)
bill = db.execute("""SELECT c.name, m.name, o.qty, o.qty * m.price, m.extra
FROM orders o JOIN customers c ON o.customer_id = c.id
JOIN menu m ON o.menu_id = m.id""")
for name, item, qty, total, extra in bill:
print(name, item, qty, total, json.loads(extra))
print("orders saved:", db.execute("SELECT COUNT(*) FROM orders").fetchone()[0])
Output:
order refused: FOREIGN KEY constraint failed
Priya kulhad chai 2 60 {'sugar': 'less'}
Priya eggless cake 1 450 {'kg': 0.5}
orders saved: 2
Ravi's order was refused as a whole: his first item was rolled back too, so only Priya's two order lines exist. Try it: add a UNIQUE phone for a new customer that reuses Priya's number, or add a combo whose extra holds a list of items.
Practice
- In Demo 3, remove
PRAGMA foreign_keys = ON. What happens to order 2, and why is that dangerous? - In Demo 5, remove the
raiseline. What are the two balances? - Add
CHECK (bal >= 0)and try to send ₹800. What happens? - Insert customer 7 twice. What's the error?
- Store orders as documents with a copy of the customer inside. Change Priya's phone in one order. What do you see?
- SQL or which NoSQL type for: (a) IRCTC seat booking, (b) a shopping cart, (c) "people you may know", (d) Instagram comments at huge scale?
Solutions and hints
1. The order is saved, pointing at a customer who doesn't exist (an "orphan" row). A JOIN finds no name for it:
import sqlite3
db = sqlite3.connect(":memory:") # no PRAGMA foreign_keys = ON
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, item TEXT NOT NULL,
customer_id INTEGER REFERENCES customers(id));
INSERT INTO customers VALUES (7, 'Priya');
""")
db.execute("INSERT INTO orders VALUES (2, 'samosa', 99)") # no customer 99
print("order 2 saved")
rows = db.execute("""SELECT orders.id, orders.item, customers.name
FROM orders LEFT JOIN customers
ON orders.customer_id = customers.id""")
print(rows.fetchall())
Output:
order 2 saved
[(2, 'samosa', None)]
2. Both steps commit together:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE acct (name TEXT, bal INTEGER)")
db.execute("INSERT INTO acct VALUES ('Priya', 500), ('Ravi', 0)")
db.commit()
def send(amount):
with db: # commit if both succeed
db.execute("UPDATE acct SET bal = bal - ? WHERE name = 'Priya'", (amount,))
db.execute("UPDATE acct SET bal = bal + ? WHERE name = 'Ravi'", (amount,))
send(200)
print(db.execute("SELECT * FROM acct").fetchall())
Output:
[('Priya', 300), ('Ravi', 200)]
3. Refused, and rolled back as a whole:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE acct (name TEXT, bal INTEGER CHECK (bal >= 0))")
db.execute("INSERT INTO acct VALUES ('Priya', 500), ('Ravi', 0)")
db.commit()
def send(amount):
with db:
db.execute("UPDATE acct SET bal = bal + ? WHERE name = 'Ravi'", (amount,))
db.execute("UPDATE acct SET bal = bal - ? WHERE name = 'Priya'", (amount,))
try:
send(800) # Priya only has 500
except sqlite3.IntegrityError as e:
print("refused:", e)
print(db.execute("SELECT * FROM acct").fetchall())
Output:
refused: CHECK constraint failed: bal >= 0
[('Priya', 500), ('Ravi', 0)]
4.
Traceback (most recent call last):
File "main.py", line 5, in <module>
db.execute("INSERT INTO customers VALUES (7, 'Ravi')")
~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.IntegrityError: UNIQUE constraint failed: customers.id
5. The copies drift apart again:
# orders as documents, each with a copy of the customer
orders = {
"o1": {"item": "chai", "customer": {"name": "Priya", "phone": "98100"}},
"o2": {"item": "samosa", "customer": {"name": "Priya", "phone": "98100"}},
}
orders["o1"]["customer"]["phone"] = "99999" # update one copy
for key, doc in orders.items():
print(key, doc["customer"]["phone"])
Output:
o1 99999
o2 98100
6. (a) SQL with transactions: one seat, one passenger, payment and booking together. (b) Key-value (or document): fetched by user id, short-lived. (c) Graph: follow friend edges. (d) Wide-column or document: enormous write volume, simple access by post id. Any well-argued answer is fine.
Quick quiz
# IRCTC Tatkal: 10:00 am, thousands
# of people want the LAST seat.
# Exactly one may get it, and the
# payment must match the booking.
# Which fits best?
- A. Key-value cache
- B. SQL with transactions
- C. Graph database
- D. No database, just a file
Answer
B, SQL with transactions. One seat, one passenger, and the payment and booking succeed or fail together: atomicity and isolation.
Interview questions
- What is an update anomaly? How does normalization prevent it?
- What's the difference between a primary key and a foreign key?
- What does a JOIN do? Write one for orders and customers.
- What is a schema? Give three kinds of constraints.
- What is a transaction? Explain each letter of ACID with a bank transfer.
- Name the four main types of NoSQL databases, with an example and a use case for each.
- When would you choose SQL over NoSQL, and the other way round?
- Why might a document database store copies of data, and what's the risk?
Glossary
| Term | Meaning |
|---|---|
| Database | A program that stores data safely and answers questions about it |
| Query | One question asked to a database |
| Update anomaly | Copies of one fact disagree after only some are updated |
| Relational model | Codd's 1970 idea: data in tables (relations) linked by keys |
| Relational database | A database built on the relational model (SQLite, MySQL, PostgreSQL) |
| SQL | Structured Query Language: the language for relational databases |
| SQLite | A small SQL database that ships with Python (import sqlite3) |
| Table / row / column | A grid of one kind of thing / one record / one field |
| Primary key | A column whose value uniquely identifies each row |
| Foreign key | A column that refers to another table's primary key |
| JOIN | Combining rows of two tables where keys match |
| Normalization | Splitting data into tables so each fact is stored once |
| Schema | The rules for the data: tables, columns, types, constraints |
| Constraint | A rule the database enforces: PRIMARY KEY, NOT NULL, UNIQUE, REFERENCES, CHECK |
NOT NULL |
The value must be present |
NULL |
"No value" in SQL |
PRAGMA foreign_keys = ON |
SQLite setting that turns foreign key checks on |
IntegrityError |
sqlite3 error when a constraint is broken |
OperationalError |
sqlite3 error for a bad statement, like an unknown column |
ALTER TABLE |
SQL command to change a table's columns |
| Transaction | A group of steps the database treats as one unit |
| Commit | Make a transaction's changes permanent |
| Rollback | Undo all of a transaction's changes |
| Autocommit | Every statement is committed on its own (isolation_level=None) |
| ACID | Atomicity, Consistency, Isolation, Durability: a transaction's guarantees |
| Document | A JSON-like record that can have its own shape |
| JSON | A text format for data: {"name": "chai", "price": 30} |
| Document database | Stores documents; e.g. MongoDB, Couchbase |
| NoSQL | "Not only SQL": databases that don't use the relational table model |
| Key-value store | Stores values by key; e.g. Redis, DynamoDB |
| Wide-column store | Tables with rows whose columns can differ; e.g. Cassandra, HBase |
| Graph database | Stores nodes and edges; e.g. Neo4j, Amazon Neptune |
| Scale up / scale out | A bigger machine / more machines (episode 5) |
| Polyglot persistence | Using several kinds of database in one system |
with db: |
In Python's sqlite3, runs a block as one transaction |
executescript |
Runs several SQL statements at once |
Summary
- Copies of one fact drift apart (update anomaly). Relational databases store each fact once, in tables linked by primary and foreign keys, and JOIN them back.
- The schema and its constraints refuse bad data on every write (
FOREIGN KEY constraint failed). - A transaction makes several steps all-or-nothing: the crash left ₹500 / ₹0 instead of losing ₹200. Its guarantees are ACID.
- When every record has a different shape, a fixed schema becomes a wall; documents let each record have its own shape.
- NoSQL has four main types: document, key-value, wide-column, graph. Choose per data; when unsure, start with SQL.
Next episode: put a million customers in a table, look one up by phone number, and the database reads every single row. Indexing: you'll make that query thousands of times faster with one line.