SCOUG OS/2 For You - April 1999
The "Sundial" SIG
Database Design Was First on the Agenda
by Sheridan George
for This New Special Interest Group
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!
The Southern California OS/2 User Group
P.O. Box 26904
Santa Ana, CA 92799-6904, USA
Copyright 1999 the Southern California OS/2 User Group. ALL RIGHTS
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.