Systems Analysis and Design, Tenth Edition End of Chapter Solutions. CHAPTER 9 Data Design

Document Content and Description Below

IT 510 Systems Analysis and Design, Tenth Edition End of Chapter Solutions. CHAPTER 9 Data Design Chapter Exercises s Questions 1.In the auto shop examples on page 348, what are some problems t... hat might arise in Mario’s system? Why won’t Danica run into the same problems? Provide specific examples in your answer. Mario relies on two file-oriented systems, sometimes called file processing systems, to manage his business. The two systems store data in separate files that are not connected or linked, as shown in Figure 9-3. Unfortunately, using two separate systems means that some data is stored in two different places, and the data might or might not be consistent. For example, three data items (Mechanic No, Name, and Pay Rate) are stored in both files. This redundancy is a major disadvantage of file-oriented systems, because it threatens data quality and integrity. In fact, Figure 9-3 includes a typical discrepancy, which involves the pay rate for Jim Jones. Danica, on the other hand, uses a database management system (DBMS) with two separate tables that are joined, so they act like one large table as shown in Figure 9-4. In Danica’s system, the tables are linked by the Mechanic No field, which connects the tables. Notice that except for the common field, no other data items are duplicated. The DBMS design, also called a relational database or relational model, was introduced in the 1970s and continues to be the dominant approach for organizing, storing, and managing business data. The error in Mario’s file could not occur in Danica’s relational database, because an employee’s pay rate is stored in only one place. (Pages 348-350) 2. What is a DBMS? Briefly describe the components of a DBMS. A DBMS provides an interface between a database and users who need to access the data. In addition to interfaces for users, database administrators, and related systems, a DBMS also has a data manipulation language, schema, and physical data repository. (Page 350) 3. Describe a primary key, candidate key, secondary key, foreign key, and a combination key. Use your imagination to provide an example of each key that is not in the textbook. A primary key is the field or combination of fields that uniquely and minimally identifies a single member of an entity. A candidate key is a field or combination of fields that could serve as a primary key; only one of the candidate keys can be designated as the primary key. A secondary key is a field or combination of fields that serves as a basis for some type of retrieval or processing. A foreign key is a field or combination of fields whose value must match a primary key value in some other table or file. Students should be able to suggest any number of “keys” from their personal experience. This might be a good team task, with a competitive element thrown in. (Pages 356-357) 4.What are entity-relationship diagrams and how are they used? What symbol is used for a relationship? What is an associative entity? Provide an example. Entity-relationship diagrams (ERDs) are graphical representations of an information system. They are used to show how each entity relates to the other entities in the system, but Systems Analysis and Design, Tenth Edition Page 2 of 33 they do not show the flow of data through the information system. A rectangle is used to represent an entity, while relationships are represented by diamonds. As shown in Figure 9-16, the event or transaction that links the two entities in an M:N relationship is actually a third entity, called an associative entity that has its own characteristics. (Pages 360-361) 5. What is cardinality, and what symbols do you use in the crow’s foot notation method? Cardinality describes how an entity relates to other entities in the ERD. In a specific relationship, an entity can be mandatory, meaning it must be present, or optional, meaning it may be present. Crow’s foot notation displays cardinality in a graphical manner using symbols. A single bar indicates one possibility, a double bar means one and only one, a circle indicates zero and a crow’s foot indicates many possibilities. (Page 362) 6.What are data warehousing and data mining? How do businesses use these tools? A data warehouse is an integrated collection of data that can include seemingly unrelated information, no matter where it is stored in the company. Because it can link various information systems and databases, a data warehouse provides an enterprise-wide view to support management analysis and decision making. Data mining software looks for meaningful patterns and relationships among data. For example, data mining software could help a consumer products firm identify potential customers based on their prior purchases. The terms are related in the sense that both refer to data storage and extraction. The data warehouse, however, describes data organization and storage; while data mining focuses on methods of obtaining valuable business knowledge from a data warehouse. (Page 385) 7.What is an unnormalized design, and how do you convert an unnormalized design to 1NF? In your answer, refer to specific pages and figures in this chapter. A record is said to be in first normal form if it contains no repeating groups. To convert an unnormalized record to first normal form you would expand the primary key of the record to include the key of the repeating group.. (Pages 365-368) 8.How would you define second normal form? How do you convert a 1NF design to 2NF? In your answer, refer to specific pages and figures in this chapter. To be in second normal form, a record must first be in first normal form and all fields that are not part of the primary key must be dependent on the entire primary key. To convert from 1NF to 2NF, you would create a new record for each field and a possible combination of the fields in the primary key. For each new record, designate one field or combination of fields as a primary key. Place the remaining fields with the primary key on which they depend. When finished placing the fields, discard any record that does not contain any additional fields other than its primary key. The remaining records are the 2NF of the original record. Students should have no difficulty citing specific pages and figures as needed. (Page 368) 9.How would you define third normal form? How do you convert a 2NF design to 3NF? In your answer, refer to specific pages and figures in this chapter. A record is in third normal form if it is in second normal form and if no nonkey field is dependent on another nonkey field. To convert a record to 3NF you would move the dependent field to a new record, which has the field it is dependent on as the primary key. (Page 371) 10.How would a specific date, such as April 27, 2015, be represented as an absolute date? Systems Analysis and Design, Tenth Edition Page 3 of 33 An absolute date is the total number of days from some specific base date. Using a base date of January 1, 1900, the absolute date value of April 27, 2015 is 42121. (Page 389) Discussion Topics 1. Are there ethical issues to consider when planning a database? For example, should sensitive personal data (such as medical information) be stored in the same DBMS that manages employee salary and benefits data? Why or why not? Answers will vary. Students may raise concerns about privacy and confidentiality. Although these can be addressed (in theory) by strong security policies and procedures, many people are uncomfortable about sensitive data being available in these situations. 2. Suggest three typical business situations where referential integrity avoids data problems. Students might respond with examples similar to the following. Referential integrity would avoid problems such as:  Entering an order for a customer number that does not exist  Deleting a customer who still has open orders pending  Entering a part number in an order for a part that has not been entered in the system yet  Entering a reservation code for a passenger who has not been entered in the system yet  Registering a student for a course that has not been entered into the system yet  Deleting a course that has students in it 3. Consider an automobile dealership with three locations. Data fields exist for stock number, vehicle identification number, make, model, year, color, and invoice cost. Identify the possible candidate keys, the likely primary key, a probable foreign key, and potential secondary keys. To identify candidate keys, students must determine which fields in the record are unique to a single vehicle. Only the stock number and vehicle identification number meet this requirement and could serve as primary keys. Because the stock number probably is smaller and locally generated by the automobile dealership, it might be the best choice for the primary key. It is possible that the model field is in the form of a code, such as Z99 or 4DX, which might serve as a foreign key for a record in the MODEL table. The dealership might want to access, display, or print information by make, model, year, color, or invoice cost — so these fields are potential secondary keys. A record design might resemble the following: VEHICLE (STOCK NO, MAKE, MODEL, YEAR, COLOR, INVOICE COST, LOCATION) In this example, LOCATION would be a foreign key to link the VEHICLE table to the LOCATION table. 4. Do we use lots of codes in our personal and business lives? How many can your class name? Students should have no difficulty in responding based on their personal experience, perception, and imagination. Projects 1. Search the Internet to find information about date formats. Determine whether the date format used in the United States is the most common format. When they search the Internet, students will learn more about the four data storage formats, EBCDIC, ASCII, Unicode, and binary, that are described on pages 433-434. With regard to date fields, students might mention the model established by the International Organization Systems Analysis and Design, Tenth Edition Page 4 of 33 for Standardization (ISO), which requires a format of four digits for the year, two for the month, and two for the day (YYYYMMDD). Also, as the SoccerMom case on page 390 suggests, the United States has many trading partners who use different methods of formatting dates. In all likelihood, the U.S. is in a minority. Two trends have emerged, however: multinational U.S. firms can and do use different formats for international transactions, and many foreign firms with U.S. import and export customers make modifications in the interest of consistency. Finally, point out to students that multinational language and format support is built into many software applications. 2. Visit the IT department at your school or at a local business and determine how the organization uses database management systems. Describe your findings in a memo. Answers will vary, and it might be interesting to have students share the results of their field visit with the class. Students probably will find that most schools and businesses rely on relational databases to manage their operations. In some cases, however, file processing systems are used to handle specific tasks such as payroll or end-of-month processing, and students might find examples of these situations. 3. Use Microsoft Access or similar database software to create a DBMS for the imaginary company called TopText Publishing, which is described in Case In Point 9.1 on page 365. Add several sample records to each table and report to the class on your progress. Students must read the TopText Publishing fact statement to understand the relationships among the entities. An associative entity will be needed to join the M:N relationship between Author and Book. When these entities are transformed into tables, a partial list of fields might include the following: Table Fields (the primary key is underlined) Author Author ID, SSN, Last Name, First Name, Street, City, State, Zip Code, Telephone, Commission Rate Book ISBN No, Number, Title, Copyright Date, Price Book-Author ISBN No, Author ID Systems Analysis and Design, Tenth Edition Page 5 of 33 4. Visit the bookstore at your school or in your area. Interview the manager or store employees to learn more about the business and the entities that are involved in bookstore operations. Remember that an entity is a person, place, thing, or event that affects an information system. Draw an ERD, including cardinality, that describes the bookstore’s operations. Answers will vary. A sample ERD might look like the following: [Show More]

Last updated: 1 year ago

Preview 1 out of 33 pages