Database Terminology

and Concepts
A database
Database – a collection of information related to a particular topic or purpose.
is a structure that organizes and stores data electronically. The data is
stored using a database management system (DBMS) such as Microsoft
Access, MySQL or Microsoft SQL Server. Data is organized into rows,
columns, and tables, so that it can be easily accessed, managed and
updated.
Criteria – the conditions that control which records to display in a query

Database management system – a program such as Access, that stores, retrieves,

arranges, and formats information contained in a database

Database model – the structure of the information stored in the database. This model should
included how each individual piece of information relates to all the other information in the
database

Database modeling – the process of strategically planning where to store each piece of
information you wish to include in your database

Proper planning, even in the initial pencil-and-paper stage, ensures that the database you
create and maintain is efficient and provides easy access to the information you need most.
A well-designed database should eliminate the need to enter the same data repeatedly and
prevent duplication of information, thereby maintaining the integrity of the data

Datasheet – a format of columns and rows displaying information.

display formats (format) - Specifies how data is displayed and printed. An

Access database provides standard formats for specific data types, as does an
Access project for the equivalent SQL data types. You can also create custom
formats.

Field – a specific item of information containing a homogenous set of values throughout the
table. Fields appear as columns in a table and as cells in a form

field data types - a characteristic of a field that determines what kind of data it
can store. For example, a field whose data type is Text can store data consisting
of either text or number characters, but a Number field can store only numerical
data.
Field list – a small window that lists the fields of a selected table or data source.

Form – a structured document with specific areas for viewing or entering data
one record at a time. Forms can be constructed in columnar, tabular, datasheet,
or a simple justified format.

Join line – the line between two tables identifying the common field between
them.
Query – a request for a particular collection of data in a database.

Microsoft Access Basic Page 1

 Database Terminology
and Concepts
Query By Example (QBE) grid – the portion of the Query Design window used
for selecting fields, setting criteria, and setting sort order in a select query.
QBE grid rows

ROW DESCRIPTION
Field Displays the name of the field used in the query.
Table Displays the name of the table from which a field is selected.
Sort Determines the order in which to display the records in the recordset.
Show Determines whether the field used appears in the recordset.
Criteria Displays selective criteria used to filter the query.
Or Displays additional criteria for the query.

There are two types of databases: Nonrelational and relational.

Nonrelational database – also called a flat file, stores information in one table. Nonrelational

databases are useful for information stored in a single list, such as a list of student names,

addresses, and phone numbers

Relational database – is useful for maintaining and analyzing complex

information stored in a number of tables. For example, in addition to storing
student names, you can list TAKS test scores, or demographic information
related to the students in other tables in the same database.
A relational database contains tables that relate to another table
by using a relationship. The tables are connected by a common
field. The relationships are defined as:
 One-to-many relationship – a relationship in which a record in the primary table can be
related to one or more records in the related table.

Example: There is a one to many relationship between the Customers table

and Orders table. A customer may have many orders in the Order table.

 Many to Many: For every record in Table A, there are multiple records in
Table B, and vice versa. In this case, a third table called a Join Table is
created to which will contain only unique values
Example: Many to Many relationship between Orders and Products with the
table ProductsOrders functioning as the Join Table. The table
ProductsOrders holds all the details about each order and what products it
contains. Its primary key is a combination of the primary key of the Orders
and Products table.
One-to-one relationship – a relationship between two tables in which for each
record in the first table, there is only one corresponding record in the related
table.
Microsoft Access Basic Page 2
 Database Terminology
and Concepts

Keys
Key fields are used to build the relationships between data in different
tables.

 Primary Key: Primary Key – a field in a table whose value is uniquely identifies each
record in the table .(Usually auto-numbered, unique ID for internal tracking
and for matching records between related tables. Each value is unique to
the table and cannot be null).
 Foreign Key: A field in a related table pointing back to the Primary Key in
another table. For auto-numbered fields it is defined as a number value.
Notes on Primary Keys

 The Primary key should always be the first field in each table, followed by
