CAP Theorem in System Design states that in a distributed system, you need to choose between consistency (C) or availability (A) when faced with a network partition (P).
In non-distributed systems, CAP Theorem does not apply as you can have both consistency and availability with one database.
Network partitions are inevitable in distributed systems and can lead to either prioritizing consistency (CP) or availability (AP) in system design.
System design examples like Social Media Feed and Flight Booking demonstrate how CAP Theorem is used to make decisions based on prioritizing either availability or consistency.