Is this considered one transaction: Say I query a list of Opportunities, I update these Opportunities and run an 'Update' DML on the list. This then triggers some Apex and Flows. Is this all considered ONE transaction?
Yes. Without over-complicating things, a transaction is one Debug Log. This could include recursive updates via triggers, etc.
2) How is Atomicity guaranteed when using Database.Insert or Database.Update that allow for partial success?
The records that do not error out are atomically updated. This is useful for situations where you're updating records in bulk, and you want each record to be treated as if it were its own transaction.
For example, if you're updating a list of contacts in the Apex Data Loader, it would be inconvenient if a failure on one record resulted in an error in 199 contacts that are otherwise unrelated, but happened to be in the same transaction.
The allOrNone flag gives the developer the option to treat each record as a separate transaction, or not, without harming performance.
3) Is Salesforce fully ACID compliant?
At a basic level, yes, but it's possible to break ACID intentionally (and this can be a good thing). Aside from Platform Events, which can be dispatched even before a transaction commits, most actions can be rolled back as long as there's a failure.
By default, if a transaction ends with an error or exception, everything that happened is automatically rolled back. Developers can choose to bypass this, but it is an intentional choice that can help improve performance.
From a "purist" point of view, Salesforce is not ACID compliant. There are several ways to cause partial effects despite failure. However, these exceptions to ACID allow better system performance than if ACID was strictly enforced.