dBASE onLine Training
The Hands-on Project
Michael Nuwer
November 11, 2002
|
dBASE onLine Training
|
|
The Database |
The Hands-on Project will use a database called Pets. You have already created a set of tables while working with the Tutorial Project, and there is not much need to practice with the table designer, so you will not need to create the tables used in the Pets database. The tables you will need were copied to the \Pets\Tables folder when the Hands-on Project was installed in Session 1.
We will need to create a database alias to use those tables in the Pet Clinic application. Use dQuery to create a database alias named "Pets" that points to these tables.
After you have create the alias, leave dQuery open so that we can use it to explore the structure of the Pets database.
In the remainder of this session we will describe the Pets database. You should become familiar with the tables and their fields, with the data types used, with the indexes and with the relationships.
The database is composed of six tables. There are four main tables: Customer.dbf, Pets.dbf, Visits.dbf, and VisitDetails.dbf, and two tables, Treatments and Animals, that contain reference information.
The Customer table contains information about each of the Clinic's customers. Add this table to the dQuery design surface. This will help you see the fields and the data in the tables.
The primary key for this table is an autoincrement field named CustomerID. This field is used to link customer data with data in other tables.
In the Pets system there is another field that is called Customer Number. The Customer Number field is needed so that our user can find and reference customer records, but the field is treated like any other field in the table. It is not used to relate data in other tables.
The Customer Number is assigned based on the customer type. The Clinic has three types of customers -- Individuals, Pet Stores and Zoos. For Individuals, the Customer Number is the first two letters of the last name plus a three-digit sequence number. For pet stores and zoos, it is the first letters of the first two major words plus a three-digit sequence number.
The use of both a CustomerID field and a Customer Number field is one way to design a database. It enables us to use the CustomerID field to link tables but it is never visible to the user. The Customer Number is visible to the user, but if a data entry mistake is made, our table relations are not effected.
You can use dQuery and the following table to review the remaining fields in this table.
-------------------------------------------------------------------
CUSTOMER.DBF
-------------------------------------------------------------------
Field Field Name Type Length Dec Index
1 CustomerID AUTOINCREMENT 4 Y
2 Customer Number CHARACTER 10 Y
3 Type of Customer CHARACTER 3 N
4 Name CHARACTER 30 Y
5 Street CHARACTER 35 N
6 City CHARACTER 35 N
7 State CHARACTER 2 Y
8 Zip Code CHARACTER 10 N
9 Phone CHARACTER 13 N
10 Customer Since DATE 8 N
11 Last Visit Date DATE 8 N
12 Discount NUMERIC 10 2 N
13 Outstanding Balance NUMERIC 10 2 N
-------------------------------------------------------------------
Next let's look at the indexes for this table. Right click on the Customer1 query object and select the "Set Index" item. In the Set Index dialog form you will see four indexes. The first three indexes are created without an expression. The "Name" index uses an expression that converts the Customer's name to all upper case. This will help use locate records in our forms.
The Pets Table
The Pets table contains information about each animal served by the clinic. Add the Pets table to the dQuery surface and let's review the fields.
The PetID field is an autoincrement type and it is the primary key for this table. Since each customer in this database has one or more pets (a one-to-many relationship), the Pets table also contains a foreign key for CustomerID. With this field we will be able to find the customer who owns the pet.
For example, look at the data grid for the Pets2 query in dQuery. We can see that the first four pets (the ones with PetID 1, 2, 3, and 4) are owned by the Customer with the CustomerID of 10.
Each pet in the Pets table is assigned a pet number. This number is constructed from the Customer Number plus a hyphen plus a sequential number. For Example, the Pet Number for "Bobo" is AC001-01 while the Pet Number of "Presto" is AC001-02. The Customer with the CustomerID 10 owns both animals.
You should also look at the Indexes for this table. Bring up the Set Index dialog form. You will see four indexes. Pet Name is indexed with an expression, PetID is the primary key in the table, CustomerID is the foreign key, and Pet Number is the final index. Notice that when the Pet Number is the active index, the pets will be sorted by customer and grouped together.
-------------------------------------------------------------------
PETS.DBF
-------------------------------------------------------------------
Field Field Name Type Length Dec Index
1 PetID AUTOINCREMENT 4 Y
2 CustomerID LONG 4 Y
3 Pet Number CHARACTER 8 Y
4 Pet Name CHARACTER 35 Y
5 Animal Type LONG 4 N
6 Type of Animal CHARACTER 20 N
7 Breed CHARACTER 20 N
8 Date Of Birth DATE 8 N
9 Gender CHARACTER 7 N
10 Colors CHARACTER 50 N
11 Neutered_Spayed LOGICAL 1 N
12 Length NUMERIC 5 N
13 Weight NUMERIC 5 N
14 Last Visit Date DATE 8 N
15 Current Vaccinations LOGICAL 1 N
16 Deceased LOGICAL 1 N
17 Comments MEMO 10 N
-------------------------------------------------------------------
The Visits Table
Next you should add the Visit table to the dQuery surface. Each Pet has one or more visits to the clinic where services are provided. In the Visits table, the primary key is the autoincrement field called VisitID. This table contains a PetID field which is a foreign key used to relate a visit with a pet. The Visit Number is a field that is visible to the user and is constructed from the four-digit year, the Julian day and a sequence number.
-------------------------------------------------------------------
VISITS.DBF
-------------------------------------------------------------------
Field Field Name Type Length Dec Index
1 VisitID AUTOINCREMENT 4 Y
2 PetID LONG 4 Y
3 Visit Number CHARACTER 11 Y
4 Visit Date DATE 8 Y
5 Follow Up Type CHARACTER 20 N
6 Follow Up Date DATE 8 N
7 Total Amount NUMERIC 10 2 N
8 Payment Type CHARACTER 10 N
9 Date Paid DATE 8 N
10 Amount Paid NUMERIC 10 2 N
11 Taxable LOGICAL 1 N
12 Tax Rate NUMERIC 10 2 N
13 Invoiced LOGICAL 1 N
-------------------------------------------------------------------
The VisitDetails Table
The VisitDetails table contains the treatment and medication services administered during a visit. Add the VisitDetails table to the dQuery surface so that we can review the fields.
The primary key in this table is the ItemID field; the VisitID field is a foreign key relating the item to the visit. These two fields are the only indexed fields in the table.
-------------------------------------------------------------------
VISITDETAILS.DBF
-------------------------------------------------------------------
Field Field Name Type Length Dec Index
1 ItemID AUTOINCREMENT 4 Y
2 VisitID LONG 4 Y
3 Visit Type CHARACTER 20 N
4 Treatment Code CHARACTER 4 N
5 Price NUMERIC 10 2 N
-------------------------------------------------------------------
In addition to the four tables discussed above, the Pets database has two reference or lookup tables. Animals contain a list of valid animals types. Treatments contain treatment and medication names and prices.
-------------------------------------------------------------------
TREATMENTS.DBF
-------------------------------------------------------------------
Field Field Name Type Length Dec Index
1 Treatment Code CHARACTER 4 Y
2 Treatment CHARACTER 30 N
3 Price NUMERIC 10 2 N
-------------------------------------------------------------------
-------------------------------------------------------------------
ANIMALS.DBF
-------------------------------------------------------------------
Field Field Name Type Length Dec Index
1 AnimalID AUTOINCREMENT 4 Y
2 Animals CHARACTER 25 Y
-------------------------------------------------------------------
This concludes our overview of the Pets database. You can close the dQuery designer without saving datamodule. The next step is to create some datamodules for the application.
The Legal Stuff: This document is part of the dBASE onLine Training Program created by Michael J. Nuwer. This material is copyright © 2001-2002, by Michael J. Nuwer. dBASE, dBASE Plus, dBASE SE and dB2K are copyrighted, trademarked, etc., by dBASE, Inc., the BDE (Borland Database Engine) and BDE Administrator are copyrighted, trademarked and all that by Borland Software. This document may not be posted elsewhere without the explicit permission of the author, who retains all rights to the document.