OK, let's see if I can clear this up a bit...
First, the Aurora was made for two reasons:
1) Make it possible to run a QL on a VGA monitor (since at that time monitors based on the TV standard were already getting scarce and expensive)
2) Add more resolutions and colors to the QL
The first part is done by making a new video controller from scratch, but the second is more complicated because more screen space and more colors require more RAM to hold the image data - and this has to be added externally. In total up to 256k of extra RAM must be added.
The problem here is that te maximum number of addresses available on the J1 expansion connector is 1M (1024k) and on a normal QL this holds everything, ROM, motherboard RAM, expansion RAM, any expansion boards such as floppy and IDE etc.
In other words, there is a shortage of available addresses.
The GC was the first widely available board to tackle this, together with adding a faster CPU. The way it does this is by using only those addresses on the J1 expansion connector that are connected with essential QL hardware on the motherboard - most of the stuff addressed on the GC (such as 2M of RAM) do not appear on the J1 connector at all.
In the case of the GC, only the ROM (initially), ROM slot, motherboard IO and screen RAM addresses are used, and in the case of the screen addresses, only for writing (the significance of this will become apparent later on).
While the original 68008 CPU can only has a total of 1M address range, the one on the GC has a 16M range, but only 2M was used, and none of the remaining 14M were available to the user, especially on the J1 connector, so this was kind of a missed opportunity on the GC, that was mode available on the SGC.
The SGC can also access the top 256k of addresses ($C0000..$FFFFF) on the J1 connector.
It is important to note that addresses on the J1 connector are not always the same as what the CPU on the SGC sees, in other words, there is a sort of 'address translation' from the CPU to external bus.
In the case of the SGC, it can access several addresses on the J1 conenctor, ROM and ROM slot, motherboard Io area, screen 0 and 1, and finally, what was originally specified as an IO expansion area, the top 256k, which appears on the J1 connector as $C0000..$FFFFF.
That being said, from the point of the SGC, this appears at $4C0000..$4FFFFF, hence this is where the Aurora video RAM can be seen by the SGC.
In order to also be compatible with the old screen areas at $20000 and $28000, part of the Aurora video RAM appears also at these addresses (these are the same from the point of the SGC as on the actual J1 connector). The Aurora actually does a similar thing to what the SGC does but in reverse, when accessed at he old QL screen areas, it re-maps this into it's video RAM so that the old scr0 accesses actually go to $4Cxxxx. This however only happens when mode 4 or 8 is selected, and only one way (old screen to new screen RAM), so it is used to test for Aurora presence in the driver - write a long word zero to $4C0000, then a long word with a key value to $20000, read long word from $4C000, if it is the key value, Aurora is present.
When Aurora is used with SGC, the whole 256k of it's video RAM can be accessed (on SGC this is $4C0000 to $4FFFFF) but the video circuits are limited to displaying data within the first 240k, by the programming of the SCAN EPROM on the Aurora.
The reason the last 16k is left unused is that once the Aurora is present, expansion by any other hardware would be limited to the same as on the GC, which is essentially the ROM port, so the last 16k is available for something like a QUBIDE. If it is installed (and it's jumpers set to $FC000 - to the SGC this will look like $CFC000), it will disable the last 16k of the Aurora video RAM and take those addresses for itself.
One side effect of the last 16k being available as RAM if no expansion card has taken that space, is that you could load an expansion ROM image into those addresses, and after resetting the QL, it will still remain there and will be recognized as a ROM
The way the Aurora screen RAM is displayed as an image on the screen depends on the mode chosen, and there are 4 in total. In the standard mode 4 or 8 the pixels map to bits in the video RAM the same as on the original QL except the size of the bitmap is 1024x1024 in mode 4, and 512x1024 in mode 8. In the 16 color mode (which was unfortunately never used), the bitmap is 1024x512, and in the 256 color mode it's 512x512. This always remains that way as far as the hardware capability goes. On top of that, the chosen resolution and the last 16k being reserved limit what is actually displayed. If the chosen resolution is smaller than the bitmap, then the requested resolution is positioned in the top lefthand corner of the bitmap. If the chosen resolution is larger than the bitmap, the resolution will be limited to the size of the bitmap, in either x or y direction as applicable. Also, because the last 16k of video RAM is reserved, the maximum usable size of the bitmaps is limited to 960 rows in modes 4 and 8, and 480 rows in modes 16 and 256.
Now back to how the original QL screen memory area is handled:
Aurora does not support the second screen option (scr1). This is because writes to the first screen (scr0) are remapped into the Aurora RAM so that it appears as a 512x256 (mode 4) or 256x256 (mode 8) pixel area in the top lefthand corner of the larger bitmap, but only if mode 4 or mode 8 is used. The problem is that under normal operation (no scr1) the RAM addresses used for scr1 are used as normal RAM, including a bunch of system data structures, so remapping writes into Aurora video RAM would corrupt the screen in any of the higher resolution or color modes.
It should be noted that the SGC does a lot of it's own tricks when the old scr0 or scr1 are accessed, in particular only writes to those addresses are done to the J1 connector so that the data ends up in the original motherboard RAM, for the 8301 ULA to display it on the screen. At the same time it is written to the same addresses in the SGC on-board RAM. It is only ever read (and MUCH faster) from the SGC on-board RAM. In fact, there is an option to switch off writing to scr1 if it is not used as it speeds up access to system data structures at those addresses, as the SGC does not have to wait for the slow write to old motherboard RAM to complete.
There are other tricks:
SGC does reads and writes on J1 to the motherboard IO area at addresses $18000..$1BFFF, only reads to the ROM slot addresses at $C000..$FFFF, and the entire ROM area at $0000..$FFFF (as appears on J1) appears once more as $400000..$40FFFF to the SGC but read-only. That last one is a damn shame, in fact the hardware would have been simpler if the full bottom 256k on J1 appeared at $40000..$43FFFF to the SGC with full read and write, as many interesting things could have been done, including being able to use re-writable flash ROMs instead of the original QL ROM or EPROm chips. Unfortunately, since the ROM can only be accessed for read, and there is no write function, there can be no writes to a flash chip that could be used instead of an EPROM.