Databases

Study Notes - Access

Use the text to find the definitions of the terms which are not defined below.

Hierarchy of Data:

Field - characteristic about an object, person, place or event. A field is a category of information. (a column)

Record - group of fields. Specific information about an object, person, place or event. (a row)

Table - group of related records. (a matrix)

Database - group of related tables.

To test your understanding of the above terms, answer the following questions:
1. Would a customer be considered a field or a record?
2. Would an account number be considered a field or a record?
3. Would an employee be considered a field or a record?
4. Would an invoice amount be considered a field or a record?
(see answers at the bottom of this page)

Database Design: the arrangement of data into tables and fields. Before data can be entered into a table, we must define the structure of the table. To create (or modify) the structure of a table, go to design view by clicking on the design button.

You can create or modify the fields for a table in design view shown below.

Data Types: Pick the data type based on the type of information that will be stored in the field. Use the TEXT data type to store characters. We should pick the TEXT data type to store telephone numbers or social security numbers since these fields will not ever be used in a calculation. Use the NUMBER data type to store numbers that will be used in calculations. These two data types are similar to the Value vs. Label concept we studied in Excel. There are several other data types that can be used; Memo, Date/Time, Currency, AutoNumber, Yes/No, OLE Object, Hyperlink and Lookup Wizard.

Attribute: das ---We can set the default value attribute of a state field to AL if we expected most records to have AL for the state field. This would make data entry faster since we could skip the field, unless we wanted to enter a different state. Set the Required attribute to Yes to require that the user enter information in a specific field and not leave it blank. For example, it is common to require that an invoice have an account number - not allowing the user to leave the account number field blank on an invoice..

Relational Database:

Primary Key: Unique identifier. A field that uniquely identifies a particular record in a table. We use the primary key to link tables together.

Join: find records in two tables that have identical values in matching fields.

Foreign Key: a field in one table whose values are required to match the primary key of another table.

Referential Integrity: a property that ensures that the value in a foreign key must match the primary key in another table.

Index: ordered list which is based on one field.

Data Redundancy: Storing the same fact in more than one place. Avoid data redundancy as it can waste disk space and cause inconsistent data. This means that we would not store a customer name in two different tables. Instead, we would link to a second table by using a customer number.

Reports:

Detail Lines in Reports: Lines that print for each record.

Forms:

Query: a question we ask in a way that a database can answer. Often, we will search a table for a specific record or group of records - For example, searching for all clients that live in Alabama.

Computed Field: a field whose value is based on another field - For example, a tax field may be computed by multiplying the tax rate times the invoice amount.

Update Query: a query which automatically changes the value of specified fields in a table at once.

(Answers to Questions)
1. A customer would be considered a record in a customer table. Fields for a customer might include name, account number, address, city, state, or zip code.
2. An account number would be considered a field in a customer table.
3. An employee would be a record in an employee table. Fields for each employee might include social security number, name, address, withholding status.
4. An invoice amount would be considered a field in an invoice table.