The CAP Theorem, a fundamental principle in distributed systems, outlines that it is impossible for a distributed data store to simultaneously guarantee more than two out of the following three properties: Consistency (C), Availability (A), and Partition Tolerance (P). Specifically, this theorem points to a trade-off between these properties in the event of a network failure, making it a cornerstone consideration when designing distributed systems.

Understanding CAP Theorem Components

Consistency guarantees that all nodes see the same data at the same time. A write operation in one part of the distributed system must be immediately visible to all other parts.

Availability ensures that the system continues to operate despite node or network failures. A request to the system should always result in a response, even if some data is not up-to-date.

Partition Tolerance refers to the system's ability to continue functioning in spite of network partitions that prevent communication between subsets of nodes.

RDBMS and CAP Theorem

Relational Database Management Systems (RDBMS) are designed with a strong focus on ensuring consistency and data integrity. Historically, they operate on single servers, but modern demands have pushed them towards more distributed architectures. Even so, when considering the CAP theorem, most traditional RDBMS focus on consistency and availability, often at the expense of partition tolerance.

Why Isn't RDBMS Partition Tolerant?

Partition tolerance in a distributed system involves the system's capability to continue functioning correctly even when there are communication breaks between nodes in the network. For RDBMS, which traditionally prioritize atomicity, consistency, isolation, and durability (ACID properties), maintaining transactional consistency across partitions is challenging.

When a network partition occurs, an RDBMS must choose between:

1. Limiting the operation to unaffected parts of the network (promoting consistency but sacrificing availability in partitioned areas).
2. Allowing operations to continue and accepting some level of data inconsistency until the partition is resolved (sacrificing consistency).

Most RDBMSs choose to maintain consistency within partitions, thus becoming unavailable in other partitions. This is because guaranteeing immediate consistency across partitions can be enormously complex and could potentially involve an intricate system of data synchronization and conflict resolution once the partition is resolved.

Why is RDBMS Available?

An RDBMS can be structured to maintain high availability within its operational boundaries considering no partitions. In environments without partitions, RDBMS can handle a large number of failures by re-routing requests or replicating data across different nodes within the same partition. Thus, in a non-partitioned network, an RDBMS generally delivers high availability.

However, here lies the nuanced difference: if the network partitions, the DBMS might not be accessible across the partitions, but within any single partition, the system remains available and consistent.

Examples and Usage Variations

Consider an RDBMS used for a banking system where transactions are critical and must be entirely consistent and reliable. In this case, ensuring that transactions are consistent in every node is crucial. If a network partition occurs during a transaction, it's preferable for the system to halt operations (become unavailable in part of the system) rather than risk processing transactions that might conflict with others happening on another side of the partition.

Summarizing Key Points

In conclusion, while RDBMS offers strong consistency and internal availability, it struggles with partition tolerance due to its intrinsic design trade-offs enforced by the CAP theorem. These limitations are crucial for system architects and database administrators to consider when designing and selecting data management solutions for applications depending on the priorities among consistency, availability, and partition tolerance.