When designing a system that requires complex JOIN operations across multiple tables with strict ACID compliance, which database type would generally be the preferred choice and why?

A SQL relational database, for its structured data model and support for complex queries and transactions.

A NoSQL document database, for its flexible schema and horizontal scalability.

A NoSQL wide-column store, for its ability to handle large datasets efficiently.

A NoSQL key-value store, for its simplicity and high performance for simple lookups.

Consider a social media platform aiming to store user profiles, posts, and follower relationships. If the primary access pattern involves retrieving a user's posts and their immediate follower list with high read throughput, which NoSQL database model is likely to be the most suitable and why?

A NoSQL document database, to store user profiles and their associated posts and follower lists in a denormalized manner.

A SQL relational database, due to its ability to handle structured data and relationships.

A NoSQL graph database, to efficiently model and query the complex follower relationships.

A NoSQL key-value store, for its extremely fast retrieval of individual posts based on post ID.

When dealing with rapidly changing data schemas, such as an IoT platform collecting sensor data with varying attributes over time, which database paradigm generally offers greater agility during development and deployment?

NoSQL document databases, due to their flexible and schema-less nature.

SQL relational databases, due to their strict schema enforcement.

SQL databases employing JSON data types, as a compromise.

NoSQL graph databases, as they are optimized for relationships.

A system requires extreme horizontal scalability to handle billions of active users and petabytes of data, where occasional data inconsistencies are acceptable for non-critical data. Which of the following best describes the typical trade-offs made by systems prioritizing these aspects?

Prioritizing availability and partition tolerance over strong consistency (CAP theorem).

Prioritizing strong consistency (ACID) over availability and partition tolerance (CAP theorem).

Prioritizing relational integrity over schema flexibility.

Prioritizing complex query capabilities over raw read/write performance.

For an application that needs to perform real-time analytics on user clickstream data, aggregating events by user ID and timestamp ranges, which database would be most efficient?

A NoSQL wide-column store (like Cassandra or HBase), for its ability to efficiently query large datasets by row key and column ranges.

A NoSQL key-value store, for simple data retrieval.

A SQL relational database, for its structured query language.

A NoSQL graph database, for its focus on relationships.

When migrating a legacy application that heavily relies on complex stored procedures and referential integrity enforced at the database level, what is a primary consideration when evaluating a move to a NoSQL database?

The need to redesign application logic to handle data relationships and transactions outside the database.

The ability of the NoSQL database to directly execute SQL stored procedures.

The ease of migrating existing SQL schema directly into a NoSQL document structure.

The improved performance for simple key-value lookups, which is the primary benefit.

A content management system needs to store articles, user comments, and tags. Articles can have many tags, and users can comment on articles. Which database design approach would best balance performance for retrieving articles with their tags and comments, while minimizing read redundancy?

A NoSQL document database where each article document embeds its tags and comments.

A SQL relational database with tables for articles, tags, and comments, linked by foreign keys.

A NoSQL key-value store where the key is the article ID and the value is the entire article content.

A NoSQL graph database to represent articles, tags, and comments as nodes and relationships.

Which of the following best describes the 'eventual consistency' model often found in distributed NoSQL databases?

Data updates propagate through the system over time, and all replicas will eventually be consistent, but there might be a delay.

All replicas are updated simultaneously, ensuring immediate consistency across the system.

Data is guaranteed to be consistent only if the network is reliable and there are no partitions.

Read operations always return the most recent write, even if it means sacrificing availability during network issues.

Consider the CAP theorem. If a system prioritizes being always available and can tolerate network partitions, what is the consequence for its consistency guarantees?

It must offer eventual consistency or weaker consistency.

It must offer strong consistency.

It can offer strong consistency if it scales horizontally.

Consistency is not a concern for such systems.

A system needs to store user sessions, where each session is identified by a unique session ID. The primary operation is to retrieve or update the session data based on this ID. High throughput and low latency for these operations are critical. Which database type is generally best suited?

A NoSQL key-value store, for its simplicity and high performance for direct lookups.

A SQL relational database, for its transaction support.

A NoSQL graph database, for its relationship modeling.

A NoSQL wide-column store, for its scalability.

When deciding between SQL and NoSQL for a new project, what is a significant factor that might push the decision towards SQL?

The requirement for ACID transactions across multiple operations.

The need for a flexible, rapidly evolving schema.

The necessity to scale writes to millions of operations per second without complex sharding.

The desire to avoid predefined data structures.

What is a primary advantage of using a NoSQL document database for storing product catalogs where products can have varying attributes (e.g., electronics have different specs than clothing)?

It allows each product document to have a unique set of fields, accommodating diverse attributes without schema changes.

It enforces a rigid schema, ensuring all products have the same set of attributes.

It provides built-in support for complex SQL JOINs to link related product information.

It guarantees ACID transactions for all catalog updates.

SQL vs NoSQL in system design

SQL vs NoSQL in system design - Test Paper