Chapter 09

Systems Analysis and Design, Ninth Edition

Page 1 of 33

Systems Analysis and Design Ninth Edition

End of Chapter Solutions CHAPTER NINE

DATA DESIGN ANSWERS TO CASE-SIM: SCR ASSOCIATES CASE-SIM: SCR ASSOCIATES 1. List all the entities that interact with the TIMS system. Start by reviewing the data library, previous e-mail messages, DFDs, and other documentation. Answers will will vary. Based Based on the overall overall fact fact situation, students students might might submit submit a list of entities that includes the following: STUDENT INSTRUCTOR INSTRUCTOR TRAINING ADMINISTRATOR CORPORATE CLIENT COURSES In addition, addition, the ACCOUNTING ACCOUNTING SYSTEM can can be shown shown as an external external entity that interacts with the TIMS system. If students use object modeling in addition to DFDs, they will find that DFD entities tend to be represented as actors in an object model (see Chapter 3 for more information about object modeling). Also, an object model probably includes object classes that appeared as data stores in the process-oriented DFDs. Regardless Regardless of the modeling modeling method, method, it is important important for for students to realize that that an entity can represent an event or transaction, such as an airline reservation. For example, when a student takes a course at SCR, the event that is recorded might be called a REGISTRATION REGISTRATION RECORD, RECORD, or or a TRAINING TRAINING RECORD. RECORD. Students Students should understand understand that these objects are associative entities, which are explained in the chapter. You might call attention to Figure 9-18 on page 408, to point out the associative entity called ORDER LINE. Again, Again, remind students students that that an entity can can be a person, person, place, place, thing, or event event — and an order line is an example that involves two other entities (ORDER and PRODUCT). Also remind students that in data design, associative entities are not always apparent at first and sometimes are identified after the logical analysis process. Also point out that associative entities typically are needed whenever a M:N relationship exists. In these situations, there usually is no common field, and the associative entity joins two other entities.

2. Draw an ERD that shows cardinality relationships among the entities. Send the diagram to Jesse. Note that the the example example contains an an entity called called TRAINING TRAINING RECORD, RECORD, which which includes includes attributes that relate to a specific student and a specific course. Also note the separate


Page 2 of 33

entity called SCHEDULED COURSE, which did not appear in the initial list. An analogy might be made to an airline flight schedule. For example, there are many instances of Flight 890, but there is only one instance of Flight 890 on June 1, 2010. As the answer for Assignment Assignment 3 indicates, indicates, this entity entity requires a combination combination primary primary key.

CORPORATE CLIENT

STUDENT

SENDS

1

M 1

GENERATES

Note: TRAINING RECORD is an associative entity that relates a specific student and a specific course

M TRAINING RECORD

M The SCHEDULED COURSE entity represents a specific course scheduled on specific date

INVOLVES

1 1 COURSES

LISTED ON

M

SCHEDULED COURSE M

1 INSTRUCTOR

TEACHES


Page 3 of 33

The following screen shows a sample set of relationships that could exist among the SCR entities:

3. For each entity, Jesse wants to see table designs in 3NF. Use standard notation format to show the primary key and the other fields in each table. The design for STUDENT, INSTRUCTOR, COURSE, and CORPORATE CLIENT are not difficult. Most students will be able to create the 3NF designs, where all fields depend solely on the primary key, which is represented by a single field. The following examples would be typical: STUDENT (SSN, LASTNAME, FIRSTNAME, ADDRESS, PHONE, BIRTHDATE, CLIENT NO) INSTRUCTOR INSTRUCTOR (INSTRUCTOR (INSTRUCTOR NO, LASTNAME, LASTNAME, FIRSTNAME, FIRSTNAME, OFFICE, OFFICE, EXTENSION EXTENSION)) COURSE (COURSE NO, COURSE NAME, HOURS, TUITION FEE) CORPORATE CLIENT (CLIENT NO, CLIENT NAME, ADDRESS, PHONE, CONTACT PERSON)

Some students might include a “scheduled course” as an entity. This Th is is a refinement that shows a good understanding of the normalization concept. A scheduled course is analogous to a scheduled flight in an airline scenario. For example, Flight 333 might be listed as a record in a flight schedule table (just as a course might be listed in a course 333 on a specific date , such as 12/31/2009, table), but Flight 333 12/31/2009, is a specific specific flight flight that flies


Page 4 of 33

that day. Thus, the following might work well as a design. Notice that the course has a date and an assigned instructor and is in 3NF: SCHEDULED COURSE (SCHEDULED COURSE NO, SCHEDULE DATE, INSTRUCTOR INSTRUCTOR NO, LOCATIO LOCATION) N) The most difficult task is to create the design for the training record that reflects the information about a specific student in a specific course. Fortunately, the analogy is very close to the school registration example that begins on page 417. The textbook example begins with an unnormalized design (Figure 9-29 on page 418) that resembles a paper registration form. The example then moves into 1NF by eliminating the repeating group, and later into 2NF and 3NF. Similarly, the training record for SCR Associates might initially look like this, where every nonkey field depends on the combination key that consists of two fields, SSN and SCHEDULED COURSE NO: TRAINING RECORD (SSN, SCHEDULED COURSE NO, PAID STATUS, STUDENT ACHIEVEMENT) ACHIEVEMENT)

4. Jesse wants to use sample data to populate fields for at least three records in each table. Better get started on this right away. STUDENT

SSN

Lastname

Firstname

Address

Phone

Birthdate

Client No

111-11-1111 Rose

Jan

101 Main Street

123-1111 1/1/81

ABC111

222-22-2222 Jones

Ralph

999 Broad Street

456-2222 12/31/75

ABC111

333-33-3333 Smith

Ellen

444 Cherry Lane

789-3333 8/13/76

DEF222

INSTRUCTOR

Instructor No Lastname Firstname Office

Extension

101

Martin

Mary

C-101

1111

102

Rodgers

Jennie

C-102

2222

103

Wade

Laura

C-103

3333

COURSE

Course No

Course Name

Hours

Tuition Fee

A101

Test Course 1

8

400

B102

Test Course 2

8

400

C103

Test Course 3

16

750

SCHEDULED COURSE

Scheduled Course No

Course No

Scheduled Date Instructor No

Location

20040204A101

A101

2/5/2009

101

Lab A

20040415A101

A101

4/16/2009

102

Lab B

20040422B102

B102

4/23/2009

102

Lab B


Page 5 of 33

TRAINING RECORD

SSN

Student Achievement

Scheduled Course No Paid Status

111-11-1111

20020204A101

Paid

Completed

111-11-1111

20020422B102

Paid

Completed

222-22-2222

20020422B102

Not Paid

Withdrew

