home
›
cheatsheets
›
database_technologies
›
database_design
Free to read Every card and diagram, and Quiz me
2
Free account Star cards. Your Quiz me marks are kept, so next time you drill just what you missed
3
Pro All 39 answers on this sheet, My sheet across every topic, and PDFs
A free account keeps your stars and Quiz me marks. Pro adds all 39 answers on this sheet, My sheet across every topic, and PDFs.
/
Read
Quiz me
Comfortable
Compact
0
Click any code to copy it
Done 0 /0
To review 0
On this sheet
01 DBMS, SQL & schema02 Model the data03 Keys & constraints04 Normalize & denormalize05 Indexes & query speed06 Transactions & locking07 Views, CTEs & triggers08 OLTP, OLAP & warehouses09 Scaling, CAP & NoSQL10 Pools, backups & security
The model Start from the workflows and their queries. Entities become tables, and relationships become foreign keys or junction tables. Normalize so each fact lives in one place. Denormalize on purpose, where reads need fewer joins.Put the rules in the database. Constraints and foreign keys reject bad data from every application. An index speeds up reads that filter, join or sort on its columns, and slows every write. Replicas copy data for more reads and for failover. Shards split data that outgrows one server.A customer has many addresses and orders, and an order can have many payments. Orders and products are many-to-many, so order_item links them.
DBMS, SQL & schema what a database system does, and how it is layered What a DBMS gives you List the advantages of a DBMS Data independence, integrity, security, concurrent access, backup and recovery, and less redundancy.
Tip A DBMS is the software that manages a database. Data independence means applications don't depend on how the data is stored.
SQL command groups Create, change or drop tables DDL, the Data Definition Language, with CREATE, ALTER, DROP and TRUNCATE.
Read and change rows DML, the Data Manipulation Language, with SELECT, INSERT, UPDATE and DELETE.
Grant or revoke access DCL, the Data Control Language, with GRANT and REVOKE.
Keep or undo a transaction's changes TCL, the Transaction Control Language, with COMMIT, ROLLBACK and SAVEPOINT.
Trap TRUNCATE removes rows, but it is DDL, not DML.
Schema and its three levels Give each user their own view of the data The external, or view, level.
Describe all the data and how it relates The conceptual, or logical, level, shared by all users.
Describe how the data is stored The internal, or physical, level, with files and indexes.
Tip A schema defines tables, columns, relationships and constraints. Each ANSI-SPARC level can change without affecting the one above.
Model the data from entities and relationships to tables From ER model to tables Draw an entity, an attribute and a relationship A rectangle, an oval and a diamond.
Name a link's cardinality How many instances can link on each side, written 1:1, 1:M or M:N.
Map an entity to a table Its identifier becomes the primary key, and its attributes become columns.
Map one-to-one, like a user and their profile A foreign key in either table, or merge the tables.
Map one-to-many, like a customer and their orders A foreign key on the many side, like orders.customer_id .
Map many-to-many, like students and courses A junction table with a foreign key to each side.
Link a table to itself A self-referencing key, like manager_id pointing at employee_id.
Tip Split a composite attribute, like an address, into columns. Give a multivalued one its own table.
Conceptual, logical and physical models Agree on entities with the business A conceptual model, with no technical detail.
Add attributes and keys A logical model, still independent of any product.
Build the model in one database engine A physical model, with tables, indexes and constraints.
Tip Work top-down for a new system, and bottom-up from a legacy system's data. Complex systems mix both.
Trap In the three-level architecture, conceptual and logical name one level. In data modeling, they are two models.
Design an e-commerce schema List the core tables Customers, addresses, products, categories, orders, order items, payments and inventory.
Store prices and totals unit_price DECIMAL(10, 2) NOT NULL Use DECIMAL, not a float, which stores some values only approximately.
Index a customer's orders by date CREATE INDEX idx_orders_customer ON orders (customer_id, created_at);
Trap Store each item's unit price at the time of purchase. Otherwise a later price change rewrites past orders.
Keys & constraints identify each row and keep bad data out Kinds of keys Identify each row A primary key, unique and never NULL. A table has one.
Find each column set that could be the primary key Candidate keys, unique and minimal. The ones you don't pick are alternate keys.
Name any column set that identifies a row A super key, like the primary key plus extra columns.
Point at a row in another table A foreign key. It may be NULL unless you add NOT NULL.
Keep values unique but allow NULL A unique key. PostgreSQL allows many NULL values, and SQL Server allows one.
Tip A composite key is a primary key of several columns, like (order_id, product_id).
Natural or surrogate key Use a value from the data as the key, like an ISBN A natural key. If it changes, every referencing table must change.
Use a generated value with no meaning as the key A surrogate key, like an auto-increment id. It never has to change.
Generate ids anywhere without a central sequence A UUID. Use time-ordered UUIDv7, since random v4 values scatter across the index.
Trap A surrogate key doesn't stop duplicate rows. Keep a UNIQUE constraint on the natural key too.
Constraints Reject a price of zero or less ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price > 0);
Reject a second user with the same email ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);
Tip The database rejects a bad write from every application, not only yours.
Trap A complex CHECK runs on every insert and update, so keep it cheap.
Referential integrity Say what referential integrity means Every foreign key is NULL or matches an existing parent row, so no row is orphaned.
When a parent is deleted, delete or unlink its children ON DELETE CASCADE ON DELETE SET NULL CASCADE deletes them. SET NULL keeps them with a NULL key.
Block a parent delete while children exist ON DELETE RESTRICT NO ACTION, PostgreSQL's default, also blocks it. A deferrable one checks at commit.
Trap PostgreSQL doesn't index a foreign key for you. Without that index, each parent delete scans the child table.
Normalize & denormalize store each fact once, then copy only on purpose Normalization and normal forms Say what normalization prevents Update anomalies. Each fact lives in one place, so you change it once.
Name the cost of normalizing More tables and more joins, which can slow reads.
Fix a column that holds two phone numbers 1NF needs atomic values. Give phones a table, one row per number.
Fix product_name in order items keyed by order and product It breaks 2NF, since it depends on only part of the key. Move it to products.
Fix dept_name stored in employees It breaks 3NF, since it depends on dept_id, not the key. Move it to departments.
Reach BCNF Every determinant, a column set that decides another column, must be a candidate key.
Tip Each form needs the one before it. Only a table with a composite key can break 2NF.
When to denormalize Decide when to denormalize When reads far outnumber writes, and joins are too slow for your response time.
Skip the category join on product pages Store the category name on each product row.
Tip Normalize first. Denormalize only where measurements show a need.
Trap Every update must now keep each copy in sync, so writes get more complex.
Indexes & query speed find rows fast, and fix a slow query How an index works Find rows without scanning the whole table An index, usually a B-tree, kept sorted with a pointer to each row.
Tell a clustered index from a non-clustered one A clustered index stores the rows in key order, one per table. Non-clustered ones point to the rows.
Name what an index costs Extra storage, and slower writes, since every index must change too.
When to add an index Pick columns to index WHERE, JOIN and ORDER BY columns, foreign keys, and columns with many distinct values.
Choose between indexing email and status Index email. A three-value status has low selectivity and barely narrows the search.
Know when to skip an index On small tables, write-heavy tables and columns that change often.
Tip Measure before you add an index, and drop the ones nothing uses.
Column order in a composite index CREATE INDEX ix ON orders (customer_id, order_id); WHERE customer_id = 42 uses the index WHERE customer_id = 42 AND order_id = 1001 uses the index WHERE order_id = 1001 leading column missing
Say which queries an index on (a, b, c) serves Queries that filter on its leading columns, the leftmost prefix rule.
Order the columns for a = 5 AND b > 42 Equality columns first, then the range or sort column.
Tip An index on (a, b) also serves a alone, so a separate index on a is usually redundant.
Speed up a slow query See how the database runs a query EXPLAIN SELECT * FROM orders WHERE customer_id = 42; Look for a full table scan.
Filter one year so an index can help WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01' Sargable, meaning an index can serve it. YEAR(OrderDate) = 2023 can't.
Answer a query from the index alone A covering index holds every selected column, so the table is never read.
Trap Don't reorder joins or WHERE conditions by hand. The optimizer picks the order itself.
Transactions & locking keep data correct when many users write at once ACID Make a transfer all or nothing Atomicity. Every step succeeds, or the whole transaction rolls back.
Keep every constraint true Consistency. The data is valid before and after.
Stop concurrent transactions from interfering Isolation. Each runs as if it were alone.
Keep committed data after a crash Durability. A committed change is permanent.
Isolation levels Dirty Re-read New rows
READ UNCOMMITTED Read changes nobody has committed yet
READ COMMITTED The default in most databases, PostgreSQL included. See only committed data
REPEATABLE READ MySQL InnoDB's default. PostgreSQL also blocks phantoms here. Get the same row back when you read it again
SERIALIZABLE Act as if transactions ran one at a time
Tip A dirty read sees uncommitted data. A non-repeatable read gets a changed row. A phantom read finds new rows.
Optimistic or pessimistic locking Lock a row while you change it SELECT * FROM Account WHERE AccountID = 1 FOR UPDATE; Pessimistic. Other writers wait for your transaction.
Update only if nobody changed the row since you read it SET Version = Version + 1 WHERE AccountID = 1 AND Version = 2 Optimistic. Each row carries a version number.
Tip Use pessimistic locking for high contention, and optimistic when conflicts are rare.
Trap An update that matches zero rows raises no error. Check the row count, then read again and retry.
Deadlocks Spot a deadlock Two transactions each wait for a lock the other holds.
Prevent deadlocks by design Lock ordering. Every transaction locks in the same global order.
Let the database break a deadlock Deadlock detection rolls back one victim, and most databases use it. A lock timeout is simpler.
Trap The victim gets an error. Your code must catch it and retry.
MVCC reader A reads 1000 reads 1000 reads 1000 writer B writes 900 commits time A runs at Repeatable Read. Its reads keep seeing 1000, even after B commits 900, because B wrote a new row version. Neither waits for the other.
Let reads and writes run side by side Keep several versions of each row. Reads and writes don't block each other.
Say which version a reader sees Its snapshot, per statement, or per transaction at Repeatable Read.
Tip Old versions take storage until garbage collection removes them. PostgreSQL calls it VACUUM.
Trap Writers still lock the rows they change. A second writer to the same row waits.
Views, CTEs & triggers queries and code the database keeps for you Views and materialized views Give a team only the columns it needs CREATE VIEW customer_contacts AS SELECT name, email FROM customers;
List what views give you Security, simpler queries, data independence and shared logic.
Insert or update through a view Only an updatable view, usually one table with no aggregates.
Store a slow query's result A materialized view, for slow reports and frequently used joins. You can index it.
Refresh a materialized view REFRESH MATERIALIZED VIEW monthly_sales; A full rebuild. Oracle can also refresh incrementally.
Trap A plain view stores no data, so it isn't faster. A materialized view is only as fresh as its last refresh.
Common table expressions Name the steps of a complex query A CTE, defined with WITH. The statement reads it like a table, as often as it needs.
Walk a tree, like an org chart WITH RECURSIVE An anchor picks the top rows, then a recursive part joins back with UNION ALL.
Trap A CTE lasts for one statement. The next statement can't read it.
Triggers Run code on every insert, update or delete A DML trigger runs BEFORE, AFTER or INSTEAD OF the change. SQL Server has no BEFORE, and MySQL no INSTEAD OF.
React to schema changes or new sessions A DDL trigger fires on CREATE, ALTER or DROP, and a logon trigger at session start. MySQL has neither.
Tip Use triggers for audit logs and rules every application must follow.
Trap A trigger slows every write to its table. Keep heavy or risky work in the application.
OLTP, OLAP & warehouses run the business, then analyze it somewhere else OLTP or OLAP Process orders and payments as they happen OLTP. Many short writes from many users, on current, normalized data.
Analyze sales over several years OLAP. Complex queries scan and aggregate history, often on denormalized tables.
Stop reports from slowing down transactions Run them on a separate system, like a data warehouse.
Tip OLTP is Online Transaction Processing. OLAP is Online Analytical Processing.
Star or snowflake schema date product sales customer store star Each dimension is one flat table, one join away from the fact table.date product sales customer category store department snowflake The product dimension splits into a chain of tables, so queries need more joins.Store each sale's quantity and amount A fact table, with a foreign key to each dimension.
Pick a schema for most analytics A star. Queries are simpler and usually faster, and Kimball recommends it.
Pick a snowflake schema instead For a big hierarchy that changes often, like product, category and department.
Trap A star repeats values like category names. Changing one means updating many rows.
Scaling, CAP & NoSQL split and copy data, and choose what to give up Partition a big table Split a big table on one server Partitioning. Queries still see one table, and the engine routes each row.
Split rows, like orders by date Horizontal partitioning, by range, list or hash of the partition key.
Split columns, or a database by feature Vertical partitioning splits a table's columns. Functional partitioning splits a database by feature.
Read only the partitions a query needs Partition pruning. It needs a filter on the partition key.
Remove a year of old orders fast Drop that year's partition instead of deleting rows.
Tip Partition at millions of rows, or when a table outgrows memory.
Trap A query that skips the partition key reads every partition. Too many partitions also slow planning.
Shard across servers Spread data across several servers Sharding. The app, middleware or a router like mongos sends each query to its shard.
Shard by ranges of the key Range sharding, like users A to M on one shard. Uneven data makes hot spots.
Spread rows evenly across shards Hash sharding. Range queries become hard.
Add a shard without moving most of the data Consistent hashing. Only part of the data moves.
Tip Directory sharding maps keys with a lookup service, which can become a bottleneck.
Trap Pick the shard key from your queries. Cross-shard joins and transactions are slow and complex.
Replication Send writes to one server and reads to many Single-leader replication. The primary is a single point of failure.
Accept writes on several servers Multi-leader replication must resolve conflicts. Leaderless, as in Cassandra, lets any replica take writes.
Choose whether a write waits for its replicas Synchronous is consistent but slower. Asynchronous is faster but eventually consistent.
Tip Older docs say master-slave. MySQL now says source and replica.
Trap An asynchronous replica lags behind. A read from it right after a write may miss that write.
CAP theorem Define C, A and P Every read gets the latest write or an error. Every working node answers. The system survives lost messages.
Choose what to give up during a network partition CP refuses some requests to stay consistent. AP answers, maybe with stale data.
Make reads see the latest write Use majority writes and linearizable reads in MongoDB, QUORUM in Cassandra, and ConsistentRead in DynamoDB.
Tip PACELC adds that with no partition, systems trade latency against consistency.
Trap Don't say "pick two of three". No distributed system can rule out partitions, so you choose C or A during one.
SQL or NoSQL Pick NoSQL For horizontal scaling, or a structure that changes too often for a fixed schema.
Pick a relational database For complex joins, and money or orders that need strong consistency.
Name the four NoSQL families Document like MongoDB, key-value like Redis, column-family like Cassandra, and graph like Neo4j.
Trap ACID no longer settles the choice. MongoDB supports multi-document transactions across collections and shards.
Pools, backups & security run the database safely in production Connection pooling Stop opening a connection per request Use a connection pool. It keeps connections open and lends them out again.
Size the pool Base it on the server's capacity, not the user count. A few dozen connections can serve thousands.
Tip HikariCP suggests twice the server's CPU cores, plus the disks that serve reads. Count zero disks when the active data is cached.
Trap Always return the connection to the pool, even when a query fails. Otherwise the pool runs out.
Backup and recovery Set the recovery targets RPO is the most data you can lose. RTO is the longest downtime you can accept.
Copy changes since the last backup Incremental. It is the smallest, but a restore needs the full backup and every incremental.
Copy changes since the last full backup Differential. A restore needs the full backup and the latest differential.
Restore to a point in time Take log backups too. SQL Server needs its full recovery model for that.
Tip Follow the 3-2-1 rule. Keep 3 copies, on 2 kinds of media, with 1 offsite.
Trap A backup you have never restored may not work. Test restores on a schedule.
Database security Give the app only the access it needs GRANT SELECT, INSERT, UPDATE ON app_db.orders TO 'app_user'@'localhost'; Least privilege, often through roles. The example is MySQL syntax.
Stop SQL injection Use parameterized queries. User input never runs as SQL.
Protect data on disk and on the network Encrypt it at rest and in transit, and rotate the keys.
Trap Test copies of production data leak too. Mask the data in non-production environments.
Nothing matches. Try a shorter word, or clear the search.
Practice 39 interview questions Full answers, from beginner to expert.
Found this useful? Pass it on.
Share on LinkedIn
Share on X
Copy link
This sheet stays free. With Pro , you can:
Every answer on this sheet. Read the full answer to all 39 questions it links, not just the 5 free ones.My sheet. The cards you star and the ones you miss in Quiz me, from every sheet, on one page to quiz and print.PDFs. Download this sheet and My sheet, laid out for A4, with every question a link to its answer.The rest of Pro. Every answer in the question library, and 1,000 AI credits a month.
