vcg04.htm

Visual C++与数据库的连接经典实例

<A NAME="E69E105"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Database Terminology</B></FONT></CENTER></H4>

<BR>

<P>Whatever data storage and retrieval mechanism is used, the fundamental element of a database is a <I>table</I>. A table is a database object that consists of a collection of <I>rows</I> (<I>records</I>) that have an identical collection of properties. The values associated with the properties of a table appear in <I>columns</I> (<I>fields</I>). Row-column (spreadsheet) terminology is most commonly used in databases that employ SQL statements to manipulate table data; desktop databases commonly use record-field terminology. This book uses the terms <I>record</I> and <I>field</I> when referring to persistent database objects (Tabledef objects) and <I>row</I> and <I>column</I> when referring to virtual tables (Recordset objects) created from Tabledef and Querydef objects. The distinction between the two forms of terminology, however, isn't very significant. Figure 4.2 illustrates the generalized structure of a database table.

<BR>

<P><B><A HREF="04vcg02.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/04vcg02.gif">Figure 4.2. The generalized structure of a database table.</A></B>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The object hierarchy of the Microsoft Jet 1.x database engine supplied with Visual Basic 3.0 and Access 1.x included a Table object, a member of the Tables collection. The Table object and Tables collections don't appear in the Microsoft DAO 3.0 Object library or the Microsoft DAO 2.5/3.0 Compatibility library. Visual C++ 4.0 supports operations on Table objects, such as the OpenTable method, for backward compatibility with Jet 1.x code.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>The following list describes the most important properties of database table objects. These property descriptions and rules apply to tables of conventional databases that use only fundamental data types: character strings and numerical values.

<BR>

<UL>

<LI>A <I>record</I> is a representation of a real-world object, such as a person, a firm, an invoice, or one side of a transaction involving money. A record of a table is the equivalent of one punched card in a deck. In formal database terminology, a row or record is an <I>entity</I>. Synonyms for <I>entity</I> include <I>data entity, </I><I>data object, data instance, </I>and <I>instance</I>. Tables are the <I>collection</I> (set) of all entities of a single entity class; statisticians call a table a <I>homogeneous universe</I>.

<BR>

<BR>

<LI>A <I>field</I> describes one of the characteristics of the objects represented by records. A field corresponds to a column of spreadsheets.

<BR>

<BR>

<LI>The intersection between a row and a column is called an <I>attribute</I> and represents the value of a significant property of a real-world object. Attributes are also called <I>cells</I> and <I>data </I><I>cells</I>&#151;terms derived from spreadsheet applications. All the attributes contained in a single column of a table are called an <I>attribute class</I>.

<BR>

<BR>

<LI>The fundamental rule of all table objects is that each field is devoted to one and only one property. (This rule is implied by the terms <I>attribute</I> and <I>attribute class</I>.) Attribute values are said to be <I>atomic,</I> used here as a synonym for <I>indivisible</I>. Each field is assigned a field name that is unique within the table. A Name field that contains entries such as &quot;Dr. John R. Jones, Jr.&quot; isn't atomic; the field actually consists of five attributes: title, first name, middle initial, last name, and suffix. You can sacrifice the atomicity of fields to a limited degree without incurring serious problems. It's common practice to combine first name and middle initial, and sometimes the suffix, into a single field.

<BR>

<BR>

<LI>It's desirable, but not essential, for each record in a table to have a set of attributes by which you can uniquely distinguish one record in the table from any other record. This set is called the <I>entity identifier</I> or <I>identifier</I>. In some cases, such as tables that contain the line items of invoices, records need not have a unique identifier. It's good database design practice, however, to provide such an identifier, even if you have to add an item number attribute class to establish the uniqueness.

<BR>

<BR>

<LI>The fields that include the identifier attributes are called the <I>primary key</I> or <I>primary key fields</I> of the table. By definition, the set of attribute values that make up the primary key must be unique for each record. Records in related tables are joined by values of the primary key in one table and by equal values in the primary key or the foreign keys of the other table. A <I>foreign key</I> is one or more attributes that don't constitute the primary key of the table. These attributes connect the record with another record in the same table or a different table.

<BR>

<BR>

<LI>Tables that contain records identifying objects that are inherently unique, such as human beings, are called <I>primary</I> or <I>base</I> tables. Each of the records of a primary table must have one or more attributes that uniquely identify the entity. Theoretically, all U.S. citizens (except newborns) have a unique Social Security number that is used for identification purposes. Thus, an employee table in the U.S. should be able to use a single attribute&#151;Social Security number&#151;to serve as an entity identifier. A duplicate Social Security number usually indicates either a data entry error or a counterfeit Social Security card.