CORPORATE CLIENT

Client No ABC111

Client Name

Address

Phone

Contact Person

Acme Company

111 Smith Avenue

111-1111 Sally Parsons

DEF222

Wetlands Trucking

222 Olcott Place

222-2222 Nell Bridges

GHI333

Cloud Nine Services

333 Jamison Square

333-3333 Sam Johnson

ANSWERS TO CHAPTER EXERCISES

Review Questions 1. Explain the main differences between a file processing system and a database system. A file-oriented file-oriented system system processes processes one or more more individual individual data files files using a method called called file processing. processing. A database database consists consists of linked linked data files, files, also called called tables, which which form an overall data structure. Compared to file processing, a database environment offers greater flexibility and and efficiency. efficiency. File processing processing systems still still exist to handle handle specific specific applications, applications, but most information systems today are designed as databases. (Page 392) 2. What is a DBMS? Briefly describe the components of a DBMS. A DBMS provides provides an interface interface between between a database database and and users who need to access access the data. In addition to interfaces interfaces for users, users, database administrators, administrators, and related related systems, systems, a DBMS DBMS also has a data manipulation language, schema, and physical data repository. (Page 396) 3. Describe a primary key, candidate key, secondary key, foreign key, and common field. A primary key is the field field or combination combination of fields that that uniquely and and minimally minimally identifies 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. A common field is an attribute that appears in more than one entity and can be used to link entities in various types of relationships. (Pages 402 - 404) 4. What are entity-relationship diagrams and how are they used? What symbol is used to represent an entity in an ERD? What symbol is used for a relationship? What is cardinality, and what symbols do you use in the crow’s foot notation method? Entity-relationship Entity-relationship diagrams diagrams (ERDs) are are graphical graphical representations representations of of an information information system. They are used to show how each entity relates to the other entities in the system,


Page 6 of 33

but 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. Cardinality describes how an entity relates to other entities in the ERD. In a specific relationship, an entity can be mandator y, meaning it must be present, or optional, meaning it may be present. Crow’s foot notation notation displays displays cardinality cardinality in a graphical graphical manner manner using symbols. A single 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 406-408)

5. What are data warehousing and data mining? Are the terms related? A data warehouse warehouse is an an integrated integrated collection collection of data that that can include include seemingly seemingly unrelated 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. purchases. The terms terms are related in the sense sense that both refer to data data storage and extraction. extraction. The data warehouse, however, describes data organization and storage; while data mining focuses on on methods of obtaining valuable valuable business business knowledge knowledge from from a data warehouse. warehouse. (Pages 430 - 431) 6. What is the criterion for a table design to be in first normal form? How do you convert an unnormalized design to 1NF? A record is is said to be in first normal normal form if it contains contains no repeating repeating groups. To convert convert an unnormalized record you would expand the primary key of the record to include the key of the repeating group. (Page 412) 7. What are the criteria for a table design to be in second normal form? How do you convert a 1NF design to 2NF? 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 would create create a new record record for each field and a possible combinatio combination n of the fields in the primary key. For each new record, designate one field or combination of fields as a primary primary key. key. Place the the remaining remaining fields with the the primary key on which 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. (Page 413-414) 8. What are the criteria for a table design to be in third normal form? How do you convert a 2NF design to 3NF? A record is is in third normal normal form if it it is in second second normal form 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 416)


Page 7 of 33

9. Explain the difference between a logical record and a physical record. A logical record record contains contains fields related to a single person, person, place, place, thing, or event. event. A physical physical record, also known as a block, consists of one or more logical records and is the smallest unit of data accessed by the operating system. Programs and people deal with logical records; computer operating systems and storage devices deal with physical records. (Page 433) 10. How would a specific date, such as September 1, 2011, be represented as an absolute date? An absolute absolute date is the total number number of days from some specific base base date. Using Using a base date of January 1, 1900, the absolute date value of September 1, 2009 is 40057. (Page 435) 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 will vary. Students Students may raise concerns concerns about about privacy and confidentiality. confidentiality. Although these these can be be addressed addressed (in theory) theory) by strong strong security policies and and procedures, 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 Referential integrity would avoid avoid problems problems such as: Entering an order for for a customer customer number number that does not not exist Deleting a customer customer who still has open orders orders pending pending Entering a part number number on an order order for a part part that has not been entered entered in the the system yet Entering a reservation reservation code for for a passenger passenger who has has not been entered entered into into the system yet Registering Registering a student student for a course course that has not not been entered entered into the the system yet yet Deleting a course that that has students 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 — so these fields are potential secondary keys. A record design might resemble the cost — following: VEHICLE (STOCK NO, MAKE, MODEL, YEAR, COLOR, INVOICE COST, LOCATION)


Page 8 of 33

In this example, example, LOCATION LOCATION would would be a foreign foreign key to link the VEHICLE VEHICLE table table to the LOCATION LOCATION table.

4. In the example shown in Figures 9-25 and 9-26 on pages 416 and 417, the 2NF customer table was converted to two 3NF tables. Verify that the four potential problems identified for 2NF tables were eliminated in the 3NF design. In the record record shown in Figures 9-25 9-25 and 9-26, 9-26, the four problems problems have have been eliminated eliminated as as follows: the creation of a new SALES-REP SALES-REP record record allows allows a change change to be made made to the salessalesrep-name without changing the CUSTOMER record. Because the sales-rep-name now is completely dependent on the sales-rep-num and because only the sales-rep-num appears in the CUSTOMER record, a sales rep can have only the name related to that sales-rep-num. A new sales sales rep without without customers customers can be assigned simply simply by adding adding a new record to the the SALES-REP record that will have no effect on the existing customer records. Finally, any customer can be deleted from the CUSTOMER record without losing the data for sales rep formerly assigned assigned to that customer. customer. Projects 1. Search the Internet to find information about data storage formats. Also do research on international 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, EBCDIC, ASCII, ASCII, Unicode, Unicode, and binary, binary, that are described described on pages 433-434. 433-434. With regard to date fields, students might mention the model established by the International Organization 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 SoccerMom case on page page 436 suggests, suggests, the United United States States has many many trading partners who who use different different methods methods of formatting formatting dates. dates. In all likelihood, likelihood, the U.S. 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. As the following following Microsoft Microsoft Excel Excel screen indicates, indicates, you easily can can select from from many different date formats.


Page 9 of 33

2. Visit the IT department at your school or at a local business and determine whether the organization uses file processing systems, DBMSs, or both. Write a brief memo with your conclusions. Answers will will vary, and it might be be interesting interesting to have students students share share the results results of their field visit with with the class. class. Students probably will will find that that most schools schools and businesses businesses rely rely on relational databases to manage their operations. In some cases, however, file processing processing systems systems are used to handle specific specific tasks tasks such as payroll payroll or end-of-month end-of-month processing, processing, and students students might find find examples examples of these these situations. 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 410. 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 might include the the following: following: Table

