Access Chapter 1

table (entity)

A collection of fields that describe one entity such as person, place, thing, or idea

database

A collection of one or more tables that contain records of information

functional dependency

A column in a table is considered functionally dependent on another column if each value in the second column is associated with exactly one value in the first folumn

report

A database object that presents the information from one or more database tables or queries in a printed format

Query

A database object that stores criteria for selecting records from one or more tables based on conditions you specify.

first normal form (1NF)

A database table that does not contain any repeating groups

Second normal form (2NF)

A database table that is in first normal form and does not contain any partial dependencies on the composite primary key

Third normal form (3NF)

A database table that that is in second normal form and the only determinants it contains are candidate keys.

relational database

A database that contains tables related through fields that contain identical data, also known as common fields

nonkey fields

A field in a table that is not part of a table's primary key

candidate key

A field or collection of fields that could function as a table's primary key, but that was not chosen to do so.

Primary Key

A field or combination of fields that creates a unique value in each record in a table

data type

A field property that determines how to store the data in the field.

common field

A field that appears in two or more tables and contains identical data that is used to relate the tables. The common field is called a primary key in the primary table and a foreign key in the related table

null value

A field value that is unknown, unavailable, or missing

module (VBA) (Visual Basic for Application)

A group of related procedures saved in a database object. -A module begins with a Declarations section and includes one or more procedures.

entity integrity

A guarantee that there are no duplicate records in a table, that each record is unique, and that no primary key field contains null values.

composite primary key

A primary key composed of two or more fields

natural key

A primary key that details an obvious and unique trait of a records, such as an ISBN code for a book

normalization

A process that reduces the chance of deletion, update, and insertion anomalies in a database

Select Query

A query that selects data from one or more tables according to specified criteria and displays data in a datasheet.

many-to-many relationship

A relationship between tables in which each record in the first table matches many records in the second table, and each record in the second table matches many records in the first table.

one-to-one relationship

A relationship between tables in which one record in the first table matches exactly one record in the related table.

one-to-many relationship 1:

A relationship between tables in which one record in the first table matches zero, one., or many records in the related table.

Referential integrity

A rule that if the foreign key in one table matches the primary key in a second table, the values in the foreign key must match the values in the primary key. When the database does not enforce referential integrity, certain problems occur that lead to inaccurate and inconsistent data.

field (column)

A single characteristic of an entity in a database. For example: fields that describe a customer might include first name, last name, address, city, state

action query

A special type of query that performs actions on a table, such as changing the contents of a field, adding records from one table to another table, deleting records from a table, or making a new table based on criteria

navigation form

A type of form that displays when you open a database and provides a controlled methods for users to open the objects in a database.

crosstab query

A type of totals query that performs aggregate function calculations on the values of one database field and allows you to determine how your summary data appears in the results.

Attachement

An Access data type that can store one or more files for each record in the database

Lookup

An Access data type that creates fields that let you look up data in another table or in a list of values created for the field.

Calculated

An Access data type that uses data from fields in the same table to perform calculations , a few of which include addition, subtraction, and multiplication.

Datasheet view

An Access view that displays the records for a table or query in rows and fields in columns. A view of data that displays fields in columns and records in rows

macro

An action or series of actions that you want Access to perform

junction table

An intermediary table created to produce many-to many relationship between two tables that do not share a one-to-one or one-to-many relationship

Cascade Delete Related Records

An option that allows Access to DELETE related records in related tables when the primary record in the primary table is deleted

Cascade Update Related Field

An option that allows Access to UPDATE the appropriate foreign key values in related tables when a primary key value in the primary table is updated.

data redundancy

An undesirable effect of storing repeated data in fields in a table that wastes space and results in inconsistent and inaccurate data. Data redundancy is often avoided by creating additional tables.

Record (row)

Collectively, the values in each field in a table for a single entity

related table

In a one-to-many relationship, the table that is on the "many" side of the relationship

primary table

In a one-to-many relationship, the table that is on the "one" side of the relationship.

repeating group

In an incorrectly designed database table, a field that contains more than one repeating value.

Hyperlink (data type)

The Access data type that displays text that contains a hyperlink (link) to an Internet or file location.

AutoNumber

The Access data type that generates a unique number in a field to produce unique values for each record in a table.

OLE Object

The Access data type that identifies files that were created in another program and then linked to or embedded in the database

Date/Time

The Access data type that stores dates or date and time combinations.

Long text

The Access data type that stores long passages of alphanumeric data

Number

The Access data type that stores numbers that are used in calculations.

Yes/NO

The Access data type that stores one of two values, such as yes or no, true or false, or on or off.

Short text

The Access data type that stores up to 255 alphanumeric characters that are not used in calculations.

foreign key

The common field in the related table between two tables that share a relationship.

SQL-specific query (Structured Query Language)

The common query language of most DBMSs, including Access. A query created in SQL can run in most database programs

insertion anomaly

The problem that occurs when a user cannot add data to a database unless other data has already been entered.

deletion anomaly

The problem that occurs when a user deletes data from a database and unintentionally deletes the only occurrence of that data in the database

updated anomaly

The problem that occurs when, due to redundant data in a database, a user fails to update some records or updates records erroneously

data duplication

The process of creating repeated records in a database, which leads to wasted space and inconsistent and inaccurate data

partial dependency

The situation in which a field is dependent on only part of a composite primary key.

transitive dependency

The situation that occurs between two nonkey fields that are both dependent on a third field; tables in third normal form should not have transitive dependents

base table (underlying table)

The table on which a query, form, or report is based.

orphaned

The term used to describe a record whose matching record in a primary or related table has been deleted.

entity

a person, place, thing, or idea

Scope creep

occurs when the original project specifications change or are increased after the project has started.