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 evident that the current model does not meet the ever-increasing demand.

You have been assigned the task of designing a new ORACLE 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 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 a Baltimore library card and an 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 the 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.

You were provided with a sample data stored in the current spreadsheet format as attached. Please be aware that it is possible that a customer has never checked out any item (as shown line 23), and it is possible that an item has never been checked out yet (as shown line 24 through 67).