The Serpent is a bit of an oddball, without much lore to it (much like the rest of the Constellations).
It has an unusual description, and that's without mentioning its appearance and role in Craglorn.
The general idea is this:
The Serpent is not fixed to any specific point of the heavens, as it is a constellation of "Unstars," somehow related to Molag Bal and a few other Daedric Princes. Instead it travels across the heavens attempting to overtake the other signs, when one of the Guardian signs isn't able to fight it off. Thus the Serpent does not appear at any specific time of year, but rather, it can appear in place of another sign.
"Those who are born under this sign are the most blessed and the most cursed" - can refer to a great many things, and we have no concrete examples outside of the Birthsign abilities as they appear in the games.
A quick note.
The Guardian signs are the Warrior, Mage, and Thief.
They have 3 Charges each, whom they protect from the Serpent.
The Warrior's Charges: The Lady, The Steed, The Lord.
The Mage's Charges: The Apprentice, The Atronach, The Ritual
The Thief's Charges: The Lover, The Shadow, The Tower
You can find some general information about TES astronomy here
it also includes links for further reading.
As for the Serpent Celestial.. We know nothing other than it supposedly is a mortal who mantled the Serpent or an aspect hereof.
Then using the chaos of the Planemeld, and Molly B's connection to the Serpent, it enhanced its own powers and overcame the 3 Guardian Celestials.