QuestionQuestion

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.
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).

Below is a list of your specific tasks:

Step #1: Analyze the current model
1. What are some of the major issues with the current file based storage model?
2. Are there data anomalies with the current solution? Can you provide specific examples of             INSERT/UPDATE/DELETE anomaly?
3. If you design your database strictly based on the current model (meaning one table to store all information), would it be considered as normalized? Would it be in 1NF, 2NF, and 3NF?
4. If not, describe and illustrate the process of normalizing it to 3NF. Identify functional dependencies and create dependency diagram before each conversion.
5. Show the final table structure after normalization and make sure it is in 3NF.

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

Step #1: Analyze the current model

1. What are some of the major issues with the current file based storage model?

a) Many cells are repeated such as

i. CUSTOMER_UNIQUE_IDENTIFIER
ii. CUSTOMER_FIRSTNAME
iii. CUSTOMER_LASTNAME
iv. CUSTOMER_STREET
v. CUSTOMER_CITY
vi. CUSTOMER_STATE
vii. CUSTOMER_ZIP

b) Many records contain null fields

2. Are there data anomalies with the current solution? Can you provide specific examples of INSERT/UPDATE/DELETE anomaly?

a) Yes there are

b) Examples
         i. INSERT
          1. Insert into Sheet1 values ('1','Feb/02/2017'', 'Mar/02/2017', 'Feb/28/2017', '1', 'Christian', 'Walker', '96 Clark Drive', 'Hicksvile', 'NY', '11801', '1', '16777216', '1234', 'Dec/31/2019', 'New York Public Library',...

By purchasing this solution you'll be able to access the following files:
Solution.docx.

$38.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Computer Science - Other Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats