In 2007, Bob and Jane Martin opened Stories on CD Company, Inc.,
a family-owned business that sells audio CDs of classic and best-selling children's stories. The Company has a number of distributors from whom they purchase the CDs for resale.

Roughly 30% of total sales occur within their single retail store; the remaining 70% are through mail order. Sales are particularly strong during the summer vacation months and Christmas shopping season.

When the business opened, Ted created a simple computerized database to handle customer orders and sales. A data form is filled out every time a customer places an order, and includes the following:

DATA ENTRY FORM

Customer number ____________	Order date ___________
First name _________________
Last name __________________
Street _____________________
City, State, Zip ___________ ____________________________
Phone: ( )__________
CD number ____________
CD Title ___________________ ____________________________
Price $________	CD shipped? Yes / No

As the business has grown, so too has the Martin’s realization that their simple database has become something of a liability. The database is filled with duplicate data, and deleting unwanted data is nearly impossible because good data is deleted along with it.

Stories on CD, Inc. has hired you to design a relational database to resolve these and other problems that plague the database.

During the course of your interview with the Martin’s about their business, several additional things come to light that may affect how you will design the database:

1. The Company offers its customers a purchasing incentive: all CDs are always discounted 40% from the retail price.

2. The Company likes to store the names of “potential” customers, even if they’ve never done business with them.

3. All information about current distributors is kept on Rolodex cards. The company would like the data to be included in the design.

4. Three times each year, the Company sends out 5–6 page brochures to advertise their top-selling and latest-release CDs, including general descriptions of each CD
(primarily the names of the stories).

5. The Company does not accept customer orders outside of the U.S.

6. The Company does not use distributors outside of the U.S.

7. A customer may buy 1 unit of 1 CD title. A customer may also buy multiple units of 1 CD title in one transaction. A customer also may buy multiple units of multiple CD titles in a transaction.

8. A CD title can be bought by a customer. A different customer can also buy the same CD title as the previous customer has bought.

Answer rating: 100% (QA)

In its normalized form a relational database can assist in overcoming the problem of duplicate data and eliminating valuable data while removing dupli... View the full answer