SCOUG OS/2 For You - April 1999

The "Sundial" SIG held its very first meeting on Tues. March 23, 1999, at Heritage Christian High School. Of the three participants, only one was familiar with databases. The other two were absolute amateurs. (Blatant commercial announcement: more experienced and inexperienced attendees are needed and welcome!)

The agenda was to look at what a relational database is and how it works. Hands on experience was via Sundial's DBExpert which is a relational database manager.

We started with flat files

To understand relational databases, it was important to see the benefits and detriments of a flat database first. A flat file of an address book was designed using 7 fields: first name, last name, address, city, state, phone1, and phone2 (see fig. 1).

Figure 1. Flat file with 2 phone number fields

Participants were shown how to add data to the flat database. This was quite easy until they had to add a person that had three phone numbers. One option was that you could decide to ignore one of the phone numbers. Or, you could solve the problem by adding another field.

This, in fact, is what we did, which required a change to the design of the table by adding Field Phone3 (fig. 2). A flat database will require modification any time a new field of data that was not anticipated by the table designer is needed.

Figure 2. Flat file modified for an additional data field (Phone3)

A relational database eases this problem

We demonstrated a solution by breaking the flat table into three tables: one for the name, address, and city; one for the phone numbers; and one for the type of phone service (home, business, cell, ...).

Table 1 consists of 6 fields: FName, LName, Address, City, state, and Phones. Here the field phones contains a computer generated 'primary key'. It is used to relate a person to all phone numbers associated with that person (fig. 3a).

Figure 3a. Main relational table showing the primary key Phones field

Table 2 consists of 3 fields (fig. 3b):

• PhoneKey: a foreign key relating to table 1's primary key
• PhoneNumber: contains the actual phone number
• PhoneType: a foreign key relating to table 3's primary key

Figure 3b. Phones table

Table 3 has only 2 fields (see fig. 3c):

• PhoneType: a computer generated Primary Key
• dType: contains the phone service type (home, business, cell, ...).

Figure 3c. PhoneCategory table

The SIG participants created a query (see fig. 4) that demonstrated how all of these tables are joined to produce useful data. They also discovered how difficult it is to fill such a set of tables by manually filling a record.

Figure 4. The query to display all names, phone numbers, and phone types.

Continuing In April

Next month we will create a form to make the job of record filling a snap. Also, we'll have a discussion about table design by expanding the address book example. From now on the 'Sundial' SIG will meet on the Thursday following the Saturday general meeting (April 22nd) - 7 PM at Heritage Christian High Sshool. Come and experience a hands on SIG!

SCOUG, Warp Expo West, and Warpfest are trademarks of the Southern California OS/2 User Group.
OS/2, Workplace Shell, and IBM are registered trademarks of International Business Machines Corporation.
All other trademarks remain the property of their respective owners.