<BR>

<BR>

<LI>Tables are logical constructs; that is, they don't need to be stored on a disk drive in tabular format. Traditional desktop database managers, such as dBASE and Paradox, have a file structure that duplicates the appearance of the table. However, most mainframe and client-server database management systems store many tables (and even more than one database) within a single database file. Microsoft Access 1.0 was the first widely accepted desktop RDBMS to store all the tables that constitute a single database in one Jet .MDB file. There is no easily discernible relationship between the physical and logical structures of tables of mainframe, client-server, and Jet database types.

<BR>

<BR>

<LI>dBASE II introduced the concept of a <I>record number</I> to the world of PC databases. The record number, returned by xBase's RECNO() function, is an artificial construct that refers to the relative physical position (called the <I>offset</I>) of a record in a table file. Record numbers change when you physically reorder (sort) the table file. Record number isn't an attribute of a table unless you create a field and add record number values (often called a <I>record ID</I> field). Record numbers that appear in Access 95's equivalent of the Data control (navigation buttons) are generated by Access, not by the Jet database engine.

<BR>

<BR>

</UL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Online help defines a base table as &quot;a table in a Microsoft Jet database.&quot; This is a Jet-centric definition of base table, not the definition commonly used in RDBMS circles.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>A database consists of one or more tables; if more than one table is included in a database, the entities described in the tables must be related by at least one attribute class (field) that is common to two of the tables. The formal statistical name for a database is <I>heterogeneous </I><I>universe;</I> a recently coined alternative term is <I>universe of discourse</I>. This book adheres to the term <I>database</I>. Object-oriented databases (OODBs) don't conform strictly to the atomicity rules for attributes. Similarly, the OLE Object field data type of Jet databases isn't atomic, because the data in fields of the OLE Object field data type contains both the object's data and a reference to the application that created the object. The object type of the contents of Jet OLE Object fields may vary from record to record.

<BR>

<P>A <I>query</I> is a method by which you obtain access to a subset of records from one or more tables that have attribute values satisfying one or more criteria. There are a variety of ways to process queries against databases. Processing queries against databases with the Jet database engine is the subject of Chapter 5. You also can use queries to modify the data in tables. This type of query is described in Chapter 8, &quot;Running Crosstab and Action Queries.&quot;

<BR>

<BR>

<A NAME="E69E106"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Flat-File Databases</B></FONT></CENTER></H4>

<BR>

<P>The simplest database form consists of one table with records having enough columns to contain all the data you need in order to describe the entity class. The term <I>flat file</I> is derived from the fact that the database itself is two-dimensional. The number of table fields determines the database's width, and the quantity of table records specifies its height. There are no related tables in the database, so the concept of data depth, the third dimension, doesn't apply. Any database that contains only one table is, by definition, a flat-file database, if the database requires that the tables be flat. (Relational databases, for example, require flat tables.)

<BR>

<P>Flat-file databases are suitable for simple telephone and mailing lists. Windows 3.x's Cardfile is an example of a simple flat-file database designed as a telephone list. Ranges of cells, which are designated as &quot;databases&quot; by spreadsheet applications, also are flat files. A mailing list database, for example, has designated fields for names, addresses, and telephone numbers. Data files used in Microsoft Word's print merge operations constitute flat-file databases.

<BR>

<P>You run into problems with flat-file databases when you attempt to expand the use of a mailing list database to include, for example, sales contacts. If you develop more than one sales contact at a firm, there are only two ways to add the data for the new contact:

<BR>

<UL>

<LI>Add a new record with duplicate data in all fields except the contact and, perhaps, the telephone number field.

<BR>

<BR>

<LI>Add new fields so that you can have more than one contact name and telephone number field per record. In this case, you must add enough contact field pairs to accommodate the maximum number of contacts you expect to add for a single firm. The added fields are called <I>repeating groups</I>.

<BR>

<BR>

</UL>

<P>Neither of these choices is attractive, because both choices are inefficient. Both methods can waste a considerable amount of disk space, depending on the database file structure you use. Adding extra records duplicates data, and adding new fields results in many records that have no values (nulls) for multiple contact and telephone number fields. Adding new fields causes trouble when you want to print reports. It's especially difficult to format printed reports that have repeating groups.

