Does the alarm work when my phone is turned off?
No, if the phone is off, it can't do anything.  If it's in sleep mode where the screen is off and it's not in use then the alarm will still function as will other types of notifications.  I have never tried this specifically on a Galaxy S but I am fairly confident this is a universal rule.