Back-End Development Database Design and Optimization 3

Question 1

What is the difference between SQL and NoSQL databases?

Accepted Answer

SQL databases use tables and schemas with ACID guarantees; NoSQL databases use flexible data models optimized for scale and specific access patterns

Answer

SQL databases use structured schemas and relational tables with strong ACID properties, while NoSQL databases offer flexible schemas and horizontal scalability with various data models like document, key-value, graph, and column-family.

Question 2

What is eventual consistency in distributed databases?

Accepted Answer

A model where data updates propagate to all nodes over time, and replicas are temporarily out of sync

Answer

Eventual consistency means that given enough time without new updates, all replicas of a distributed database will converge to the same value, even though short-term inconsistencies may exist.

Question 3

What is a stored procedure in a relational database?

Accepted Answer

A precompiled set of SQL statements stored in the database that can be executed with a single call

Answer

A stored procedure is a precompiled SQL routine stored in the database that encapsulates business logic, reduces network round-trips, and can accept parameters and return results.

Question 4

What is database replication and what problem does it solve?

Accepted Answer

Synchronizing data across multiple database instances to improve availability, read scalability, and fault tolerance

Answer

Replication copies data from a primary database to one or more replicas, enabling read scaling (distributing queries), high availability, and disaster recovery.

Question 5

What is query caching and when should it be used?

Accepted Answer

Storing the results of expensive or frequently repeated queries in memory to avoid re-executing them against the database

Answer

Query caching (using tools like Redis or Memcached) stores query results in fast in-memory storage, reducing database load for read-heavy, infrequently-changing data.

Back-End Development Practice Test

Back-End Development Practice Test

Back-End Development Database Design and Optimization 3