<BR>

<P>Regardless of the deficiencies of flat-file databases, many of the early mainframe computers offered only flat-file database structures. All spreadsheet applications offer &quot;database&quot; cell ranges that you can sort using a variety of methods. Although spreadsheet &quot;databases&quot; appear to be flat, this is seldom truly the case. One of the particular problems with spreadsheet databases is that the spreadsheet data model naturally leads to inconsistencies in attribute values and repeating groups. Time-series data contained in worksheets is a classic example of a repeating group. The section &quot;Organizing Entity Classes&quot; shows you how to deal with inconsistent entity classes that occur in worksheet &quot;databases,&quot; and the section &quot;Normalizing Table Data&quot; describes how to eliminate repeating groups.

<BR>

<BR>

<A NAME="E69E107"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>The Network and Hierarchical Database Models</B></FONT></CENTER></H4>

<BR>

<P>The inability of flat-file databases to efficiently deal with data that involved repeating groups of data led to the development of a variety of different database structures (called <I>models</I>) for mainframe computers. The first standardized and widely accepted model for mainframe databases was the <I>network model,</I> developed by the Committee for Data System Languages (CODASYL), which also developed Common Business-Oriented Language (COBOL) to write applications that manipulate the data in CODASYL network databases. Although the CODASYL database model has its drawbacks, an extraordinary number of mainframe CODASYL databases remain in use today. There are billions of lines of COBOL code in everyday use in North America.

<BR>

<P>CODASYL databases substitute the term <I>record type</I> for <I>table,</I> but the characteristics of a CODASYL record type are fundamentally no different from the properties of a table. CODASYL record types contain <I>pointers</I> to records of other record types. A pointer is a value that specifies the location of a record in a file or in memory. For example, a customer record contains a pointer to an invoice for the customer, which in turn contains a pointer to another invoice record for the customer, and so on. The general term used to describe pointer-based record types is <I>linked list;</I> the pointers link the records into an organized structure called a <I>network</I>. Network databases offer excellent performance when you're seeking a set of records that pertain to a specific object, because the relations between records (pointers) are a permanent part of the database. However, the speed of network databases degrades when you want to browse the database for records that match specific criteria, such as all customers in California who purchased more than $5,000 worth of product &quot;A&quot; in August 1995.

<BR>

<P>The problem with CODASYL databases is that database applications (primarily COBOL programs) need to update the data values and the pointers of records that have been added, deleted, or edited. The need to sequentially update both data and pointers adds a great deal of complexity to transaction-processing applications for CODASYL databases.

<BR>

<P>IBM developed the <I>hierarchical model</I> for its IMS mainframe database product line, which uses the DL/1 language. The hierarchical model deals with repeating groups by using a data structure that resembles an upside-down tree: Data in primary records constitutes the branches, and data in repeating groups makes up the leaves. The advantage of the hierarchical model is that the methods required to find related records are simpler than the techniques needed by the network model. As with the CODASYL model, a large number of hierarchical databases are running on mainframe computers today.

<BR>

<BR>

<A NAME="E69E108"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>The Relational Database Model</B></FONT></CENTER></H4>

<BR>

<P>The <I>relational database model</I> revolutionized the database world and allowed PCs to replace expensive minicomputers and mainframes for many database applications. The relational database model was developed in 1970 by Dr. E. F. Codd of IBM's San Jose Research Laboratories. The primary advantage of the relational model is that there is no need to mix pointers and data in tables. Instead, records are linked by <I>relations</I> between attribute values. A relation consists of a linkage between records in two tables that have identical attribute values. Figure 4.3 illustrates relations between attribute values of relational tables that constitute part of a sales database.

<BR>

<P><B><A HREF="04vcg03.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/04vcg03.gif">Figure 4.3. Relationships between tables in a sales database.</A></B>

<BR>

<P>Because relational tables don't contain pointers, the data in relational tables is independent of the methods used by the database management system to manipulate the records. A relational database management system is an executable application that can store data in and retrieve data from sets of related tables in a database. The RDBMS creates transitory <I>virtual pointers</I> to records of relational tables in memory. Virtual pointers appear when they are needed to relate (<I>join</I>) tables and are disposed of when the database application no longer requires the relation. The &quot;joins&quot; between tables are shown in Figure 4.3. Joins are created between primary key fields and foreign key fields of relational tables. The primary and foreign key fields of the tables in Figure 4.3 are listed in Table 4.1.

<BR>

<BR>