Your local library is going to start renting vinyl record albums because many of its customers are interested in analog music. The library has asked you to model the data requirements for this new offering. You have learned the following:
As with its book collection, the library may own one or many copies of each album in its vinyl record collection.
The library will track basic information about each customer who rents an album, such as name, home address, phone number, and credit card information (type of card, name of card holder, credit card number, expiration date). The credit card information is required to pay the rental fee (and, if applicable, late return fee), and to cover costs incurred if a copy of an album is damaged or not returned.
The same copy of an album may, over time, be rented to many customers. Each time a copy of an album is rented, the library will record which employee processed the rental, and assign a return due date that is 14 calendar days after the rental date. The library also wishes to track the actual return date and which employee processed the return (because the albums are fragile, they have to be returned in person). All rental information will be kept indefinitely so the library can measure activity and trends.
A customer may have multiple simultaneous active rentals of album copies. Over time, a customer could potentially rent the same album copy several times.
The library will charge the same rental fee regardless of the album, artist, or the condition of the album copy. The library may charge variable rental fees in the future, but they have told you not to worry about this now. But they do wish to track the current condition of each album copy.
Based on this description, draw an entity-relationship diagram in third normal form, including relationship names, primary keys, foreign keys, and other attributes, that will support the system as described. List any assumptions necessary to complete a reasonable diagram.