Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6!:Database Programming: Tools And Design
function GetCookie (name) { var arg = name + "="; var alen = arg.length; var clen = document.cookie.length; var i = 0; while (i < clen) { var j = i + alen; if (document.cookie.substring(i, j) == arg) { var end = document.cookie.indexOf (";", j); if (end == -1) end = document.cookie.length; return unescape(document.cookie.substring(j, end)); } i = document.cookie.indexOf(" ", i) + 1; if (i == 0) break; } return null; } var m1=''; var gifstr=GetCookie("UsrType"); if((gifstr!=0 ) && (gifstr!=null)) { m2=gifstr; } document.write(m1+m2+m3);            Keyword Title Author ISBN Publisher Imprint Brief Full  Advanced      Search  Search Tips Please Select ----------- Components Content Mgt Certification Databases Enterprise Mgt Fun/Games Groupware Hardware IBM Redbooks Intranet Dev Middleware Multimedia Networks OS Prod Apps Programming Security UI Web Services Webmaster Y2K ----------- New Titles ----------- Free Archive To access the contents, click the chapter and section titles. Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6! (Publisher: The Coriolis Group) Author(s): Peter G. Aitken ISBN: 1576102815 Publication Date: 08/01/98 function isIE4() { return( navigator.appName.indexOf("Microsoft") != -1 && (navigator.appVersion.charAt(0)=='4') ); } function bookMarkit() { var url="http://www.itknowledge.com/PSUser/EWBookMarks.html?url="+window.location+"&isbn=0"; parent.location.href=url; //var win = window.open(url,"myitk"); //if(!isIE4()) // win.focus(); } Search this book:  














Previous
Table of Contents
Next




Chapter 21Database Programming: Tools And Design


So, you want to play with the database bigshots? Visual Basic is the key, but picking up some basics about database design and programming tools first, will give you a big boost. Then, you can start designing your own database application.
Database programming is unlike any other kind of programming that you’ve ever used. Sure, a simple database such as the one that you tackled in Chapter 19 can be slapped together pretty easily with a little common sense and a few fundamental programming skills. But quite honestly, that’s kid stuff. The database applications that paying customers want are many times more complex—and correspondingly more difficult to program.
Visual Basic simplifies many aspects of database programming, but it doesn’t remove the need for skill and care on the programmer’s part. You need to know some fundamentals of database design and understand the tools that Visual Basic offers for database development. You will use some of these tools in the relational database that you develop in the next few chapters, but many other Visual Basic tools are available that you won’t need to use in that project. This chapter offers a quick lesson in database programming fundamentals and a brief overview of some of the more important components of the Visual Basic database toolbox.
Some More Terminology
When you do serious database development, you work almost exclusively with relational databases. But to work with relational databases, you first must know exactly what a relational database is and be familiar with the specialized terminology that is thrown around by database developers.
Relational Databases
With rare exceptions, all modern database development involves relational databases, rather than flat file databases, such as the one that you developed in Chapter 19. A flat file database contains only a single table—thus, it is two–dimensional, or flat. One dimension is represented by the fields, or columns, and the other dimension is represented by the records, or rows. Flat file databases are perfectly suitable for tasks such as address lists, but as users’ demands grow more complex, the flat file structure soon reveals its weaknesses.
The contrast between flat file and relational databases can be shown best through an example. For this example, I’ll use a database that is intended to keep track of inventory for an electronics store. Each record in the database table contains the following fields, which are necessary to hold the information about a single stock item:

•  Stock Number
•  Description
•  Type
•  Wholesale Cost
•  Retail Price
•  Quantity On Hand
•  Manufacturer Name
•  Manufacturer Order Number
•  Manufacturer Street Address
•  Manufacturer City
•  Manufacturer State
•  Manufacturer ZIP
•  Manufacturer Telephone

This list may seem adequate at first glance, but imagine what happens if the store has many stock items from one manufacturer. For each stock item, the manufacturer’s name, address, and other information have to be entered, even though this data is the same in each case. This approach is highly inefficient, not only because it wastes operator time and increases the chance of errors, but also because it consumes valuable disk space by storing all the duplicate information. The solution? A relational database. Instead of a single table that contains all the necessary information, the relational database contains multiple tables, with the information spread among them. For this example, the relational database structure might consist of two tables, as follows:
The Stock Items table:

•  Stock Number
•  Description
•  Type
•  Wholesale Cost
•  Retail Price
•  Quantity On Hand
•  Manufacturer Name

The Manufacturers table:

•  Manufacturer Name
•  Manufacturer Street Address
•  Manufacturer City
•  Manufacturer State
•  Manufacturer ZIP
•  Manufacturer Telephone

The information is not duplicated unnecessarily within this structure. One record exists in the Manufacturers table for each manufacturer, and one record exists in the Stock Item table for each stock item. The two tables are linked by the one field they have in common, Manufacturer Name. A relational database manager—the application program—has the capability to relate or join the tables in various ways, as required by the user. For example, in this application, the user probably wants (among other things) the following:

•  An automatic prompt for the manufacturer’s address information (if the manufacturer isn’t already entered in the Manufacturers table) when a new entry is created in the Stock Items table.
•  The capability to print out or display the manufacturer’s address for a particular stock item.
•  The capability to list all stock items that come from a specific manufacturer.

With two or more tables, the database is no longer flat—the multiple tables add a third dimension. The tables are designed to hold all the needed information. The relational database program can relate information in one table to information in other tables in a variety of ways, such as those just listed. The term relational derives from this capability.
Tables Are Tables
An individual table in a relational database is really no different than a table in a flat file database. Each column, or field, holds an individual piece of information, while each record, or row, holds all the information about an individual item. Note that in almost all cases, each table in a database represents something that exists in the real world—people, invoices, parts, orders, and so on.

Database programming uses some additional terminology. A table is sometimes referred to as an entity or an entity class. Rows (records) are sometimes called tuples or entity occurrences. Columns (fields) may be called attribute classes. An attribute class or field represents the most granular level of data—the smallest unit of information. The intersection of a row and column—a single field in a specific record—is an attribute. An attribute represents one unit of information about a real–world object, such as a specific individual’s last name or a specific company’s ZIP code.
Tables in a relational database have one requirement that is not necessary for flat file databases. Each table must have a primary key, a field that uniquely identifies each record in the table. In other words, the data in the primary key field must be unique for each record in the table. In the example database, Stock Number is the primary key field for the Stock Items table, and Manufacturer Name is the primary key field for the Manufacturers table. Most relational database applications support compound primary keys, which are primary keys that consist of data from more than one field. Compound primary keys can be used when the combined data from two or more fields uniquely identifies each record, as opposed to data from a single field.
You must be alert for situations in which the data being placed in the table does not include a primary key. In a table that contains name and address information, for example, you can’t be sure that the first name, last name, or even telephone number won’t be duplicated between two or more records. Granted, duplication in some situations is extremely unlikely. For example, if Social Security Number is one of the table’s fields, it may be used as the primary key—because it is unique for each individual. Lacking such a unique field, however, you can create your own primary key by adding a field to the table that will contain data that you know is unique, such as a sequential number that is incremented for each new record that is added. Note that the primary key field doesn’t have to contain meaningful data (although this is preferred)—just unique data.
A foreign key is a field whose data serves to link the records in the table with the primary key in another table. The data in a foreign key field doesn’t have to be unique for each record—in fact, it rarely is unique. In the Stock Items table, Manufacturer Name is the foreign key. The Manufacturers table doesn’t have a foreign key.
Tables are sometimes referred to as primary and dependent. A dependent table is one in which the records depend on information in another table for completion. The records in a primary table have no such dependency, being complete in and of themselves. In the example, the Stock Items table is dependent, requiring information from the Manufacturers table to provide complete data for one stock item. The Manufacturers table is a primary table, because it doesn’t depend on another table to provide complete information about a manufacturer (address, telephone, and so on). Generally, primary tables are created to avoid duplicate information being placed in another table.



Previous
Table of Contents
Next