The Elder Scrolls IV: Oblivion Construction Set is designed for the player to edit and create new content in The Elder Scrolls IV: Oblivion. The CS (Construction Set) can both be a basic and advanced tool, depending on what you plan to do. With it, you can do anything, from creating your own dimension, dungeon, or even city to adding a new NPC or house. You can totally revamp the game, with or without help. If you have the computer editing know-how, then feel free to create a new race, new bodies (such as body adjusters for fitness, bust, legs, height etc), classes and Birthsigns. Make the Imperial City become more modern, end a city such as Anvil, recreate Morrowind - you could even change the Main Quest so as it suits you!
One thing you must be very careful about is the use of a dirty mod. This is a mod which conflicts with another mod. (Obviously, if you don't plan on downloading other mods, or uploading your own, this shouldn't be a problem) For example, if a mod has nudged a rock or torch accidentally, or even on purpose, and a second mod has moved the rock or torch in a different way, these mods will conflict, and the modded game will not work. This is also very important to remember when making and uploading mods for others to use.
One good example of the powers of the construction set is the mod, called Nehrim - at Fate's Edge. This particular mod has been in development for four years and completely revamps the game. This is a fan-developed game with the aspiration to be able to compete with full-grown commercial RPGs. With an estimated playing time of 50 hours, over 60 Quests and thoroughly synchronized dialogues by professional voice actors, new weapons, armor, spells, races, models, video-sequences, specially composed new music and much more, this does not seem that far-fetched.
The CS has even got it's own wiki, in which their many tutorials can tell you everything you need to know.