Jim Watanabe, assistant director of IT for Petrie Electronics, and the manager of the “No Customer Escapes” customer loyalty system project, was walking down the hall from his office to the cafeteria. It was 4 p.m., but Jim was nowhere close to going home yet. The deadlines he had imposed for the project were fast approaching. His team was running behind, and he had a lot of work to do over the next week to try to get things back on track. He needed to get some coffee for what was going to be a late night.

As Jim approached the cafeteria, he saw Sanjay Agarwal and Sam Waterston walking toward him. Sanjay was in charge of systems integration for Petrie, and Sam was one of the company’s top interface designers. They were both on the customer loyalty program team. They were having an intense conversation as Jim approached. 

“Hi guys,” Jim said.

“Oh, hi, Jim,” Sanjay replied. “Glad I ran into you—we are moving ahead on the preliminary database designs. We’re translating the earlier conceptual designs into physical designs.”

“Who’s working on that? Stephanie?” Jim asked. Stephanie Welch worked for Petrie’s database administrator.

“Yes,” Sanjay replied. “But she is supervising a couple of interns who have been assigned to her for this task.”

“So how is that going? Has she approved their work?”

“Yeah, I guess so. It all seems to be under control.”

“I don’t want to second-guess Stephanie, but I’m curious about what they’ve done.”

“Do you really have time to review interns’ work?”

Sanjay asked. “OK, let me send you the memo Stephanie sent me (PE Figure 9-1).”

“You’re right, I don’t have time,” Jim said. “But I’m curious.
It won’t take long to read the memo, right?”
“OK, I’ll send it as soon as I get back to my desk.”
“OK, thanks.” Jim walked on to the cafeteria, and he poured himself a big cup of coffee.

Case Questions
1. In the questions associated with the Petrie Electronics case at the end of Chapter 8, you were asked to modify the E-R diagram given in PE Figure 8-1 to include any other entities and the attributes you identified from the Petrie cases. Review your answers to these questions, and add any additional needed relations to the document in PE Figure 9-1.

2. Study your answer to Case Question 9-60. Verify that the relations you say represent the Petrie Electronics database are in third normal form. If they are, explain why. If they are not, change them so that they are.

3. The E-R diagram you developed in questions in the Petrie Electronics case at the end of Chapter 8 should have shown minimum cardinalities on both ends of each relationship. Are minimum cardinalities represented in some way in the relations in your answer to Case Question 9-61? If not, how are minimum cardinalities enforced in the database?

4. Using your answer to Case Question 9-61, select data types, formats, and lengths for each attribute of each relation. Use the data types and formats supported by Microsoft Access. What data type should be used for nonintelligent primary keys? 

5. Complete all table and field definitions for the Petrie Electronics case database using Microsoft Access. Besides the decisions you have made in answers to the preceding questions, fill in all other field definition parameters for each field of each table.

6. The one decision for a relational database that usually influences efficiency the most is index definition. What indexes do you recommend for this database? Justify your selection of each index. 

7. Using Microsoft Visio, develop an E-R diagram with all the supporting database properties for decisions you made in Case Questions 9-60–65.

Can all the database design decisions you made be documented in Visio? Finally, use Visio to generate Microsoft Access table definitions. Did the table generation create the table definitions you would create manually?

Figure 9-1

Figure 8-1

Transcribed Image Text:

МЕМО To: Stephanie Welch From: Xin Zhu & Anton Washington Re: Preliminary physical database design for "No Customer Escapes" Date: June 1, 2013 We were charged with converting the conceptual database designs for the customer loyalty system to physical database designs. We started with one of the initial ERDS (see PE Figure 8-1), designed at a very high level. The ERD identified six entities: Customer, Product, Service, Promotion, Transaction, and Coupon. We discovered that all of these entities are already defined in Petrie's existing systems. The only entity not already defined is Coupon. Product and Service are defined as part of the product database. Promotion is defined as part of the marketing database. Customer and Transaction are defined as part of the core database. However, after considerable consideration, we are not sure if some of these already identified and defined entities are the same as those identified in the preliminary ERD we were given. Specifically, we have questions about Customer, Transaction and Promotion. Customer: The Customer entity is more complex than it appears. There are several ways to think about the instances of this entity. For example, we can divide Customers into those who shop online and those who shop in the brick-and-mortar stores. And there is of course some overlap. The biggest distinction between these two groups is that we know the names of (and other information about) the Customers who shop online, but we may have very little identifying information about those who shop only in the stores. For example, if an individual shops only at a store and pays only with cash, that individual meets the definition of Customer (see PE Table 8-1), but we collect no data on that individual at all. We raise these issues to call attention to the relationship between Customers and members of the customer loyalty program: All members are Customers, but not all Customers are members. We suggest that the entity called Customer in the preliminary ERD be renamed 'Member,' as we think that is a better name for this entity. We are prepared to map out the table design when this change is approved. Transaction: Petrie already has a relational table called Transaction, but that applies to all transactions in all stores and online. The customer loyalty program focuses on the transactions of its Members, so the program involves only a subset of Transactions. We suggest that the ERD be redesigned to take this fact into account, and that what is now called Transaction be renamed 'Member Transaction.' The relational tables should then be designed accordingly. Promotion: Petrie already has a relational table called Promotion. Again, the customer loyalty program, while having some interest in general promotions, focuses primarily on promotions created specifically for Members of the program. What is called Promotion in the ERD is really a subset of all of Petrie's promotions. We recommend a name change to 'Member Promotion with the associated relational table design. Finally, for the Coupon entity, which is new, we note from the ERD that Coupon only has one relationship, and that is with Customer. As it is a one-to-many relationship, the PK from Customer will be an FK in Coupon. We recommend the following table design: COUPON (Coupon ID.Customer ID, Creation Date, Expiration Date, Value)