This is the first of a series of articles on Microsoft Access, the Microsoft
Office database program. The best way to gain a full understanding of Access
is to start with the basics. This article covers database terminology and is
an introduction to Relational Database Concepts.
So what is a database?
A database is an organized collection of information. Common examples of
a database would be a telephone book, mailing list, recipe book, or a check
book.
The term database is a little different in Access. An Access database refers
to a set of data related to a specific purpose or topic, along with the tools
needed to utilize and manipulate that data, such as sorting, extracting, or
summarizing.
Database Terminology
The first term to become familiar with is a Table. An Access table is
a list of related information presented in a column/row format.
A table is broken down into additional components such as a row in a table.
Each row is referred to as a Record. So if you look up your information
in a telephone book you are reviewing your record.
Each column in a table is a category of information referred to as a
Field. In a telephone book the column of phone numbers would be considered
the Phone Number field.
One item of data, such as a single phone number, is called a Data Value.
Relational Database Concepts
Prior to understanding the concept of a Relational Database you should
first understand the concept of a Flat File Database. A spreadsheet would
be considered a Flat File database.
Let's use a mail-order Book company for example. If using a Flat File database
one item ordered would equal one record:
No problem, right? But what happens if the customer orders several books?
In a flat file database the result would be multiple records and the majority
of the fields would contain duplicate data values:
This is not an efficiently designed database!
Now let's look at the same data stored in a relational database. An Access
relational database is comprised of multiple tables each pertaining
to a specific topic:
Another key piece to the relational database concept is each table contains
a field, or a combination of fields, in which the data value uniquely identifies
the record and Access will ensure that the data values remain unique to each
record. This field is referred to as the Primary Key. A Customer Id
field would be added to the Customers table and Book Id would
be added to Book Inventory table:
If each customer is assigned a unique value—their Customer Id—which
is then referenced when placing an order. The same would be true for Book Inventory.
Each Book is assigned a unique value that is referenced in the Orders table
when a book is purchased:
Thus the tables are related to each other by a common field.
A table that contains the "parent" or "primary" information
can be linked to the appropriate row(s) in a table that contains "child"
or "related" information based on common key field of the two tables.
Relationships
Each relationship will have a Primary (parent) table and a Related
(child) table as previously described.
An easy way to determine the Primary table in the relationship is to note the
Primary key. Typically the Primary table is the table that holds the Primary
key field in the relationship.
In the above image, the Customers table is the Primary table and the Orders
table would be the Related table.
How each table is related to each other is another key concept in a relational
database.
There are two main types of Relationships: One-to-One and One-to-Many.
There is a third relationship type called a Many-to-Many relationship,
but I'll cover that type of relationship in a future article.
One-to-One Relationship
A one-to-one relationship exists when the primary record will have only one
related record. Another determining factor is both fields used the relationship
are Primary Key fields:
In the above image, you wouldn't want to assign the Customer ID to more than
one customer; therefore, the Primary Key field would be Cust ID. The same is
true for the Billing table.
Each Customer should have only one Billing Address so the Cust ID field would
be designated as a Primary Key.
If the related data value in both tables must be unique then there can only
be one matching record, thus a one-to-one relationship.
One-to-Many Relationship
A one-to-many relationship is the most common type of relationship. A one-to-many
relationship exists when the primary record can have many related records.
In this image, you can see that one customer can place many orders; therefore,
the Cust ID field in the Orders table can not be a Primary Key. This being the
case, many occurrences of the same Cust ID can be entered.
Of course this all sounds good on paper, but how to you begin to determine
how many tables you need? What fields you need? How to prevent redundant data?
Database design theory includes a design standard called Normalization—and
that will be the topic for next month's article in this series.
|
