Effect_ArrowFire is the name of the node in the nif file, EFFECT_ARCHERARROW the name of the effect defined in the Civ4EffectInfos.xml file. I have written an tutorial about this some time ago, maybe it will help you further:
http://forums.civfanatics.com/showthread.php?t=435755
And while we are at it, this is a very good collection of helpful tutorials for NifSkope as a whole:
http://forums.civfanatics.com/showthread.php?t=305606
I do not know if the permanent auras are mentioned somewhere in there, but in fact they are rather easy to add. For example, for the scientist all that is necessary to create the aura effect are marked red here:
The ATTACHABLE_HIGHLIGHT_SCIENTIST is defined in the CIV4AttachableInfos.xml.
There are also other ways to attach things like fire, like in this example for a torch fire:
There is however a lot of stuff going on in there and I am not too sure myself how it works, but at least it does work.
In the second image you can see that the parent NiNode in question is named "fx_fire_torch.nif". This however does not imply that there is an actual fx_fire_torch.nif file needed in the folder (or anywhere else), it is just the name of the node. As far as I know no nif files needs any other nif file to work, but the animation might (for example the arrow fired by the archer is a separated nif).
For the dds files, if it is defined without any file structure (only "Archer_128.dds" for example) than it will automatically search in the same folder as the nif file for the dds file. If a folder structure is defined, than it will follow this structure. However, there is often also a "shared" folder in the art folder. The dds files in this folder will also be used if no folder structure is defined.