Databases & SQL — cheat sheet

Mandatory. Source: roadmap.md section "Databases & SQL". General join/ACID/ normalization/NoSQL detail lives in 06-optional-topics.md since the roadmap files those under "optional".

• Database: an organized collection of data, stored and accessed electronically.
• Relational database: stores data as tables (rows/columns) with defined relationships between them, based on relational algebra.
• SQL: Structured Query Language — the language for querying/manipulating relational databases.
• SQL vs MySQL: SQL is the language/standard; MySQL is one specific database product that implements it. Others: PostgreSQL, SQL Server, Oracle, SQLite.
• SQL database examples: PostgreSQL, MySQL, SQL Server, Oracle, SQLite.
• NoSQL database examples: MongoDB (document), Redis (key-value), Cassandra (wide-column), Neo4j (graph).
• Data storage: tables of rows (records) and columns (fields), each row usually identified by a primary key.
• Schema: the structure/blueprint of the database — tables, columns, types, constraints, relationships.
• Primary key: the column(s) that uniquely identify each row in a table. Needed so you can reliably reference, update, join, or delete one specific row. Example: a users table's id column.
• Relationships (with examples):
  • 1-to-1: each row in A relates to exactly one row in B. E.g. a User and their UserProfile.
  • 1-to-many: one row in A relates to many rows in B. E.g. one Author has many Posts.
  • Many-to-many: many rows in A relate to many rows in B, needs a join table. E.g. Posts and Authors — a post can have several authors, an author writes several posts.
• Integrity constraint: a rule the DB enforces to keep data valid, e.g. NOT NULL, UNIQUE, FOREIGN KEY, CHECK. Example: a foreign key on post.author_id ensures it always points to a real row in author.
• ER diagram: Entity-Relationship diagram — a visual layout of tables/entities and how they relate, used when designing a schema.

Security

• SQL injection: an attacker sneaks SQL into an input field that gets concatenated directly into a query string, letting them run arbitrary SQL (read/modify/drop data, bypass auth) instead of the intended query.
• Prevention — prepared statements: a prepared/parameterized statement sends the SQL structure and the user-supplied values separately to the DB, so the driver never interprets a value as part of the SQL syntax. Plain string concatenation lets user input "break out" of the intended query; a prepared statement makes that structurally impossible.

Soft deletion

• Instead of physically removing a row, flag it as deleted (e.g. a deleted_at timestamp or is_deleted boolean) and filter it out of normal queries. Useful for audit trails, undoing accidental deletes, and not breaking foreign keys from other rows that still reference it.