Database Design: Mastering Data Types, Keys, and Queries

Database Design Fundamentals

Key Concepts and Definitions

Data Type

The data type determines what field values are allowed and what other properties the field will have.

Saving a Table

You can specify a name for a table after you click the Save button on the Table Design toolbar.

Table Structure

The final step in the creation of a table is to save the table structure to disk.

Primary Key

A primary key uniquely identifies each record in a table.

Identifying Fields

When designing a database, you need to identify the fields that will be required.

Data Redundancy

Storing the same data field in multiple tables is called data redundancy.

Composite Key

A primary key that consists of more than one field is called a composite key.

Avoiding Data Redundancy

When designing a new database, it is a good idea to avoid data redundancy.

Reflecting Contents

When defining a database, the name given to a table or other object should reflect its contents.

Field Size

When defining a field, the field size determines how much physical storage space the field requires.

Field Types and Properties

Auto Number Field

A field that consists of integer values that are controlled by Access is an Auto Number type field.

OLE Object

You can include a graphic file in an Access database by defining a field of type OLE Object.

Design View

Which view would you select if you wanted to define a table yourself from start to finish? Design View

Create Tab

To begin creating a new table, click the Create tab on the Ribbon.

Field Properties Pane

Values for field properties other than Field Name, Data Type, and Description are entered in the Field Properties Pane portion of the Table window in Design View.

Primary Key Button

After all primary key fields have been selected, you must click the Primary Key button in the Tools group on the Table Tools Design tab to complete the key definition.

Modifying and Managing Tables

Modifying Table Structure

You can modify an existing table's structure in Design View.

Changing Field Order

In Design View, you can change the order of defined fields in a table by using the mouse to drag the field to a new location.

Update & Propagate

When you change a field's property in Design View, you can use this button to update the corresponding property on forms and reports that include the field you've modified: Update & Propagate.

Donation Value Field

Tom is debating with himself about the format for the Donation Value field. What is the best reason to change the default currency data type format? All of the above.

Referential Integrity

Emma is setting up referential integrity in the database. Why would she not include the cascade delete option? All of the above.

Queries and Relationships

Query by Example (QBE)

Using QBE, you give Access an example of the information you are requesting; Access then retrieves the information that precisely matches your example.

Joined Tables

Joined tables are separate tables, but you can use the data in them as if they were one table.

Foreign Key

Foreign key fields can be unique or non-unique.

Comparison Operators

Operators used in selection criteria in a query, such as equal, not equal, or greater than, are known as comparison operators.

Select Queries

Generalized queries that specify fields and records to be selected are called select queries.