vcg04.htm

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

<BR>

<BR>

<LI>Managing concurrency issues so that multiple users can access the data without encountering significant delays in displaying or updating data.

<BR>

<BR>

<LI>Interpreting queries transmitted to the database by user applications and returning or updating records that correspond to the criteria embedded in the query statement. Virtually all client-server RDBMSs use statements written in SQL to process queries&#151;thus the generic name &quot;SQL RDBMS.&quot;

<BR>

<BR>

<LI>Often executing <I>stored procedures,</I> which are precompiled queries that you execute by name in an SQL statement. Stored procedures speed the execution of commonly used queries by eliminating the necessity of the server to optimize and compile the query.

<BR>

<BR>

</UL>

<P>Separate database applications (front ends) are responsible for creating the query statements sent to the database management system and for processing the rows of data returned by the query. Front ends, as mentioned in Chapter 1, &quot;Positioning Visual C++ in the Desktop Database Market,&quot; handle all of the data formatting, display, and report-printing chores. One of the primary advantages of using an SQL RDBMS is that the features in the preceding list, such as security and integrity, are implemented by the RDBMS itself. Thus, the code to implement these features doesn't need to be added to each different front-end application. Chapter 20, &quot;Creating Front Ends for Client-Server Databases,&quot; describes the features of client-server RDBMSs in greater detail.

<BR>

<BR>

<A NAME="E70E24"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Three-Tier Client-Server Architecture and LOBjects</B></FONT></CENTER></H5>

<BR>

<P>The stored procedures of client-server databases used to execute predefined queries and maintain database integrity use SQL, plus proprietary SQL language extensions such as Transact-SQL, used by Microsoft and Sybase SQL Server, and Sybase System 10+. SQL is a set-oriented, not procedural, programming language. Thus, dialects of SQL aren't well suited to writing programs for validating data in accordance with complex business rules. Here's an example of a complex business rule: &quot;The current credit limit of a customer is equal to the customer's maximum credit limit, less uncontested open invoices and orders in process, unless the customer has outstanding, uncontested invoices beyond terms plus 10 days, or if the total amount of contested invoices exceeds 50 percent of the total amount of open invoices. If a pending order exceeds the customer's calculated credit limit or any customer payment behind terms plus 10 days, approval must be obtained from the credit manager before accepting the order.&quot; Such a test is quite difficult to program as an SQL stored procedure, because obtaining the credit manager's approval would be difficult for the SQL stored procedure.

<BR>

<P>Three-tier client-server architecture adds a processing layer between the front-end client and the back-end server. This processing layer, often called a <I>line-of-business object</I> (LOBject), processes requests from client applications, tests the requests for conformance with programmed business rules, and sends conforming requests to the back-end RDBMS, which updates the affected tables. Each client application using the LOBject creates its own instance of the RAO (Remote Automation Object). Figure 4.4 illustrates the architecture of a three-tier client-server application that uses Microsoft Mail 3.5 to process credit approvals (or rejections) for the scenario described in the preceding paragraph.

<BR>

<P><B><A HREF="04vcg04.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/04vcg04.gif">Figure 4.4. A three-tier client-server database system for implementing a credit management LOBject.</A></B>

<BR>

<BR>

<A NAME="E70E25"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Traditional Desktop Relational Database Managers</B></FONT></CENTER></H5>

<BR>

<P>Traditional desktop database managers, such as dBASE and Paradox, combine their database management features with the interpreter or compiler that executes the application's source code. The early versions of these products let you create quite sophisticated database applications that would run on PCs with only 256K of RAM. The constraints of available RAM in the early days of the PC required that the database management portion of the desktop DBM's code include only features that were absolutely necessary to make the product operable. Thus, products of this type, which also include early versions of FoxPro and Clipper for DOS, don't truly qualify as full-fledged relational database management systems; they are more properly termed <I>database file managers</I>. You implement the &quot;relational&quot; and the &quot;management&quot; features through the application source code you write.

<BR>

<P>The components of the dBASE and Paradox DBMs that manipulate the data contained in individual tables don't provide the built-in features of true relational database management systems listed in the preceding section. (The exception is the desktop products' capability to create a file that contains one table.) You need to write application code to enforce referential and domain integrity (however, Paradox for Windows will enforce referential integrity), and a one-DOS-file-per-table system doesn't lend itself to establishing secure databases.

<BR>

