Getting to Know MS Access 2003, Part 2

There are several types of databases, as you learned in last week’s lesson. Access 2003 is a relational database. A relational database is one which stores logically-related data in tables. Tables are the most important objects in relational databases; you’ll learn more about tables next week. The other objects, such as forms, reports, and queries, are just better ways of entering, deleting, and retrieving data; we’ll learn more about these in a later lesson.

In any of your databases, you could store all your data in one table, such as you would do in a flat file database like Excel, but this wouldn’t make good use of Access’s power. Generally, you will want to distribute your data among several tables. The following are some of the major advantages of a relational database such as Access:

• Data redundancy is reduced, decreasing hard disk storage space and increasing processing speed.
  
  
• Each table in the database contains data pertinent to only one object.
  
  
• Information is easy to locate and manage because the tables are small and uncomplicated.
  
  
• Changes to data need only be made in one place in the database, not in many different locations as might be the case with Excel for example; this is because changes propagate in Access
  
  
• Distributing data across tables allows for more powerful grouping, sorting, and filtering capabilities

Data Distribution

Let’s say you are going to build a database for your contacts. We’ll call this a ‘contact management database’ because that’s what Microsoft calls it. Before you build, you must decide how to distribute your data. How do you do this?

Well, you can divide your data by the user of the information, for example by department manager or sales clerk. In our example of a contact management database however, the contacts are personal.

Another method is to distribute data so as to reduce redundancy. In our own contact management database we might create separate tables for our types of contacts as well as our actual contacts. We do this so as not to repeat ourselves by unnecessarily having to type the type of contact over and over again in the contacts table. This has an added advantage as we’ll see next.

The third method is to decide how many records you are going to have in each table. For example, if we have 50 different contact types and 150 contacts, they will need separate tables. For example, let’s say I have a large number of personal contacts. I could lump them all together into one table, but I don’t want to do this because it would be hard for me to distinguish between contacts of Organization A and Organization B, for instance. Assuming I haven’t memorized the names of all my contacts and what organization they belong to, then it would be helpful to separate them into different groups. This is where the separation of tables comes in handy.

The fourth and final method of distributing your data is in the case of old data that is mainly for archival purposes. There is no reason to have this data in your main tables; it is helpful to separate them.

Relationships

Tables can be related to each other in any one of the three ways: one-to-one, one-to-many, and many-to-many. The type of relationship you use depends on the quantity of records in each of the tables that is going to have the same value. Let’s look at relationships in more detail.

When you relate tables, at least one of the tables must have a unique value for every record. This value should uniquely identify that record. This unique value is generally called a primary key, but it also may be some other unique index with no duplicate values. Moreover it can be a combination of fields that make a unique value for each record, such as a person’s first, middle, and last name, and their city of residence combined.

You will almost always use one-to-many relationships, however. A one-to-many relationship basically says that records in the parent table will have multiple matching records in the child table or none at all. The field, in the child table, that references the parent table is defined as the foreign key of the child table. This key uniquely identifies the record that is referred to in the parent table.

In the above image you can see multiple one-to-many relationships in effect. These are represented by lines with 1’s at one end and ‘infinity’ symbols at the other. The 1 side represents the parent table, while the ‘infinity’ side represents the child table in a particular relationship. I’ll show you how to create relationships in a later lesson.

Referential Integrity

Referential integrity is an optional rule in Access. It guarantees your table relationships are legitimate, and the database will remain functional as you manipulate it. Here are some of the rules of referential integrity:

• There can be no child tables without a parent table
  
  
• A parent table cannot be deleted if child tables exist
  
  
• A child record cannot be changed so that its foreign key doesn’t have a valid match in a parent table
  
  
• The primary key of the parent table cannot be changed if it is referenced by children tables

It’s a good idea to enforce these rules and I’ll show you how to set referential integrity later on.

Database Objects

Because Access is an object-oriented database, it’s composed entirely of objects. Each of these objects, such as tables or forms, has properties that allow you to manipulate their look and feel. Moreover, each Access object holds other objects, which in turn have other properties. For example, tables are composed of fields (objects), with properties such as size and format.

Designing Your First Database

When you decide to build a database, you don’t just sit down and throw it together. You plan and analyze it. You do this by interviewing prospective users to find out what the database is supposed to do, how it’s supposed to look, and anything else that is relevant. This is not a straight path of development, but an iterative one.

Let’s take a look at the sample database that comes with Access: Northwind. It’s a good idea to explore this sample database on your own because it is a good example of a well-made database.

Here’s what the Northwind database looks like when opened:

The table view (shown above) presents us with a good illustration of data distribution. Northwind has eight distinct tables. Divisions such as Employees, Customers, and Suppliers help keep distinct data separate.

Check out the table relationships using the Tools | Relationships menu option. You saw what this looked like earlier in the section on relationships. You can see that the Orders table, for example, is related to the Customers, Shippers, Employees, and Order Details tables. In three of the four cases the Orders table is on the many side of the relationship, designating it as a child table. However, with regards to the Orders Details table is a parent table, since it is on the 1 side of the relationship.

You can discover more information about the various relationships than just whether or not they are one-to-many. Right-click one of the relationship lines and choose the Edit Relationships option from the shortcut menu to see and manipulate properties of the relationship, such as whether or not to enforce referential integrity.

Creating Your First Database

Now that you’ve seen the sample database, it’s time to make up your own. You have two main options when creating a database from scratch: you can use the wizard or you can create a blank database.

Creating a blank database is very simple. Either click the New button on the toolbar or choose File | New. Select Blank Database from the New File task pane. You’ll have to specify a name for your database, where you want to store it on your computer, and finally you must click Create. Now comes the hard part: you have to create all your tables, forms, queries, and reports.

The other way to create a new database is to use the database wizard. To open the database wizard choose File | New. Then choose a template from Office Online or select one from your computer (installed with Access 2003). If you choose to get a template from your computer, you’ll be presented with a dialog box with two tabs: General and Databases. Choose the Databases tab and you’ll a screen similar to the following:

You can highlight each template to get an idea of the templates’ style. When you find one you like, go ahead and click the OK button. For this example, I’ll choose Contact Management. Next you’ll have to choose where you want to store the file on your computer. I’ll choose the Desktop. The next dialog box looks similar to the following and gives you a summary of what information your database will contain; mine has Contact information and Call information.

Click Next and you’ll see a dialog that will give you the option to edit the fields you want to have in your tables. Optional fields are italicized and have corresponding check boxes that are not checked; check them if you want to include the fields. I’ll check the optional field ‘Birthdate’. The screen should look like the following image.

Next you’ll choose a style for your screen displays; I chose Standard. Click Next and you’ll be given a choice of style for printed reports; I chose Bold. Finally you’re given a dialog where you have to name the database you are creating; I kept the default name. You may choose to include a picture if you like by checking the box next to the words, ‘Yes, I’d like to include a picture.”  The display looks like the following:

Lastly, the wizard asks if you want to start the database right away. Do so and check out the objects in your database the wizard has created for you. As you can see, the wizard does a lot of the work of creating a good, working database that you would normally have to slave over if you created it from scratch. This can be a huge timesaver. Take some time and explore the database you’ve just created. It’s not too functional now, but next week we’ll dive into tables. As for this week, well we’re just about finished here. Make sure to poke around the Northwind sample database, and stay tuned to next week’s lesson on tables.