Fields (the primary key is underlined)

Author

Author ID, SSN, Last Name, First Name, Street, City, State, Zip Code, Telephone, Commission Rate


Page 10 of 33

Table

Fields (the primary key is underlined)

Book

ISBN No, Number, Title, Copyright Date, Price

Book-Author

ISBN No, Author ID

4. Visit the bookstore at your school or a bookstore in your area. Interview the manager or store employees to learn how the operation works and what entities are involved in bookstore operations. Remember that an entity is a person, place, thing, or event that affects the information system. Draw an ERD, including cardinality that describes the bookstore operations. Answers will will vary. A possible possible ERD might look look like the following: following:

ORDER

STUDENT

PLACED BY

M

1

1

LISTS

M

ORDER-ITEM M

COURSE

INCLUDES

1 1 BOOK

M NEEDED FOR


Page 11 of 33

ANSWERS TO APPLY YOUR KNOWLEDGE

1

Pick and Shovel Construction Company Situation: Pick and Shovel Construction Company is a multi-state building contractor specializing in medium-priced town homes. C. T. Scott, the owner, is in your office for the third time today to see how the new relational database project is coming along. Unfortunately, someone mentioned to C. T. that the delay had something to do with achieving “normalization.” “Why is all this normalization stuff so important?” he asks. “The old s ystem worked OK most of the time, and now you are telling me that we need all these special rules. Why is this necessary?” 1. How should you respond to C. T.? Write him a brief memo with your views. Students should provide a brief description of normalization that managers and other non-IT staff can understand. They should point out that flaws in the initial database design usually turn into costly, long-term problems for the organization. A relevant analogy is that the best carpenters cannot turn a poor architectural design into an excellent one. Students should explain that normalization provides a database design that is simple, flexible, and free of data redundancy. 2. Assume that the Pick and Shovel’s Sho vel’s main entities are its customers, employees, projects, and equipment. A customer can hire the company for more than one project, and employees sometimes work on more than one project at a time. Equipment, however, is assigned only to one project. Draw an ERD showing those entities. Note: The following solution combines Assignments 2 and 3. Notice that in addition to the entities named, two associative entities appear: EMPLOYEE-PROJECT ASSIGNMENT ASSIGNMENT contains contains data about about a specific specific employee employee on a specific specific project, project, and EQUIPMENT-AS EQUIPMENT-ASSIGNMENT SIGNMENT contains contains data data about a specific specific piece piece of equipment equipment assigned assigned to a specific project. The concept is similar to the associative entity that appears in the Cutting Edge case study later in the chapter. Also, have students review Figures 9-41 and 9-42, and note that SERVICE-PARTS-DETAIL and SERVICE-LABOR-DETAIL are associative entities similar to the ones in this case study.


CLIENT

Page 12 of 33

EMPLOYEE 1

EQUIPMENT 1

REQUIRES

1

ASSIGNED TO

ASSIGNED TO

M

M

EMPLOYEEPROJECT ASSIGNMENT

EQUIPMENT ASSIGNMENT M

M

INVOLVES

INVOLVES

1 M

PROJECT

1

