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 http://coursecasts.course.com, 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
|
AC 140: Plan Ahead Box (Critical
Thinking): Database Maintenance Guidelines |
|
|
AC 149: Plan Ahead Box (Critical
Thinking): Determine Whether You Should Filter Records |
|
Students
will have mastered the material in Chapter Three when they can:
l Add, change, and delete
records
l Search for records
l Filter records
l Update a table design
l Format a datasheet
l Use action queries to
update records
l Specify validation
rules, default values, and formats
l Create and use
single-valued and multivalued Lookup fields
l Specify referential
integrity
l Use a subdatasheet
l Sort records
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
FIGURES
and TABLES: Figures — 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.
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
You can use the Lecture Success
System for Access in conjunction with the Figures in the Book. To do this,
start Access and open the database from the appropriate folder. Then, start
your slide show containing the figures for the project. You can switch back and
forth between the slide show and Access by using the alt+tab key
combination. You can use the Figures in the Book to show the steps students should
follow. If students need additional reinforcement or ask questions about the
task, you can switch to Access to do a live demonstration.
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
and TABLES: 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.
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
and TABLES: 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
and TABLES: 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.
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
and TABLES: 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.
If students are unable to run update, delete, or
append queries, they did not enable the content. To correct this problem, close
the database and re-open it, making sure to follow steps 6 through 8 on page AC
141. Students always should enable the content before doing the database tasks
in this text.
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)
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
and TABLES: 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
and TABLES: 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
and TABLES: Figures — 3-63, 3-64, 3-65,
3-66
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
and TABLES: Figures — 3-67, 3-68, 3-69,
3-70, 3-71, 3-72, 3-73, 3-74, 3-75
BOXES:
1.
BTW: Relationships. Explain how to
specify one-to-one relationships.
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.
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?
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
LECTURE
NOTES
·
Use
Figures 3-76 and 3-77 to describe how to use the Ascending button to order
records
FIGURES
and TABLES: 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?
§ 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.
·
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)