vcg04.htm

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

<P ALIGN=CENTER>

<CENTER>

<FONT COLOR="#000080"><B>Table 4.1. The primary and foreign keys of the tables shown in Figure 4.3.</B></FONT></CENTER>

<BR>



<CENTER><TABLE  BORDERCOLOR=#000040 BORDER=1 CELLSPACING=2 CELLPADDING=3 >

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Table</I>

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Primary Key</I>

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Foreign Key</I>

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<A NAME="I2"></A>Customers

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Cust#

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

None

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Invoices

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Inv#

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Cust#

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Invoice Items

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Inv# and Prod#

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

Inv#</FONT>

</TABLE></CENTER><P>Relational databases require duplicate data among tables but don't permit duplication of data within tables. You must duplicate the values of the primary key of one table as the foreign key of dependent tables. A <I>dependent table</I> requires a relationship with another table to identify its entities fully. Dependent tables often are called <I>secondary</I> or <I>related tables</I>. For example, the Invoices table is dependent on the Customers table to supply the real-world name and address of the customer represented by values in the Cust# field. Similarly, the Invoice Items table is dependent on the Invoices table to identify the real-world object, in this case an invoice, to which records are related.

<BR>

<P>Three types of relations are defined by the relational database models:

<BR>

<UL>

<LI><I>One-to-one</I> relations require that one and only one record in a dependent table relate to a record in a primary table. One-to-one relations are relatively uncommon in relational databases.

<BR>

<BR>

<LI><I>One-to-many</I> relations let more than one dependent table relate to a record in a primary table. The term <I>many-to-one</I> is also used to describe one-to-many relations. One-to-many relations constitute the relational database model's answer to the repeating-groups problem. Repeating groups are converted to individual records in the table on the &quot;many&quot; side of the relation. One-to-many relations are the most common kind of relations.

<BR>

<BR>

<LI><I>Many-to-many</I> relations aren't true relations, because many-to-many relations between two tables require an intervening table, called a <I>relation table,</I> to hold the values of the foreign keys. (Relational-database theory only defines relations between two tables.) If Figure 4.3 had included a Products table to describe the products represented by the Prod# field of the Invoice Items table, the Invoice Items table would serve as a relation table between the Invoices and Products tables. Some relation tables include only foreign key fields.

<BR>

<BR>

</UL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>TIP</B>

<BR>

<BR>One situation in which a one-to-one relationship is useful is for an employees table in which the employees' names, addresses, and telephone numbers need to be available to many database users, but information about salaries, benefits, and other personal information needs to be restricted on a need-to-know basis. Databases such as Jet don't provide column-level permissions, so you create a one-to-one relationship between the table that contains the nonconfidential data and the one that contains confidential information. Then, you grant read-only permission to everyone (the users group) for the nonconfidential table and grant permission to only a limited number of users for the confidential table.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>The proper definition of the relations between entity classes and the correct designation of primary and foreign keys constitute the foundation of effective relational database design methods. The relational database model is built on formal mathematical concepts embedded in relational algebra. Fortunately, you don't need to be a mathematician to design a relational database structure. A set of five rules, discussed in the section &quot;Normalizing Table Data,&quot; defines the process of creating tables that conform to the relational model.

<BR>

<BR>

<A NAME="E69E109"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Types of Relational Database Managers</B></FONT></CENTER></H4>

<BR>

<P>The preceding description of the relational database model made the important point that the properties of (such as the data in) a relational table object are independent of the methods used to manipulate the data. This means that you can use any relational database management application to process the data contained in a set of relational tables. For example, you can export the data in the tables of an IBM DB2 mainframe database as a set of text files that preserve the tables' structure. You can then import the text files into tables created by another database management system. Alternatively, you can use Jet, an ODBC driver for DB2, and a network gateway to the DB2 database to access the data directly. The independence of data and implementation in relational databases also lets you attach tables from one database type to another. You can join the attached tables to the native tables in your Jet database without going through the export-import exercise. Thus, you can design a relational database that can be implemented with any relational database manager.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>TIP</B>

<BR>

<BR>Relational database managers differ in the types of data that you can store in tables and in how you name the fields of tables. Many RDBMSs, such as SQL Server, include the long varbinary field data type, which lets you store image data in tables; others, including the most commonly used versions of IBM's DB2, don't support long varbinary fields or their equivalent. You can embed spaces and other punctuation symbols in Jet table and field names, but you can't in most other RDBMS tables. If you're designing a database that may be ported from the original RDBMS to another relational database implementation, make sure you use only the fundamental field data types and conform to the table- and field-naming conventions of the least versatile of the RDBMSs.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>There are, to be sure, substantial differences in how relational database systems are implemented. These differences are often overlooked by people new to database management or people converting from a mainframe database system to a desktop database manager. The following sections discuss how mainframe, minicomputer, and client-server databases differ from traditional desktop database managers.

<BR>

<BR>

<A NAME="E70E23"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Relational SQL Database Management Systems</B></FONT></CENTER></H5>

<BR>

<P>Full-featured client-server relational database management systems separate the database management application (server or back end) from the individual (client) applications that display, print, and update the information in the database. Client-server RDBMSs, such as Microsoft SQL Server 6.0, run as a process on the server computer. Most client-server systems in use today run under one or more flavors of the UNIX operating system, but Windows NT 3.5+ rapidly is gaining ground on UNIX as an application server operating system. The client-server RDBMS is responsible for the following activities:

<BR>

<UL>

<LI>Creating new databases and one or more files to contain the databases. (Several databases may reside in a single fixed-disk file.)

<BR>

<BR>

<LI>Implementing database security to prevent unauthorized people from gaining access to the database and the information it contains.

<BR>

<BR>

<LI>Maintaining a catalog of the objects in the database, including information on the owner (creator) of the database and the tables it contains.

<BR>

<BR>

<LI>Generating a log of all modifications made to the database so that the database can be reconstructed from a backup copy combined with the information contained in the log (in the event of a hardware failure).

<BR>

<BR>

<LI>Usually preserving referential integrity, maintaining consistency, and enforcing domain integrity rules to prevent corruption of the data contained in the tables. Most client-server RDBMSs use preprogrammed triggers that create an error when an application attempts to execute a query that violates the rules.