Question

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.

Answer 1

Entity Relationship (E-R) diagram for database management system of Wally’s Wonderful World of Wall coverings:

• Entity-Relationship diagram for database management system of Wally’s Wonderful World of Wall coverings is shown below:

In the above ER diagram:

This system contains the six entities such as EMPLOYEE, DEPENDENT, BRANCH, ITEM, CUSTOMER, and ACCOUNT.

EMPLOYEE entity type:

• The entity EMPLOYEE is identified by the identifier has EMPLOYEE ID.

• The attributes of the EMPLOYEE entity has Employee Name, Employee Address, Employee Phone, Employee Title, Employee Salary, Employee Age, and Employee Hire Date.

• The multivalued attribute of the EMPLOYEE entity has Employee Skill.

• The composed attribute of the EMPLOYEE entity has Street, City, State, and ZipCode.

BRANCH entity type:

• The entity BRANCH is identified by the identifier has Branch No.

• The attributes of the BRANCH entity has Branch Location and Square Footage.

• The multivalued attribute of the BRANCH entity has Address, City, State, and ZipCode.

CUSTOMER entity type:

• The entity CUSTOMER is identified by the identifier has Customer ID.

• The attributes of the CUSTOMER entity has Customer Name, Customer Street Address, Customer City, Customer State, Customer Zip Code, Customer Phone, Customer DOB, and Customer Primary Language.

ACCOUNT entity type:

• The entity ACCOUNT is identified by the identifier has Account No.

• The attributes of the ACCOUNT entity has Account Type, Account Balance, Account Payment Date, and Account Payment Amount.

ITEM entity type:

• The entity ITEM is identified by the identifier has Item ID.

• The attributes of the ITEM entity has Item Description, Item Type, Item Pattern, Item Color, and Item Size.

DEPENDENT weak entity type:

• The weak entity DEPENDENT is identified by the identifier has Dependent Name.

• The attributes of the DEPENDENT entity has Dependent Relationship and Department Age.

Carries relationship:

The weak relationship between the entity EMPLOYEE and entity DEPENDENT is “Carries” and the cardinality relationship is “mandatory one to optional many”.

• This is because; employee may have more than one dependent.

Employs relationship:

The relationship between the entity EMPLOYEE and entity BRANCH is “Employs” and the cardinality relationship is “mandatory many to mandatory one”.

• This is because; employee must work in exactly one branch.

Sells relationship:

The weak relationship between the entity BRANCH and entity ITEM is “Sells” and the cardinality relationship is “mandatory many to mandatory many”.

• This is because; branch may sell all items or may sell only one item.

Serves relationship:

The relationship between the entity BRANCH and entity CUSTOMER is “Serves” and the cardinality relationship is “mandatory many to optional many”.

• This is because; branch has more than one customer and customer may or may not have one or more branch.

Contains relationship:

The relationship between the entity ITEM and associative entity ORDER is “Contains” and the cardinality relationship is “mandatory many to optional many”.

• This is because; orders are collected from one or more items.

Owns relationship:

The relationship between the entity CUSTOMER and entity ACCOUNT is “Owns” and the cardinality relationship is “mandatory one to optional many”.

• This is because; customer may have more than one account or no account.

Consists Of relationship:

The relationship between the entities ITEM is “Consists Of” and the cardinality relationship is “optional many to optional many”.

• This is because; item holds more than one items.

ORDER associative entity type:

• The associative entity ORDER between the entity BRANCH and entity CUSTOMER.

• It contains the attributes of the “Order Date” and “Order Credit Status”.

o The cardinality relationship between entity BRANCH and associative entity ORDER is “mandatory one to optional many”.

o The cardinality relationship between entity CUSTOMER and associative entity ORDER is “mandatory one to optional many”.