Performance: index · query plan · seq scan · explain analyze · covering index · composite index
Scale: sharding · replication · partitioning · read replica · connection pool · vertical vs. horizontal scaling
0 / 10 completed
1 / 10
In database terminology, what is an index?
A database index is a separate data structure (typically a B-tree) that maps column values to row locations, allowing the database to find matching rows in O(log n) instead of scanning all rows O(n). Analogy: a book index vs. reading every page. Trade-off: indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE must also update the index). Types: primary index (unique, usually the primary key), composite index (multiple columns), covering index (includes all columns the query needs — avoids fetching the row at all), full-text index (for text search).
2 / 10
What does ACID stand for in the context of database transactions?
ACID stands for: Atomicity (a transaction either fully completes or fully rolls back — no partial commits), Consistency (the database moves from one valid state to another — all constraints remain satisfied), Isolation (concurrent transactions behave as if they ran sequentially — no dirty reads), Durability (once committed, data survives crashes — written to disk). ACID is guaranteed by most relational databases (PostgreSQL, MySQL, SQL Server). NoSQL databases often relax ACID in favour of availability and scalability, following the BASE model (Basically Available, Soft state, Eventually consistent).
3 / 10
What is database normalization?
Normalization organizes a relational database to minimize redundancy and dependency by decomposing tables into smaller, related ones. Normal forms: 1NF — atomic values, no repeating groups; 2NF — no partial dependencies on a composite key; 3NF — no transitive dependencies. Example: storing a customer's city name in every order row is denormalized — normalization moves city to a separate customers table. But normalization isn't always the goal: denormalization (intentional redundancy) is used for read performance in analytics and data warehouses.
4 / 10
Complete with the correct database term: "The query was taking 8 seconds because it was doing a full table _____ on 50 million rows — adding an index on the user_id column dropped it to 12ms."
A full table scan (or sequential scan) means the database reads every row in the table to find matching results — O(n). This is the worst case for query performance on large tables. An index scan uses the index to jump directly to matching rows — O(log n). EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) show the query plan and tell you whether an index is being used. Key vocabulary for query performance discussions: query plan, cost estimate, seq scan (sequential scan), index scan, bitmap index scan, nested loop join.
5 / 10
What is database sharding?
Sharding is horizontal partitioning: splitting data across multiple database servers (shards), each responsible for a range of records. Example: users with IDs 1–1M on shard 1, 1M–2M on shard 2. Each shard handles both reads and writes for its partition. Compare: replication (all shards have a copy of the same data — for read scaling and failover) vs. sharding (each shard has a unique subset — for write scaling). Sharding adds significant complexity: cross-shard queries, re-sharding when load grows, shard key selection. Famous examples: Cassandra, MongoDB, Vitess (MySQL sharding), CockroachDB.
6 / 10
Code Review Comment: 'This query is incredibly slow! It's scanning the entire `orders` table. We need to optimize this.' What does the reviewer likely mean when referring to 'scanning the entire table'?
A full table scan means the database engine is reading every single row in the `orders` table to find the data it needs. This is extremely slow for large tables because it doesn't use an index to quickly locate specific records. The reviewer is highlighting a fundamental performance issue – the query isn't leveraging indexing effectively. Options C and D are possible contributing factors but don't directly describe the 'scanning' behavior.
7 / 10
Slack Message: 'Hey team, I'm seeing a lot of timeouts when running our API endpoint for user profile retrieval. The database is returning NULL values for several key fields.' What's the *most* likely cause?
The message describes 'timeouts' and 'NULL values,' strongly suggesting an issue with database connectivity. A depleted connection pool prevents new connections, causing timeouts when attempting to access the database. While options B and D could be *contributing* factors (if the schema is wrong or data is missing), they don't directly explain the core symptom – a lack of available connections to the database server. The question focuses on the immediate problem described.
8 / 10
PR Description: 'Implemented a new `INDEX` constraint on the `product_name` column in the `products` table. This should significantly improve query performance when searching for products by name.' What is the *primary* purpose of this change?
An index is a data structure that dramatically speeds up database queries. By creating an index on the `product_name` column, the database can quickly locate rows matching that name without scanning the entire table. This significantly improves search performance – the core benefit of indexing. Options A, C, and D are related to other database concepts (data consistency, uniqueness constraints, compression) but not the fundamental function of an index.
9 / 10
Standup Update: 'We're experiencing performance issues with our reporting query that joins data from three different tables. The query is taking over 30 minutes to run and we suspect a lack of appropriate indexing.' The query was taking 8 seconds because it was doing a full table _____ on the `customers` table — adding an index on the `customer_id` column dropped it to 12ms.
The phrase 'full table scan' describes a database operation where the entire table is examined, row by row, to find the data needed for a query. A 'cluster' index is typically used in conjunction with a full table scan to speed up this process. 'Partitioning', 'replicate' and 'scan' are incorrect terms here; a full table *scan* is the problem being solved.
10 / 10
API Response (Partial): `{"status": "success", "data": {"shards": ["shard1", "shard2", "shard3"]}}`. What does the 'shards' array likely represent in this context?
The term 'sharding' involves dividing a large dataset into smaller parts (shards) which are then distributed across multiple servers or databases. This improves query performance and overall system scalability. The response indicates that the data is being split into separate instances for better resource management – reflecting this concept.
SQL Joins— useful for Database queries (Backend Developer)
Backend— useful for Backend fundamentals (Full-Stack Developer)
Frequently Asked Questions
What does the "Database Vocabulary" vocabulary exercise cover?
This exercise tests real IT vocabulary related to database vocabulary through 10 multiple-choice questions, each built from realistic workplace sentences rather than abstract definitions.
Is this vocabulary exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is completely free — no account, sign-up, or payment required.
How many questions does this exercise have?
This exercise has 10 questions. Each one shows a real-world sentence or scenario with multiple-choice options and an explanation once you answer.
What happens after I answer a question?
You'll see immediate feedback showing whether your answer was correct, along with a short explanation of why — then a button to move to the next question, and a full results screen at the end.
Can I retry the exercise if I get questions wrong?
Yes. Once you reach the results screen, click "Try again" to reset your answers and go through the exercise from the start as many times as you like.
Do I need to create an account to take this exercise?
No account is needed. Your answers are scored in your browser during the session — nothing is saved to a server, so you can jump straight in.
Is my progress saved if I leave the page?
No — progress within an exercise resets if you navigate away or reload. Each exercise is short enough to complete in a few minutes in one sitting.
Are these vocabulary exercises connected to other topics?
Yes — this module shares real-world context with 3 other vocabulary modules. See "Related vocabulary" below to keep building a connected skill set.
How is this different from reading a glossary or blog article?
Exercises like this one are active recall drills — you have to choose the correct term or phrasing yourself, which builds retention faster than passively reading a definition.
Where can I find more vocabulary exercises?
Browse the full Vocabulary exercises hub for hundreds of modules covering Agile, DevOps, security, databases, architecture, and more — organised by IT role and skill.