Before we begin, let's look at some of the advantages of a database.
+Access data more quickly and easily.
+All the data is in a single place; in your database
+Able group together data that comes from several sources.
+Easy to make Ad hoc searches or analysis. Ex: How many of product X do we have in stock? Who are top ten customers? What are our best products? Who are my best salespersons? My worst ?
+Better follow-up on data.
+Better data management.
+Better data analysis.
+Better decision-taking.
+Better able to meet the needs of the customers.
+Automate some repetitive tasks.
Why learn how to use a relational database?
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 of 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, relations between each of them. Here is how 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 in 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 is 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. Ex: Employees, Inventory, Customer, Suppliers, Vehicles, Contacts etc.
Database: A group of tables, queries, forms, reports and 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 lists, 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 |
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 recording 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 it's necessary to 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.
Warning !
If you are in the computer lab to create your first database, make sure to save the file in drive A: of your PC. Do not create your database on the hard drive. Otherwise, a technician will be obliged to come to help you to move it on to your floppy disk. Every school semester, a student "loses" his database that he, or she, created on the hard drive instead of his or her personal floppy disk.
*From the File menu, select the New option.
Access will ask you the name that you wish to give to the new database and where (drive, diskette, folder ...) you want to place it.
*For the purpose of this demonstration, call it ACCESS1.MDB.
Press the OK button.
Now you created the database, it remains you still have to create tables, enter the data, create the queries, forms, reports, macros and the modules for your database. So, there's still a lot of work to do. But it's a start.
Repair a database
It can happen that you accidentally removed the floppy disk that contains your database before having closed it in Access. Or, the floppy disk has a problem that can wreck a database. This can damage a database. Access offers you a way for repairing a damaged database. The procedure is made in two stages: repair and compact the database. The first one isolates the problem and the second removes the problematic parts from the database.
*Open 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
The last stage isolated 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, 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 du Quebec, 7,5 % 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 following text.
References
None have been entered at this time. You may suggest some site by sending an e-mail with the Web address.
No comments:
Post a Comment