It obviously doesn't connect with any sort of database. How is this even possible?
Thinking of it as "password protection" slightly misrepresents the actual situation.
What happens when you password-protect a zip file is that the archive is encrypted using a symmetric algorithm (same key to encrypt and decrypt) using the password as the key.
The unzipper program "checks" whether the key is correct the same way I check whether the key to my front door is correct: If it opens the lock, it was the correct key.
So in this case the unzipper attempts to decrypt the data using the password you provide, and if the output is a properly structured archive, it was the correct password.
(I'm skipping the whole cryptography debate WRT collisions and possible duplicate keys for now; this is about how the concept works in theory rather than a specific implementation that may or may not have flaws)
EDIT: As user MobyDisk points out in comments, in the case of Zip specifically, the structure and the file tree are not encrypted, just the files themselves, as well as checksums for each file. If the password you use decrypts the file, and the decrypted checksum matches, you had the right password.
It depends on the specific zip crypto algorithm.
For example, the original ZIP specification used the password to initialize a set of three 32-bit decryption keys. Then the ZIP header (12 random bytes placed at the beginning) was decrypted and then:
"After the header is decrypted, the last 1 or 2 bytes in Buffer SHOULD be the high-order word/byte of the CRC for the file being decrypted...This can be used to test if the password supplied is correct or not."
(ZIP Spec, section 6.1)