CH-7 Structuring System Requirements: Conceptual Data Modeling – Essentials of Systems Analysis and Design : Book Guide

Chapter 7 Structuring System Requirements: Conceptual Data Modeling Chapter Overview Chapter 7 presents the techniques used to structure the data requirements for an information system application. This chapter emphasizes entity-relationship (E-R) diagramming; the most common notation used by practicing systems and data analysts. This chapter explains how E-R diagramming is used along with process and logic modeling techniques to develop a thorough, unambiguous description of system requirements. In addition to the standard constructs of the E-R model (entities, attributes, and relationships), the data-oriented questions that should be raised during requirements determination are presented. Instructional Objectives Specific student learning objectives are included at the beginning of the chapter. From an instructor’s point of view, the objectives of this chapter are to: 1. Emphasize the importance of understanding organizational data and convince students that unless they can represent the data requirements of an application unambiguously in logical terms, they cannot implement a system that will effectively serve the needs of management. 2. Present the E-R model as a conceptual data model that can be used to capture the structure and much (although not all) of the semantics (or meaning) of data in several front-end stages of the systems development process. 3. Show students how data, process, and logic models all represent data requirements, but that conceptual data models (such as E-R diagrams (ERD)) provide a more thorough and stable representation of data than do other types of system structures. 4. Show students, via an example from Hoosier Burger, how to match data requirements from data and process system models. This example emphasizes the differences between data stores and data entities, yet shows how to reconcile process and data models to be sure each model covers all data requirements. 5. Explain to students that conceptual data modeling for an Internet-based electronic commerce application is no different than the processes followed for other applications. Classroom Ideas 1. This chapter covers a topic addressed in most database management courses. Depending on your curriculum, this chapter may review previously covered material or may be covered (in more depth) in a subsequent course. We believe that conceptual data modeling is not strictly a database topic, but is essential for thorough systems analysis, thus it is an activity that should not be assigned to only specialists (database analysts). Although you are strongly encouraged to cover this chapter in your systems analysis and design course, it would be good to coordinate how you address this topic with those who teach database courses. This chapter is carefully written for the systems analysis and design student. First, data modeling is presented as a step of the larger systems development process. Second, questions to ask users and to investigate via other requirements determination techniques are introduced, again placing data modeling within the whole systems development effort. Finally, the wording is less technical, and the breadth and depth of coverage has been reduced to make the material more accessible to those who do not have an extensive database background. This chapter is an excellent refresher for those who have already studied E-R modeling and will provide a solid introduction to E–R modeling for those students who will study this topic later in a database course. 2. This is a very detailed chapter, and there are many concepts as well as notations to cover. It is recommended that you devote at least two lecture periods to this chapter, and if possible, schedule a third session that is devoted entirely to working sample exercises with students. 3. It is important to review with students how central a role data modeling and data design play in systems development. Discuss Figure 7–2 with your students and show examples of the types of data models, designs, and code developed in each phase of the systems development process. Discuss who in the organization is most involved in each of these phases and how end users may best participate in the process. 4. Introduce the notation that is used in the chapter for E-R diagrams (Figure 7–5). Give an example (and then have students give examples) for each of the constructs shown in the figure. 5. Contrast the terms “entity type” and “entity instance.” Discuss other examples, such as STUDENT (with each student in the classroom as an instance). Warn students that the term “entity” is often used either way, with the meaning intended to come from the context in which it is used. 6. Discuss the representation of multivalued attributes and repeating groups in E-R diagrams and give some examples. If students are familiar with programming languages that support arrays and other data structures of repeating data, use this understanding to emphasize the need to separate conceptual from physical data modeling. 7. Discuss relationships and their different degrees (Figure 7–6). Have students develop additional examples. 8. Introduce the concept and notation of cardinalities in relationships. Again, have your students develop additional examples. 9. As a general suggestion, show (develop step-by-step) many examples of small (two to three entities) and larger (eight to ten entities) E-R diagrams in class. Develop these from your own personal experience. Three approaches work well and mixing these is best. One approach is to give students descriptions of an organization and have them identify entities, attributes, and relationships (with degrees and cardinalities). You can do this as a group exercise, asking for volunteers or calling on students in class. The second approach is to show E-R diagrams and ask factual and interpretive questions about the business depicted in the diagram (such as, how many faculty advisors might a student have, or would all universities have only one department associated with each course and why). Yet a third approach is to pair students and have one student in the pair develop an E-R diagram for an exercise you give them, and then have the other student in the pair read the diagram to see if it agrees with the description. Once each pair of students develops a diagram both are satisfied with, have several teams present their diagrams to the class and discuss differences. You can also have students bring in copies of computer system forms and reports and develop E-R models for each. Some of the Problems and Exercises at the end of the chapter can be used for such in-class practice problems. 10. Unary and ternary relationships can be especially difficult for some students. Present several examples of each (for unary, for example, a hierarchical organization structure or the relationships between geographical areas or governmental territories; for ternary, for example, a faculty member advising students about majors, or a customer buying products through different sales channels). 11. If you have the time, an exciting way for students to better appreciate conceptual data modeling is to listen to a guest speaker who has developed an enterprise data model for a local organization. Students are usually amazed by how many entities and relationships exist in any reasonably-sized organization (several dozen entities and relationships are common, and models with a hundred entities exist). Such a guest can usually discuss: the difficulties in developing this data model; misunderstandings people had or controversies that existed before the data model was developed; how the data model is being used to guide the development of many new or redesigned systems; and the administrative effort necessary to maintain such a data model (as well as many other topics). 12. We have discovered that students who study process modeling before data modeling often have some difficulties with this chapter. First, students may try to include entities for the sources and sinks for the process model. You must emphasize that data entities have to be described by attributes, and each instance must have a primary key. Further, there will usually be multiple instances. For example, in a data model for a retail store, a student might include the store or the store’s manager as a data entity. Such concepts do not satisfy the definition of a data entity. Second, students may try to use relationships to represent data flows rather than structural associations between entities. This often occurs when students try to model system outputs as entities. For example, some students will create an entity for a major system report (say a monthly sales summary report), and then show a relationship between the customer entity and the report. Emphasize that although copies of the report may be kept on file, system outputs are derived from other data; these data are used to produce any of the system outputs, and hence the outputs are redundant. Lecture Notes Chapter 7 introduces students to data modeling concepts. Data models show the definition, structure, and relationships within data. Figure 7–1 shows that data modeling is performed during systems analysis. Data modeling is important because: (1) the characteristics of data captured during data modeling are crucial in the design of databases, programs, computer screens, and printed reports; (2) data rather than processes are the most complex aspects of many modern information systems; (3) the characteristics about data are permanent; and (4) structural information about data is essential to generate programs automatically. Conceptual Data Modeling A conceptual data model is a detailed model that shows the overall structure of organizational data while being independent of any database management system or other implementation considerations. Conceptual modeling is performed during systems analysis, generally at the same time as other requirements analysis and structuring steps. An entity-relationship diagram (E-R) is a commonly used data model, showing how data are organized within an information system. Interviews, questionnaires, and JAD sessions are common techniques used to gather information for conceptual modeling. A conceptual model is usually built for the existing system, then another conceptual model is prepared for the new system. As Figure 7–2 shows, different kinds of data models and database design occur during the systems development life cycle. For instance, during systems design a logical data model is prepared and physical file and database design is performed; during systems implementation and operation, database and file definitions are prepared, and the data model is evolved. A project repository links the data models and database design. As Figure 7–3 shows, the entity-relationship diagram is the primary deliverable of conceptual modeling. Conceptual modeling results in as many as four diagrams, including: (1) an E-R diagram that covers just the data needed in the project’s application; (2) an E-R diagram for the application system being replaced; (3) an E-R diagram for the whole database from which the new application’s data are extracted; and (4) an E-R diagram for the whole database from which data for the application being replaced is drawn. Conceptual modeling also produces a set of entries about data objects; these entries are stored in the project repository. Gathering Information for Conceptual Data Modeling When performing requirements structuring, the analyst must formulate questions and the investigation to include a data focus. These questions identify the rules and policies governing how work is performed. Table 7–1 identifies categories of questions to ask. Data modeling is performed using both top-down and bottom-up approaches. As Figure 7–4 shows, an analyst uses specific documents to gain a bottom-up perspective of the data. Introduction to Entity-Relationship Modeling On an E-R diagram, three main constructs are used: data entities, relationships, and their associated attributes. This textbook uses the crow’s foot modeling notation. Figure 7–5 shows the basic E-R diagram notations. An entity is a person, place, object, event, or concept in the user environment about which the organization wishes to maintain data. An entity type is a collection of entities that share common properties, and an entity instance is a single occurrence of an entity. An attribute is a characteristic of an entity. While a candidate key uniquely identifies each instance of an entity, an identifier is a candidate key that has been chosen as the unique, identifying characteristic for an entity type. When selecting an identifier, apply the following rules: (1) choose a candidate key that will not change its value over the life of each instance of the entity type; (2) choose a candidate key such that, for each instance of the entity, the attribute is guaranteed to have valid values and not be null; (3) avoid the use of intelligent keys; and (4) consider substituting single-attribute surrogate keys for larger composite keys. The analyst represents a multivalued attribute using a weak entity. A relationship is an association between instances of one or more entity types. Conceptual Data Modeling and the E-R Model One of conceptual modeling’s primary goals is to capture as much of the meaning of data as possible. The more information gathered and modeled about these business rules, the better the conceptual design. The degree of a relationship indicates the number of entity types that participate in a relationship. Figure 7–6 shows three common relationships: unary, binary, and ternary. The cardinality of a relationship is noted on an E-R diagram; both minimum and maximum cardinalities are noted. An associative entity is an entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances. Figures 7–7 and 7–8 provide examples of how to model associative entities. An Example of Conceptual Data Modeling at Hoosier Burger The Hoosier Burger’s food ordering system example illustrates how to prepare a conceptual data model. Figure 7–9 is the level-0 data flow diagram for Hoosier Burger’s new logical inventory control system; Figure 7–10 is the preliminary E-R diagram for Hoosier Burger’s inventory control system; Figure 7–11 is the final E-R diagram for Hoosier Burger’s inventory control system. Electronic Commerce Application: Conceptual Data Modeling Conceptual data model preparation for an electronic commerce application follows the same approach used for more traditional applications. The authors use Pine Valley’s WebStore as an Internet-based application example for conceptual modeling. Table 7–2 lists the customer and inventory information for the WebStore; Figure 7–12 shows the level-0 data flow diagram for the WebStore. Tables 7–3 identifies the data flows within each data category; Table 7–4 identifies the unique data flows within each data category. Figure 7–13 shows the WebStore’s E-R diagram. Matching Questions Solutions Answers for the Key Terms Checkpoint section are provided below. The number following each key term indicates its location in the key term list. Review Questions Solutions 7-1. What characteristics of data are represented in an E-R diagram? Answer: An E-R diagram shows many characteristics of data, including the definition, structure, and relationships within data. Additionally, this diagram shows cardinalities, relationship degrees, and business rules. 7-2. What elements of a data flow diagram should be analyzed as part of data modeling? Answer: Data stores, data flows, and even processes all provide information for data modeling. A data store often represents one or more data entities and their associated attributes. All data in data flows must either be stored in some entity, be computed from data in entities, or in rare circumstances pass through the system. The description of a process can shed light on business rules that must be represented in the data model. 7-3. Explain why a ternary relationship is not the same as three binary relationships. Answer: A ternary relationship represents the simultaneous association of three entities (such as a selling relationship links a customer with a product and salesperson), not three binary relationships (between a sale entity/associative entity and customer, sale and product, and sale and salesperson). 7-4. When must a many-to-many relationship be modeled as an associative entity? Answer: A many-to-many relationship must be modeled as an associative entity when there are attributes associated with the relationship. 7-5. Which of the following types of relationships can have attributes associated with them: one-to-one, one-to-many, many-to-many? Answer: One-to-one and many-to-many relationships (associative entities) may have attributes. For example, a one-to-one unary relationship between employees, Married to, may have a Date Married attribute, and a many-to-many binary relationship between students and courses, Takes, may have a Grade attribute. 7-6. What is the degree of a relationship? Give an example of each of the relationship degrees illustrated in this chapter. Answer: The degree of a relationship indicates the number of entity types participating in a relationship. The three most common relationships are unary, binary, and ternary. An employee working for a department is an example of a binary relationship. A part composed of other parts is an example of a unary relationship. A customer placing an order with a salesperson is an example of a ternary relationship. 7-7. Give an example of a ternary relationship (different from any example in this chapter.) Answer: An example of a ternary relationship might be that of a car service. A particular driver and car might be assigned to a particular client. 7-8. List the deliverables from conceptual data modeling. Answer: The primary deliverable for the conceptual modeling part of analysis is an E-R diagram, showing the major categories of data and the business relationships between them. A full set of entries about data objects to be stored in the project repository is also produced. 7-9. Explain the relationship between minimum cardinality and optional and mandatory participation. Answer: Minimum cardinality refers to the minimum number of instances of entity B that can be associated with entity A. If the minimum cardinality of B is one, then entity B is a mandatory participant in the relationship. However, if the minimum cardinality for entity B can be zero, then entity B can be thought of as an optional participant in the relationship. 7-10. List the ideal characteristics of an entity identifier attribute. Answer: An identifier that meets the criteria set forth in the chapter would be an ideal choice. The criteria include: (1) choosing an attribute that will not change its value over the life of each entity type; (2) choosing an attribute that for each instance of the entity will have valid values and will not be null; (3) avoiding intelligent key usage; and (4) substituting surrogate keys for large composite keys. 7-11. List the four types of E-R diagrams produced and analyzed during conceptual data modeling. Answer: E-R diagrams are produced: (1) to cover just the data needed in the project’s application; (2) for the application system being replaced; (3) to document the entire database from which the new application’s data are extracted; and (4) for the whole database from which data for the application being replaced are drawn. 7-12. What notation is used on an E-R diagram to show a lower-bound or upper-bound limit on the "many" side of a one-to-many relationship? Answer: A fixed number, such as the number 6, is placed above or below the crow’s foot notation next to the entity. 7-13. Explain the difference between a candidate key and the identifier of an entity type. Answer: A candidate key is an attribute(s) that uniquely identifies each instance of an entity type. An identifier is a candidate key that has been chosen as the unique, identifying characteristic for that entity type. 7-14. What distinguishes a repeating group from a simple multivalued attribute? Answer: A multivalued attribute is a single attribute that may legitimately assume more than one value for each entity instance. A repeating group is a set of two or more multivalued attributes that are logically related. 7-15. How do analysts generate alternative solutions to information systems problems? Answer: Analysts consider many issues in developing alternative solutions to information system problems. Of particular interest are the system owner’s and users’ prioritized system objectives and system (and development) constraints. Analysts consider which design strategies would minimally satisfy objectives and not violate constraints, on the one hand, as well as which design strategies would meet or exceed objectives with minimal violation of constraints on the other hand. There are many possible design strategies between these two extreme positions. 7-16. How do managers decide which alternative design strategy to develop? Answer: While alternative design strategies may be compared in many different objective ways, the actual design strategy chosen by management will depend on what management’s true objectives are for a particular development project. Management may ignore constraints, or alternatively, choose the least expensive system to develop, regardless of which design strategy appeared to be the best in the objective comparison. Problems and Exercises Solutions 7-17. Assume that at Pine Valley Furniture each product (described by Product No., Description, and Cost) is comprised of at least three components (described by Component No., Description, and Unit of Measure) and components are used to make one or many products (i.e., must be used in at least one product). In addition, assume that components are used to make other components and that raw materials are also considered to be components. In both cases of components being used to make other components, we need to keep track of how many components go into making something else. Draw an E-R diagram for this situation and place minimum and maximum cardinalities on the diagram. This is a version of a bill-of-materials structure in which components are different entities from products, but raw materials are considered components. The exercise also indicates a minimum cardinality of three for the number of components composing a product, but no such restriction is placed on components as part of other components. Microsoft Visio was used to prepare this answer. Answer: 7-18. A performance venue hosts many concert series a year. Performers have a name and perform several times in a concert series (each constituting a performance with a different date). Concert series have one or more performers and have a name and a specified seating arrangement. A concert series is held in one (and only one) of several concert halls, each of which has a room number. Represent this situation of concerts and performers with an E-R diagram. Answer: 7-19. A restaurant chain has several store locations in a city (with a name and zip code stored for each), and each is managed by one manager. Managers manage only one store. Each restaurant location has its own unique set of menus. Most have more than one menu (e.g., lunch and dinner menus). Each menu has many menu items, and items can appear on multiple menus, and with different prices on different menus. Represent this situation of restaurants with an E-R diagram. Answer: 7-20. Consider the E-R diagram in Figure 7-7. a. What is the identifier for the CERTIFICATE associative entity? b. Now, assume that the same employee may take the same course multiple times, on different dates. Does this change your answer to part a? Why or why not? c. Now, assume we do know the instructor who issues each certificate to each employee for each course. Include this new entity in Figure 7-7 and relate it to the other entities. How did you choose to relate INSTRUCTOR to CERTIFICATE and why? Answer: The identifier is a combination of Employee_ID and Course_Name. If an employee is permitted to take the course multiple times, then the identifier is no longer unique. Therefore, another identifier will need to be specified. The new identifier could be a combination of Employee_ID, Course_Name, and Date. Students will also specify other identifiers for this situation. The inclusion of the INSTRUCTOR entity would make this a ternary relationship. The INSTRUCTOR entity would have a mandatory one cardinality. The identifier for the CERTIFICATE associative entity would be a combination of Employee_ID, Course_Name, Instructor_ID, and possibly Date. 7-21. Consider the E-R diagram of Figure 7–20. Based on this E-R diagram, answer the following questions: a. How many PROJECTs can an employee work on? Answer: This is a many-to-many relationship, so any number of employees can work on a project. b. What is the degree of Used_on relationship? Answer: Two (Binary). c. Do any associative entities appear in this diagram? If so, name them. Answer: No. d. How else could the attribute Skill be modeled? Answer: This attribute could be modeled as a separate entity, to which task would be related. e. Is it possible to attach any attributes to the Works_on relationship? Answer: Attributes such as begin_date, end_date, or performance_rating would be appropriate attributes. f. Could TOOL be modeled as an associative entity? Answer: No. Tool is in a one-to-many relationship, and associative relationships only occur in many-to-many relationships. 7-22. A car rental is an association between a customer, sales agent, and a car. Select a few pertinent attributes for each of these entity types and represent a rental in an E-R diagram. Answer: 7-23. Consider the E-R diagram in Figure 7–21. Are all three relationships—Holds, Goes_on, and Transports—necessary (i.e., can one of these be deduced from the other two)? What, if any, reasonable assumptions make all three relationships necessary? Answer: A vessel holds potentially many consignments, and a consignment is on at most one vessel, which probably means that Holds tracks the consignments currently held on a vessel (and the vessel, if any, which currently holds a consignment). A vessel goes on potentially many voyages, but a voyage involves only one vessel. The Transports relationship says that a consignment is transported on zero to many voyages (which may involve the same or different vessels), and a voyage transports zero to many consignments. Given that a consignment might be on many voyages, and even though each voyage involves exactly one vessel, we do not know from just Transports and Goes_on which vessel a consignment is currently on (there are no attributes from which to infer this). Thus, it would appear that we need all three relationships. 7-24. Draw an E-R diagram to represent the sample customer order in Figure 7-4. Answer: A suggested answer is provided below. The following E-R diagram was created with Microsoft Visio. 7-25. A company database contains an entity called EMPLOYEE. Among other information, the company records information about any degrees each employee has earned, along with the graduation date for the degree. a. Represent the EMPLOYEE entity and its degree attributes using the notation for multivalued attributes. Answer: b. Represent the EMPLOYEE entity and its degree attributes using two entity types. Answer: c. Finally, assume the company decides to also keep data about the institution from which the employees’ degrees were earned, including name of institution, city, and state where the institution is located. Augment you answer to part b to accommodate this new entity type. Answer: 7-26. Consider the Is_married_to unary relationship in Figure 7-6. a. Draw minimum and maximum cardinalities for each end of this relationship. b. Assume we wanted to know the date on which a marriage occurred. Augment this E-R diagram to include a Date_married attribute. c. Because persons sometimes remarry after the death of a spouse or divorce, redraw this E-R diagram to show the whole history of marriages (not just the current marriage) for persons. Show the Date_married attribute on this diagram. Answer: Suggested answers are provided below. Microsoft Visio was used to prepare the following answers. 7-27. Draw an E-R diagram for each of the following situations. (The scenarios are provided in the textbook.) Answer: Suggested answers are provided below. The ERDs were prepared using Microsoft Visio. 7-28. Re-create the spreadsheet in Figure 7–19 in your spreadsheet package. Change the weights and compare the outcome to Figure 7–19. Change the rankings. Add criteria. What additional information does this “what if” analysis provide for you as a decision maker? What insight do you gain into the decision-making process involved in choosing the best alternative system design? Answer: In the first accompanying spreadsheet, the weights have been changed to reflect a weighting that represents systems development projects in governmental agencies. In these settings, a great deal of weight is given to the costs and time frame of the system being proposed. As often happens in governmental agencies, by weighting costs and timing more heavily in this example the outcome has changed. Alternative A and Alternative C have traded places; Alternative A now has the highest score. By changing the weighting, we may have just traded the more technically proficient solution for the more cost-effective solution (at least in terms of “short term” costs). The second accompanying spreadsheet reflects changes in the ratings of Alternative B. With slight improvements in the ratings for this alternative, it now has the highest score. This shows how important the ratings can be and how subtle changes and/or biases in ratings can have significant impacts on the final outcome. The third spreadsheet has the additional criteria of “references” and “in use”. “References” are written references from other organizations currently using the vendor’s systems. “In use” means that the technology proposed must have been in use by a paying customer for six months. These are very common criteria for organizations that value stability of the vendor, technology over performance, and having state of the art technology. These additional criteria change the outcomes; Alternative B now has the highest score. These changes appear to have caused the more stable vendor (i.e., the vendor with the better references and the track record for using the technology it has proposed) to be chosen over the vendor who scored higher on other performance indicators. 7-29. The method for evaluating alternatives used in Figure 7–19 is called weighting and scoring. This method implies that the total utility of an alternative is the product of the weights of each criterion times the weight of the criterion for the alternative. What assumptions are characteristic of this method for evaluating alternatives? That is, what conditions must be true for this to be a valid method of evaluation alternatives? Answer: For this method of evaluation to be valid, one assumes that all relevant criteria are known and included and that the weights and ratings are accurate. More important, this method assumes that the alternatives and criteria lend themselves to a quantitative analysis. Some people argue that some parts of the analysis inherently cannot be quantified. For example, it is difficult to truly rate and weight the amount of trust that you can place in a vendor and your belief that they will indeed follow through on their claims. Further, this method assumes that the criteria are independent of each other, and thus scores are additive. 7-30. Weighting and scoring (see Problem and Exercise 7-29) is only one method for comparing alternative solutions to a problem. Go to the library, find a book or articles on qualitative and quantitative decision making and voting methods, and outline two other methods for evaluating alternative solutions to a problem. What are the pros and cons of these methods compared to the weighting and scoring method? Under weighting and scoring and the other alternatives you find, how would you incorporate the opinions of multiple decision makers? Answer: One other quantitative method for choosing between alternative systems is to choose almost exclusively based on cost. This may seem far-fetched, but in most state and federal agencies information systems are chosen this way. People in these agencies are often forced by law to choose the least expensive system that meets some minimum level of adequacy for the relevant information system needs. Indeed, many states and parts of the federal government are attempting to change this method of awarding governmental contracts. One other more qualitative method often used is to choose the vendor that not only supplies the necessary system but also provides the best value-added services and components. For example, many companies will choose a vendor that, in addition to the basic system, is willing to supply additional training, free components, less expensive maintenance, or has offered to enter into a strategic partnership that is of mutual benefit to both companies. When multiple decision makers collaborate and make the decision together, there are several methods for incorporating their interactive input. For example, for a quantitative analysis such as that presented in Figure 7–8, each decision maker first enters her ratings for each alternative across each criterion. The data are then summarized using a spreadsheet, and the average and/or summary rating across all decision makers is used to choose a vendor. In addition, group support systems can tally the individual, anonymous ideas, comments, and/or votes of multiple, collaborating decision makers. Because there are many multi-criteria decision-making methods (e.g., another popular one uses pairwise comparisons), students may come up with many alternatives for this question. 7-31. Prepare an agenda for a meeting at which you would present the findings of the analysis phase of the SDLC to Bob Mellankamp concerning his request for a new inventory control system. Use information provided in Chapters 5 through 7 as background in preparing this agenda. Concentrate on which topics to cover, not the content of each topic. Answer: Basically, students should include in the meeting the deliverables from the three major subphases of analysis: requirements determination, requirements structuring, and alternative generation and selection. Some other key issues that must be decided are who else to invite to the meeting, the level of detail to present to Bob Mellankamp, and, subsequently, how long the meeting should last. The meeting should not only summarize the findings from analysis but also validate these findings by outlining the process followed. An updated BPP should be covered. Without more information, it may be difficult for the students to decide on these other key issues. Have them identify the information that would be needed to decide on these issues. For example, they may want to know more about Mellenkamp’s personality and preferences before deciding. 7-32. The owner of two pizza parlors located in adjacent towns wants to computerize and integrate sales transactions and inventory management within and between both stores. The point-of-sale component must be very easy to use and flexible enough to accommodate a variety of pricing strategies and coupons. The inventory management, which will be linked to the point-of-sale component, must also be easy to use and fast. The systems at each store need to be linked so that sales and inventory levels can be determined instantly for each store and for both stores combined. The owner can allocate $40,000 for hardware and $20,000 for software and must have the new system operational in three months. Training must be very short and easy. Briefly describe three alternative systems for this situation and explain how each would meet the requirements and constraints. Are the requirements and constraints realistic? Why or why not? Answer: Some basic system alternatives include: (1) build the system in house using a programming language such as C or Microsoft Visual Basic; (2) purchase off the shelf packages, such as Microsoft Excel and Access, along with the necessary computer and telecommunications equipment, and use these to build the necessary systems in-house; (3) purchase custom software and a turn-key system from either a generic information systems consulting firm or from a specialized systems provider for the food retail or pizza retail industry; and (4) outsource the systems to an outside firm. Each of these alternatives has unique advantages and disadvantages. Given that the system needs to be stable, easy to use, and built relatively quickly, and given that this organization probably has little or no in-house systems personnel, the third and fourth alternatives are most realistic. 7-33. Compare the alternative systems from Problem and Exercise 7-32 using the weighted approach demonstrated in Figure 7–19. Which system would you recommend? Why? Was the approach taken in this and Problem and Exercise 7-32 useful even for this relatively small system? Why or why not? Answer: It will be useful for students to create a spreadsheet like the one presented in Figure 7–19. They should list in the criteria category each of the requirements and constraints described in the problem, plus any others that they believe are relevant. As best they can, have them weigh each of these criteria and use them to rank each of the four alternatives presented in the previous answer. They may have to make some assumptions to complete each of the ratings. This method of analysis should be useful even for this relatively small system. The method will force the decision maker to flesh out relevant criteria and weighting and be as objective as possible in rating each alternative on each criterion. 7-34. Suppose that an analysis team did not generate alternative design strategies for consideration by a project steering committee or client. What might the consequences be of having only one design strategy? What might happen during the oral presentation of project progress if only one design strategy is offered? Answer: Having only one design strategy can be problematic in several ways. First, there will be no guarantee that the best, the correct, or even an adequate system for the situation is being developed or purchased. This is not obvious because it is unclear if other alternatives were considered, and if they were, those present cannot see why the one choice won out. Second, if the one strategy is because only one vendor is used, there are no benefits from having multiple vendors compete for an RFP. For example, the vendor has no incentive to keep its price as low as possible. Third, without the detailed, public systems specifications that are part of a competitive bid process, there is not likely to be much in the way of written documentation to refer back to if the vendor does not fulfill its promises. If the analysts present only one design strategy during the oral presentation to the project steering committee or client, the recommendations are likely to be (at worst) rejected, or (at best) accepted with great skepticism. It is also possible that those present at the meeting will start to generate alternatives, each representing that person’s position. The meeting will likely quickly deteriorate since a fair assessment of ad hoc alternatives cannot be done within the limits of a meeting. In any event, this is not a good way to begin the development of an information system (or to build a career). 7-35. Assume you are designing a database for a local used car dealership. Attributes for a car include the vehicle identification number, stock number, make, model, year, and trim. What would you use for the primary key in this entity? What attributes are likely to be foreign keys associated with other entities? Answer: Either the stock number or vehicle identification number could be used as the primary key. Make, model, and trim are likely to be foreign keys associated with other entities. Discussion Questions Solutions 7-36. Discuss why some systems developers believe that a data model is one of the most important parts of the statement of information system requirements. Answer: Four reasons were provided in the textbook: (1) the characteristics of data captured during data modeling are crucial in the design of databases, programs, computer screens, and printed reports; (2) data rather than processes are the most complex aspects of many modern information systems; (3) the characteristics about data are permanent; and (4) structural information about data is essential to generate programs automatically. 7-37. Using Table 7–1 as a guide, develop a script of at least ten questions you would ask during an interview of the customer order processing department manager at Pine Valley Furniture. Assume the focus is on analyzing the requirements for a new order entry system. The purpose of the interview is to develop a preliminary E-R diagram for this system. Answer: Students should identify numerous questions to ask Pine Valley Furniture’s manager. Students should design their questions to collect information about data entities, candidate keys, attributes and secondary keys, security controls, cardinalities and time dimensions of data, relationships, and integrity rules. Possible questions include: (1) What data are maintained by the customer ordering system? (2) How do you distinguish each customer record from every other customer record? (3) What information do you collect about each customer? (4) Does this information differ if the customer is new, as opposed to a repeat customer? (5) What information do you keep about each order? (6) Who has access to customer records? (6) Is there a limit to the number of outstanding orders a customer can have at any one time? (7) How long do you keep customer information? (8) What are the return policies for products? (9) What types of reports do you use? (10) Who can modify the customer, order, and/or product data? 7-38. If possible, contact a systems analyst in a local organization. Discuss with this systems analyst the role of conceptual data modeling in the overall systems analysis and design of information systems at his or her company. How, and by whom, is conceptual data modeling performed? What training in this technique is given? At what point(s) is this done in the development process? Why? Answer: The answer to this question depends on the organization that the student chooses to contact. Encourage students to investigate how the systems analyst’s conceptual modeling role in a small organization will vary as opposed to his role in a much larger organization. Role of Conceptual Data Modeling: • Purpose: Conceptual data modeling is crucial for defining the high-level structure of data within an organization. It helps in understanding and representing the essential data entities and their relationships without delving into technical details. This ensures that all stakeholders have a clear, shared understanding of the data requirements and business rules. Who Performs Conceptual Data Modeling: • Typically Done By: Systems analysts or business analysts, often in collaboration with subject matter experts and stakeholders. They use this model to bridge the gap between business needs and technical design. Training in This Technique: • Training Provided: Analysts usually receive formal training in data modeling techniques, including workshops, courses, and certifications (e.g., in UML or ER modeling). Organizations may also provide in-house training tailored to their specific methodologies. Timing in Development Process: • When Done: Conceptual data modeling is performed early in the systems development life cycle, during the requirements analysis phase. This is done before detailed design and implementation to ensure that the data requirements are accurately captured and agreed upon. Why It’s Important: • Reason: It helps in creating a clear framework for data structures and relationships, which is essential for developing detailed designs and ensuring that the final system aligns with business needs. This early modeling minimizes misunderstandings and rework later in the development process. This approach ensures that data-related decisions are well-informed and aligned with the overall system objectives. 7-39. Talk to MIS professionals at a variety of organizations and determine the extent to which CASE tools are used in the creation and editing of entity-relationship diagrams. Try to determine whether or not they use CASE tools for this purpose; which CASE tools are used; and why, when, and how they are used. In companies that do not use CASE tools for this purpose, determine why not and what would have to change in order to use them. Answer: The answer to this question is dependent to a degree on the organization contacted. The systems analysts who use CASE tools for conceptual modeling will probably mention the ease with which CASE tools facilitate the preparation of the model and modifications that are made to the model as it evolves. Also, the ability to link the data objects stored in the project repository is of significant benefit. Extent of CASE Tools Use: 1. Use of CASE Tools: • Yes: Many organizations use CASE (Computer-Aided Software Engineering) tools for creating and editing entity-relationship (ER) diagrams. These tools streamline the process, provide templates, and facilitate accurate and consistent diagram creation. • No: Some organizations may not use CASE tools, often relying on manual methods or simpler software like Microsoft Visio. 2. CASE Tools Used: • Popular Tools: Common CASE tools include Microsoft Visio, Lucid chart, ER/Studio, IBM InfoSphere Data Architect, and Oracle SQL Developer Data Modeler. These tools offer features for diagramming, data modeling, and integration with databases. 3. Why, When, and How They Are Used: • Why: CASE tools are used to improve efficiency, ensure accuracy, and maintain consistency in data modeling. They support features like auto-layout, version control, and integration with other development tools. • When: They are typically used during the design phase of systems development, specifically in the conceptual and logical design stages to visualize data structures and relationships. • How: Users create and edit ER diagrams using graphical interfaces, drag-and-drop features, and automated tools for generating code or database schemas. 4. Companies Not Using CASE Tools: • Reasons for Not Using: Lack of budget, insufficient training, or preference for simpler tools. • What Would Change: To adopt CASE tools, companies would need to invest in software licenses, provide training for staff, and integrate these tools into their development workflows. Overall, CASE tools are valued for their ability to enhance the accuracy and efficiency of ER diagram creation, though some organizations may have barriers to their adoption. 7-40. Ask a systems analyst to give you a copy of the standard notation he or she uses to draw E-R diagrams. In what ways is this notation different from notation in this text? Which notation do you prefer and why? What is the meaning of any additional notation? Answer: Many articles discussing various data modeling notations are available on the Web. As an alternative to contacting a systems analyst, you can ask students to locate one or more of these standard notations and then compare and contrast these notations. If time permits, you may even have students present their findings to the class. 1. Standard Notation Copy: • Request: Obtain a copy of the standard E-R diagram notation used by the systems analyst. 2. Comparison with Textbook Notation: • Differences: The notation used by the analyst might differ in terms of symbols, shapes, or conventions. For example, while textbook notations often use rectangles for entities and diamonds for relationships, some notations may use different symbols or add additional details like cardinality or participation constraints in various ways. 3. Preferred Notation: • Preference: Personal preference for notation can vary based on clarity, ease of use, and familiarity. For instance, you might prefer a notation with more intuitive symbols or one that includes more detailed constraints. 4. Additional Notation: • Meaning: Additional notations might include extended features like detailed cardinality constraints, role names, or multi-valued attributes. These can provide more nuanced information about the relationships and constraints in the database schema. Example Answer: "The standard notation provided by the systems analyst uses diamond shapes for relationships with additional annotations for cardinality and participation constraints, whereas the textbook notation is simpler and primarily uses rectangles and diamonds without these details. I prefer the analyst’s notation for its clarity and the additional context it provides about the relationships. The extra notation, such as cardinality constraints, helps in understanding the exact nature of relationships between entities, which is valuable for a comprehensive database design." 7-41. Consider the purchase of a new PC to be used by you at your work (or by you at a job that you would like to have). Describe in detail three alternatives for this new PC that represent the low, middle, and high points of a continuum of potential solutions. Be sure that the low-end PC meets at least your minimum requirements and the high-end PC is at least within a reasonable budget. At this point, without quantitative analysis, which alternative would you choose? Answer: Because pricing and capabilities change rapidly, ask students to visit the Web sites of several vendors, including Compaq, IBM, Gateway, and Dell. For discussion purposes, three alternatives are presented below; however, please note that the information provided in this answer will need to be updated each semester. Low-end alternative: 2.4GHz Intel Celeron processor, 256MB of RAM, 40GB hard drive, 17" monitor, Microsoft Windows XP Home Edition, Microsoft Office 2003 Standard Edition. Cost is about $800. Mid-range alternative: 2.8GHz Intel Pentium 4 processor, 256MB of RAM, 60GB hard drive, 17" flat panel monitor, Microsoft Windows XP Home Edition, Microsoft Office 2003 Professional Edition. Cost is about $1,200. High-end alternative: 3.2GHz Intel Pentium 4 processor, 512MB of RAM, 160GB hard drive, 21" flat panel, Microsoft Windows XP Professional, Microsoft Office 2003 Small Business Edition. Cost is about $2,600. 7-42. For the new PC described, develop ranked lists of your requirements and constraints as displayed in Figure 7–19. Display the requirements and constraints, along with the three alternatives, as done in Figure 7–19, and note how each alternative is rated on each requirement and constraint. Calculate scores for each alternative on each criterion and compute total scores. Which alternative has the highest score? Why? Does this choice fit with your selection in the previous question? Why or why not? Answer: The spreadsheet below presents the quantitative analysis for the three alternatives described in the previous answer. Speed, storage, ease of use, reliability, costs, and time to operation were the criteria used. The mid-range alternative has the highest score because it has acceptable rankings for the performance-oriented criteria and scores well on the highly weighted criteria of costs. As is often the case, people often buy more technology than they really need. The counter argument is that, for PCs, one should buy as much power as they can reasonably afford so the technological life of their equipment is longer, and they will be better able to take advantage of new software as it becomes available. Case Problems Solutions 7-43. Pine Valley Case Exercises Solutions a. What entities are identified in the above scenario? Can you think of additional entities? What interrelationships exist between the entities? Answer: Customers, orders (purchases), and inventory (items) are mentioned in the scenario. Closely associated to purchase activity are sales promotions. A customer may respond to zero or more promotions; a promotion is associated with one or more products; an inventory item (product) is contained on zero or more orders; a customer may have zero or more outstanding orders. b. For each entity, identify its set of associated attributes. Specify identifiers for each entity. Answer: Several suggested attributes for each entity are provided below. Mention that this information will be normalized at some point during analysis, resulting in the creation of new entities and the identification of new attributes. Here’s a concise summary of the entities, their attributes, and identifiers: 1. CUSTOMER_PROFILE • Identifier: `CustomerID` • Attributes: Name, Primary Address, Alternative Address, Phone, Fax, BuyerName, E-mail Address, School Affiliation, DOB, Gender, FirstPurchaseDate, AnnualIncome, CustomerType, MaritalStatus, Occupation, Dependents, InitialContact 2. INVENTORY • Identifier: `SKU` • Attributes: ProductName, Description, Material, Color, Price, Lead Time 3. ORDER • Identifier: `OrderNumber` • Attributes: OrderDate, CustomerID, ShipDate 4. PROMOTION • Identifier: `PromotionNumber` • Attributes: PromotionDescription, BeginningDate, EndingDate Note: This data will be normalized, potentially leading to new entities and attributes for improved organization and efficiency. c. Based on the case scenario and your answers to parts a and b, prepare an entity relationship diagram. Be sure to specify the cardinalities for each relationship. Answer: A suggested answer is provided below. The following diagram was prepared using Microsoft Visio. The suggested answer includes three additional entities (SelectedPromotion, PromotedItem, and OrderDetails). Discuss why these additional entities might be necessary with your students. d. How does this conceptual model differ from the WebStore’s conceptual model? Answer: Both systems share data about customers, orders, and inventory. However, these systems also require unique data (attributes) about each entity. 7-44. Hoosier Burger Case Exercises Solutions a. Based on the information provided in the case scenario, what entities will Hoosier Burger need to store information about? Answer: As illustrated in Figure 7–10, Hoosier Burger currently stores information about its sales, item sales, products, recipes, inventory items, invoice items, and invoices. The new delivery system will require information about charges, delivery customers, and order histories. b. For the entities identified in part a, identify a set of attributes for each entity. Answer: A suggested answer is provided below. c. Specify an identifier for each entity. What rules did you apply when selecting the identifier? Answer: The identifiers are noted in the previous answer. Four rules were mentioned in the chapter; these include: (1) choosing a candidate key that will not change its value over the life of each instance of the entity type; (2) choosing a candidate key such that, for each instance of the entity, the attribute is guaranteed to have valid values and not be null; (3) avoiding the use of intelligent keys; and (4) considering the substitution of single-attribute surrogate keys for larger composite keys. d. Modify Figure 7–10 to reflect the addition of these new entities. Be sure to specify the cardinalities for each relationship. Answer: A suggested answer is provided below. Microsoft Visio was used to prepare the E-R diagram. 7-45. Corporate Technology Centers Case Exercises Solutions a. What entities are identified in the above scenario? Can you identify additional entities? Answer: The primary entities are course, staff, location, and student. Reinforce to students that these entities will be normalized in a later chapter. b. For each entity identified in part a, specify a set of associated attributes. Answer: A suggested answer is provided below. c. Select an identifier for each entity. What rules did you apply when selecting the identifier? Answer: The identifiers are noted in the previous answer. The rules discussed in the chapter are: (1) choosing a candidate key that will not change its value over the life of each instance of the entity type; (2) choosing a candidate key such that, for each instance of the entity, the attribute is guaranteed to have valid values and not be null; (3) avoiding the use of intelligent keys; (4) considering the substitution of single-attribute surrogate keys for larger composite keys. d. Based on the case scenario and your answers to a, b, and c, prepare an entity relationship diagram. Be sure to specify the cardinalities for each relationship. Answer: Several students may identify a higher degree relationship for location, course, and staff. Although students will need to make several assumptions, a simple, suggested entity relationship diagram is provided below. Microsoft Visio was used to prepare the following E-R diagram. 7-46. Pine Valley Furniture Case Exercises Solutions a. Generally speaking, what alternative design strategies were available to Pine Valley Furniture? Answer: Table 7–5 identifies requirements, constraints, and three alternatives for the new system, but does not specify how the alternatives are sourced. However, the case does suggest that in-house development is the best option. Pine Valley Furniture has six potential sources of software; these include hardware manufacturers, packaged software producers, custom software producers, enterprise-wide solutions, application service providers, and in-house development. The most likely candidates are custom software producers and in-house development. Because of the marketing group’s unique information needs, the most likely alternative design strategy will probably involve in-house development and be designed to work with the company's existing platform. If you have the class time, encourage your students to research different, specific design strategies that might be beneficial for Pine Valley. Then ask your students to update the information provided in Tables 7–5 and 7–6. b. Of the alternative design strategies available to Pine Valley Furniture, which were the most viable? Why? Answer: As mentioned above, the most likely candidates are custom software producers and in-house development. The marketing group’s unique information needs require custom software development, requirements unlikely provided by hardware manufacturers, packaged software developers, application service providers, or enterprise-wide solutions. Additionally, the competitive nature of this new system may necessitate in-house development. It appears from the weighted approach (prepared in Part c), that Alternative C is the best choice. c. Using the information provided in Table 7–6, calculate the scores for each alternative. Answer: A suggested answer is provided below. d. Based on the information provided in Tables 7–5 and 7–6, which alternative do you recommend? Answer: Based on the weighted factor approach, Alternative C appears to be the best choice. However, this alternative requires $350,000 to develop, incurs $100,000 in hardware costs, and requires 9 months to operation. Alternative B has a slightly lower rating, but requires only $200,000 to develop, incurs $80,000 in hardware costs, and requires 7 months to operation. 7-47. Hoosier Burger Case Exercises Solutions a. Generally speaking, what alternative design strategies are available to Hoosier Burger? Answer: Table 7–7 identifies the new requirements, constraints and three alternatives for Hoosier Burger. As part of its design strategy, Hoosier Burger will likely acquire a new hardware platform and system software. Also, Hoosier Burger has six potential sources of software; these include hardware manufacturers, packaged software producers, custom software producers, application service providers, enterprise-wide solutions, and in-house development. However, the most likely candidates are custom software producers or packaged software producers. If you have the class time, encourage your students to research different, specific design strategies that might be beneficial for Hoosier Burger. Then ask your students to update the information provided in Tables 7–7 and 7–8 to reflect their findings. b. Is an enterprise resource planning system a viable option for Hoosier Burger? Why or why not? Answer: Due to Hoosier Burger’s size and its cost, an enterprise resource planning system is not the best choice for Hoosier Burger. c. Modify Figure 7–19 to incorporate the criteria mandated by the new delivery system. Which alternative should be chosen? Answer: Based on the analysis provided in the following spreadsheet, it appears that Alternative B should be chosen. d. Assuming that Alternative C is still chosen, update Hoosier Burger's economic feasibility analysis to reflect the changes mentioned in this scenario. Answer: Although Alternative C has the lowest score, it may still be chosen because of its many benefits. The students will need to make several assumptions, because the case does not specify how much of the development costs are already reflected in the one-time costs currently shown in Figure 7–19. PETRIE’S ELECTRONICS Case Question Solutions 7-48. Review the data-flow diagrams you developed for questions in the Petrie’s Electronics case at the end of Chapter 6 (or diagrams given to you by your instructor). Study the data flows and data stored on these diagrams and decided whether you agree with the team’s conclusion that the only 6 entity types needed are listed in the case and in Figure 7-1. If you disagree, define additional entity types, explain why they are necessary, and modify Figure 7-1 accordingly. Answer: Answer will vary. Any additional entities should be properly modeled in an E-R diagram similar to Figure 7-1. To review and update the entity types: 1. Analyze Diagrams: Examine data flows and data stores in your DFDs. 2. Compare with Case: Compare with the 6 entity types listed in the case and Figure 7-1. 3. Identify Missing Entities: If you find additional entities needed based on the data flows and stores, define them. 4. Update Figure 7-1: Add any new entity types to the diagram, ensuring they are correctly integrated with data flows and processes. 5. Justify Changes: Explain why the additional entities are necessary, such as for capturing omitted interactions or data requirements. This will ensure the DFD accurately represents all essential components of the system. 7-49. Again, review the DFDs you developed for the Petrie’s Electronics case (or those given to you by your instructor). Use these DFDs to identify the attributes of each of the six entities listed in this case plus any additional entities identified in your answer to Question 1. Write an unambiguous definition for each attribute. Then, redraw Figure 7-1 by placing the six (and additional) entities in this case on the diagram along with their associated attributes. Answer: To complete this task: 1. Review DFDs: Analyze the developed DFDs for Petrie’s Electronics. 2. Identify Entities and Attributes: • List the six entities plus any additional ones found. • Define each attribute clearly (e.g., "Customer ID: Unique identifier for customers"). 3. Redraw Figure 7-1: • Place all identified entities on the diagram. • Add attributes next to each entity for clarity. Ensure the updated diagram accurately reflects all entities and their attributes as per the case requirements. 7-50. Using your answer to Case Question 7-49, designate which attribute or attributes form the identifier for each entity type. Explain why you chose each identifier. Answer: Answers will vary, according to the answer to Question 7-48. For each entity type: 1. Customer: Customer ID — Unique identifier for each customer to distinguish between individuals. 2. Order: Order ID — Unique identifier for each order to track and manage orders individually. 3. Product: Product Code — Unique identifier for each product to differentiate between items. 4. Supplier: Supplier ID — Unique identifier for each supplier to manage supplier information effectively. 5. Employee: Employee ID — Unique identifier for each employee to track and manage employee records. 6. Invoice: Invoice ID — Unique identifier for each invoice to ensure accurate billing and record-keeping. Explanation: Each identifier is chosen because it uniquely distinguishes records in the system, ensuring precise tracking and management of data for each entity. 7-51. Using your answer to Case Question 7-50, draw the relationships between entity types needed by the system. Remember, a relationship is needed only if the system wants data about associated entity instances. Give a meaningful name to each relationship. Specify cardinalities for each relationship and explain how you decided on each minimum and maximum cardinality on each end of each relationship. State any assumptions you made if the Petrie’s Electronics cases you have read so far and the answers to questions in these cases do not provide the evidence to justify the cardinalities you choose. Redraw your final E-R diagram in Microsoft Visio. Answer: Answers will vary, according to the answer to Question 7-48. To draw the relationships: 1. Customer - Order: • Relationship: Places • Cardinalities: 1:N (One customer can place many orders; each order is from one customer.) 2. Order - Product: • Relationship: Contains • Cardinalities: M:N (An order can have many products; a product can be in many orders.) 3. Supplier - Product: • Relationship: Supplies • Cardinalities: 1:N (One supplier supplies many products; each product is supplied by one supplier.) 4. Employee - Order: • Relationship: Processes • Cardinalities: 1:N (One employee processes many orders; each order is processed by one employee.) 5. Order - Invoice: • Relationship: Generates • Cardinalities: 1:1 (Each order generates one invoice; each invoice is for one order.) Assumptions: Based on typical business practices. Action: Redraw the E-R diagram with these relationships and cardinalities in Microsoft Visio. 7-52. Now that have developed in your answer to Case Question 7-51 a complete E-R diagram for the Petrie’s Electronics database, what are the consequences of not having an employee entity type in this diagram? Assuming only the attributes you show on the E-R diagram, would any attribute be moved from the entity it is currently associated with to an employee entity type if it were in the diagram? Why or why not? Answer: Answers will vary. Not having an employee entity in the diagram means that employee activity while interacting with the system cannot be tracked. Consequences of Not Having an Employee Entity Type: 1. Missing Information: The absence of an employee entity means you cannot directly track or manage employee-specific details related to order processing. 2. Limited Tracking: Without the employee entity, it’s harder to associate orders with the employees who processed them, impacting performance tracking and accountability. Attributes Moving to Employee Entity: Order Attributes: Attributes related to who processed the order (e.g., "Employee ID") would be moved to the employee entity if it were included. This ensures proper tracking and management of employee information. Reason: Employee-related details and performance metrics should be managed separately to maintain data integrity and support detailed reporting. 7-53. Write project dictionary entries (using standards given to you by your instructor) for all the entities, attributes, and relationships shown in the E-R diagram in your answer to Case Question 7-51. How detailed are these entries at this point? What other details still must be filled in? Are any of the entities on the E-R diagram in your answer to Case Question 7-51 weak entities? Why? In particular, is the SERVICE entity type a weak entity? If so, why? If not, why not? Answer: Answers will vary, according to the answer to Question 7-48. Project Dictionary Entries: 1. Customer: • Attributes: Customer ID, Name, Contact Information. 2. Order: • Attributes: Order ID, Order Date, Customer ID. 3. Product: • Attributes: Product Code, Name, Price. 4. Supplier: • Attributes: Supplier ID, Name, Contact Information. 5. Employee: • Attributes: Employee ID, Name, Position. 6. Invoice: • Attributes: Invoice ID, Invoice Date, Order ID. Detail Level: Basic descriptions and attributes are provided; need to add constraints and data types. Weak Entities: • SERVICE: Not specified; typically weak if it relies on another entity for identification. If SERVICE has its own unique identifier, it is not a weak entity. 7-54. What date-related attributes did you identify in each of the entity types in your answer to Case Question 7-51? Why are each of these needed? Can you make some general observations about why date attributes must be kept in a database based on your analysis of this database? Answer: Answers will vary. Date objects are needed anytime the date or time of the creation or update of the object are needed (especially in recording transactions and the like). Date-Related Attributes Identified: 1. Order • Order Date: Indicates when the order was placed. Needed to track the timing of transactions and for order processing. 2. Invoice • Invoice Date: Shows when the invoice was issued. Needed for billing cycles, financial records, and tracking payment due dates. Why These Dates Are Needed: • Order Date: Helps in managing and analyzing sales trends, order fulfillment, and customer service. • Invoice Date: Essential for financial management, accounting, and ensuring timely payments. General Observations: • Tracking and Analysis: Date attributes are crucial for tracking transactions, analyzing trends, and managing financial records. • Audit and Compliance: Ensures proper documentation for auditing and compliance with financial regulations. Solution Manual for Essentials of Systems Analysis and Design Joseph S. Valacich, Joey F. George, Jeffrey A. Hoffer 9780133546231