Bitcoin Core keeps block data as files in the file system and blocks indexes in a LevelDB database to make the search process of transactions and blocks faster. But Bitcoin Core also stores block headers in memory as well, doesn't it? If yes, what is the purpose of having both storage mechanisms together?
I think you are confusing what it means to store things "in memory". As you say, indeed, the block index DB and the chainstate DB are both levelDB databases stored on disk. However, while Bitcoin Core is running, parts (or all) of these databases are loaded into memory to speed things up. The "in memory" part isn't a separate database. So there is no redundancy/duplication.