Use info from Part a to do part b, c, etc.

Wally Los Gatos, owner of Wallys Wonderful World of Wallcoverings has hired you as a consultant

to design a database management system for his new online marketplace for wallpaper, draperies,

and home decorating accessories. He would like to track sales, prospective sales, and customers.

Ultimately, hed like to become the leading online retailer for all things related to home decorating.

During an initial meeting with Wally, you and Wally developed a list of business requirements to

begin the design of an E-R model for the database to support his business needs.

a. Wally was called away unexpectedly after only a short discussion with you, due to a sticky

situation with the pre-pasted line of wallcoverings he sells. He gave you only a brief description of his

needs and asked that you fill in details for what you expect he might need for these requirements.

Wally expected to be away for only a short time, so he asked that you go ahead with some first

suggestions for the database; but he said, keep it basic for now, well do the faux finishes later.

Before Wally left, he requested the following features for his system:

At a basic level, Wally needs to track his customers (both those who have bought, and those Wally

has identified as prospective buyers based on his prior brick-and-mortar business outlets), the

products he sells, and the products they have bought.

Wally wants a variety of demographic data about his customers so he can better understand who is

buying his products. Hed like a few suggestions from you on appropriate demographic data, but he

definitely wants to know customer interests, hobbies, and activities that might help him proactively

suggest products customers might like.

A customer record should hold a customer number, customer name, customer type customer address including city, state, region and zip code, hobbies, etc. as a minimum.

b. True to his word, Wally soon returned and had a few questions that he wanted the database to

allow him to answer, including the following (you can answer Wally by explaining the entities,

attributes, and relationships that would allow the questions to be answered):

Would the database be able to tell him which other customers had bought the same product a

given customer or prospective customer had bought or was considering buying?

Would the database be able to tell him even something deeper, that is, what other products other

customers bought who also bought the product the customer just bought (that is, an opportunity for

cross-selling)?

Would he be able to find other customers with at least three interests that overlap with those of a

given customer so that he can suggest to these other customers other products they might want to

purchase?

Draw a revised E-R design to support these specific questions.

Wally sells a variety of products to both residential and business customers. Residential customers

have a $1000 credit limit, while business have $10000 c.l. In order to keep track of the products for

sale the company needs to keep records that include a product identifier, a product description, a

product finish and a product retail price. In addition, they need to know the product line the product

belongs to and the retaining warehouse.

c. Wally generates more ideas for the new online shopping site, including the following requirements:

Wally wants to be able to suggest products for customers to buy. Wally knows that most of the

products he sells have similar alternatives that a customer might want to consider. Similarity is fairly

subtle, so he or his staff would have to specify for each product what other products, if any, are

similar.

Wally also thinks that he can improve sales by reminding customers about products they have

previously considered or viewed when on his online marketplace.

Customers can order products online, by phone, or onsite. You need to keep track of the products

that have been ordered. The Orders record should contain the order number, the date ordered, the

items and the quantity ordered, the customer that placed the order and the sales channel used.

Products viewed by a customer should also be recorded so that a reminder can be sent on a regular

basis.

d. Wally

was even more enthusiastic about making his company successful. Wally came in with two

additional requirements for this database:

Wally had learned the hard way that in todays world, some of his customers have multiple homes

or properties for which they order his products. Thus, different orders for the same customer may go

to different addresses, but several orders for the same customer often go to the same address.

Customers also like to see what other people think about products they are considering buying. So,

the database needs to be able to allow customers to rate and review products they buy, and for

other customers considering purchasing a product to see the reviews and ratings from those who

have already purchased the product being considered. Wally also wants to know what reviews

customers have viewed, so he can tell which reviews might be influencing purchases.

Reviews records should contain as a minimum, the product ID and product description, the order

date and a 250-character review field. Also, an indication as to weather the customer is business, or

residential and a viewed counter field.

He asks that you go ahead and work on adding these requirements to the database,

Before going to his doctor, Wally, ever engaged in his business, wanted to make sure your database

design could handle the following needs:

One of the affinities people have for buying is what other people in their same geographical area

are buying (a kind of keep up with the Jones phenomenon). Justify to Wally why your database

design can support this requirement or suggest how the design can be changed to meet this need.

Customers want to search for possible products based on categories and characteristics, such as

paint brushes, lamps, bronze color, etc.

Customers want to have choices for the sequence in which products are shown to them, such as

by rating, popularity, and price. Justify to Wally why your database design can support these needs

by redesign your database E-R diagram to support these additional requirements.

You need to draw an Entity Relationship diagram that will show all the entities that describe the data involved and the relationships among these entities.