Minor: Move MemoryCatalog*Provider into a module, improve comments

[alamb]

Which issue does this PR close?

part of https://github.com/apache/datafusion/issues/11182

Rationale for this change

There is a distinction between catalog API (the traits) and the provided simple implementations (e.g. MemoryCatalogProvider*) that is not well reflected in the module structure now.

If we make the code structure better mirror the distinction, it will both be clearer and set us up for future simplifications

What changes are included in this PR?

1. Move MemoryCatalog*Provider into a module
2. Improve doc comments
3. Leave backwards compatible pub use

Are these changes tested?

With existing CI

Are there any user-facing changes?

Better docs, no functional change