Why learn about relational databases ?
This type of database has several advantages compared to a simple "flat file" database. It uses much less space because it removes the repetition
or the redundancy for some of the data. To better understand the advantages of a relational
database management system (RDBMS), let us use an example of invoices to compare
these two types of databases and to show some basic concepts of a RDBMS.
Let's look at some of the data you would normally find on an invoice:
Ex: Date, Invoice number, Salesperson ID Number, Product ID, Quantity sold,
Unit price, Product description, Subtotal, Taxes, Total...
Here are some of these data in one simple database.
Invoice No. |
Customer |
Product |
Description |
Unit price |
Qty... |
1001 |
ABC |
415 |
TRIANGLE |
12.75 |
50 |
1002 |
ABC |
416 |
SQUARE |
10.00 |
20 |
1003 |
XYZ |
415 |
TRIANGLE |
12.75 |
75 |
The main idea of a relational database is to distribute the data into many
distinct databases or tables and then create connections, links, or relations between each of them. Here's how the fields of an invoice could
be divided in a relational database.
Invoice |
Customer |
Employee |
Inventory |
Invoice No. |
Customer No. |
Employee No. |
Product No. |
Product ID |
Address |
Last name |
Description |
Qty sold |
Postal code |
First name |
Unit price |
Employee ID |
Phone no. |
Social Security No. |
Available qty |
Customer No. |
Person in charge |
Hiring date |
Ordered qty |
In the previous tables, the field Customer No. in the Invoice table gets the data about
the customer from the Customer table if you make a link between the Customer No. fields of the two tables. After that, you don't need to rewrite the data
of the customer every time you enter an invoice! The database can fetch the data on the customer
thanks to a relation between the Invoice and Customer tables on the Customer No. field. The
same thing occurs between the Invoice table and the Employee and Inventory by their common fields Employee No. and Product No. respectively.
Because of the relations or links between the tables, you don't have to rewrite redundant
or repetitive data. This leaves more space for other data. To some extent,
it also avoids errors while rewriting the same data like "typos". For the three
invoices of this example, you save space and time by not having to rewrite the
data about the customer ABC nor the description and the unit price of the product
415. And this is just with three invoices ! Image the savings when you have
hundreds or thousands of invoices per day !
Definition
The use of a database brings also new terms that you need to understand. Here is the
list of the terms that will be used for all the pages in Access' section.
Field: |
An information about a person, a thing or an event that you need for the database.
Ex: color, size, model, date, name, first name, telephone, addresses, description,
comments etc. |
Record: |
A group of fields that describes a person, a thing
or an event. Ex: name, first name, date of birth, telephone, social
security number,company employee ID, address, telephone, fax, person in
charge, department...
The field phone number can be used by three different places. It can be
the phone number of an employee, a person in another department or another
company.
Make sure you can distinct how it's used. The best way would be in the fields name: Employee_Phone_No, Customer_Phone_No, Retailer_Phone_No, Departement_Phone_No ... |
Table: |
A group of records that have a subject or a theme in common; usualy about a person, an object or an event.
Ex: Employees, Inventory, Customer, Suppliers, Vehicles, Contacts etc. |
Database: |
A group of tables, queries, forms, reports and some programming
that constitutes a complete data system. Ex: Invoicing,
Inventory management, Fleet of vehicles, bookings etc. |
The definition of a database now takes a larger role than it had before.
The old definition was for what we now call a table; a file that contains the list of fields, their properties and the of the
data. It now contains the tables, the queries, the forms, the reports,
the macros and the modules to develop an "application" or a complete "system"
that answers to the specific needs of the user.
What are the components of Access?
Tables: |
Structure of the table, list of fields, their properties or caracteristics and the data
the user enters are stored there. |
Queries: |
Search for data that answers certain criteria determined
by the user. |
Forms: |
Presents the data on the screen in a practical
way for the user to view, enter and modify data on screen. |
Reports: |
Presents the data on paper in a practical
way for the user. |
Macros: |
Develops routines to automate certain tasks. |
Modules: |
Programming for a database |
Create a database
This page will
show you the stages that must be followed to create a database. To avoid repeating the text, you'll be asked to go and read the text other Web pages on tables and relations of this section
on Access. The
exercise will consist in creating a database to follow the invoicing of a company.
The first stage to create a database is the analysis. It's
without a doubt the most important stage. In fact, it's also true for any application
or document that you create. Think first ! If you pass too quickly on this stage,
you'll lose a lot of time and efforts to redo what you missed
out before. Before you start creating a database, you must have a clear idea of
what your needs will be. You must prepare a detailed analysis
of your needs. What will the data that you'll need? Ex: What data do I need on
my customers, on my suppliers, on my inventory, on my staff etc.
A technique, among others, than you can use consists in working from the "top-down". Think of the forms and reports that you'll need. Knowing the
results that you need, you can then find what fields
you will need to reach these results. For example, you must know the quantities
sold and the unit price before knowing the sales' total.
You can also use the "bottom-up" technique. From the base, you determine
all the data that are required for your queries, forms and reports of your
database. You can use both techniques to insure that you have all the necessary
data.
For this exercise, take a moment and write on a sheet of paper
the fields you can find on an invoice. Try to find as many fields
as you can.
To avoid cheating, I put the list of the fields that I found at the bottom
of this page. You can compare with my results after having done the exercise. Press this link to see the list.
The second stage: group together the data
into tables.
You can then group together the fields into entities in a table
that have a common theme. Ex: Will the fields "Discount" or "Terms of payment"
go to the "Invoice" table or with "Customer"? It all depends on your
conditions you have to fulfill.
To start things off, I'm going to demonstrate first my technique to group together
fields into tables and to determine the relations between these tables. Later,
we will look at the normalization technique. My technique is a little more simplistic
and asks for a little of experience. The use of the technique of the normalization
is, in a way, more difficult to carry out. But it's infallible when you understand
and apply it correctly. These two ways try to reach the same result: to group
together fields in tables in the most efficient way possible. For starters ...
My technique.
The concept is to distribute fields in the biggest number of possible tables.
But there are certain rules that should be followed:
Regroup
fields into tables that have something in common: an object, a person or an event
for example.
Fields
should not apper twince, unless there fields that help link two different tables.
No
double, or triple, data entry.
No
fields that you can determine by using other field in the database. . Ex.: Total
= Qty * Unit price
It's necessary to group the fields into tables. What are the fields can be
grouped together? To help you in this task, base yourselves on elements of your
everyday life, something concrete. For the invoice, you can find elements that
are on the invoice, the customer, the products and the salesperson. That makes
four tables that will be used: Invoice, Customer, Product, Employee.
Make sure that fields are in the right table. There should be no tables hidden
inside another. Each is distinct.
With the exception of fields in common that are used for "connecting" the
tables together, a field should not be in several tables. Again, let's take
for example, the field Telephone number. Although, in this example, it could
be in the Employee and Customer table, it's not the same thing. One is the number
of an employee, the other of a customer. They have the same name but don't carry
the same information.
Determine the primary keys
A primary key is a field, or a series of fields, that allows to differentiate
a record of the others. For example, although you can have several invoices
with the same date, the same quantity bought, to the same customer with the
same salesperson, there will not be two invoices with the same invoice number.
A primary key is not compulsory for a table. It does becomes essential when
you want to connect two tables. At least one of the two tables must have a primary
key.
Try to determine that field would be suited for a primary key for the Employees table. Certainly not the field Sex. Unless there is only a man and
a woman in the company. This limits a little the growth of the company! Smoker
or not ? Same thing. There are only two possible values (M or F, Yes or No...).
Because one cannot enter the same value twice in a primary key, these fields
are not a good choice.
It's possible to use the field Last name until two persons have the
same name. Then, it's always possible to create a primary key using of two
fields: Last name and First name. This key will work correctly
until two employees have the same first and last name. To solve this problem,
you may create a primary key using of the Last name, First name and the date of birth. This primary key will work correctly
until... This can continue for a long time.
After a certain point, you'll see that it's better to have a field named
Employee's ID number or Social security number to distinguish each of the records
of the table. But there are occasions where you must have a primary
key that consists of several fields. One of these situations will be explained
a little farther on this page.
Take a moment to try to determine that fields in the tables would be best
suited as the primary key or be part of the primary key. The explanation to
apply the primary key to one or several fields of the table is on the following
Web page on tables.
Determine the relations between tables
Determine the types of relations ( cardinality)
There are three types of relations: 1 to 1 , 1 to Many and Many to Many.
The picture above demonstrates the list of tables with the fields that compose
them. The rows indicates the relations between these tables. A many to many
relation, like between Invoice and Products, requires a compound
or intermediary table, with at least the primary key of both tables to be included.
That's the reason for the new table Transition Fact-Inv.
The normalization and the normal forms.
The advantage of a relational database is to avoid as much as possible the
repetition of data. The normalization serves at separating the list of fields
into several tables so you can have a more efficient database. The normal forms
gradually removes some the problems that you can find in databases. You can
use various normal forms (1st , 2nd , 3rd...)
For the moment, we are going only to look at the first three normal forms.
Before showing how to carry out you the first normal forms by using the list
of fields that one finds in an invoice, here are the rules that the tables
in your database should follow so that you have an efficient database.
The first normal firm is necessary to eliminate the repetitive groups by separating
them in several tables.
The work to be carried out to reach the first normal form is to avoid completely
the repetition in data entry.
But it's also necessary to implement this form to make sure that the user
is not going to enter several times the same data. For example, it would not
be efficient to have a table "Invoice" that would contain fields " Name of
the customer " , " Address of delivery " , " person in charge ". It would not
pass in the first normal form. The reason is that it would be necessary that
the user enters for every invoice the same data that he already has entered
in the previous invoices for the same customer. After all, how many times can
you enter the same address? That's not really efficient! It's for that reason
that it's necessary "to cut" the list of the fields that you need into several
tables to have an efficient form for data entry.
The second normal form helps eliminate partial dependences. Make sure that
all the fields of the table belong to the same primary key. Otherwise, it will
be necessary to split the fields and to create a new table.
The third normal form is eliminate the transitive dependences (the what???)
make sure that there are no tables that are hidden from the others.
Also, tables should not contain calculated fields. For example, a table should
not have the fields "Subtotal", "Total", " GST ", " TVQ ", "VAT" (value added
tax in Europe) or other taxes " because you may calculate them from
the data that are already in tables. For example, you may have the
"Subtotal" by multiplying " Quantities sold " by "Unit price". So, it's useless
to take that space for subtotals in tables.
The third stage is to determine the relations between the various tables. It's necessary to look that are the possible relations between the entities.
To have a relation, two tables should have at least a field in common. You can
connect an invoice to a customer with the field " Client ID". You can also connect
a product to an invoice by the field " Product ID " etc. You should have to
at this moment notice that certain fields would be better placed in another
entity. Make the changes in the tables if necessary. When you'll have grouped
the fields together and determined the relations, you'll have your base for
the creation of tables.
Now that you have the entities (tables) and the fields that compose them,
think of what your forms and your reports should look like. Do the fields that
you chose answer all your needs? Take all the time required for this analysis.
It will cost you a lot more time and effort if you pass too quickly on the creation
process and forget important elements.
Repair a database
Problem can happen when you try to save or move a database file. Access offers you a
way for repairing a damaged database. The procedure is made in two stages: repair
and compact the database. The first part isolates the problem and the second
removes the problematic parts from the database.
Open
Microsoft Access but don't open any database.
From
the Tools menu, select the Utility option.
Select
the Repair database option.
Select
the database that needs to be repaired on your hard drive or diskette.
Press the Repair button.
Access will take a few moments to repair your database. If everything works
well, Access will show you a message indicating that the database was repaired
successfully.
Compact a database
This previous part isolates the problem. But it's still in the database. The compact
operation will remove the records, the tables, the queries and the reports
that you removed from your database, but also the problem. Although they are
not accessible anymore, they are still in the database file.
Open
Access but don't open any database.
From
the Tools menu, select the Utility option.
Select
the Compress a database option.
Select
the file to compress the hard drive or the diskette.
Press the Compress button.
The operation to compact a database consists in creating a new database with
only the elements that are still useful. Access will ask you for
the name of the new database.
Enter
a name for the new database of your choice in the Name of file box.
Press
the Enter key or the Save button.
In spite of this procedure, it's always possible that the database is too damaged
to be repaired. For that reason, as much for Access's databases as for any other
document, that you should have a copy of your documents. Don't forget that with
Access, it's impossible to make a copy while the database is opened. You can
copy the file of the database only after having closed Access.
List of fields
At the beginning of this page, I asked you to determine the list of fields
that you would find on an invoice. Here is the list of fields that I found.
There are probably even more.
Date, Invoice ID Number, Customer's ID number, Customer's address, City, Phone
number, Fax Number, E-mail Address, Address for delivery, Person in charge,
Terms of payment, Number of product, Description of the product, Unit price
of the product, Quantity bought, Total for the item, Subtotal, GST (Goods and
Services Tax or GST, sales tax of 5 % on the total in Canada, possibly a sales tax or a value added tax in your area), TVQ (Taxe of vente the Quebec or TVQ,
9,975 % sales tax of the total and the GST), Grand total, Discount, Order form,
Employee ID number, Name of the salesperson...
Wow! It's a little more than you might have though. click
here to return to the text.
|