Wally Los Gatos, owner of Wally's Wonderful World of Wallcoverings, Etc., 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, he'd 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, we'll 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. He'd 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.
b. True to his word, Wally soon returned, but said he could only step into the room for a short time because the new Tesla he had ordered had been delivered, and he wanted to take it for a test drive. But before he and his friend Elon left, he had a few questions that he wanted the database to allow him to answer, including the following:
• 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?
Prepare queries or explanations to demonstrate for Wally why your database design in part a of this exercise can support these needs, or draw a revised design to support these specific questions.
c. Wally is thrilled with his new Tesla and returns from the test drive eager to expand his business to now pay for this new car. The test drive was so invigorating that it helped him to generate 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.
Unfortunately, Wally's administrative assistant, Helen, in her hunt for Wally, knocked on the door and told Wally that his first-born child, Julia, had just come in asking to see her father so that she could show him her new tattoo, introduce him to her new "goth" boyfriend, and let him know about her new life plans. This declaration, obviously, got Wally's attention. Wally left abruptly, but asked that you fill in the blanks for these new database requirements.
d. Fortunately, Wally's assistant was just kidding, and the staff had actually thrown a surprise birthday party for Wally. They needed Wally in the staff dining room quickly before the ice cream melted and the festive draperies adorning the table of presents had to be returned to the warehouse for shipment to Rio for display at the summer Olympics. Now overjoyed by the warm reception from his trusted associates, 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 today's 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 to buy. 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.
Yet again, Wally has to leave the meeting, this time because it is time for his weekly pickle ball game, and he doesn't want to brush off his partner in the ladder tournament, which he and partner now lead. He asks that you go ahead and work on adding these requirements to the database, and he'll be back after he and his partner hang their new trophy in Wally's den.
e. Although still a little sweaty and not in his normal dapper business attire, Wally triumphantly hobbled back to the meeting room. Wally's thigh was wrapped in what seemed to be a whole reel of painter's tape (because he didn't have any sports tape), nursing his agony of victory. Before limping off 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, or redesign your database to support these additional requirements.