OK, this is kind of complictaed, but its the best I can come up with.
You need a NES-on-a-chip based console. A real NES might work but
1.) Who wants to destroy the NES? and
2.) NES-on-a-chip based consoles are easier to modify electrically (no, really, being just a chip and the electric components).
Now, let's get the software out of the way first. You just want to play the SMB theme (or level 1 music) correct? Well then we'll need to convert the NSF into a bootable NES ROM. There are programs that do this, one being tossed around quite frequently is NSFulator (to which I cannot find a direct link.

), there are others as well. You can then supply a NSF from Zophar's Domain and turn it into a ROM which will play the song on powerup. Huzzah! You now just need to flash the EEPROM of your NES-on-a-chip based console, and congrats, you have the brains of a doorbell!
Well, the software at least. The hardware is a bit tricky. What you're going to need to do is connect speakers to the NES-on-a-chip console's A/V jacks so you can hear the doorbell, but that isnt the tricky part. You'll need to devise electric circuitry that turns the system on for 10 seconds. Why 10 seconds? That way the song is recognizable, and it turns off so you don't have to manually turn it off before you answer the door.
Now that is where I will leave you hanging unfortunately, because I haven't the slightest clue when it comes to circuit building. But hopefully this post can give you a good idea on how to start.
I'd love to see a success story from this idea, let us know if you decide to persue it! ^_^
Hehe, somehow I didn't think of actually emulating the theme song exactly as it sounds on the NES. All the stuff MegaKitsune said is neat, but I personally wouldn't go to all that trouble. If you want the "authentic NES sound," it'd be much easier to carefully record the actual SMB theme song playing on a NES and either
A) Play it back on a looped cassette tape, ala an old answering machine when the doorbell switch is hit
B) Play it back from some sort of CD player. A modern portable one with a AC power adapter would do the trick handily. Best audio quality for the price with this option. How you'd trigger it, however, I do not know. There may be CD players with remote control ports that you could interface to.
C) Play it back on a flash-based digital sound player. You can probably buy a kit that will store a certain length of digitized sound and play it back on command.
Those three solutions would be MUCH easier than what MK's suggesting.. but of course, if you want true ultra-nerd points, you should do what he said.
