CAP Theorem for Database Design | Dataford Interview Questions

Can you explain the CAP theorem and its implications for database design?

Use the supplied system and observation records to summarize behavior during network partitions. Classify each system based on whether it rejected requests, returned potentially stale responses, or showed both behaviors.

Output

One row per system, including systems without observations.
Return system_name, partition_events, unavailable_events, stale_reads, and observed_behavior.
Classify behavior as CP-oriented, AP-oriented, Mixed behavior, or No observed partition tradeoff.
Order by system_id ascending.

Schema

systems

Column	Type	Description
system_idPK	INT	Unique system identifier
system_name	VARCHAR(100)	System name

cap_observations

Column	Type	Description
observation_idPK	INT	Unique observation identifier
system_id	INT	Referenced system
partition_active	BOOLEAN	Whether a network partition was active
request_served	BOOLEAN	Whether the request received a response
response_consistent	BOOLEAN	Whether the response reflected the latest consistent state
observed_at	DATE	Observation date

Your question is CAP Theorem for Database Design. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem