![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
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!
Sorting RecordsIn a relational database, the records in each table do not exist in any meaningful order. In most cases, the physical order of records in the disk file is the order in which the records are enteredwhich rarely, if ever, has any meaning. Of course, the structure of the disk files is something that you should never have to be concerned about. Any decent relational database application should completely isolate the user from any worries about disk files, file structure, and the like. Does this mean that you cant sort the records in your tables? Of course notsorting is an important aspect of using databases. Rather than physically sorting the records, however, a database application uses logical sorting, based on indexes. Used in this context, index doesnt mean the same thing as the index in the back of a book, although the meaning is similar, because a database index also indicates where to find things. Each index is based on one or more fields in the table. The index itself is sorted based on the data in that field, and each entry in the index contains a pointer that specifies the physical location of the corresponding record in the table. This format is illustrated in Figure 21.1. When you want records to be sorted based on a particular field, simply tell the database program to use the index that is based on that field. During display, printing, and so on, the table records appear in the order specified by the index. You can quickly change to another sort orderordering by name rather than ZIP code, for exampleby using another index.
In addition to sorting the records in a table, an index greatly speeds up searches, or queries, based on the data in the indexed field. If you are searching for data in a nonindexed field, the database application must look through all the records, one at a time, for a match. If the field has been indexed, however, a binary search algorithm can be used, which greatly increases the speed of the process. Heres how a binary search works:
While you are designing a database, the following question usually arises: By which field or fields should a table be indexed? Although you increase your options by having more indexes, you also slow down performance, because all the indexes must be updated whenever new records are entered. A table should be indexed based on its primary key field, which some database applications do automatically. A table should also be indexed based on any foreign indexes that it may contain. Other indexes depend on the details of the application, and are usually restricted to fields that are used as the basis for queries. JoinsExtracting information from database tables is one of the most common tasks that database users need to perform. When this operation involves two or more tables, it is called a multitable selector, more commonly, a join. Three possible types of join exist to join two tables:
Database Front End ApplicationsWhen you work with Visual Basic to create a database application, youre actually generating a database front end, which is an application that can be used to view and manipulate data in a database. The back end is the set of related database tablesand in some cases, the database engine that serves as the interface between the front end and the tables. The terms client and server are sometimes used instead of front end and back end. Why bother with this separation between the front and back ends of a database? Why not just combine all database functionality into a single application? The answers to these questions is clear if you consider how most organizations use a database. An organizations various databases cover a wide range of informationpersonnel records, salary information, sales data, inventory information, and so on. The various users within the organization, however, need to access selected subsets of this information in specific ways. The personnel department, for example, requires access to the personnel data and needs to look at information such as Social Security contributions and medical insurance payments. The shipping department has no use for personnel information (and may even be denied access to it), but shipping does need to view sales and inventory data. If the front and back ends of the database were combined in a single application, a single application would have to cover the needs of all potential users. It would be unavoidably large, complex, and cumbersomenot to mention difficult to customize for new or changed needs of a specific group of users. Additionally, and perhaps even more importantly, maintaining database integrity and validity would become a nightmare. The front end/back end approach avoids most of these problems. Each group of users can be given its own specialized software that is designed to do just what the group needs it to do, and nothing more. With a single programthe back ends database enginecoordinating all access to the actual database files, matters of integrity and validity can be dealt with more easily, and controlling access to different parts of the database becomes feasible. Database front ends fall into two categories:
Decisionsupport applications, which vary widely in scope, are the most common type of database front end. This type of program can be extremely specialized and limitedfor example, an application that displays only client and sales information can be designed for sales personnel. These programs can also be extremely flexible, as in complex managementinformation systems (MIS) that provide summary data on all information in a companys database. Decisionsupport applications generally have readonly access to the database filesthat is, they can read the data but cant change it. Two approaches, often combined, can be taken toward the design of decisionsupport applications. Users can be given one or more fixed, nonmodifiable ways of examining the data, or they can be given the capability to design custom queries and reports. The former method has the advantage of requiring less training of the final users, but productivity suffers if the programs predefined capabilities arent closely matched to the users needs. Transactionprocessing applications provide the capability to add new data to a database and to edit or delete existing data. Because a transactionprocessing application can change the actual table data, some additional programming considerations come into play. The application has the responsibility of preserving the integrity, or accuracy, of the data in the tables. Many databases are client/server databases. The terms client and server are being used somewhat differently here than they were earlier in the chapter, although it is still related to the concept of front end and back end. A client/server database refers to a multiuser database, to which multiple front ends are connected from different computers scattered around an organization. The client computers are connected via a network to the server computer on which the database back end is located. Multiuser client/server databases present some special problems, which are dealt with in Chapter 24.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |