Microsoft Office 2003 - Anvari



Microsoft Office Access 2007

Chapter Three: Maintaining a Database

A Guide to this Instructor’s Manual:

We have designed this Instructor’s Manual to supplement and enhance your teaching experience through classroom activities and a cohesive chapter summary.

This document is organized chronologically, using the same heading in red that you see in the textbook. Under each heading you will find (in order): Lecture Notes that summarize the section, Figures and Boxes found in the section, if any, Teacher Tips, Classroom Activities, and Lab Activities. Pay special attention to teaching tips, and activities geared towards quizzing your students, enhancing their critical thinking skills, and encouraging experimentation within the software.

In addition to this Instructor’s Manual, our Instructor’s Resources CD also contains PowerPoint Presentations, Test Banks, and other supplements to aid in your teaching experience.

For your students:

Our latest online feature, CourseCasts, is a library of weekly podcasts designed to keep your students up to date with the latest in technology news. Direct your students to , where they can download the most recent CourseCast onto their mp3 player. Ken Baldauf, host of CourseCasts, is a faculty member of the Florida State University Computer Science Department where he is responsible for teaching technology classes to thousands of FSU students each year. Ken is an expert in the latest technology and sorts through and aggregates the most pertinent news and information for CourseCasts so your students can spend their time enjoying technology, rather than trying to figure it out. Open or close your lecture with a discussion based on the latest CourseCast.

Table of Contents

|Chapter Objectives |2 |

|AC 138: Introduction |2 |

|AC 138: Project — Maintaining a Database |2 |

|AC 140: Plan Ahead Box (Critical Thinking): Database Maintenance Guidelines |3 |

|AC 140: Starting Access |3 |

|AC 141: Updating Records |3 |

|AC 148: Filtering Records |4 |

|AC 149: Plan Ahead Box (Critical Thinking): Determine Whether You Should Filter Records |4 |

|AC 156: Changing the Database Structure |5 |

|AC 162: Mass Changes |6 |

|AC 165: Validation Rules |6 |

|AC 178: Changing the Appearance of a Datasheet |7 |

|AC 178: Plan Ahead Box (Critical Thinking): Determine Whether Changes to the Format of a Datasheet Are Desirable |7 |

|AC 181: Multivalued Fields in Queries |8 |

|AC 185: Referential Integrity |8 |

|AC 185: Plan Ahead Box (Critical Thinking): Identify Related Tables in Order to Implement Relationships Between the |8 |

|Tables | |

|AC 192: Ordering Records |9 |

|AC 192: Special Database Operations |9 |

|End of Chapter Material |10 |

|Glossary of Key Terms |11 |

Chapter Objectives

Students will have mastered the material in Chapter Three when they can:

• Add, change, and delete records

• Search for records

• Filter records

• Update a table design

• Format a datasheet

• Use action queries to update records

• Specify validation rules, default values, and formats

• Create and use single-valued and multivalued Lookup fields

• Specify referential integrity

• Use a subdatasheet

• Sort records

AC 138: Introduction

LECTURE NOTES

• Discuss what it means to maintain a database

o Modifying data to keep it up-to-date

o Mass update or deletions

o Restructuring the database

o Filtering records

o Changing the datasheet appearance

o Backing up and compacting the database

CLASSROOM ACTIVITIES

1. Class Discussion: Ask students what type of maintenance a student database requires (entering grades, changing addresses, and so on).

2. Critical Thinking: If a database is not maintained or if incorrect data is entered into the database, serious problems can occur. What problems could occur if a student database is not maintained? What problems could occur if a database that maintains financial data (such as a credit card database) has incorrect data?

AC 138: Project — Maintaining a Database

LECTURE NOTES

• Use Figure 3-1 to illustrate the various types of activities involved in maintaining a database such as JSP Recruiters

• Review the tasks that will be covered in this chapter

o Adding, changing, and deleting records

o Filtering records

o Changing the structure of a table

o Making mass changes to a table

o Creating validation rules

o Changing the appearance of a datasheet

o Enforcing relationships by creating referential integrity

o Ordering records

o Performing special database operations such as backing up and compacting a database

FIGURE: 3-1

TEACHER TIPS

Before students begin the activities in this project, it is a good idea for them to make a backup copy of the database. The Special Database Operations section that begins on page AC 192 illustrates how to back up a database. The BTW on page AC 192 explains what to do if students receive a compact error message when they open a database and.

AC 140: Plan Ahead Box (Critical Thinking): Database Maintenance Guidelines

LECTURE NOTES

• Discuss the general database maintenance guidelines

o Determine when it is necessary to add, change, or delete records in a database

o Determine whether you should filter records

o Determine whether additional fields are necessary or whether existing fields should be deleted

o Determine whether validation rules, default values, and formats are necessary

o Determine whether changes to the format of a datasheet are desirable

o Identify related tables in order to implement relationships between the tables

TEACHER TIPS

These guidelines are intended to help students with their critical thinking skills. Students should understand the reasons for maintaining a database. Spend a significant amount of class time reviewing each of these guidelines and use example databases, such as a student database or a database of credit card information to which students can relate. These points are emphasized at appropriate locations in the chapter. Also, Cases and Places 3, 4, and 5 challenge students to apply these guidelines to maintaining a database.

AC 140: Starting Access

LECTURE NOTES

• For figures that match those in the book, change screen resolution to 1024 x 768

• Review the steps to start Access

• Review the steps to open a database

AC 141: Updating Records

LECTURE NOTES

• Describe how to create a simple form using Figures 3-2 through 3-4

• Use Figure 3-5 to illustrate adding records using a form

• Define searching and use Figures 3-6 and 3-7 to illustrate searching for a record

• Use Figure 3-8 to describe how to update the contents of a record

• Using Figures 3-9 and 3-10 to review how to delete a record

FIGURES: 3-2, 3-3, 3-4, 3-5, 3-6, 3-7, 3-8, 3-9, 3-10

BOXES:

1. Other Ways: Encourage your students to explore other ways of adding a record.

2. Other Ways: Encourage your students to explore other ways of searching for a record.

3. Other Ways: Encourage your students to explore other ways of deleting a record.

TEACHER TIPS

If students find that while typing data into a field, they are overwriting the existing data instead of inserting data, it is because they are in Overtype mode rather than Insert mode. Use the insert key on the keyboard to toggle between Overtype mode and Insert mode.

Explain that when you delete the records, they are removed permanently from the database. In some database management systems, deleting records is a two-stage process. First, records are marked for removal. The records remain in the table but cannot be updated and will not be retrieved in searches. Records are removed permanently from the database in a separate step. It is important to back up a database before adding, changing, or deleting records.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) In the database environment, what does searching mean? (Answer: Looking for records that satisfy some criteria)

2) Where do I find the Find button? (Answer: On the Home tab on the Ribbon)

LAB ACTIVITIES

1. Have students use Access Help to find keyboard shortcuts that can be used when editing data.

AC 148: Filtering Records

LECTURE NOTES

• Discuss the four types of filters available in Microsoft Access

• Use Figures 3-11 through 3-13 to illustrate using Filter By Selection

• Describe how to toggle a filter using Figure 3-14

• Review the steps to clear a filter

• Use Figures 3-15 through 3-17 to describe common filters

• Using Figures 3-18 through 3-20 discuss using Filter By Form for more complex criteria

• Describe how to use Advanced Filter/Sort using Figures 3-21 and 3-22

• Review how filters and queries are related

o Can apply a filter to the results of a query just as you can to a table

o Can save filter settings as a query when you use either Filter By Form or Advanced Filter/Sort to create the filter

o Can restore filter settings

FIGURES: 3-11, 3-12, 3-13, 3-14, 3-15, 3-16, 3-17, 3-18, 3-19, 3-20, 3-21, 3-22

BOXES:

1. BTW. Using Wildcards in Filters. Review the wildcards that you can use in filters.

TEACHER TIPS

The filter by selection method produces a subset of the table. This is useful when you need to update a field in several records with the same value.

Make sure students understand the difference between the Toggle Filter button and the Clear All Filters button. The Toggle Filter button redisplays all records but does not clear any filters that have been applied.

LAB ACTIVITIES

1. Have students use Access Help to find other wildcards that can be used in filters.

AC 149: Plan Ahead Box (Critical Thinking): Determine Whether You Should Filter Records

• Consider creating a query if you frequently will want to display records that satisfy precisely the same criterion

• When viewing data in a datasheet or form, it is easier to create a filter than to create a query to restrict the records to view

• If you create a filter and decide you need to use it in the future, you can save it as a query

• Decide which type of filter to use

AC 156: Changing the Database Structure

LECTURE NOTES

• Define structure

• Review the steps to delete a field

• Describe how to add a new field using Figure 3-23

• Define Lookup field and use Figures 3-24 through 3-28 to illustrate creating a lookup field

• Define multivalued fields and review the abbreviations and descriptions in Table 3-1

• Review the steps to create a multivalued field

• Review the steps to save changes and close a table

• Discuss how to modify single or multivalued lookup fields

FIGURES: 3-23, 3-24, 3-25, 3-26, 3-27, 3-28

BOXES:

1. BTW: Moving a Field in a Table Structure. Explain how to move a field in a table structure.

2. BTW: Multivalued Fields. Review the problem of upsizing a database that contains multivalued fields.

3. BTW: Modifying Table Properties. Describe how to modify table properties.

4. Other Ways: Encourage your students to explore other ways to add a new field to a table structure.

TEACHER TIPS

One of the major advantages of a database management system is the ease with which new fields can be added. If a form exists for a table and you add a field, the form will not show the new field, however. If you delete the form and re-create it, the new field will appear. In In the Lab 2, students delete the split form they created in Chapter 1 and re-create the form with the Item Type field.

CLASSROOM ACTIVITIES

1. Class Discussion: Ask students for other reasons that would cause an organization such as JSP Recruiters to change a database structure.

AC 162: Mass Changes

LECTURE NOTES

• Define the four types of action queries: update, delete, append, and make-table

• Describe how to use an update query using Figures 3-29 and 3-30

• Use Figures 3-31 and 3-32 to illustrate using a delete query

• Review the steps to use an append query

• Describe how to use a make-table query

FIGURES: 3-29, 3-20, 3-31, 3-32

BOXES:

1. Other Ways: Encourage your students to explore other ways to create an update query.

2. Other Ways: Encourage your students to explore other ways to create a delete query.

TEACHER TIPS

Students use an append query in the Extend Your Knowledge exercise. They also use action queries in Cases and Places 5.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) What type of action query allows you to add the results of a query to an existing table? (Answer: Append query)

2) What type of action query allows you to add the results of a query to a new table? (Answer: Make-table query)

AC 165: Validation Rules

LECTURE NOTES

• Define validation rules and validation text

• Define required field, range of values, and default value

• Use Figure 3-33 to illustrate specifying a required field

• Use Figure 3-34 to illustrate specifying a range

• Describe specifying a default value using Figure 3-35

• Illustrate specifying a collection of allowable values using Figure 3-36

• Use Figure 3-37 to discuss specifying a format

• Use Figure 3-38 to illustrate saving validation rules, default values, and formats

• Discuss the effects on updating a table that contains validation rules using Figures 3-39 through 3-43

• Use Figures 3-44 through 3-46 to describe using a lookup field

• Use Figures 3-47 through 3-50 to illustrate using a multivalued lookup field

• Describe how to resize a column in a datasheet using Figures 3-51 through 3-53

• Using Figures 3-54 through 3-56 explain how to add totals to a datasheet

• Review the step to remove totals from a datasheet

FIGURES: 3-33, 3-34, 3-35, 3-36, 3-37, 3-38, 3-39, 3-40, 3-41, 3-42, 3-43, 3-44, 3-45, 3-46, 3-47, 3-48, 3-49, 3-50, 3-51, 3-52, 3-53, 3-54, 3-55, 3-56

BOXES:

1. BTW: Using Wildcards in Validation Rules. Explain the use of wildcards in validation rules.

2. BTW: Changing Data Types. Explain why you would want to change a data type.

3. Other Ways: Encourage your students to explore other ways to change a column size.

TEACHER TIPS

If students find themselves stuck in a record when attempting to update it, because Access says the value is invalid, and they cannot find a way to correct the value, this probably means that the validation rule being referenced is faulty. Carefully note which validation rule is causing the problem. Press the esc key to erase the new record completely. Then, check and correct the offending validation rule.

The ability to add totals to the datasheet is a new feature in Access 2007.

CLASSROOM ACTIVITIES

1. Class Discussion: Ask students if they have ever been affected by invalid data in a database. If so, how?

2. Critical Thinking: When a validation rule is violated, Access displays the text in the Validation Text property box. Why is it important that this text is helpful to the user?

LAB ACTIVITIES

1. Have students use Access Help to find other wildcards that can be used in validation rules.

2. Have students test the validation rules by copying their database and making intentional errors to the copied database.

AC 178: Changing the Appearance of a Datasheet

LECTURE NOTES

• Use Figure 3-57 to review all the buttons available to change the appearance of a datasheet

• Use Figures 3-58 and 3-59 to illustrate changing gridlines in a datasheet

• Use Figures 3-60 and 3-61 to illustrate changing the colors and font in a datasheet

• Describe how to use the Datasheet Formatting dialog box using Figure 3-62

• Review the steps to close the datasheet without saving the format changes

FIGURES: 3-57, 3-58, 3-59, 3-60, 3-61, 3-62

AC 178: Plan Ahead Box (Critical Thinking): Determine Whether Changes to the Format of a Datasheet Are Desirable

LECTURE NOTES

• Review the questions you should ask when considering changes to the format of a datasheet

o Would totals or other calculations be useful?

o Would different gridlines make the datasheet more useful?

o Would alternating colors in the rows make them easier to read?

o Would a different font and/or font color make the text stand out?

o Is the font size appropriate?

o Is the column spacing appropriate?

AC 181: Multivalued Fields in Queries

LECTURE NOTES

• Use Figures 3-63 and 3-64 to illustrate querying a multivalued field showing multiple values on a single row

• Use Figures 3-65 and 3-66 to illustrate querying a multivalued field showing multiple values on multiple rows

FIGURES: 3-63, 3-64, 3-65, 3-66

AC 185: Referential Integrity

LECTURE NOTES

• Define referential integrity and foreign key

• Explain one-to-many relationship

• Use Figures 3-67 through 3-71 to illustrate specifying referential integrity

• Discuss the effect of referential integrity on a database using Figures 3-72 and 3-73

• Describe a subdatasheet using Figures 3-74 and 3-75

• Describe how to find duplicate records using the Find Duplicates Query Wizard

• Describe how to find unmatched records using the Find Unmatched Query Wizard

FIGURES: 3-67, 3-68, 3-69, 3-70, 3-71, 3-72, 3-73, 3-74, 3-75

CLASSROOM ACTIVITIES

1. Assign a Project: Have students research referential integrity and write a report detailing their findings. The report should include:

1) What it is?

2) Why it is important?

3) What makes supporting it in a relational database difficult?

4) How did developers solve the problems involved in supporting it?

TEACHER TIPS

Spend some time explaining referential integrity concepts to students. These concepts can be related to the concept of redundancy that was discussed in Chapter 1. Using more than one table in a database eliminates redundancy but there needs to be some way to link the tables and prevent errors. Referential integrity is a key characteristic of the relational data model and all relational database management systems must have the ability to enforce referential integrity.

AC 185: Plan Ahead Box (Critical Thinking): Identify Related Tables in Order to Implement Relationships between the Tables

LECTURE NOTES

• Decide how to handle deletes

o Prohibit deletions

o Cascade the delete

• Decide how to handle updates

o Prohibit updates

o Cascade the update

AC 192: Ordering Records

LECTURE NOTES

• Use Figures 3-76 and 3-77 to describe how to use the Ascending button to order records

FIGURES: 3-76, 3-77

BOXES:

1. Other Ways: Encourage your students to explore other ways to order records.

AC 192: Special Database Operations

LECTURE NOTES

• Define backup, recover, and live database

• Review the steps to back up a database

• Explain compact and review the steps to compact and repair a database

• Discuss additional operations

o Open another database

o Close a database without exiting Access

o Save a database with another name

o Check for dependent objects

o Delete a table or other object

o Rename an object

o Change object properties

• Review the step to quit Access

BOXES:

1. BTW: Certification: For more information on the MCAS program see Appendix F or visit the Access 2007 Certification Web page.

2. BTW: Quick Reference: Point out the location of the Quick Reference Summary and the Access 2007 Quick Reference Web page.

3. BTW: Compacting Error Message on Opening Database. Explain this error message and how to restore the database to its default view.

CLASSROOM ACTIVITIES

1. Critical Thinking: Databases should be backed up periodically. What factors determine how frequently to back up a database?

End of Chapter Material

▪ Learn It Online is a series of online student exercises that test your knowledge of chapter content and key terms.

▪ Apply Your Knowledge is a student assignment that helps you to reinforce the skills and apply the concepts you learned in this chapter.

▪ Extend Your Knowledge is a student assignment that challenges you to extend the skills you learned in this chapter and to experiment with new skills. You may need to use Help to complete the assignment.

▪ Make It Right is a student assignment that requires you to analyze a presentation and correct all errors and/or improve the design.

▪ In the Lab (Lab): In the Lab is a series of student assignments that ask you to design and/or create a presentation using the guidelines, concepts, and skills presented in this chapter. The assignments are listed in order of increasing difficulty.

▪ Cases and Places is a series of student assignments where you apply your creative thinking and problem solving skills to design and implement a solution.

Glossary of Key Terms

• action query (AC 162)

• append query (AC 162)

• backing up (AC 193)

• backup copy (AC 193)

• cascade the delete (AC 185)

• cascade the update (AC 185)

• compact (AC 193)

• default value (AC 165)

• delete query (AC 162, AC 163)

• delete the records (AC 148)

• filter (AC 148)

• Filter By Form (AC 153)

• Filter By Selection (AC 149)

• Find Duplicates Query Wizard (AC 191)

• Find Unmatched Query Wizard (AC 191)

• foreign key (AC 185)

• format (AC 168)

• format symbol (AC 168)

• layout (AC 175)

• live database (AC 193)

• Lookup field (AC 158)

• maintaining the database (AC 138)

• make-table query (AC 162)

• multivalued fields (AC 160)

• one-to-many relationship (AC 185)

• range of values (AC 165)

• recover (AC 193)

• referential integrity (AC 185)

• required field (AC 165)

• resizing (AC 175)

• restructure the database (AC 138)

• save copy (AC 193)

• searching (AC 145)

• structure (AC 156)

• subdatasheet (AC 190)

• update query (AC 162)

• validation rules (AC 165)

• validation text (AC 165)

Top of Document

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download