---------------------------------------------------------------- USING DATABASES - THAT FEELING OF POWER ---------------------------------------------------------------- It is said that there are three classes of programs which interest business and home computer users: Word processors help you talk about money. Spreadsheets help you keep track of money. But databases help you MAKE money. Given that provocative insight, let's probe deeper . . . A database program allows you to store, sort, organize, retrieve and prepare reports about information in a logical way. Facts, figures and ideas all benefit from careful organization, retrieval, sorting and storage. Spreadsheets come with a formal layout (rows and columns) and are best used for examining relations BETWEEN items and MANIPULATING the results. Databases require a more STRUCTURED format for the information. They are, however, more flexible in layout and allow more reporting variety on the information contained in the database. Most databases work with records or forms (like 3 x 5 cards). You can specify the information contained: names, addresses, client types and so on. Each form within a database always contains the same information. Just like filing cards which always contain the same entries. A good way to think of a database is a shoebox with many baseball cards - each with the same headings (field names). Each card of course contains different information. An important term used in describing and working with databases is the FILE which is a collection of similar RECORDS like a shoebox full of baseball cards. A RECORD is the data items which are duplicated to make up the file (each baseball card.) Each record has FIELDS which are items within like the date of birth for an individual baseball player. Lower still is the CHARACTER is simply an individual number, symbol or alphabet letter. Characters are sometimes called character strings. Common character data might include names, product labels, numeric identification or other data. In addition some fields are fixed in length and must be set prior to database use (e.g., PC FILE:DB) while others allow flexible length character strings to be entered (e.g., Microsoft Works). These limits are set by either or both the available disk space and available RAM memory space. But many databases extend still further. New database products include capabilities for graphing relationships between sets of data, producing summaries and even working with pictures. Another useful feature is error checking within a database which means that if the field is preset to only allow zip codes, if a dollar amount or state abbreviation is mistakenly entered, the computer will beep and pause for another try. Further databases can be preset to only work in some fields with real numbers or integers depending on data needs. An example database screen: ================================================= ---- CUSTOMER LIST ---- name: product used: address: frequency of use: city: social security no: state: birthday: zip: category: remarks: date of first contact: ================================================= The above is a simple screen layout for a database customer file or christmas card mailing list. Many databases can also list information in columns or a list view) and in many different report styles. The exciting idea behind databases is that you can retrieve a single record or groups of records very quickly. You could, for example, locate all customers who use a particular product. Or perhaps all customers in the same city over a certain age (calculated from the birthday field) who purchased from your store within the last year. There are two general styles or types of database programs: single file (also called flat file or file manager type) and multi-file (also called relational). Single file type databases operate on one set of information at a time while multi-file (relational) databases work with many interrelated sets of data at once. An example of single file would be cards in a recipe box; the cards could be sorted, searched and checked one at a time. A relational, multi-file database could interrelate a set of recipe cards to a set of cooks and a set of diners and a set of dates available for dinner - all at the same time! Complex, but useful . . . Generally, single file databases are easy and relatively inexpensive (as well as being suitable for beginners). Examples: Microsoft Works, Pc-file, Q & A and Reflex. Multi-file databases are more complex, and more expensive. Examples: dBase, Paradox and Rbase. Some databases store excess data during program operation on disk, while others store data only in limited RAM memory during operation and then place data on disk when the computer is shut off. Disk based databases can hold more information but are a little slower searching and sorting. RAM based databases are fast but can only hold a finite amount of data since they must contain everything within RAM. Generally many relational databases are disk based while flat file, simple databases are RAM memory based. Reporting capabilities are also of interest. A database must be able to "let you see" or REPORT the data in different ways. Some format possibilities are columns, cross tabs, graphs and extracted lists. The idea is flexibility in printing of reports. The sorting of data is crucial. A sort refers to the reordering of data on the disk or within RAM memory. You might wish to sort your database into a list by zip code order to take advantage of bulk mailing rates. Later you might wish to resort the same data into a list with high income customers are the top and low income customers at the bottom. The useful possibilities are endless. Disk based database programs usually store a special INDEX file which cross references the main data and keeps track of where individual records are located. This allows for faster searching and sorting since the smaller index is examined rather than churning through all the records to find or delete data. Multiple indexes refers to the capability to have separate indexes on one database. Each index reflects a different sorting pattern. Searching or selecting data can be tedious or easy depending on the program. Some programs like Q & A allow you to recall data by asking in simple english sentences or phrases. This may be easy at first but limits greater intricacy later. Most programs allow for expressions which can search at a refined level but require phrases such as: STATE="WA" or STATE="OR" and INCOME >="$45,000" This is a bit more "mathematical" in appearance but isn't hard for the beginner to master with practice. This more "mathematical" approach to searching allows pinpoint accuracy. Modern database programs can act a bit like spreadsheets in allowing the calculation of fields. This means that if a number or item is entered into a field of the database, another field will instantly "fill itself in" with a number or word automatically. This calculation of results is based on the entry of the previous field! Many databases have built in functions just like spreadsheets. You can use them to calculate loan amounts, quarters within the calendar year, cosines, and other results based on a previous field of data. Databases can operate in one (or several) of three distinct ways: 1) Menus are present which you pull down to select choices such as sorting or deleting data. 2) Macros are invoked which simply play back pre-recorded sequences of typed commands. For example you press the key combination ALT-S and a special macro you pre-recorded earlier issues a series of commands to the keyboard which tell it to retrieve a certain file, search it for high income customers, sort the data into order by zip code and then print a report list. All this from one keystroke! 3) The final method for database operation is full programming mode which allows the package to perform under the control of a sophisticated series of commands which accept data, sort and search it and make decisions as to which part of the program should be used next. A programmed database is useful when full sophistication is needed or when untrained workers must not be allowed to tinker with the underlying database. Programming controls the steps the database uses to perform its job. Increasingly, external vendors provide written programs which can, for example, allow a package such as dBase to function as an accounting package for a pharmacy or a fund raising database for a church. The program changes the structure and performance of the database to a precisely tuned information system for a specific need. Yet another area of interest is the importing and exporting (transfer) of data between database programs. Some computer users maintain data and report on results from a database program. However the data can be transferred from the database to a spreadsheet for closer analysis, graphing or consolidation. This transfer capability also allows you to "take your data" to another database program if you outgrow the capabilities of a simple database package which becomes limited in capabilities and must be discarded. You may have to discard the program, but need to take the data from it into a newer database program. Tutorial finished. Have you registered PC-Learn to receive your bonus disks? Registration is encouraged. Shareware works on the honor system! Send $25 to Seattle Scientific Photography, Department PCL6, PO Box 1506, Mercer Island, WA 98040. Latest version of PC-Learn and two bonus disks shipped promptly!