"Rocky Horror" is being used as an adjective qualifying 'Picture Show'
The full sentence would be, 'The Rocky Horror Picture Show' - it is the picture show/movie about Rocky Horror..
This occurs frequently in the titles of televsion shows eg
The Benny Hill Show
The Catherine Tate Show
The Brady Bunch
The Ed Sullivan Show
The Tonight Show