any foreign key(s).
 There is only one Primary Key per table.
 Primary keys should never be actual data – not even something unique
like a Social Security number or Student ID. These values are obtained
from an outside source and, while they seem unique and reliable, they
could produce data entry errors.
Primary Key and Table Relationships

To avoid duplication of information, a good database modeling technique is to

use a field or fields that uniquely identify each record in a database table. Such
fields might include unique employee ID number or a student ID number. A field
that functions in this way is called a primary key. By default, Microsoft Access
sorts records in primary key order, but primary keys serve other functions as well.
Primary keys establish a relationship between specific records in two tables
containing a common field.
Primary keys are displayed in bold in the Relationships window.

 Data Fields
A data field which is one piece of information you track in your
database. Each data field in the table can define the
characteristics of its data as a string value, numeric value,
date and/or time values
Object – a component of a database, such as a table, query, form, or
report

Microsoft Access Basic Page 3

 Database Terminology
and Concepts

Tables
A table is a set of data represented by columns and rows. A column is
referred to as a field and a row is a combination of column values and is
referred to as a record. Tables contain a unique set of characteristics and
they store data of the same type in each row.

Data types in a Table

Microsoft Access provides two field data types to store data with text or
combinations of text and numbers: Text and Memo.
Use a Text data type to store data such as names, addresses, and any numbers
that do not require calculations, such as phone numbers, part numbers, or postal
codes. A Text field can store up to 255 characters, but the default field size is 50
characters. The FieldSize property controls the maximum number of characters
that can be entered in a Text field.
Use the Memo data type if you need to store more than 255 characters. A
Memo field can store up to 65,536 characters. If you want to store formatted tex

Microsoft Access Basic Page 4

 Database Terminology
and Concepts
or long documents, you should create an OLE Object field instead of a Memo
field.
Both Text and Memo data types store only the characters entered in a field;
space characters for unused positions in the field aren't stored.
You can sort or group on a Text field or a Memo field, but Access only uses the
first 255 characters when you sort or group on a Memo field.

Microsoft Access provides two field data types to store data containing numeric
values: Number and Currency.
Use a Number field to store numeric data to be used for mathematical calculations,
except calculations that involve money or that require a high degree of accuracy.
The kind and size of numeric values that can be stored in a Number field is
controlled by setting the FieldSize property. For example, the Byte field size will
only store whole numbers (no decimal values) from 0 to 255 and occupies 1 byte of
disk space.
Use a Currency field to prevent rounding off during calculations. A Currency field is
accurate to 15 digits to the left of the decimal point and 4 digits to the right. A
Currency field occupies 8 bytes of disk space.
Number and Currency fields provide predefined, or you can create a custom
format

Microsoft Access provides the AutoNumber data type to create fields that
automatically enter a unique number when a record is added. Once a number is
generated for a record, it can't be deleted or changed. An AutoNumber field can
generate three kinds of numbers: sequential numbers that increment by one,
random numbers, and Replication ID (also referred to as GUIDs—globally unique
identifiers) numbers. AutoNumbers that increment by one are the most common
kind of AutoNumber and are a good choice for use as a table's primary key.
Random AutoNumbers will generate a random number that is unique to each
record within the table..
(Primary Key: One or more fields (columns) whose values uniquely identify each
record in a table. A primary key cannot allow Null values and must always have a
unique index. A primary key is used to relate a table to foreign keys in other
tables.)

Criteria – the conditions that control which records to display in a query

Microsoft Access Basic Page 5

 Database Terminology
and Concepts
Record – an individual listing of related information consisting of a number of
related fields stored in a table. A record is also called a row in the datasheet.

Recordset – the set of records and fields that result from running a query.

Related table – a table with a common field that uses values stored in a primary
table.

Relationship – the direct or indirect association between any two tables in a

database.
Report – a formatted collection of information organized to provide printed data
on a specific subject.

Select Query – a query that answers a question about one or more tables by
limiting the number of records and fields displayed.

Table – an arrangement of related information stored in columns and rows.

Data Flow Diagram

Table

Query

