Northshore Community Center operates a video lending library, and wants to use one its PC computers for electronic instead of manual record-keeping for the video-lending operation. Some of the stock of videos has been donated by various patrons, and other portions purchased out of the community center's general fund.
The video library has a large number of movies, some of which have the same title (often because of "remakes"). For each movie, you want to keep track of its name, the date it was first released, its primary language, and its category (such as "Comedy", "Action", "Documentary", etc.).
The movies are in various formats, including Blu-Ray, DVD, and even VHS for some older, rarer titles; sometimes you have multiple copies of the same movie in the same format (for very popular films). Each copy is identified by a unique barcode number (which you can easily read by attaching an inexpensive USB barcode scanner to the PC). For each copy, you keep track of the date you acquired it, whether it was obtained used or new, and whether it was donated or purchased.
Each client of the community center has a registration card with a unique account number.
For each client, you want to keep first name, last name, date of birth, address information,
and phone number. Suppose that you have access to a table giving the city
name and state for every zip code in the community center's service area.
Part 1. Initially, suppose we are not interested in tracking our clients' past video borrowing history; we simply wish to know which videos are currently in the stock, which ones are currently on loan, when they were loaned, and who has each loaned video. Design a database using only one-to-many relationships.
Part 2. Suppose we change our minds, and want to keep track of past history, including when videos were loaned and when they were returned. We would like the database to be able to tell us all the past loans on any movie copy, and all the past loans for any client. How would we change the design of the database?