<P>The commercial success of dBASE (especially dBASE III+) and Paradox for DOS created a user base of perhaps six million people. (Borland claims there are four million dBASE users worldwide, about the same number of copies of Microsoft Access that had been sold when this book was written.) Thus, dBASE and Paradox product upgrades need to be backwardly compatible with tens of millions of .DBF and .DB files and billions of lines of dBASE and PAL code. New features, such as file and record locking for multiuser applications and the capability to use SQL to create queries, are add-ins (or tack-ons) to the original DBM. Thus, both dBASE and Paradox are currently losing market share to relatively low-cost client-server RDBMSs such as Microsoft SQL Server, Microsoft Access, a hybrid of the desktop DBM and the full-featured RDBMS, and Visual C++.

<BR>

<BR>

<A NAME="E70E26"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Microsoft Access: A Hybrid RDBMS</B></FONT></CENTER></H5>

<BR>

<P>Microsoft Access is a cross between a conventional desktop DBM and a complete, full-featured RDBMS. Access uses a single database file that includes all the tables that are native to the database. Access's Jet database engine enforces referential integrity for native tables at the database level, so you don't need to write Access Basic (Access 1+) or VBA (Access 95) code to do so. Jet enforces domain integrity at the field and table level when you alter the value of a constrained field. Jet databases include system tables that catalog the objects in the database, and the database drivers handle concurrency issues.

<BR>

<P>Access lets you break the back end/front end barrier that separates RDBMSs from desktop DBMs. Application code and objects, such as forms and reports, can be included in the same database file as the tables. Microsoft used Access's ability to include both front-end and back-end components in a single .MDB file as a strong selling point. It soon became apparent to Access developers that separating application and database objects into individual .MDB files was a better design. You create a Jet database that contains only tables and attach the tables to an Access .MDB file that provides the front-end functionality. User names and passwords are stored in a separate .MDW workgroup library file. This is necessary because a Jet .MDB file contains only one database. Here again, you can put sets of unrelated tables in a single .MDB file. Jet's flavor of SQL (which is proprietary, as are most other implementations of SQL) is the native method of manipulating data in tables, not an afterthought. The Jet DLLs that implement the database functionality are independent of the MSACCESS.EXE file, which includes the code you use to create forms and reports. Jet databases are about as close as you can get to an RDBMS in a low-cost, mass-distributed software product.

<BR>

<P>Using Jet .MDB database files with Visual C++ front ends approximates the capabilities and performance of client-server RDBMSs at a substantially lower cost for both hardware and software. If you're currently using one-file-per-table DBMs, consider attaching the tables to a Jet database during the transition stage while both your new Windows front ends and your present DBM or old character-based DOS applications need to simultaneously access the tables. Once the transition to Visual C++ front ends is complete, you can import the data to a Jet database and take full advantage of the additional features that .MDB files offer. If you outgrow the Jet database structure, it's a quick and easy port to SQL Server 6.0 for Windows NT 3.5 using the Microsoft SQL Server ODBC driver.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>You no longer need a copy of Access in order to take advantage of Access's built-in enforcement of referential integrity and the security features of Jet databases. Visual C++ 4.0 and the Jet 3.0 Data Access Object now provide programmatic implementation of referential integrity and security. The Jet database engine and the 32-bit Jet ODBC 2.0 driver now support the SQL FOREIGN KEY and REFERENCES reserved words to define relationships during table creation. However, don't yet implement the SQL-92 CHECK reserved word, which lets you enforce domain integrity with ranges or lists of values that constrain attribute values. If you're seriously into database development with Jet 3.0 databases, purchasing a copy of Access for Windows 95 (sometimes called Access 7, and referred to as Access 95 in this book) quickly repays your investment, because Access lets you establish relationships in a graphic relationships window, supplies a simple method of adding field-level and table-level constraints, provides a graphic query-by-design window, and generates Jet SQL statements for you.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E68E29"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Modeling Data</B></FONT></CENTER></H3>

<BR>

<P>The first step in designing a relational (or any other) database is to determine what objects need to be represented by database entities and what properties of each of these objects require inclusion as attribute classes. The process of identifying the tables required in the database and the fields that each table needs is called <I>data modeling</I>. You can take two approaches when data modeling:

<BR>

<UL>

