The accursed Ravens proved that a good defense with a mediocre offense can get to and win the SB.
Your example of Marino's Dolphins is a good one of why the converse cannot.
My own beloved 2001 Patriots were quite frankly a below average offensive team. Yet their defense and special teams made up for it enough for them to win it all.