Access 2003

Introduction

By the end of this lesson, you should be able to:

• Understand the usefulness of a database
• Define a relational database
• Appreciate databases using real-world examples

Organizing our world

The world generates an enormous amount of data from virtually every imaginable aspect of living—credit cards, store merchandise, telephone systems, websites, etc. Years ago, this was a manual effort to track and report against this information. Today, computer systems and databases manage this information. Databases simplify our lives.

Think of an address book you might buy in a pharmacy. In it, you will write all of your important contacts—friends, family, relatives, companies, recruiters, and other people in your network. The address book contains all of the data you need to contact anyone at any time.

Organizing our world

What if you needed to pull out only contacts who represent family members? Perhaps another family member might want this information. You would have to flip through every page of your address book and write down the exact same information onto another piece of paper, or perhaps even another address book. This is time consuming. Contact records are duplicated. A change to one phone number means it has to be changed in two or more places: in the address book and anywhere else where you recorded the information.

Over time, your address book will become old and worn. The records in it will be crossed out and changed as people move from one place to another or change their phone numbers. You need a new address book. And then you need to rewrite all pf that information again into a brand new book.

It's a lot of work.

What you need is a database.

What is a relational database?

A database maintains order and structure in our lives. It is easy to maintain. It manages information and then shares it with other tables and databases.

Many entries in your address book will undoubtedly contain identical information—name, address, city, state, zip code, and phone number. This information might be written into a table called the Contacts table.

You may want to structure your address book so family members are separated from companies called during an employment search, for example. These categories—family, friend, relative, company—might appear in a second table called Contact Types.

Maybe you want write notes for certain calls. This is useful during an employment search. Information pertaining to a telephone call—date, time, and notes, for example—might be saved to a third table called Calls.

What is a relational database?

The information contained in any one table might in and of itself tell us very little. The Contact Types table, for example, provides relatively little information that stands on its own: Family, Friends, Relatives, Companies, Recruiters, and Network.

However, if you could relate this table to the Contacts table—name, address, and phone—then you could separate contacts by category. The Contact Types table becomes useful, therefore, when related to another table.

Similarly, by relating the Calls table with the Contacts table, you can relate specific phone calls to specific people.

The power of a relational database is twofold: Information is managed in separate tables to make maintenance easier, and data can be combined by relating different tables.

Real-world examples of database applications

Databases play an enormous role in nearly every aspect of our lives. Think of the amount of credit card transactions that take place during a given day, from receiving authorization at the point of sale to applying the purchase to your credit card to the store receiving payment from the credit card company. There are many stores, many banks, and many credit cards involved. Databases are all around you. You only need to learn how to view the world in terms of databases.

Think of your favorite department store. Chances are the one you shop at is just one of several in the chain. And these stores are probably located in different states. Databases manage this information.

Inside the store there are different departments: Ladies, Mens, and Electronics, to name a few. Within Electronics, there are different categories called classes, and these might be represented by radios, televisions, CDs, and DVDs. More databases.

And within any given class—CDs, for example—there are subclasses like jazz, rock, country, and classical. Within rock there are bands, and within bands there are recordings.

Get the idea? Even more databases.

This structure categorizes different kinds of information at almost every level of the store. It is essential to maintaining order and accuracy.

Real-world examples of database applications

How do these databases work together? Well, everything in retail begins with the product or item. Each item has a barcode that uniquely identifies it. These items are defined in an item database and are associated with a specific department, defined in a department database.

When a buyer decides to purchase some products for sale in a store, he or she creates an order in a purchase order database, pulling the individual items to buy from the item database. The order might then be transferred to communications databases that electronically transmit the order to the vendor, and also to an accounts payable database to pay for the merchandise.

Trucks deliver merchandise to the store (remember that the vendor has its own database indicating what is shipped), where the products received are input into a receiving database. The received quantities are then matched against the original order database to make sure all ordered products were received.

An inventory database might tell the sales clerk which items to move to the selling floor because the shelves are bare, and what other items might be stored in the stockroom. For items moved to the selling floor, a shelf planning database might tell the clerk exactly where to place the product on the shelf.

When you shop and take items to the checkout counter, the cash register will look up the price of each item in the price lookup database. Want to pay by check or credit card? Databases are used to ensure sufficient funds are available in the account.

Every item sold in the store needs to be removed from inventory so it can be reordered. This adjustment might be made in the inventory table to alert the buyer that a certain amount of items need to be ordered to refresh the store's inventory.

Many things happen, and many databases are used to make sure the products you want make their way from the manufacturer to the stores and then into your home.

Challenge!

The next time you're in a restaurant, at a supermarket, or are simply driving through a stoplight, think of what is going on around you and try to see what goes on behind the scenes as if everything supporting it is managed by a database.

Here are some examples:

• A restaurant needs to buy food to make menu items. The menu is structured in some manner. The restaurant has to pay bills. Customers have to pay for meals.
• The supermarket is stocked with items. Items are ordered, shipped, and stocked in the store. The supermarket has bills to pay. The cash register looks up prices. The customer pays for products.
• Streets are built in towns. Traffic lights are installed at intersections. How do these lights work? Do they all turn red at the same time? Do they all turn green at the same time?

Now think of your home, and view it in terms of a database. For example, you buy products. How is your checkbook managed? There are different rooms in your house. What are they, and what is contained in each? You develop dinner throughout the week. Is there a menu driving what you have, or do you eat certain dishes on certain nights?

Come up with two different aspects of home living, and think about whether they can be supported by a database. This is not to say that we are going to create a database for it; it's just an exercise to think of a structure or organization behind a particular process.