Transcript Access Project 3

Maintaining a Database
Access Project 3
What is Database Maintenance ?
 Maintaining a database means modifying the data to
keep it up-to-date. This includes:
 Adding records
 Changing data in records
 Deleting records
 In addition to record maintenance, database
restructuring is necessary periodically. This includes:
 Changing field characteristics (ie. type, size)
 Removing existing fields
 Creating indexes
2
Updating Records (AC 117)
 You can use Datasheet view or Form view to add
records
 In both Datasheet view and Form view, records are
ordered by primary key
3
Searching for a Record (AC 118)
 “Search” is used when making changes to records
 Use the Find or Replace tabs
 Select the field in which your search criteria must
match; values are restricted to that field
 Wildcards also can be used.
For example, Clicking the Name field, and entering
Fa* in the Find What text box will find all records
where the client name begins with Fa.
4
Filtering Records (AC 121)
 Use Find to quickly locate a record – note that all
other records will still appear
 To have only the record or records that satisfy the
criterion appear  use a filter
 3 Types of Filters:
 Filter By Selection – simple filter
 Filter By Form – use for multiple criteria
 Advanced Filter/Sort – complex criteria
5
Deleting Records (AC 125)
 When you delete records, in Access, they are
permanently deleted from the database
 It is important to back up a database before adding,
changing, or deleting records
 In some DBMS, deleting records is a two-stage
process. First, records are marked for removal and
then removed permanently in a separate step
ACCESS IS NOT A 2-STEP PROCESS
6
Adding a Field and
Changing the Size of a Field (AC 127)
 You may want to add a new field to your table
 You can insert a blank row in the table for the field
 The size of a field can be increased or decreased
 If the field size is decreased, any existing data larger
than the new field width will be truncated
7
Mass Changes (AC 135)
 Mass changes to records are made thru queries:
Update, Delete, Append, and Make-table queries
 Update query – makes same change to all records
 Delete query - deletes a group of records
 Once records are deleted using a delete query, the operation
cannot be undone
 To preview the records to be deleted, view the query in
Datasheet view before running it
 Append query - adds records to an existing table
 Make-table query - adds records to a new table
8
Validation Rules (AC 139)
 Validation Rules are rules to be followed when entering data
 Validation text – the message that will appear if a user violates
the validation rule
 Required field – a field in which the user must enter data
 Range of values – entry must lie within a certain range of values
 Default value – a value that will display on the screen in a
particular field before the user begins adding a record
 Legal value – accepted values for a field, others are rejected
 Validation rules make it easier to enter data. When a data type is
declared as Number or Currency, Access automatically validates
the type of data that can be stored in the field (only numbers can
be entered in the field).
9
Referential Integrity (AC 150)
 The property that ensures that the value in a foreign
key must match that of another table’s primary key
 In Access, referential integrity is defined via
relationships between tables using the Relationships
command
 A One-to-Many Relationship  means that one record
in the 1st table is related to many records in the 2nd
table (ie. one trainer associated with many clients)
10
Referential Integrity (AC 150)
 If we were to delete out trainer 42’s data from the
database, the clients associated with 42 would now be
orphan records (not linked to any trainer)
 To avoid this problem:
1) avoid such a deletion
2) cascade the delete, allow the deletion but
automatically delete related records (ie. clients)
 If we were to update trainer 42’s number to 62, we
could:
1) avoid such an update
2) cacade the update, allow the update but
automatically make the change for related clients
11
Creating and Using Indexes (AC 157)
 Indexes are used both for retrieving records quickly
and listing records in a different order.
 Indexes can optimize the performance of the database
 Access uses indexes automatically. In some DBMS
the user must indicate that an index is to be used.
12
Creating and Using Indexes (AC 157)
 Create an index on a field if one or more of the
following conditions are present:
 The field is the primary key of the table (auto-index in Access)
 The field is the foreign key in a relationship you have created
 You frequently will need your data to be sorted on the field
 You frequently will need to locate a record based on a value in
this field
 Although indexes improve efficiency for sorting and
finding records, they can slow the editing, adding, and
deleting of data because they occupy disk space.
13
Creating and Using Indexes (AC 157)
 Single-field index
 An index whose key is a single field
 You may allow duplicates in the index key (two records that
have the same value). For name, duplicates should be allowed
 Multiple-field indexes
 An index whose key is a combination of fields
14
Special Database Operations (AC 163)
 Backup and Recovery
 To backup the database  File Back Up Database
 Compacting and Repairing a Database
 When you delete objects (tables, records, etc.) from a database,
the space previously occupied is unavailable for use
 You must compact the database to get this space back
 3 step process:
 Compact the original database to a different name
 If successful, delete original database
 Rename compacted database to original name
15
Maintaining a Database
Access Project 3
Any Questions?