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

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?

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 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?

Accepted Answer

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

Answer

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?

Accepted Answer

Atomicity, Consistency, Isolation, Durability

Answer

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?

Accepted Answer

Intentionally introducing redundancy into a database schema to improve read performance

Answer

Denormalization adds redundant data or combines tables to reduce the number of joins needed for frequently-executed read-heavy queries, trading storage for speed.

Back-End Development Practice Test

Back-End Development Practice Test

Back-End Development Database Design and Optimization