This old chestnut, probably the most common scenario of all for students to undertake as a project within databases and yet in the vast majority of schools it will simply be taught incorrectly with just 3 tables. You need 5 tables at the core of it to make it actually "work" and this series of screencasts and footage of my and my family badly acting out how a DVD hiring shop works endeavors to explain how to do this project properly.
Note the videos for this project were made a fair few years ago and I use Access 2003 to build the database, however I have not re-done them as there is not really any difference expect the interface design in Access 2007/10. The techniques are basically the same.