database design

 
Imagine that you work for a data service company that specializes in data storage, data processing, and data streamlining for public libraries. The current implementation is a file based system with all data stored in a single spreadsheet. As the company expands the business to serve more libraries, it becomes obvious that the current model does not meet the ever increasing demand.
You have been assigned the task to design a new relational database to replace the current file based storage. It will be a single centralized database to store information needs for all libraries. Some key information to be stored includes data about customers, libraries, catalog, physical items, and check in/out transactions.
Additional general business background information is listed below:
a. Each library system operates independently. Each library system issues its own library card to its customers.
b. A customer can have multiple library cards, one for each library system. For example, a customer can have both Baltimore library card and Annapolis library card. Different library systems may issue cards with the same number.
c. A library has many branch locations. Customers visit a branch to check in/check out items.
d. Different libraries share generic catalog information. Currently there are two types specific catalog items – book and DVD.
e. Each branch stores and maintains physical items. General information about each physical item can be found by checking catalog. There could be multiple copies for the same title so each copy needs to be stored separately. A branch uses a copy number to identify a particular copy so that to keep track of information such as purchase date.