<LI><I>Application-oriented</I> design techniques start with a description of the type of application(s) required by the potential users of the database. From the description of the application, you design a database that provides the necessary data. This is called the <I>bottom-up</I> approach, because applications are ordinarily at the bottom of the database hierarchy.

<BR>

<BR>

<LI><I>Subject-oriented</I> design methodology begins by defining the objects that relate to the subject matter of the database as a whole. This approach is called <I>top-down</I> database design. The content of the database determines what information front-end applications can present to the user.

<BR>

<BR>

</UL>

<P>Even though application-oriented design might let you quickly create an <I>ad hoc</I> database structure and the applications to accomplish a specific goal, bottom-up design is seldom a satisfactory long-term solution to an organization's information needs. It's common to find several application-oriented databases within an organization that have duplicate data, such as independent customer lists. When the firm acquires a new customer, each of the customer tables needs to be updated. This is an inefficient and error-prone process.

<BR>

<P>Subject-oriented database design is a far more satisfactory method. You might want to divide the design process into department-level or workgroup-related databases, such as those in the following list:

<BR>

<UL>

<LI>A <I>sales</I> database that has tables that are based on customer, order and line item, invoice and line item, and product entity classes.

<BR>

<BR>

<LI>A <I>production</I> database with tables for parts, suppliers, bills of material, and cost accounting information. The product and invoice tables of the sales department's database would be attached to the production database.

<BR>

<BR>

<LI>A <I>personnel</I> database with tables for employees, payroll data, benefits, training, and other subjects relating to human-resources management. The production and sales databases would attach to the employees table&#151;production for the purposes of cost accounting purposes, and sales for commissions.

<BR>

<BR>

<LI>An <I>accounting</I> database with tables comprising the general ledger and subsidiary ledgers. The accounting database would attach to the majority of the tables in the other databases to obtain access to current finance-related information. Accounting databases often are broken into individual orders, accounts receivable, accounts payable, and general ledger databases.

<BR>

<BR>

</UL>

<P>There is no fixed set of rules to determine which shared tables should be located in what database. Often, these decisions are arbitrary or are based on political, rather than logical, reasoning. Department-level databases are especially suited for multiuser Jet databases running on peer-to-peer networks with 30 or fewer users. Each department can have its own part-time database administrator (DBA) who handles backing up the database, granting and revoking the rights of users to share individual tables in the database, and periodically compacting the database to regain the space occupied by deleted records.

<BR>

<BR>

<A NAME="E69E110"></A>

<H4 ALIGN=CENTER>

<CENTER>

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

<BR>

<P>Diagramming relations between tables can help you visualize database design. <I>Entity-relation</I> (E-R) <I>diagrams,</I> also called <I>entity-attribute-relation</I> (EAR) <I>diagrams,</I> are one of the most widely used methods of depicting the relations between database tables. The E-R diagramming method was introduced by Peter Chen in 1976. An E-R diagram consists of rectangles that represent the entity classes (tables). Ellipses above table rectangles show the attribute class (field) involved in the relation. Pairs of table rectangles and field ellipses are connected by parallelograms to represent the relation between the fields. Figure 4.5 is an E-R diagram for the Customers and Invoices tables of the database described in Figure 4.3 and Table 4.1. The &quot;1&quot; and &quot;m&quot; adjacent to the table rectangles indicate a one-to-many relationship between the two tables.

<BR>

<P><B><A HREF="04vcg05.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/04vcg05.gif">Figure 4.5. An entity-relationship diagram showing the relationship between the Customers and Invoices tables.</A></B>

<BR>

<P>E-R diagrams describe relations by predicates. One of the dictionary definitions of the noun &quot;predicate&quot; is &quot;a term designating a property or relation.&quot; If you remember parsing sentences in English class, you'll observe that &quot;Customers&quot; is the subject, &quot;Are Sent&quot; is the predicate, and &quot;Invoices&quot; is the predicate object of a complete sentence. E-R diagrams can describe virtually any type of allowable relation between two tables if you add more symbols to the basic diagram shown in Figure 4.5. A large number of E-R diagrams are required to define relationships between the numerous entities in enterprise-wide databases.

<BR>

<BR>

<A NAME="E69E111"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Using Modeling Tools for Database Design</B></FONT></CENTER></H4>

<BR>

<P>Designing databases to accommodate the information requirements of an entire firm is a major undertaking. Thus, computer-aided software engineering (CASE) tools often are used to design complex database systems. CASE tools for database design usually include the following capabilities: