How did I not see this thread!
It looks like the main question was already answered, so there's not much point in me replying, but I can't resist because I'm a total dork and I love this stuff:
The NES communicates with the cartridge by performing various reads and writes from different addresses. An 'MMC' is an additional chip on the cartridge which redirects addresses to different places, allowing for more memory to be accessed. Since they are effectively in control of the read/write line and can determine what the NES 'sees' when it tries to read from the cartridge, they can do a bunch of other neat tricks too, as others have mentioned in this thread.
The term "mapper" is often used interchangeably with "MMC", but a mapper is more like a general description of the entire cartridge layout -- and not just
the MMC chip. For example, mapper 024 and 026 use the exact same MMC, but are different mappers because the cartridge has a very different layout.
As far as memory goes, I recently came to a conclusion. 256k games consist of 16 banks. With how bank swapping works, it appears that NES games could reach up to 4 megabyte, with 256 banks max. Is this correct, or could it even be expanded further to 2 bytes or more for 65536+ banks if the NES hardware was continued and remained relevant.
There's no limit. The NES has no idea what hardware is on the cartridge, and it doesn't care. As long as the cart gives the NES data when it tries to read, the NES is happy.
Whatever hardware you can attach to a cartridge is fair game. On the SNES, they even started adding additional processors on the carts.