As for the "realistic dungeon with a purpose" thing, check out Incursion for an example of that gone horribly wrong. It's a really nice try and it might get better...but right now, it has some vague concepts of "regions you might find in a dungeon", puts them in big squares, and sticks them together. I mean individually they look awesome, since each area type has its own level generator...big caverns, twisty kobold warrens, magical research laboratories...but they just don't make any remote kind of sense as a whole, so it just looks weird. Okay, why do I need to go through the Room of Elements and cross lava and fog in order to get to the bedrooms?!
Yeh, I thought of incursion when I was pondering on how to do this. I liked the detailed areas, but they didn't form a nice whole. A castle setting would solve that. A large enough castle would surely have multiple kitchens, barracks, smaller libraries and other rooms strewn about. Especially if it's a castle inhabited by a whole community. (of monsters, but still) By keeping the frequency and types of rooms grouped together logically, the player would find himself in an interesting and believable environment.
On the lower levels you'd find tons of guard rooms, armouries, kitchens, pantries, small chapels, sleeping quarters and so on, with some more expensive rooms grouped around the Throne room or something.
Higher up, you'd start to encounter small libraries, alchemy labs, bedrooms, storage rooms, dining rooms and so on. There'd be less guards, but they'd be better trained and there'd always be some recruits rushing up the stairs as backup.
Even higher up you'd have ballrooms, great dining rooms, master bedrooms, Elite Guards, museums, arboretums, aviaries, larger chapels, great libraries,... This is where more powerful monsters will hang out, no just guards. Libraries mean spellcasters, arboretums and aviaries mean ents and giant skeletal eagles...
Not to mention the towers where outside flying monsters would rush in through the windows to get you, with guards coming up and down the stairs. And a boss at the top.
The other way down you'd have the dungeons filled with rats, bats and undead or rampaging tortured monsters. And experimental torture chambers with lethal traps and stuff.
Further down would just be caves with increasing difficulty all the way down, in case you want a real challenge.
A few of the rooms would be just empty or collapsed or turned into a lair of sorts. And there'd be secret passages all over to make up for locked hallways doors.