Back-End Development Database Design and Optimization 1 — Questions and Answers
Question 1: What is database normalization and what is its primary goal?
- Compressing the database to save disk space
- Organizing data to reduce redundancy and improve data integrity (Correct answer)
- Encrypting database records
- Indexing all columns for faster queries
Correct answer: Organizing data to reduce redundancy and improve data integrity
Normalization is the process of structuring a database schema to minimize data redundancy and eliminate anomalies during insert, update, and delete operations.
Question 2: What is the difference between a primary key and a foreign key?
- A primary key encrypts rows; a foreign key decrypts them
- A primary key uniquely identifies each row in a table; a foreign key references the primary key in another table (Correct answer)
- A primary key is required; a foreign key is optional and unique
- They are the same thing with different names
Correct answer: A primary key uniquely identifies each row in a table; a foreign key references the primary key in another table
A primary key uniquely identifies a record within its own table, while a foreign key establishes a link to the primary key of a related table to enforce referential integrity.
Question 3: What is a database index and how does it improve performance?
- A backup copy of the database for disaster recovery
- A data structure that allows the database to find rows faster without scanning every row (Correct answer)
- A constraint that enforces column uniqueness
- A schema diagram showing table relationships
Correct answer: A data structure that allows the database to find rows faster without scanning every row
An index is an auxiliary data structure (typically a B-tree) that maps column values to row locations, enabling the database engine to locate data without a full table scan.
Question 4: What does ACID stand for in database transactions?
- Atomicity, Consistency, Isolation, Durability (Correct answer)
- Access, Control, Integrity, Distribution
- Automation, Concurrency, Indexing, Durability
- Atomicity, Caching, Integrity, Durability
Correct answer: Atomicity, Consistency, Isolation, Durability
ACID properties guarantee that database transactions are processed reliably: Atomicity (all-or-nothing), Consistency (valid state), Isolation (concurrent transactions don't interfere), Durability (committed data persists).
Question 5: What is denormalization and when is it used?
- Removing all indexes to save space
- Intentionally introducing redundancy into a database schema to improve read performance (Correct answer)
- Splitting a large table into smaller ones
- Converting a relational database to a document store
Correct answer: Intentionally introducing redundancy into a database schema to improve read performance
Denormalization adds redundant data or combines tables to reduce the number of joins needed for frequently-executed read-heavy queries, trading storage for speed.
Question 6: What is an N+1 query problem in back-end development?
- A query that returns one extra row than expected
- A performance issue where fetching N records triggers N additional queries instead of using a join or batch load (Correct answer)
- A database schema with N+1 tables
- An off-by-one error in SQL LIMIT clauses
Correct answer: A performance issue where fetching N records triggers N additional queries instead of using a join or batch load
The N+1 problem occurs when code fetches a list of N items and then executes a separate query for each item, resulting in N+1 total queries instead of one efficient joined query.
Back-End Development Database Design and Optimization 1
What is database normalization and what is its primary goal?