3. Add cardinality notation to your ERD. See suggested solution to Task 2. 4. Create 3NF table designs. Answers will vary. Students Students might might suggest other fields fields for the entities. entities. A sample sample set of designs might be as follows. Note the combination primary keys in the two associative entities. CLIENT (CLIENT NO, CLIENT NAME, ADDRESS, TELEPHONE, CONTACT PERSON) EQUIPMENT EQUIPMENT (EQUIPMENT (EQUIPMENT NO, NO, DESCRIPTION, DESCRIPTION, PURCHAS PURCHASE E DATE, VALUE, VALUE, VENDOR) EMPLOYEE EMPLOYEE (EMPLOYEE (EMPLOYEE NO, NO, SSN, NAME, ADDRESS ADDRESS,, TELEPHONE, TELEPHONE, BIRTHDATE) BIRTHDATE) PROJECT (PROJECT NO, CLIENT NO, DATE STARTED, STATUS) EQUIPMENT-AS EQUIPMENT-ASSIGNMENT SIGNMENT (EQUIPM (EQUIPMENT ENT NO, PROJECT PROJECT NO, NO, COSTS) EMPLOYEE-PROJ EMPLOYEE-PROJECT-ASSI ECT-ASSIGNMENT GNMENT (EMPLOYEE (EMPLOYEE NO, NO, PROJECT PROJECT NO, HOURS) HOURS)


2

Page 13 of 33

Puppy Palace Situation: Puppy Palace works with TV and movie producers who need dogs that can perform special tricks, such as headstands, somersaults, ladder climbs, and various dog-andpony tricks. Puppy Palace has about 16 dogs and a list of 50 tricks from which to choose. Each dog can perform one or more tricks, and many tricks can be performed by more than one dog. When a dog learns a new trick, the trainer assigns a skill level. Some customers insist on using dogs that score a 10, which is the highest skill level. As an IT consultant, you have been asked to suggest 3NF table designs. You are fairly certain that a M:N relationship exists between dogs and tricks. 1. Draw an ERD for the Puppy Palace information system. See the answer to Assignment 2. 2. Indicate cardinality. Note: The following solution combines Assignments 1 and 2. The objective is to create an ERD that that will represent represent the M:N relationship relationship between between puppies and tricks. tricks. To accomplish accomplish this, it is necessary to use an associative entity (PUPTRICK) that contains data about a specific puppy and a specific trick. With 16 dogs and 50 tricks, this table might contain hundreds of records. The unique primary key will be the combination of a puppy number and a trick number, as shown in the answer to Assignment 4. A second associative entity (CUSTOMER-REQUEST) also is shown. This entity is based on the assumption that one customer might place requests, and that any specific combination in PUPTRICK could be requested by more than one customer. The unique primary key for CUSTOMER REQUEST REQUEST would be be the combination combination of the the customer customer number, number, the trick requested, requested, and and the skill level needed.


PUPPY

Page 14 of 33

CUSTOMER 1

TRICK 1

KNOWS

1

LEARNED IN

SPECIFIES

M

M

CUSTOMERREQUIREMENT M

M

MATCHES

1 M

PUPTRICK

M

3. Identify all fields you plan to include in the dogs and tricks tables. For example, in the dogs table, you might want breed, size, age, name, and so on. In the tricks table, you might want the trick name and description. You will need to assign a primary key in each table. Hint: table. Hint: Before you begin, review some database design samples in this chapter. You might spot a similar situation that requires an associative entity that you can use as a pattern. In addition, remember that numeric values work well in primary key fields. Answers will vary. This This might be be a good time time to point out out the importance importance of breaking breaking a name into several fields, rather than treating it as a single field. Separate fields provide flexibility flexibility in sorting and and report report design and and allow the creation of of professional-looking professional-looking form letters. letters. For example, example, many databases use six separate separate fields fields for names: names: Salutation, Salutation, First Name, Middle Initial, Last Name, Suffix, and Nickname. This arrangement allows you to create create mailing mailing labels with the full name name (for example, example, Col. John John J. Sullivan, Sullivan, Jr.) and begin the greeting with a nickname (for example, Dear Jack). Also, in the PUPPY table, point out that it is better to use a date of birth field that never needs updating, rather than an age field that would need periodic updating.


Page 15 of 33

4. Create 3NF table designs. Note the combinat combination ion primary keys specified specified for the two two associative associative entities. entities. CUSTOMER (CUST NO, SALUTATION, FIRST NAME, MIDDLE INITIAL, LAST NAME, SUFFIX, SUFFIX, NICKNAME, NICKNAME, STREET, STREET, CITY, STATE, STATE, ZIP, ZIP, TELEPHONE) TELEPHONE) PUPPY (PUP NO, NAME, BREED, SIZE, BIRTHDATE, SHOT STATUS, FAVORITE FOOD) TRICK (TRICK NO, TRICK NAME) PUPTRICK (PUP NO, TRICK NO, SKILL LEVEL) CUSTOMER-REQUIREMENT (CUST NO, TRICK NO, SKILL LEVEL, STATUS)

3

Mayville Public Library Situation: Mayville is a rural village with a population of 900. Until now, Mayville was served by a bookmobile from a larger town. The Mayville Village Council has authorized funds for a small public library, and you have volunteered to set up an information system for the library. Assume that the library will have multiple copies of certain books. 1. Draw an ERD for the Mayville library system. The following ERD is from the sample project in Visible Analyst, Student Edition. It is reproduced here as an example of an ERD that uses crow’s foot notation. Note that the entities named BOOK CHECKOUT, CHECKOUT LIST, and COPY have different symbols. If you share this example with students, explain that Visible Analyst recognizes three types of entities. The first is called a fundamental entity (rectangle shape) that describes a real-world object, such as BOOK or USER. The second is an associative entity (rectangle shape with a diamond inside) similar to ones described in this chapter. An associative associative entity, entity, such as BOOK BOOK CHECKOUT, CHECKOUT, exists exists in order order to link and and relate two other entities. Visible Analyst also identifies a third entity type, called an attributive entity (rectangle shape with rounded rectangle inside). An attributive entity is used to show that data is wholly dependent on the existence of another entity. In the example, COPY is wholly dependent on the existence of BOOK. Point out that the ERD also would be correct if COPY were shown as a fundamental entity, but the attributive entity does allow the additional information to be shown.


Page 16 of 33 Is Wait Waiting Fo r Book On

USER

Borrows Book During

Contains

Book Checked Checked O ut By By

BOOK CHECKOUT

Is Part Of Con tains tains Each

BOOK Is Borrowed Involves During

Is Writes Written By

CHECKOUT LIST

Has Is of

COPY

AUTHOR

2. Indicate cardinality. See the answer to Assignment 1. 3. Identify all fields you plan to include in the tables. Answers will vary. Students Students should should be imaginative imaginative and and suggest suggest field attributes attributes that would would be appropriate and typical in a library information system that Mayville Public Library would use. 4. Create 3NF table designs. Answers will vary. The The following is adapted from from the example example used in the Visible Visible Analyst, Student Edition. Note the combination primary keys in several of the tables. USER (USER ID, NAME, BORROW LIMIT, NUMBER CHECKED OUT, FINES DUE) AUTHOR AUTHOR (AUTHOR (AUTHOR ID, AUTHOR AUTHOR NAME, NAME, BIRTHDATE, BIRTHDATE, COUNTRY COUNTRY OF BIRTH, BIRTH, DEATH DATE) DATE) BOOK (CALL (CALL NO, AUTHOR AUTHOR NAME, NAME, ISBN, ISBN, PUBLISHER, PUBLISHER, DATE PUBLISHED) PUBLISHED) COPY (CALL NO, COPY NO, DATE ACQUIRED, CONDITION, CHECKOUT STATUS) BOOK CHECKOU CHECKOUT T (CALL NO, NO, COPY NO, NO, USER ID, ID, DATE OUT, OUT, DATE DUE BACK) CHECKOUT LIST (CALL NO, USER ID, DATE REQUESTED) 4

Western Wear Outfitters Situation: Western Wear is a mail-order firm that offers an extensive selection of casual clothing for men and women. Western Wear plans to launch a new Web site, and the company wants to develop a new set of product codes. Currently, 650 different products exist, with the possibility of adding more in the future. Many products come in various sizes, styles, and colors. The marketing manager asked you to develop an individualized product code that can identify a specific item and its characteristics. Your initial reaction is that it can


Page 17 of 33

be done, but the code might be fairly complex. Back in your office, you give the matter some thought. 1. Design a code scheme that will meet the marketing manager’s stated requirements. Answers will vary. Some Some firms use use a straight straight numeric sequence code, because because it is simple simple and can be assigned automatically by the system. Other firms choose a significant digit code that indicates item, style, color, and size. A combination code might assign two digits for the type of item (10=hat; 64=belt), a dependent number for the style, followed by several digits that show details such as color and size. For example, consider the following design for Western Western Wear: Wear: Item Hat, felt, black, black, size 7 Hat, felt, black black , size 8 Hat, felt, tan, tan, size 7 Belt, dress, dress, black, size 32

Product Code 10-772-B7 10-772-B7 10-772-B8 10-772-T7 10-772-T7 64-385-B32 64-385-B32

2. Write a brief memo to the marketing manager suggesting at least one alternative to the code she proposed, and state your reasons. Answers will vary. Students Students should should be able to support their their choice choice of code with with businessbusinessrelated logic and reasons. You might bring in samples of mail order catalogs that use different code schemes or ask students if they are personally familiar with other codes . 3. Suggest a code scheme that will identify each Western Wear customer. The two most common code schemes are straight sequence codes automatically assigned by the system and codes that combine alphabetic and sequence blocks. An example of the latter might be the first four alphabetic letters of the customer’s last name followed by a 4-, 5-, or 6-digit dependent number. For example, Jason Melville might be MELV28645. Many firms like this approach because it provides some indication of the customer’s customer’s last name (which is valuable both to the customer and the company). 4. Suggest a code scheme that will identify each specific order. Unless there is some other reason, the order entry system automatically assigns order numbers dependently. If it is desirable to code the source of the order (such as W for Web-based, or T for telephone), you could devise a code that includes an identifying character for this purpose. ANSWERS TO CASE STUDIES

New Century Health Clinic Assignments 1. Create an initial entity-relationship diagram for the New Century Health Clinic system. Answers will will vary depending depending on on the logical logical design that that students prepared during the systems analysis phase of the SDLC. Students should understand and apply the guidelines on ERDs and cardinality described in this chapter. The form of the diagram is not


Page 18 of 33

— systems analysts use various styles, and each CASE tool has its own way of important — displaying the information. The main objective is to represent the entities and the relationships among them. A sample diagram follows: PAYS

SU BMITTED BMITTED TO INSURANCE CO

OFFERS INSURANCE PROGRAM

CLAIM

EMPLOYER

PROVIDES INSURANCE COVERAGE PRODUCES APP LIES LIES TO

PAYS HOUSEHOLD

CHARGE

PAYMENT

INCLUDES

PATIENT

IS BASED ON SCHEDULES

CPT CODE CODE A ND

APP T SERV ICE IS

APP OINT OINTMENT

FEE

INCLUDED IN

INVOLVES PROCEDURES USING PE RFORMS

PROVIDER

2. Normalize your table designs. Answers will will vary depending depending on on the logical logical design that that students prepared during the systems analysis phase of the SDLC. Students should apply the guidelines described in this chapter and consider the SWL normalization examples described on pages 447-448. A normalized normalized record record design for the the entities in the ERD required in Assignment Assignment 1 follows: follows: PATIENT (PATIENT NUMBER, PATIENT FIRST NAME, PATIENT LAST NAME, DATE OF BIRTH, HOUSEHOLD NUMBER, RELATIONSHIP CODE) APPOINTMENT APPOINTMENT (APPT (APPT NUMBER, NUMBER, PATIENT PATIENT NUMBER, NUMBER, APPOINTMENT APPOINTMENT DATE, DATE, APPOINTMENT APPOINTMENT TIME, TIME, PROVIDER PROVIDER NUMBER) NUMBER)


Page 19 of 33

APPT SERVICE SERVICE (APPT (APPT SERVICE SERVICE NUMBER, NUMBER, APPT NUMBER, NUMBER, CPT CPT CODE, STATUS, STATUS, FEE, EXPLANATION) PROVIDER (PROVIDER NUMBER, PROVIDER NAME) EMPLOYER (EMPLOYER (EMPLOYER NUMBER, NUMBER, EMPLOYER EMPLOYER NAME, NAME, INSURANCE INSURANCE GROUP GROUP NUMBER) HOUSEHOLD HOUSEHOLD (HOUSEHOLD (HOUSEHOLD NUMBER, NUMBER, HOUSEHOLD HOUSEHOLD LAST LAST NAME, HOUSEHO HOUSEHOLD LD FIRST NAME, HOUSEHOLD ADDRESS, HOUSEHOLD CITY, HOUSEHOLD STATE, HOUSEHOLD ZIP CODE, EMPLOYER NUMBER, HOUSEHOLD HOME PHONE, HOUSEHOLD WORK PHONE, PRIOR BALANCE, CHARGES YTD, HOUSEHOLD HOUSEHOLD PAYMENTS PAYMENTS YTD, YTD, INSURANCE INSURANCE PAYMENTS PAYMENTS YTD) YTD) INSURANCE INSURANCE COMPANY COMPANY (INSURANCE (INSURANCE COMPANY COMPANY NUMBER, NUMBER, INSURANC INSURANCE E COMPANY COMPANY ADDRESS, ADDRESS, INSURANCE INSURANCE COMPANY COMPANY CITY, CITY, INSURANCE INSURANCE COMPANY COMPANY STATE, STATE, INSURANCE INSURANCE COMPANY COMPANY ZIP CODE, CODE, INSURANCE INSURANCE COMPANY PHONE) PHONE) CPT CODE AND FEE (CPT CODE, CPT DESCRIPTION, STANDARD FEE) CLAIMS (CLAIM NUMBER, INSURANCE COMPANY NUMBER, APPT SERVICE NUMBER, NUMBER, CLAIM AMOUNT, AMOUNT, CLAIM DATE) MTD CHARGES CHARGES (CHARGE (CHARGE NUMBER, NUMBER, APPT APPT SERVICE, SERVICE, PATIENT PATIENT NUMBER, NUMBER, CHARGE DATE, FEE) FEE) MTD PAYMENTS PAYMENTS (PAYMENT (PAYMENT NUMBER, NUMBER, HOUSEHOLD HOUSEHOLD NUMBER, NUMBER, PAYMENT PAYMENT DATE, DATE, PAYMENT AMOUNT, PAYMENT SOURCE, INSURANCE COMPANY NUMBER)

Note: Point out that the APPT SERVICE record might have a primary key based on the unique combination of APPOINTMENT and CPT CODE because only one procedure normally would be performed on a particular patient during a specific appointment. Assigning a primary key key of APPT APPT SERVICE SERVICE NUMBER, NUMBER, however, however, makes it easier easier to link this file to other entities. Also mention that assigning primary keys to the Claims, MTD charges, and MTD payments transaction files makes it easier to trace specific transactions and provides an audit trail. 3. If you identified any new entities during normalization, create a final entity-relationship diagram for the system. The ERD in Assignment 2 represents the final ERD for the New Century system. 4. Write a memo for your documentation file that contains your recommendation about whether a file processing or a database environment should be used. Attach copies of your ERD(s) and normalized designs. After reviewing the chapter material, including the SWL case study, most students will recommend that New Century use a database approach in the design of the new system. In responding, students should note the characteristics of a file processing environment and compare them to the database environment. As the text points out, the trend is toward relational database designs that provide a more powerful, flexible, and scalable environment for users throughout an organization. Students also should note that a database approach involves certain disadvantages, including a high degree of security and extensive backup/recovery features. Students also should review Figures 9-3 and 9-4, which highlight the differences between the two approaches. Students can attach the ERD(s) and normalized designs they prepared for Assignments 1, 2, and 3.


Page 20 of 33

Personal Trainer, Inc. Assignments 1. Review the Personal Trainer fact-finding summary in Chapter 4 and draw an ERD with cardinality notation. Assume that system entities include members, activities and services, and fitness instructors. Students should focus on three main entities: members, activities and services, and fitness instructors. In addition, the associative entity called training record is necessary to link students to activities/services, as shown in the following sample ERD. 2. Design tables in 3NF. As you create the database, include various codes for at least three of the fields. Sample 3NF records might look like this. Notice that the associative entity, TRAINING RECORD, RECORD, has a combination combination primary primary key composed composed of three fields fields that uniquely uniquely identify identify a specific student in a specific course on a specific date. Note: answers on codes will vary. Encourage students to review the material on pages 423425 and select suitable codes. MEMBER (MEMBER (MEMBER ID, LASTNAME, LASTNAME, FIRSTNA FIRSTNAME, ME, ADDRESS, ADDRESS, PHONE) PHONE) INSTRUCTOR INSTRUCTOR (INSTRUCTO (INSTRUCTOR R ID, LASTNAME, LASTNAME, FIRSTNAME, FIRSTNAME, ADDRESS, ADDRESS, PHONE) PHONE) ACTIVITY ACTIVITY (ACTIVITY (ACTIVITY NO, COURSE COURSE NAME, NAME, DATE, HOURS, HOURS, TUITION TUITION FEE) TRAINING RECORD (MEMBER ID, ACTIVITY NO, DATE, FITNESS LEVEL)

ACTIVITY

MEMBER

OR SERVICE

1

1

ENROLLS

CREATES

M

TRAINING RECORD

M

M

1 ASSIGNED TO

INSTRUCTOR


Page 21 of 33

3. Use sample data to populate the fields for at least three records in each table. MEMBER MemberID

Lastname

Firstname

Address

Phone

111-11-1111

Wade

Gregg

101 Main Street

123-1111

222-22-2222

Copeland

Amanda

999 Broad Street

456-2222

333-33-3333

Grosjean

Jim

444 Cherry Lane

789-3333

INSTRUCTOR InstructorID

Lastname

Firstname Address

Phone

101

Burton

Andrew

203 Court Street

123-1111

102

Dallas

Jim

99 Highway 66 East

456-2222

103

Xenos

Laura

3 Pointe Plaza

789-3333

ACTIVITY ACTIVITY

ActivityNo Course Name

Date

Hours

Tuition Fee

A101

Test Course 1

9/1/2009

10

25

B102

Test Course 2

10/1/2009

12

35

C103

Test Course 3

11/1/2009

14

45

TRAINING RECORD MemberID

Activity No

Date

Fitness Level

111-11-1111

A101

9/1/2009

A-1

111-11-1111

B102

10/1/2009 A-2

222-22-2222

B102

10/1/2009 A-3

4. Recommend a date format for the new system. Should Personal Trainer adopt a single international standard, or should the format be determined by the country in which the center is located? Write a message to Susan with your recommendation. Personal Trainer faces an issue that has become important as many companies enter the international marketplace. Should a small multinational company attempt to maintain a standard date format, or is it better to adapt to the formats used in the countries where it operates? Although the Personal Trainer situation involves dates, it could involve currency formats, numeric numeric representatio representations, ns, weights and and measures, measures, or any other conventions conventions that that differ from one country to another. There is no standard answer to this question. Personal Trainer must use the local format in all external relationships in order to avoid confusion and operational problems in Canada and to maintain customer satisfaction. But what about about internal internal communicatio communications? ns? Many users users might feel feel that the best best approach approach is a program or macro that automatically translates dates into the format best understood by the recipient. For example, in a communication from Personal Trainer headquarters in Chicago to the Toronto supercenter about the Canada Day holiday on July 1, 2009, the date would appear in Canadian format as 1.7.2009 — even though the Chicago user entered it as 7/1/2009. Others might feel that Personal Trainer should adopt a consistent


Page 22 of 33

standard for all internal communications. If so, perhaps the ISO 9000 format would be acceptable.

FastFlight Airlines Assignments 1. Create an ERD for the reservations system. Sample ERD:

PASSENGER

1

REQUESTS

M 1

M RESERVATION

M

ENTERED

1 AGENT

FOR

FLIGHT


Page 23 of 33

2. Create 3NF table designs for the system. Sample 3NF table designs follow: FLIGHT (FLIGHT NUMBER, ORIGIN, DESTINATION, DEPART TIME, ARRIVE TIME, FREQUENCY) RESERVATION RESERVATION (FLIGHT (FLIGHT NUMBER, NUMBER, FLIGHT FLIGHT DATE, DATE, PASSENGER PASSENGER NUMBER, NUMBER, RESERVATION RESERVATION CODE, CODE, SEAT NUMBER, NUMBER, AGENT AGENT NUMBER) NUMBER) PASSENGER (PASSENGER NUMBER, PASSENGER NAME) RESERVATION RESERVATION AGENT AGENT (AGENT (AGENT NUMBER, NUMBER, AGENT NAME) NAME) 3. For each of the entities identified, design tables and identify the possible candidate keys, the primary key, a probable foreign key, and potential secondary keys. See the answer to Assignment 4. 4. Use sample data to populate the fields for three records. Note: The solutions for Assignments 3 and 4 have been combined. The first part of the solution shows the table design and keys that were created in Assignment 3. The second part of the solution solution shows shows the sample sample data. a. FLIGHT (FLIGHT NUMBER, ORIGIN, DESTINATION, DEPART TIME, ARRIVE TIME, FREQUENCY) Candidate keys: Flight number is the only candidate key. No other field will identify flights uniquely. Primary key: Flight number is the primary key. Foreign key: This record requires no foreign key. Secondary keys: Potential secondary keys include ORIGIN, DESTINATION, DEPART TIME, ARRIVE TIME, and frequency. FastFlight might want to access or display data on the basis of any of these fields, or a combination of them. Using this design, sample data for three FLIGHT records might look like this: FLIGHT NUMBER

ORIGIN

DESTINATION

DEPART TIME

ARRIVE TIME

FREQUENCY

368 890 900

AUG HPN AUG

NAS AUG AUG

0923 1410 1535

1017 1555 1620

TTH DLY TTH

b. RESERVATION (FLIGHT NUMBER, FLIGHT DATE, PASSENGER NUMBER, RESERVATION RESERVATION CODE, CODE, SEAT NUMBER, AGENT NUMBER) NUMBER) Candidate keys: There are no candidate keys other than the combination of FLIGHT NUMBER, FLIGHT FLIGHT DATE, DATE, and PASSENGE PASSENGER R NUMBER. NUMBER. RESERVATION RESERVATION CODE CODE is a unique value assigned to a primary key combination. Primary key: The combination of FLIGHT NUMBER, FLIGHT DATE, and PASSENGER NAME is the primary key. key. Foreign key: PASSENGER NUMBER and AGENT NUMBER are foreign keys. Secondary keys: PASSENGER NUMBER, AGENT NUMBER, and SEAT NUMBER also can be used as secondary keys. Using this design, sample data for three RESERVATION records might look like this: FLIGHT NUMBER

FLIGHT DATE

PASSENGER NUMBER

RESERVATION CODE

SEAT NUMBER

AGENT NUMBER

Systems Analysis and Design, Ninth Edition FLIGHT NUMBER

FLIGHT DATE

PASSENGER NUMBER

368 890 890

7/6/07 6/1/07 6/1/07

ALER1098 BROT4657 BROT6182

Page 24 of 33 RESERVATION CODE

AXQTBC SUVZAB RHJBVS

SEAT NUMBER

AGENT NUMBER

2A 11A 11B

412 686 686

c. PASSENGER (PASSENGER NUMBER, PASSENGER NAME) Candidate keys: There are no candidate keys other than PASSENGER NUMBER. Primary key: PASSENGER NUMBER is the primary key. Foreign key: This record requires no foreign key. Secondary keys: PASSENGER NAME is a secondary key. Using this design, sample data for three PASSENGER records might look like this: PASSENGER NUMBER

ALER1098 BROT4657 BROT6182

PASSENGER NAME

Alerion, Nathan Brothers, Barbara Brothers, Sam

d. RESERVATION AGENT (AGENT NUMBER, AGENT NAME) Candidate keys: There are no candidate keys other than AGENT NUMBER. Primary key: AGENT NUMBER is the primary key. Foreign key: This record requires no foreign key. Secondary keys: AGENT NAME is a secondary key. Using this design, sample data for three RESERVATION AGENT records might look like this: AGENT NUMBER NUMBER

412 014 686

AGENT NAME NAME

Alexis, Ramon Madre, Jefferson Vasquez, Wanda

ANSWERS TO CHAPTER CAPSTONE CASE: SOFTWEAR, LIMITED (SWL)

1. Rick asked you to help him put together a brief progress update for Michael Jeremy and several other top managers. Specifically, Rick wants you to explain the concept of normalization without using a lot of technical jargon. Rick wants you to summarize the concept using plain English and simple examples. To help Rick, students must understand the normalization rules in the text and write a summary in simple language that managers will understand. Explain to students that managers expect important company data to be free of problems and complexities. Managers Managers will understand understand that data data is a valuable valuable corporate corporate resource resource that that must be accurate, easy to maintain, and adaptable in a changing business environment. The bottom line is that a good design will be significantly less expensive to maintain and help boost customer satisfaction. Although they probably are not interested in the details, managers should know that data must be organized in a special way to achieve these objectives. The process of organizing the data is called normalization.


Page 25 of 33

The normalization process creates specific requirements for data that produce better quality results, just as companies set standards for quality control objectives. First normal form is better than unnormalized data, second normal form is even better, and third normal form is what most businesses use in their day-to-day operations. Tell students that managers might ask for a specific example of how normalization works, and they should be able to respond with a relatively simple answer. Although the next section section goes somewhat somewhat beyond the SWL Team Tasks assignment, it can help students test their understanding of normalization. The instructor should be prepared to display specific examples of the record designs described. Although normalization normalization can be be complex, complex, some systems systems analysts analysts like to explain explain the concept with the following phrase, which is easy to remember: in a properly designed record in third normal form, all nonkey fields depend on the key, the whole key, and nothing but the key. The following example demonstrates how this phrase would apply: Suppose you wanted to design a system to track your service reps and the customers assigned to them. You could set up a table with one record per service rep and with fields for as many customers as you estimate a service rep could possibly have. The service rep number would serve as the primary key. Suppose the current policy was to assign a maximum of 20 customers to each service rep, so you designed the record with 20 fields. Now ask, what would happen if the company suddenly decided to raise the limit to 25? The current database would be useless and you would have to start all over. This is an example of an unnormalized record, because it contains a repeating group — the customer fields. First normal form: Suppose that you moved the customer data into a separate table with one record per customer, using the customer number as a primary key. In each customer record, include a field called service rep number, which can relate customers to their assigned reps and to the service rep’s name. You have removed the repeating group and created two records in first normal form. Second normal form: To be in second normal form, a record must be in first normal form and all fields must depend on the entire primary key — not just a portion of it. Therefore, if a 1NF record has a single primary key such as the service rep number or a customer number to identify each individual uniquely, the record is automatically in second normal form. If a combination of two or more fields forms a primary key that uniquely identifies each record, however, then the record is in second normal form only if all the nonkey fields depend on the entire primary key — not just part of it. Think of it this way: in second normal form, all fields must depend upon the key, the whole key. In this case, you would have to remove all the fields except the ones that depend on the entire primary key key and place place them into their own separate separate table. table. Third normal form: Now suppose you have a table in second normal form, where all the nonkey fields depend on the entire primary key, but there is one nonkey field that also depends on another nonkey field. For example, in the customer record described above, the service rep number and service rep name both depend on the customer number, but the service rep name also depends on the rep number, which creates redundancy, multiple data entries, and possible inconsistency. To get to third normal form, you must remove remove the service service rep name name to a separate separate table, table, which can contain contain other other information about the rep, such as telephone number or location. Remember, to be in


Page 26 of 33

third normal form, all nonkey fields must depend on the key, the whole key, and nothing but the key.

2. At SWL, each employee is assigned to a specific department. Employees from several departments often are assigned to special project teams, however, when a new product is launched or for major marketing events. Carla wants to develop a project management system to track the projects, employees assigned, and accumulated project hours. She believes that employees and projects are in a M:N relationship. She showed you an initial design where all data is stored in a single table: PROJECT DATA (PROJECT-NUMBER, PROJECT-NAME, START-DATE, PROJECT-STATUS, (EMPLOYEE-NUMBER, EMPLOYEE-NAME, JOB-TITLE, DEPT-NUMBER, DEPT-NAME, PROJECT-HOURS)) How would you describe Carla’s design? Carla’s design is unnormalized, because it contains a repeating group. When you analyze the design, you see that she has put all the fields into a single table, and that the inner set of parentheses parentheses contains contains the repeating repeating group group of six fields. fields. 3. Carla wants you to create an ERD, including cardinality, for the project management system. She says that you probably will need to add an associative entity. At this point point students should should realize realize that some some type of of associative associative entity might might be needed needed to relate employees to projects in a M:N relationship. Refer to the M:N examples on page 407 to remind students that in this type of relationship, an additional entity called an associative entity is needed. If the ERD is drawn with the associative entity included, it might look like this:


Page 27 of 33

DEPARTMENT

1

EMPLOYS

M

EMPLOYEE

PROJECT

1

1

CHARGES

M

INCURS

PROJECT-HOURS

M

4. After you create the ERD in the previous step, design a table for each entity, in third normal form. By following following the examples examples in the the chapter, chapter, students should should be able able to construct construct four tables, tables, all of which are in 3NF: PROJECT (PROJECT-NUMBER, PROJECT-NAME, START-DATE, PROJECTSTATUS) EMPLOYEE EMPLOYEE (EMPLOYEE-NU (EMPLOYEE-NUMBER, MBER, EMPLOYEE-NA EMPLOYEE-NAME, ME, DEPARTMENT-NU DEPARTMENT-NUMBER, MBER, JOB-TITLE) JOB-TITLE) PROJECT HOURS (PROJECT-NUMBER, EMPLOYEE-NUMBER, PROJECT-HOURS) DEPARTMENT DEPARTMENT (DEPARTMEN (DEPARTMENT-NUMBER T-NUMBER,, DEPARTMENT-NAM DEPARTMENT-NAME) E)


Page 28 of 33

Manage the SWL Project You have been asked to manage SWL’s new information system project. One of your of your most important activities will be to identify project tasks and determine when they will be performed. Before you begin, you should review the SWL case in this chapter. Then list and analyze the tasks, as follows: LIST THE TASKS Start by listing and numbering at least ten tasks that the SWL team needs to perform to fulfill the objectives of this chapter. Your list can include SWL Team Tasks and any other tasks that are described in this chapter. For example, Task 3 might be to Identify all entities, and Task 6 might be to Create an initial ERD. An answer might include include tasks listed listed in the Chapter Chapter Introduction Introduction Case, and other other examples examples of tasks found in the SWL case or elsewhere in the chapter. A sample answer follows:

Task List Review data data design design terms and concepts concepts,, including normal normal forms and cardinality cardinality Determine Determine whether whether the system requires a relational database database Identify all all entities Determine Determine the relationships relationships among among system system entities entities Draw an ERD ERD that includes includes cardinality cardinality notation notation For each entity, list all necessary fields and identify a primary key Create table designs in third normal form Consider codes that might be used when developing the system Consider any physical design issues Consider suitable data control measures          

ANALYZE THE TASKS Now study the tasks to determine the order in which they should be performed. First identify all concurrent tasks, which are not dependent on other tasks. In the example shown in Figure 9-52, Tasks 1, 2, 3, 4, and 5 are concurrent tasks, and could begin at the same time if resources were available. Other tasks are called dependent tasks, because they cannot be performed until one or more earlier tasks have been completed. For each dependent task, you must identify specific tasks that need to be completed before this task can begin. For example, you would want to identify all the entities before you could create an initial ERD, so Task 6 cannot begin until Task 3 is completed, as Figure 9-52 shows. Answers will will vary. A sample sample answer answer follows:

Concurrent Tasks 1. Review data design terms and concepts, including normal forms and cardinality 2. Determine whether the system requires a relational database 3. Identify all entities

Dependent Tasks 4. Determine the relationships among system entities 5. Draw an ERD that includes cardinality notation 6. For each entity, list all necessary fields and identify a primary key 7. Create table designs in third normal form


Page 29 of 33 8. Consider codes that might be used when developing the system 9. Consider any physical design issues 10. Consider suitable data control measures

For each dependent task, a sample list of predecessor tasks follows:

Dependent Tasks 4. Determine the relationships relationship s among system entities 5. Draw an ERD that includes cardinality notation 6. For each entity, list all necessary fields and identify a primary key 7. Create table designs in third normal form 8. Consider codes that might be used when developing the system 9. Consider any physical design issues 10. Consider suitable data control measures

Predecessor Tasks 1,2,3 1,2,3 5 6 7 7 7

Chapter 3 describes project management tools, techniques, and software. To learn more, you can visit the Features section on your Student Study Tool CD-ROM, or visit the Management Information Systems CourseMate Web site at www.cengagebrain.com and locate the project management resources library for this book. On the Web, Microsoft offers demo versions, training, and tips for using Project 2010. You also can visit the OpenWorkbench.org site to learn more about this free, open-source software. The Features section on the Student Study Tool CD-ROM includes Web links for Microsoft Project and Open Workbench, and a user guide for Open Workbench. Instructors can demonstrate Microsoft Project if it is available, and show students how to download and install Open Workbench. Instructors also can create additional assignments that use project management software skills in a systems development environment


Page 30 of 33

ANSWERS TO READY FOR A CHALLENGE Instructor Note: Remind students students that to view sample sample answers answers to Practice Practice Tasks, they they can visit the Management Information Systems CourseMate Web site at www.cengagebrain.com www.cengagebrain.com , , navigate to the resources for this chapter, and locate Ready for a Challenge.


Page 31 of 33

The ERD shown above joins the AUTHOR and GAME tables with an associative entity, called AUTHORS and and GAMES. GAMES. The crow-foot crow-foot notation notation is explained explained in Chapter 9 of the textbook. textbook. As shown, one and only one author can create many games, or none. On the other hand, one and only one game can be created by at least one, or many authors.

Microsoft Access was used to create a database with the tables, fields, and relationships shown above.

A one-letter one-letter code has has 26 possible possible values. values. A two-letter two-letter code has has 26 x 26, 26, or 676 possible values. values. A three-letter three-letter code has has 26 x 26 x 26, or 17,576 values values – and so on. As the following following table table shows, a six-letter code would have have 308,915,776 308,915,776 possible possible values values .


Page 32 of 33

Have students review the design. What about normalization? The database seems to be in 2NF, because each nonkey field depends on the whole key in each table. But what about the GAME table? Notice that there is a field for Category Code , and another field for Category Description . This suggests that Category Description is dependent on the primary key, which is Product ID , but is also dependent on another field: Category Code . That means the table is in 2NF, but in not 3NF. To be in 3NF, each field must depend on the key, the whole key, and nothing but the key. As the textbook textbook explains, explains, the solution is to remove any fields that that depend depend on another another nonkey nonkey field, and place them in a new table where the nonkey field is the primary key. In the example shown below, a new table called CATEGORY has been created, with a primary key of Category Code . Now the design is in 3NF design, because the nonkey field called Category Description now depends on the key, the whole key, and nothing but the key.


Page 33 of 33

The theoretical answer is more than 4.5 billion combinations, which will surprise many students. That is more than half the population of the Earth! We know that a four-letter code has 26 x 26 x 26 x 26, or 456,967 possible values. Adding four four numeric digits digits would would multiply that total by 10,000, 10,000, for a grand total total of 4,569,760,000. Of course, the answer is only theoretical, because many (perhaps most) letter combinations will not be used at all. The code might also be impractical, because popular combinations, such as SMIT (which could stand for Smith, Smithson, Smithwick, Smithson, and so on) would be limited to 10,000 usages each. In a truly huge population, like the census, that could be a problem. This might be a good time to challenge students to suggest other codes that would be both practical and easy to use.

Chapter 09

Recommend Documents