Back-End Development Database Design and Optimization

Question 1

What is database normalization and what is its primary goal?

Accepted Answer

Organizing data to reduce redundancy and improve data integrity

Answer

Compressing the database to save disk space

Answer

Encrypting database records

Answer

Indexing all columns for faster queries

Question 2

What is the difference between a primary key and a foreign key?

Accepted Answer

A primary key uniquely identifies each row in a table; a foreign key references the primary key in another table

Answer

A primary key encrypts rows; a foreign key decrypts them

Answer

A primary key is required; a foreign key is optional and unique

Answer

They are the same thing with different names

Question 3

What is a database index and how does it improve performance?

Accepted Answer

A data structure that allows the database to find rows faster without scanning every row

Answer

A backup copy of the database for disaster recovery

Answer

A constraint that enforces column uniqueness

Answer

A schema diagram showing table relationships

Question 4

What does ACID stand for in database transactions?

Accepted Answer

Atomicity, Consistency, Isolation, Durability

Answer

Access, Control, Integrity, Distribution

Answer

Automation, Concurrency, Indexing, Durability

Answer

Atomicity, Caching, Integrity, Durability

Question 5

What is denormalization and when is it used?

Accepted Answer

Intentionally introducing redundancy into a database schema to improve read performance

Answer

Removing all indexes to save space

Answer

Splitting a large table into smaller ones

Answer

Converting a relational database to a document store

Question 6

What is an N+1 query problem in back-end development?

Accepted Answer

A performance issue where fetching N records triggers N additional queries instead of using a join or batch load

Answer

A query that returns one extra row than expected

Answer

A database schema with N+1 tables

Answer

An off-by-one error in SQL LIMIT clauses