Database Management System

1
Maintaining a Database Access Project 3
2
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

3
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

4
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.

5
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

6
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

7
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

8
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

9
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).

10
Referential Integrity (AC 150)

• The property that ensures that the value in a
  foreign key must match that of another tables
  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)

11
Referential Integrity (AC 150)

• If we were to delete out trainer 42s data from
  the database, the clients associated with 42
  would now be orphan records (not linked to any
  trainer)
• To avoid this problem1) avoid such a
  deletion2) cascade the delete, allow the
  deletion but automatically delete related
  records (ie. clients)
• If we were to update trainer 42s number to 62,
  we could1) avoid such an update2) cacade the
  update, allow the update but automatically
  make the change for related clients

12
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.

13
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.

14
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

15
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

16
Maintaining a Database Access Project 3
Any Questions?