Office 2007 - Access

Lesson 5: Creating a Database

5/72 Lessons 

A Relational Database

You could describe a database as a collection of data, such as a directory or a subdirectory. However, for the creation of such a database, or rather a card, we do not require access, this can be done in “Excel” also since it is simply the creation of a table.

But when we create a database in “Access”, we create a database that consists of several tables that are linked together.

This is called a relational database.

An example of a relational database is for example, a database for a mail order company.

This company can offer multiple products to a customer and can supply a product to multiple customers. If we enter these orders in a card, then your data is repeated. As in the picture below, the name and address of the customer are repeated.


The relational database eliminates all the redundant data.

In a relational database, we make several tables.

All you have to do is, to add a field with unique data to different table. Through this unique feature, we can identify each record in our table.

In order to enter a unique data in a table, we add a “Primary key”.

A “primary key” is a field in the table with unique data.

We add data so that each table has a “primary key”.
In the illustration below for the “Customers” table, it is the “Customer ID” field.
For the “Orders” table, it is the “Number” field.
For the “Articles” table, it is the “ArticleID”.
The “Primary keys” for each table in the image below are shown in BOLD.

This prevents duplication of customer in the “Customers” table or the same number twice in the “Orders” table or the same article twice in the “Articles” table.

In the “Orders” table, I add the “Primary keys” fields from the two other tables. This later connects these three tables together.

So the “Customer ID” field and “Article ID” are to be added to the “Orders” table.

Each relationship consists of a primary table and a related table.

The primary table usually contains the “Primary key”.

Thus in the example below, the “Customers” table is the Primary table and the “Orders” table is the related table.

In the “Customers” table, “Customer ID” is the “Primary key” and in the “Orders” table, the “Customer ID” field is added but, this is not a “Primary key”.

This means that the “Customer ID” field in the “Customers” table may occur only once but, in the “Orders” table, it can occur multiple times.

That is precisely what we want. We want multiple customer orders.

When we talk about relational databases, we have three possible “Relationships”:

  1. one-to-one relation
  2. one-to-many relation
  3. many-to-many relation

A one-to-one “Relationship” happens when you have the “primary key” from the primary table related to just one field in the related table. Both fields are “Primary keys”.

Example, the customer has a private address, and billing address.

The most commonly used “Relationship” is one-to-many “Relationship” where, the primary field in the primary table, may have multiple related fields in the related table.

One example is that a customer can place multiple orders.

The third “Relationship” many-to-many “Relationship”. For a many-to-many “Relationship”, you need to have a third table (if you add it).
We call this an intermediate table or a connection table.
This table splits the many-to-many “Relationship” into two one-to-many “Relationships”.
In other words, a many-to-many “Relationship” is actually two or more one-to-many “Relationships”.
You put the “Primary key” from both tables in the intermediate table.

You've completed Lesson 5