Database Management Lecture: Access

What are databases?

• Collections of related information
• Large, easily organized
• Easy to manipulate (sort)
• Easy to retrieve data (search)

Structure of Relational Databases

• Tables
  • Basic unit for storing collections of data
  • 1 or more tables make up a database
  • In Access, you design a database one table at a time in Design or Datasheet view
• Records
  • Tables are composed of records (usually 1 row of table = 1 record)
  • Contains info for 1 entry
• Fields
  • Records are composed of a collection of related fields
  • Each field corresponds to one unit of information (one name, one phone number, etc.)
• Field names
  • Describes the contents of a particular field
  • Usually describes one column in a table (see picture)

Picture: Example of a single table in a database

Examples

• The Telephone book
  • Each person is a record
  • Fields: Name, Address, Phone Number
  • Search for an address? or number?
• Inventory list
• List of courses taken at Union

Designing a Database

Some thought should go into design before creating it:

• Structural Options
  • What does each record look like?
  • How many fields?
  • What fields should go together in the same table? Which should go into different tables? (This determines how easy it is to search your database.)
  • What are legal values for a field?
• In the real world, designing a database well means answering questions about the database's function:
  • Who is going to use the database?
  • What kind of data will it hold?
  • What kind of queries should the database be expected to answer?
  • How is the database organized (sorted, etc.)?
  • What is the data going to be used for?

Using Access

Creating Tables

Design View

• Use to define the database schema, i.e. the structure of each record
• Determine field names and data types
• Use an input mask to define a pattern as to how data should be entered
• Format fields to be currency, dates, etc.
• Use a lookup field to specify a list of allowable entries in the field
• Define a primary key to act as a unique identifier for each record

Picture: Creating a table in Design View

Datasheet View

• Use to enter data into individual fields to create records
• Input masks, formatting, and lookup fields defined in Design view are used during data entry
• New record is always the blank record at the bottom
• Can double-click on field names to edit
• Can toggle with Design view to fine tune your table (see picture below)

Picture: Entering records in Datasheet View

Queries (Searching a Database)

Select Queries

• Use Datasheet view to specify table(s), field name(s), and other criteria
• Each column represents criteria for one field name (see picture below)
• Use expressions (and expression builder) for complex queries involving ranges and logical operators
• Queries can be saved to disk; useful for frequently-run queries

Picture: Defining a select query. Here, the Like operator is used to return all addresses on Elm street

Parameter Queries

• Like Select queries, but each time query is run, user can specify different criteria that the query uses to retrieve answers
• Define a prompt inside square brackets in the "Criteria" row
• Can use wildcards for pattern matching within a field (see table below)
  

  Picture: This parameter query will prompt the user to enter a street name. It will then return names and addresses of those having an address anywhere on that street. Note the use of the * wildcard to match ANY address on the street given.

• Common operators:

  Operator	Meaning
  <>	Not equal
  >,<	Greater than, Less than
  >=, <=	Greater than (Less than) or equal to
  =	Exact match
  Like	Pattern match
  Between	Range match
  And, Or, Not	Logical operators

• Common wildcards:

  Wildcard	Meaning	Example
  *	Multiple characters	L*rie matches Lorie or Laurie
  ?	Single character	D?n matches Don or Dan
  #	Single digit	1# matches any number between 10-19
  []	Range	[a-z] matches any alphabetic character
  !	Not	[!a-z] matches anything except an alphabetic character

Other types of Queries

• Crosstab queries: Summarizes query answers in spreadsheet format
• Action queries: Result of query updates database (be careful!)

Forms

• Creating easy guides for data entry
• Creating easy guides for running queries
• Creating a switchboard -- a group of action buttons that easily allow other forms, reports, and queries to be built and executed

Using the Form Wizard

1. Start Form Wizard from main Database Window
2. Select which tables and fields you wish your form to contain (see picture below)
   

   Picture: Forms can make data entry easier by incorporating fields from different tables. Here, we are creating a form to enter orders. We have already selected First name and Last name fields from one table. And we are about to add the date field from a different table to our form.

3. Continue through walkthrough, picking the order of your fields, layout (columns, justified, etc.) and a form name
4. When finished, Access will create a form for you (see sample below)

Picture: Data can be entered into the form in Form View. Updates to the different tables takes place automatically. The form helps the user to enter data by filling in fields for you if it can. For example, since the field labelled purchase is the primary key in the Product table, selecting "widget" automatically fills in the Unit Price field for $3.00

Customizing your form

• Edit your form in Design View
• Add headers and footers
• Add elements to your form such as buttons, labels, and text boxes
• Add other fields to your form which display database information or performs a customized calculation (see picture below)

Picture: This picture shows the same form as the previous picture, but in Design View, where editing can occur. Notice that we've added a new field: Total Price, which automatically computes the total cost based on the multiplication of two other fields. The user can type in this expression directly (field names must go within square brackets), or s/he can use the Builder to build the expression.

Reports

Using the Report Wizard

1. Start Report Wizard from main Database Window
2. Follow walkthrough similar to Form Wizard. Reports can contain field information from multiple tables
3. Specify grouping and sorting criteria
4. Add summary information (averages, etc.) if desired
5. Access will create the report for you (see picture below)

Picture: A sample report shown in Layout Preview mode. The Report Wizard lets you group and summarize data in several different ways. Here, orders are grouped by employee.

Customizing your report

• Edit your report in Design View
• Headers and footers can be applied at each section (that is, at each band)
• Add elements to your form such as buttons, labels, and text boxes

Picture: Editing a report in Design View