Wrap-up and resources

Key Takeaways

1. SQL - CRUD: four statements (INSERT/SELECT/UPDATE/DELETE) that work on sets of rows, not row by row; preview rows with a SELECT using the same WHERE before any update or delete, use HAVING to filter groups and WHERE to filter rows, and watch out for NULL.
2. SQL - Joins & Relationships: a foreign key ties a child to its parent and JOIN reassembles what the design split apart; put matching conditions in ON, not WHERE, with LEFT JOIN, turn on PRAGMA foreign_keys, and avoid NOT IN with NULL.
3. SQL - Database Design & Normalization: every fact in one place: atomic values (1NF), the whole key (2NF), nothing but the key (3NF); pick the ON DELETE action that fits the relationship’s meaning and write the rules as constraints.
4. Python & databases: the driver (MySQLdb) sends SQL with %s placeholders and saves nothing before commit; the ORM (SQLAlchemy) maps a table to a class and a row to an object, but it writes SQL for you without excusing you from reading it, watch for N+1, and open a short session per unit of work.

Glossary

Term	Definition	Part
CRUD	The initials of the four basic data operations: Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE)	1
Constraint	A rule the database itself enforces on every row: `NOT NULL`, `UNIQUE`, `CHECK`, `DEFAULT`, `PRIMARY KEY`; on violation the statement is cancelled with an error	1
NULL	Means “no value / unknown”, not zero and not an empty string; any comparison with it yields NULL, so we test it with `IS NULL`	1
Placeholder (`?`)	A marker in a SQL statement whose value is passed separately from the text so the engine treats it as data, not code, the main defense against SQL injection	1
Type Affinity	SQLite’s flexible typing: a column’s declared type is a preference, not a hard rule, except in tables declared `STRICT`	1
Aggregate / GROUP BY / HAVING	An aggregate (`COUNT/SUM/AVG/MIN/MAX`) collapses rows into one value; `GROUP BY` splits rows into groups, and `HAVING` filters groups after aggregation while `WHERE` filters rows before it	1
Primary Key / Foreign Key	A Primary Key uniquely identifies a row in its table; a Foreign Key in a child table points to a Primary Key in a parent table, the basis of joins and relationship integrity	2
Referential Integrity	The guarantee that no foreign key points at a missing parent row; in SQLite it requires `PRAGMA foreign_keys = ON` on every connection	2
JOIN (INNER / LEFT)	Combining rows of two tables with an `ON` condition: `INNER` keeps only matches, `LEFT` keeps every left row with NULL where there is no match (RIGHT and FULL exist since SQLite 3.39.0)	2
Cartesian Product / CROSS JOIN	Pairing every row with every row of another table with no condition; yields m × n rows, which is what happens when you forget the join condition	2
Junction Table	A table that represents a many-to-many relationship with a foreign key to each side and a composite primary key, like `order_items`	2
Anti-join	A pattern that finds parent rows with no counterpart: `LEFT JOIN … WHERE child.id IS NULL` or `NOT EXISTS`	2
Subquery	A `SELECT` inside a `SELECT`: a scalar value, a list for `IN`, or `EXISTS`; a correlated one references the outer query and is recomputed for each row	2
ON DELETE (CASCADE / SET NULL / RESTRICT)	The specified fate of child rows when the parent is deleted: delete them, null their key, or block the deletion	2
Functional Dependency	“A determines B” (A → B): each value of A corresponds to exactly one value of B; the foundation for spotting redundancy and normalizing	3
Normalization (1NF/2NF/3NF)	Decomposing a flat table into related tables to remove redundancy: 1NF atomic values, 2NF no dependence on part of the key, 3NF no dependence on a non-key column	3
Anomaly	The three redundancy problems: contradiction on update, inability to insert an independent fact, and loss of other facts when a row is deleted	3
Candidate / Surrogate Key	Candidate: any valid key (guarded with `UNIQUE` if not chosen as primary); surrogate: a system-generated number with no outside meaning that never changes	3
Cardinality	The number of rows allowed at each end of a relationship: one-to-one, one-to-many, many-to-many	3
Denormalization	Deliberately repeating a fact to speed up reads, paid for by keeping the copies identical by hand; always start from a normalized design	3
Lossless Decomposition	A split that can be reassembled by joins into exactly the original table, so nothing is lost and no phantom rows appear	3
ER Diagram	A diagram of entities, their attributes, relationships and counts, drawn before writing the tables	3
Driver	A library that talks to the database server and runs SQL (like MySQLdb), following the PEP 249 contract	4
Cursor	An object obtained from the connection that runs queries and reads rows	4
Placeholder	A marker (`%s` in MySQLdb) put where a value goes; values are passed separately so the driver escapes them	4
SQL Injection	Input that changes a query’s meaning because it was pasted into it; prevented by passing values as parameters	4
Transaction	A group of changes finalized together with commit or cancelled together with rollback	4
ORM	A layer that maps a table to a class and a row to an object and generates SQL on your behalf	4
Engine	An object created once from the database URL that manages a connection pool	4
Session	A workspace that tracks objects and writes them to the database inside a transaction	4
flush	Sending pending SQL inside the transaction without finalizing it	4
N+1	One query for the list plus one more per item because of lazy loading	4
Document	A JSON-like object holding the whole thing.	5
Embedding	Putting related data inside the document itself.	5
Cache-aside	Read from the cache; on a miss read the database, then store.	5

Sources

The best outside resources for these lessons, classified by kind and level. All are open to everyone and need no account on any platform. Use the filters to narrow the list.

0 resources