Form Report
Microsoft Access Basic Page 6
 Database Terminology
and Concepts

OBJECT FUNCTION
Table Stores a collection of information about a specific database topic.
Query Lists specific fields and records from a table based on selective
criteria.
Form Displays data from a table or a query one record at a time.
Report Presents data from a table or query in printable format.
Page Documents created in Access for display on an intranet or the
Internet.
Macro Automates a repetitive series of commands.
Module Collects a group of Visual Basic for Applications declarations and
procedures and stores them as a unit.

OBJECT VIEWS FUNCTION

Table Design View Create and design a table to your specifications.
Datasheet View View and modify the data in a row-and-column
format.
Query Design View Create and design a query to your specifications.
Datasheet View View and modify the query data in a row-and-
column format.
SQL View View the query as an SQL statement.
Form Design View Create and design a form to your specifications.
Form View Modify and view data in the fields of the form.
Datasheet View View and modify the form data in a row-and-
column format.
Report Design View Create and design a report to your specifications.
Print Preview View the report as it appears when printed.
Layout Preview View the layout of the report to confirm design
specifications.
Page Design View Create and design an Access Data Access Page to
your specifications.
Page View View and modify as a Web page.

KEYBOARD METHODS

Microsoft Access Basic Page 7

 Database Terminology
and Concepts
KEY EFFECT
[TAB] Next field
[SHIFT TAB] Previous field
[HOME] First field of current record
[END] Last field of current record
[DOWN ARROW] Down one record
[UP ARROW] Up one record
[CTRL HOME] First field of first record
[CTRL END] Last field of last record

DATA ENTRY KEYBOARD SHORTCUTS

SHORTCUT DESCRIPTION
[CTRL ‘] Inserts a value from the same field in the previous record.
[CTRL ;] Inserts the current date.
[CTRL :] Inserts the current time.
[CTRL +] Adds a record.
[CTRL -] Deletes a record.

KEYBOARD EDITING SHORTCUTS

SHORTCUT DESCRIPTION
[DELETE] Deletes the character to the right of the insertion point.
[BACKSPACE] Deletes the character to the left of the insertion point.
[INSERT] Switches between insert mode and type-over mode.
[ESC] Cancels changes in the current field or current record.
[F2] Switches between editing mode and navigation mode.
[SHIFT F2] Opens a window for editing the contents of long fields.
[RIGHT ARROW] Moves the insertion point to the next field or one
character to the right.
[CTRL RIGHT ARROW] Moves the insertion point one word to the right.
[LEFT ARROW] Moves the cursor one character to the left within a
field.
[CTRL LEFT ARROW] Moves the insertion point one word to the left.
[END] Moves the insertion point to the end of the line.
[CTRL END] Moves the insertion point to the end of the field in a
multiple-line field.
[HOME] Moves the insertion point to the beginning of the line.
[CTRL HOME] Moves the insertion point to the beginning of the field
in a multiple-line field.
[TAB] Moves the insertion point to the next field.
[DOWN ARROW] Moves the insertion point to the next record.
[ENTER] Moves the cursor one field to the right.

COMPARISON OPERATORS

Microsoft Access Basic Page 8

 Database Terminology
and Concepts
OPERATOR MEANING EXAMPLE
= (optional) Equal to =50
< Less than <10
> Greater than >100
<= Less than or equal to <=25
>= Greater than or equal to >=25
<> Not equal to <>0

REPORT DESIGN SECTIONS

SECTION FUNCTION
Report header Displays a header at the top of the first page only. Includes
name or the source table or query.
Page header Displays a header at the top of every page. Inserts field
names from the source table or query.
Group header Displays a header at the beginning of each new group.
Inserts the field name used for grouping.
Detail Displays the records of the fields listed in the group header.
Group footer Displays a footer at the conclusion of each group. Inserts
the field name used for grouping.
Page footer Displays a footer on the bottom of every page. Typically
includes page numbers and dates.
Report footer Displays a footer on the last page only. Can include any
summary information, such as a grand total.

Microsoft Access Basic Page 9