INTRODUCTION TO DBASE III+ ========================== Database management systems have long been used to organize and manipulate large collections of business data. These systems are powerful computer programs that can effectively manage a huge number of data elements. However, the systems are expensive and run only on large, sophisticated computers. These restrictions were removed by the introduction of dBase II, the first database management program for microcomputers. With dBase II, users of smaller computers could begin to enjoy computerized data management at an affordable price. dBase III is an enhancement of dBase II. dBase III performs most database management functions. The program is actually a set of tools with which you can organize and manipulate data in a simple and yet effective manner. DEFINITION OF A DATABASE: "A database is a collection of useful information organized in a specific manner." For example, you can view a personal telephone directory as a database: Albert K. Zeller (212) 457-9801 James C. Smith (206) 123-4567 Barry J. Thompson (983) 329-9876 Mary R. Nelson (543) 555-9238 Thomas L. Kessler (834) 798-0932 Rick C. Becker (832) 873-9823 This telephone directory is a listing of names and telephone numbers arranged randomly as they were entered. However, you can organize these telephone numbers in a specific order according to your preference. For example, you can group the entries by area codes and then alphabetically by the last name: James C. Smith (206) 456-9873 Albert K. Zeller (212) 457-9801 Thomas J. Lee (543) 827-0398 Mary R. Nelson (543) 555-9238 Rick C. Becker (832) 873-9823 Jack L. Kessler (834) 798-0932 Barry J. Thompson (983) 329-9876 This data is now in a more "workable" format. TYPES OF DATABASES Information stored in a database can be organized, or viewed, in a number of ways. As a result, you can define many kinds of databases according to how the information is organized. The two most popular kinds of databases are: 1. Hierarchical Databases 2. Relational Databases HEIRARCHICAL DATABASE A Hierarchical Database organizes its contents in a hierarchical tree model. Besides identifying the data elements in the database, the hierarchical tree defines the relationship among the data elements. The simplest one is the model that organizes all the elements in the database in a one-to-one relationships. For example: Peter Roberts Sam Wyse /---------Instructor --\ Instructor | | | Business 101 Economics 100 History 102 | | | Alice Baker James Smith Denny Walker James Smith Danny Walker George Davis Eileen Hason Floyd Nixon The data elements in this database include the names of the instructor, classes, and the students in these classes. When the tree is viewed from bottom to top, the link between a student and a class is defined as a one-to-one relationship. This means that one student in the tree model belongs to only one class. When James Smith takes more than one class, his name appears twice in the hierarchical database as two separate elements. When the tree is viewed from top to bottom, one-to-many relationships are used to describe the links between an instructor and the classes taught or between the class and its students. For instance, Instructor Peter Roberts "owns" two classes, and each class in turn has several students. RELATIONAL DATABASES The Relational Database structure was originally developed for use on large computer systems, but recently they have become popular business applications for microcomputers because of their simple structure. A Relational Database organizes a database as a two dimensional table consisting of rows and columns. Each row contains information that belongs to a given entry in the database. Data within a row is subdivided into several items that are held in the columns in the table. For example, the classroom database can also be viewed in a relational model: Column 1 Column 2 Column 3 1 Peter Roberts Business 101 Alice Baker 2 Peter Roberts Business 101 James Smith 3 Peter Roberts Business 101 Eileen Hason 4 Peter Roberts Economics 100 James Smith 5 Peter Roberts Economics 100 Danny Walker 6 Peter roberts Economics 100 Floyed Nixon 7 Sam Wyse History 101 Danny Walker 8 Sam Wyse History 101 George Davis The elements in the classroom database are organized in a table of 8 rows and 3 columns. Each row is a data record, and each column is a data field. DATA RECORD A data record holds the data items for a single entry. As each data record is entered, it is given a number. This number represents the order in which the data item is stored in the database. Users can subsequently identify a data record by its record number. DATA FIELD A data field is a storage unit for holding single data items within a data record. Each data field is given a name by which it can be identified in the data base. DATABASE STRUCTURE It is important to develop a design for the database before you start. It is possible to change the design or structure of your database later, but it is more efficient to plan your design well initially so that it will require fewer modifications later. In setting up your structure, one of the most important things to consider will be the type of reports you desire for the output of your data. You will need to know what information you wish to have in your reports as well as some idea about the formats you desire for your reports. A database structure consists of the following: o Field name o Field type o Field width FIELD NAME Field names may have up to 10 characters (so in many cases you may have to abbreviate). Spaces may NOT be included in field names, but you may use an underline (_) embedded within a name to suggest a space (such as zip_code). All names must begin with a letter. It is wise to use at least 2 separate fields for a person's name in case you want the option of displaying your data alphabetically by last name. FIELD TYPE In addition to assigning a field name, you must tell dBASE what type of data will be contained in each Field. There are five types of data fields. They are: (C)haracter/Alphanumeric (N)umeric (L)ogical (D)ate (M)emo Character Fields contain either letters, numbers, or a combination of both. (A telephone number or zip code would fall under this category). Data entered as a character field will be "left justified" -- that is all the data will be lined up evenly along the lefthand margin. Numeric Fields contain only numbers, and should be used when you plan to do calculations. Number labeled as a numeric will be entered with right justification, which will allow for consistent alignment of your figures. (You can not enter a $ or a comma in this field.) Logical fields are used when there are only two possible responses, either true or false/yes or no are both acceptable. Logic fields are limited to a width on one, and it is not necessary to type the space allocation in the structure. Date Fields are used to store dates. It is possible to create a data entry program to display the date in a variety of forms, however the default format is: mm/dd/yy and the field width is always 8. Memo Fields are designed to store large amounts of text. They are stored in an ancillary file with a .dbt extension. FIELD WIDTH When you set up your database structure, you must also define the width or length of each of your fields. If you have a numeric field, you may also wish to set the number (if any) of decimal places to appear to the right of the decimal. APPLICATIONS OF A RELATIONAL DATABASE A relational database provides an effective means for maintaining and manipulating a large amount of information. Some useful functions you can perform on a relational database are: Maintaining and updating the contents of the database Locating and retrieving data that meets certain specifications. Sorting or rearranging a set of data items into a sequence or order Data maintenance involves adding data to the database, changing part or all of the contents of the database, and deleting items from the database. New records can be inserted into a relational database, and any part of an item can be modified to deleted. So, in our telephone example, a new number can be added by appending the file and adding the record to the end of the file. Any item in the database, such as an area code or local number can be modified or replaced. Locating and retrieving data in the database is another important function of a database management system. In a relational database, finding a record that contains a specific item in one field is a relatively simple task. The ability to sort or rearrange the data records in a database is another valuable feature in a relational database management system. For example, in a mailing application, having all the names and addresses in ascending or descending order by ZIP code is often desirable. ORGANIZATION OF DATA As a relational database management system, dBASE III operates on the premise that all information to be stored in a database is defined in a relation structure. That is that items are arranged in rows and columns, where each row is a data record and each column is a data field. Information in a data table is stored in memory as a data file. Depending on the nature of the information, many different types of data files are available, each is reserved for storing a specific kind kind of data. The types of data (disk) files that can be set up in dBASE III are: Database Files (.dbf) Database Memo File (.dbt) Index File (.ndx) Program File (.prg) Format File (.fmt) Memory file (.mem) Report Form File (.frm) Text output file (.txt) A database file is the same as a relational data table in that the data elements are in records and fields. A database memo file is similar to a database file. It is used to store large blocks of text that are called memos. An index file provides the necessary working space for an indexing operation With an index file, a set of data can be used in a logical order rather than the order in which the records were entered in the database. A program file stores a collection of commands that are to be processed in batch processing mode. Processing tasks are defined in a set of command procedures. These commands are then executed as a batch. The collection of commands is stored in a command file which is considered to be a computer program. With a batch command file, a processing menu can be designed so users can select specific tasks during execution. A Format file stores information that specifies the format of output. The format defined in a format file is used with data items in a database to generate custom reports. A Label file contains information for printing labels with the label command. A label file, which is similar to a format file stores the specifications for printed labels. A memory File stores the contents of the active memory variables. Memory variables represent temporary memory locations that can hold computational results which may be used again for later processing. The contents of a memory variable may be an alphanumeric string or a numeric value. Report Form Files contain information for generating custom styled reports. Text Output Files. Stores text that can be shared with other computer programs. For instance, a table of data created in dBASE III may be written to a text output file. Then after you have exited from dBASE III, the contents of the file can be read by other software. SPECIFICATIONS ON THE SIZE AND CAPACITY OF DBASE III FILES Maximum records per file 1 billion Maximum characters per file 1 billion Maximum fields per record 128 Maximum characters per record 4,000 Maximum characters per text field 254 Maximum characters per memo field 4,000 Maximum characters per memo file 512,000 Maximum characters per data field 8 Index key length 100 Command line length 254 NUMERIC ACCURACY 15.9 digits 999,999,999,999,999.9 Largest number: 1 x 10+308 Smallest number: 1 x 10-307 MEMORY VARIBLES 256 Active memory variables 6,000 total characters The following lines must be added to your computer's CONFIG.SYS file for dBase-III+ to run properly: Files=20 Buffers=24 DBASE HELP COMMAND The Help Command brings up information about dBASE III commands and their operational rules. These help messages are self explanatory. You can activate the help menu by pressing the F1 function key or by typing .HELP. EXITING FROM DBASE III At any time you can interrupt a command and return to the DOS prompt by pressing the (ESC) key. To exit from dBASE III and return to the dos prompt (A>) type .QUIT CREATING AND EDITING A DATABASE When the dot prompt is displayed, you can create a new database field. To create a new database file type: .CREATE To enter a command line, you must press the RETURN key. The name of a database file can include up to eight characters, the first of which must be a letter of the alphabet. The remaining can be letters, numeric digits, or underscores. A database file name cannot contain blank spaces or symbols other than the underscore (_). dBASE III recognizes a space as a separator between two data items. A file name with a space is not accepted by the program. When you enter an illegal file name, you will see one of the following messages: Syntax Error or File is not Accessible SAMPLE DATABASE Create a new database called Employee by typing at the dot prompt: .Create Employee After you enter an acceptable file name, the field definition form of the data structure appears. The figure below shows the definition form after the CREATE EMPLOYEE command is accepted. -----------------------start of figure----------------------------------- B:Employee.dbf Bytes remaining: 4000 Fields defined: 0 field name type width dec field name type width dec 1 Char/text -------------------------end of figure----------------------------------- Names start with a letter; the remainder may be letters, digits, or the underscore. The name of the database file appears in the upper left hand corner of the field definition form. dBASE III adds the file identifier .DBF to the file name to indicate the type of data involved. The upper right corner shows the amount of available memory and the number of fields that have been defined. This information is updated as new fields are defined and added to the data structure. The main body of the form provides space to define the specifications of each data field, such as the: Name of the Data Field Type of Data Field Width of Data Field After you enter the CREATE command, the cursor is shown at the beginning of a datafield on the field definition form. At this point you can enter the Field Name, Type, Width, and so on. Below is a sample data structure for our example Employees.dbf. Enter the information on your field definition form just as you see it here. When you are finished, your form should look identical to the one you see below. -----------------------start of figure----------------------------------- B:Employee.dbf Bytes remaining: 4000 Fields defined: 0 field name type width dec field name typ e width dec 1 First_Name Char/text 12 2 Last_Name Char/text 10 3 Area_Code Char/text 3 4 Phone_No Char/text 8 5 Male Logical 1 6 Birth_Date Date 8 7 Annual_Pay 8 2 -------------------------end of figure----------------------------------- The data fields First_Name, Last_Name, Area_Code, and Phone_No are defined as character/text fields to store the employee's name and phone number. The width of these fields is set to accommodate the number of alphanumeric characters in the fields. The logical field Male identifies the sex of the employee and accepts only a single character of (T) for true or (F) for false. An employee's birth date is defined as the date field Birth_Date in the form mm/dd/yy. The numeric field Annual Pay is used to store the employee's annual salary. When all the fields are defined, you can terminate the process by pressing the RETURN key twice. At this point the data structure has been saved and the following message will appear: Input data records now? (Y/N) If you answer yes to the question, the program displays the data entry form on the screen. (To continue with our sample employee, press (YES)). You will see a display of the first data entry form. It should look just like the form below. Record No. 1 FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE ? BIRTH_DATE / / ANNUAL_PAY . ENTERING DATA INTO A DATA ENTRY FORM Each data record is given a record number that appears at the top of the screen. Below the record number, the data entry form shows the names of the data fields defined in the data structure. By positioning the cursor in the desired field, you can enter the contents of the data fields in the space provided. When the field is full, the cursor moves to the nest field. You can move the cursor by pressing RETURN after you type a data item. When the last data field is filled, a new data entry form is displayed. You can return to the last record by pressing the PGUP key. Data entered in a database file is saved record by record. As soon as the last field of a data record has been completed, the record is added to the database file. At that point you can terminate the process. To terminate data entry, after you have entered your last field, press (CTRL)-END. This will save the displayed data record and return you to the dot prompt. To continue with the sample database file employee.dbf, enter the following data into the data entry form. Save your records by pressing (CTRL)_END. THE USE AND DIR COMMANDS Database files are usually saved on a floppy or hard disk in auxilary memory. To conserve memory, only the active database files are stored in random-access memory for quick access. The use command tells the computer to retrieve the contents of a file from the auxilary memory and store the contents in RAM. The format of the USE command is: .USE So, the command to activate the database file employee.dbf would be the following: .USE employee If you forget the names of your database files, the Dir command will display the names of the database files stored on the disk. The format of the DIR command is: .DIR The file directory lists the names of the database files, the total number of records, the amount of memory used by the records, and the date when the records were last updated. Below is an example of a file directory: .DIR Database files # records last update size EMPLOYEE.DBF 10 01/27/86 820 820 bytes in 1 files. 361472 bytes remain on drive. . DISPLAY STRUCTURE COMMAND To verify the data structure of the active file, you can use the DISPLAY STRUCTURE command. Display Structure shows the data field specifications defined when you created the structure. The format of the command is: .DISPLAY STRUCTURE To continue with our employee.dbf sample type .USE Employee .Display Structure Below is a sample of what you should see. .USE EMPLOYEE .DISPLAY STRUCTURE Structure for database : B:EMPLOYEE.dbf Number of data records : 10 Date of last update : 01/27/86 Field Field name Type Width Dec 1 FIRST_NAME Char 10 2 LAST_NAME Char 10 3 AREA_CODE Char 3 4 PHONE_NO Char 8 5 MALE Logical 1 6 BIRTH_DATE Date 8 7 ANNUAL_PAY Numeric 8 2 DISPLAY, LIST, AND BROWSE COMMANDS The display command is used to show all or part of the records in an active database file. The unconditional DISPLAY ALL command is used to display all the records in a file. The format for the command is: .DISPLAY ALL Below is an example of what you would see when this command is applied to the employee.dbf database file. .USE EMPLOYEE .DISPLAY ALL Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 1 JAMES C. SMITH 206 123-4567 .T. 07/04/60 22000.00 2 ALBERT K. ZELLER 212 457-9801 .T. 09/20/59 27950.00 3 DORIS A. GREGORY 503 204-8567 .F. 07/04/62 16900.00 4 HARRY M. NELSON 315 567-0235 .T. 02/15/48 29000.00 5 TINA B. BAKER 415 787-3154 .F. 10/12/56 25900.00 6 KIRK D. CHAPMAN 618 625-7854 .T. 08/04/61 19750.00 7 MARY W. THOMPSON 213 432-6782 .F. 06/18/55 24500.00 8 CHARLES N. DUFF 206 456-9873 .T. 07/22/64 13500.00 9 WINSTON E. LEE 503 365-8512 .T. 05/14/39 34900.00 10 THOMAS T. HANSON 206 573-5085 .T. 12/24/45 28950.00 To just display the first record in a database file, the command is: .DISPLAY Below is what you would see if this was applied to employee.dbf .USE EMPLOYEE .DISPLAY Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 1 JAMES C. SMITH 206 123-4567 .T. 07/04/60 22000.00 Data record can also be displayed with the LIST command. The list command is identical to the DISPLAY command, except that DISPLAY lists with periodic pauses, whereas the LIST displays the record continuously. When the number of records is small enough to be shown on one screen, the LIST and DISPLAY commands can be use interchangeably. For a long list, however, the DISPLAY command is more appropriate because the records will be displayed with periodic pauses. If the LIST command is used, part of the listing will be scrolled off the screen. Because monitors are limited to a screen width of 80 characters, records with more than 80 characters are printed to the screen in a wrap-around fashion. You can deal with this problem in 2 ways: 1. Use the DISPLAY command & specify the desired fields 2. Use the BROWSE command To fit the screen width, you can display only certain fields of the data records. Selected data fields can be specified in a DISPLAY ALL command as follows: .DISPLAY ALL For our sample type the following: .DISPLAY ALL FIRST_NAME,LAST_NAME Below is an example of what you would see: .USE EMPLOYEE .DISPLAY ALL FIRST_NAME,LAST_NAME Record # FIRST_NAME LAST_NAME 1 JAMES C. SMITH 2 ALBERT K. ZELLER 3 DORIS A. GREGORY 4 HARRY M. NELSON 5 TINA B. BAKER 6 KIRK D. CHAPMAN 7 MARY W. THOMPSON 8 CHARLES N. DUFF 9 WINSTON E. LEE 10 THOMAS T. HANSON The BROWSE command can also be used when dealing with the display. The format of the command is: .BROWSE Because of screen limitations, only the first 80 characters are displayed. However, you can display the rest of the fields by using the cursor movement commands. For example, the (CTRL)right arrow will pan the screen right and the (CTRL)left arrow will pan the screen left. You can see a summary of these keystrokes by pressing the F1 function key. Below is an example of this screen. -----------------------start of figure----------------------------------- Record No. 1 Rentals CURSOR <-- --> UP DOWN DELETE INSERT MODE: INS CHAR: DEL EXIT: ^END FIELD: HOME END PAGE: PGUP PGDN FIELD: ^Y ABORT: ESC PAN: ^^ HELP: F1 RECORD ^U SET OPTIONS: ^HOME LAST_NAME--FIRST_NAME-ADDRESS--------------CITY-----------STATE--ZIP-- Neuhoff Luann 8653 Rita Drive Bloomington IN 47401 Lakeland Lionel 4902 Bluffside Road Muskegon MI 49450 Butler Sarah 45 Macedonia Road Raleigh NC 27606 Roarke John 87899 Gallatin SW Roanoke VA 24018 Randolph Charles 894 Grigsby Road Knoxville TN 37922 Greystoke Arthur 8897 Plaines Lane Cheyenne WY 82005 Lock Joseph 5788 Certified Way Carson City NV 89701 Campball Chester 2716 Etoile Way LaFayette KY 42254 Cooke Jonathan 568 East Westbourn Mission Ridge SD 57557 Lyman William 78 Rye Street Topeka KS 66699 -------------------------end of figure----------------------------------- The browse command cans also be used for editing purposes. You can add a new record to the file by pressing the down arrow key to the last record. The message Add New Record? (Y/N) will appear at the top of the screen. If you press Y, a set of blank spaces will be displayed at the bottom of the screen, and a new data record can be entered in the corresponding fields. You can use this process to add as many new data records as necessary. The BROWSE command can also be used to edit or modify existing records. APPEND COMMAND The APPEND command is used to add data records to the end of a database. The format of this command is: .APPEND This command works in conjunction with the .USE command. In order to append a database file, you must have a file in the active mode. In other words, you must invoke a .USE command before you can append the file. To continue with our sample employee.dbf type the following: .USE EMPLOYEE .APPEND The append command adds a new data record at the end of the file by displaying the entry form for record number 5. Enter the following information (records 5-10) into the data entry form. EDIT COMMAND You can edit the contents of a data record at any time by using the edit command. Data stored in a record can be edited, changed, or replaced. The format for the command is: .EDIT RECORD When the record is displayed, you can use the arrow keys to position the cursor at the data field you wish to edit. If you move your cursor outside the current record, the previous or next record will be displayed. To save the changes that you just made press (CTRL) END, and the program will return you to the dot prompt. To continue with our sample record file by typing in the following command: .EDIT RECORD 5 Change the phone number in the filed PHONE_NO from 787-3154 to 567-8967. DELETE/PACK The process of deleting a data record involves two steps: 1. You need to mark the data records to be deleted 2. You can use the pack command to permanently remove the data records The format for the DELETE command is: .DELETE RECORD .DELETE RECORD 5 or .DELETE ALL or .DELETE FOR .DELETE FOR AREA_CODE = "503" After the data records are marked for deletion, you can use a PACK command to actually remove the records. The format for the command is: .PACK Once PACK is executed, the files are permanently removed. RECALL Data records are marked for deletion and only removed when the PACK command is invoked. You can erase deletion marks from data records by using the recall command. The format of the command is: .RECALL RECORD .RECALL ALL .RECALL FOR ZAP All the data records in a database file can be erased at one time with the zap command. The format of the command is: .ZAP This command deletes the data records without erasing the data structure. The zap command achieves the same effect as a combination of the following commands: .DELETE ALL .PACK Remember, once the article are ZAPPED, the records are erased from the file and cannot be recovered. MODIFYING THE DATABASE STRUCTURE In order to add or delete field names, or change the space allocation in the width column, you need to invoke the Modify structure command. It is advisable to first make a temporary copy of your database file in case any problems should occur during the modification process. To make a backup of your database, enter the following commands: .USE .COPY TO .COPY FILE TO In our sample employee.dbf you would type the following commands: .USE employee .COPY to tempemp1 * NOTE: we do not have a memo file When you are sure you have a backup of your original structure, you may invoke the MODIFY STRUCTURE command. The format of this command is: .MODIFY STRUCTURE The structure of your original database file will then appear on the screen. Using your cursor command controls, you may move around the the screen and make any corrections to the # of fields, the width of present fields, or the type of the present fields. Do NOT change the data field names!! This would result in a result in a loss of data within that particular field. You can insert a new field by positioning the cursor at the desired location and pressing (CTRL) N. A blank field will be displayed, and field definitions can then be entered as usual. To delete a field, Place the cursor at the beginning of the field and press (CTRL) U. To end the modification process, press (CTRL) END. Your new structure will be saved. DISPLAYING DATA FROM THE DATABASE ================================= QUEREYING THE DATABASE We have already demonstrated the usefulness of the DISPLAY command, however you can use a conditional display command to display selected records when you add a logical relation or a qualifier. For example, to display only records (in our sample database employee) with an area code of "206", you would enter the following command: .DISPLAY FOR area_code = '206' The format of the command is : .DISPLAY FOR A qualifier is defined as: FOR the field name You can show relationships between the search key and search object with relational operators. Acceptable relational operators are: = equal to <> or # not equal to > greater than >= greater than or equal to < less than <= less than or equal to There are some minor differences in the DISPLAY FOR command depending upon the type of field you are searching on. The examples on the following pages will demonstrate these differences: CHARACTER FIELDS The format of the command is: .DISPLAY FOR ='specific data' . use employee . display for area_code = "206" record# FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 1 James C. Smith 206 123-4567 .T. 07/04/60 29000.00 8 Charles N. Duff 206 456-9873 .T. 07/22/64 13500.00 10 Thomas T. Hanson 206 573-5085 .T. 12/24/45 28950.00 NUMERIC FIELD The format of the command is: .DISPLAY FOR =specific data . use employee . display for annual_pay >= 28000 record# FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 4 Harry M. Nelson 315 567-0235 .T. 02/15/58 29000.00 9 Winston E. Lee 503 365-8512 .T. 05/14/39 34900.00 10 Thomas T. Hanson 206 573-5085 .T. 12/24/45 28950.00 LOGIC FIELD The format of the command is: .DISPLAY FOR . list for male record# FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 1 James C. Smith 206 123-4567 .T. 07/04/60 22000.00 2 Albert K. Zeller 212 457-9801 .T. 09/20/59 27950.00 4 Harry M. Nelson 315 567-0235 .T. 02/15/58 29000.00 6 Kirk D. Chapman 618 625-7854 .T. 08/04/61 19750.00 8 Charles N. Duff 206 456-9873 .T. 07/22/64 13500.00 9 Winston E. Lee 503 365-8512 .T. 05/14/39 34900.00 10 Thomas T. Hanson 206 573-5085 .T. 12/24/45 28950.00 Date Field THE FORMAT OF THE COMMAND IS: .DISPLAY FOR MONTH = # . use employee . display for month(birth_date)=10 record# FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 2 Albert K. Zeller 212 457-9801 .T. 09/20/59 27950.00 5 Tina A. Baker 415 787-3154 .F. 10/12/56 25900.00 The format of the command is: .DISPLAY FOR DTOC='the date' . use employee . display for dtoc(birthdate)='05/14/39' record# FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 9 Winston E. Lee 503 365-8512 .T. 05/14/39 34900.00 The format of the command is: .DISPLAY FOR YEAR =19__ . use employee . display for year(birth_Date)=1955 record# FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 7 Mary W. Thompson 213 432-6782 .F. 06/18/55 24500.00 The format of the command is: .DISPLAY FOR DAY=# . use employee . display for day(birth_date)=04 record# FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 1 James C. Smith 206 123-4567 .T. 07/04/60 22000.00 3 Doris A. Gregory 503 204-8567 .T. 07/04/62 16900.00 6 Kirk D. Chapman 618 625-7854 .T. 08/04/61 19750.00 The format of the command is: .DISPLAY FOR CMONTH='Month' * Note the month must be typed with the 1st letter being a capital letter . use employee . display for cmonth(birth_date)='February' record# FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 4 Harry M. Nelson 315 567-0235 .T. 02/15/58 29000.00 Logical Operators o .AND. o .NOT. o .OR. The .AND. logical operator is used to specify multiple relations in a qualifier. The operator .AND. requires that all relations are true before the command is carried out. The .NOT. logical operator will display all the records where the condition is not true. The .OR. logical operator will search and display all records that meet both specified conditions. Below are some examples that illustrate the use of the logical operators. .USE EMPLOYEE .DISPLAY FOR LAST_NAME<'L' . AND. ANNUAL_PAY>18000 Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 5 TINA B. BAKER 415 787-3154 .F. 10/12/56 25900.00 6 KIRK D. CHAPMAN 618 625-7854 .T. 08/04/61 19750.00 10 THOMAS T. HANSON 206 573-5085 .T. 12/24/45 28950.00 .USE EMPLOYEE .DISPLAY FOR AREA_CODE = '206' .OR. AREA_CODE='503' Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 1 JAMES C. SMITH 206 123-4567 .T. 07/04/60 22000.00 3 DORIS A. GREGORY 503 204-8567 .F. 07/04/62 16900.00 8 CHARLES N. DUFF 206 456-9873 .T. 07/22/64 13500.00 9 WINSTON E. LEE 503 365-8512 .T. 05/14/39 34900.00 10 THOMAS T. HANSON 206 573-5085 .T. 12/24/45 28950.00 .USE EMPLOYEE .DISPLAY FOR LAST_NAME<'L' .AND. ANNUAL_PAY>18000 .AND. .NOT. MALE Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 5 TINA B. BAKER 415 787-3154 .F. 10/12/56 25900.00 GOTO COMMAND A GOTO command can be used to point out any data record in a database file. Simply specify the destination of the GOTO instruction in the command. The Format of the command is: GOTO DISPLAY .use employee .goto record 6 .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 6 KIRK D. CHAPMAN 618 625-7854 .T. 08/04/61 19750.00 GOTO TOP, GOTO BOTTOM The record pointer can be positioned at the first or last data record in the database file. The format for these commands are: .DISPLAY TOP .DISPLAY BOTTOM .use employee .goto top .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 1 JAMES C. SMITH 206 123-4567 .T. 07/04/60 22000.00 .use employee .goto bottom .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 10 THOMAS T. HANSON 206 573-5085 .T. 12/24/45 28950.00 SKIP Any data record that is logically located before or after the current record can be selected using the skip command. The format of the command is: .SKIP To move the pointer, you can enter a positive integer as the number of records to skip. You can move the pointer backwards by assigning a negative integer. When the number of records is not specified, one data record will be skipped. .use employee .goto 2 .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 2 ALBERT K. ZELLER 212 457-9801 .T. 09/20/59 27950.00 .skip 5 Record no. 7 .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 7 MARY W. THOMPSON 213 432-6782 .F. 06/18/55 24500.00 .skip -3 Record no. 4 .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 4 HARRY M. NELSON 315 567-0235 .T. 02/15/48 29000.00 .skip Record no. 5 .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 5 TINA B. BAKER 415 787-3154 .F. 10/12/56 25900.00 LOCATE FOR In some applications, you may need to find a specific record without knowing the record number. A telephone operator may want to find the phone number of a person without knowing the record number of the persons record number. In this case, you can use a LOCATE command with a qualifier. The format of this command is: .LOCATE FOR Remember that dBASE III recognizes a difference between upper and lower case letter. Be sure you type the "object data" in the exact format in appears in the database. .locate for first_name='James C.' Record = 1 .display record# FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 1 James C. Smith 206 123-4567 .T. 07/04/60 22000.00 .locate for last_name='Smith' .and. first_name='James C.' Record = 1 .display annual_pay record# ANNUAL_PAY 1 22000.00 REPLACE WITH You can restore the contents of a specific field with a REPLACE command. An unconditional REPLACE command specifies both the field to be replaced and the contents to replace the existing data. The format of the command is: .REPLACE WITH The REPLACE WITH command replaces the contents of the the specified field with the new contents given in the command. The data type of the new contents must be identical to that of the field to be replaced. Below is an example of an unconditional REPLACE command. .use employee .goto 5 .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 5 TINA B. BAKER 415 787-3154 .F. 10/12/56 25900.00 .replace phone_number with '567-7777' 1 record replaced .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 5 TINA B. BAKER 415 567-7777 .F. 10/12/56 25900.00 You can use a conditional REPLACE command to replace the records that satisfy a specific condition. The format for a conditional REPLACE command is: .REPLACE WITH FOR .use employee .display area_code,phone_number for area_code<='300' Record # AREA_CODE PHONE_NO 1 206 123-4567 2 212 457-9801 7 213 432-6782 8 206 456-9873 10 206 573-5085 .replace area_code with '216' for area_code='206' 3 records replaced .display area_code,phone number for area_code<='300' Record # AREA_CODE PHONE_NO 1 206 123-4567 2 212 457-9801 7 213 432-6782 8 206 456-9873 10 206 573-5085 SORTING, INDEXING AND SUMMARIZING DATA The contents of a database file are organized by data fields and data records. As we said before, data records are stored in a file in the order in which they were entered, and each record is assigned a record number for later reference. The ten data records created and stored in the employee database file are stored in the order they were entered. Data records organized in this manner may not be suitable for your needs, therefore data records often need to be rearranged. dBASE III offers 2 ways of rearranging the data in a database file, the SORT command and the INDEX command. The SORT command creates a sorted file that contains the data fields of the ordered data records. If the number of data fields in each record is large, the sorting process may be a long process. In addition, the sort process creates an entirely new database and therefore uses much more space on the disk. A file that has been sorted, maintains no direct relationships to the original database file. The records in the sorted database will receive new record numbers. An indexed file can only be used in relationships to the original database file. An index file takes up less space on the disk and can be created more quickly than a sorted file. Indexed files can be kept up-to-date, as long as the index has been opened before any changes (edits, additions, and deletions) are made to the database. INDEX ON COMMAND The indexing operation creates a target file in which data records are arranged in ascending order alphabetically, chronologically, or numerically, based on the key field specified. The target file becomes an index file with an extension of .NDX. The format of this command is: .INDEX ON TO The key field can be a character/text field, a date field, or a numeric field. Logical & memo fields can not be used in an indexing operation. Using our employee example, lets create an index file based on the key field of Last_Name. Our Goal is to have our database sorted in ascending, alphabetical order keyed on the last name of the employee. An illustration of this example is shown on the next page. .use employee .index on last_name to lastnamei 10 records indexed .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 5 TINA B. BAKER 415 787-3154 .F. 10/12/56 25900.00 6 KIRK D. CHAPMAN 618 625-7854 .T. 08/04/61 19750.00 8 CHARLES N. DUFF 206 456-9873 .T. 07/22/64 13500.00 3 DORIS A. GREGORY 503 204-8567 .F. 07/04/62 16900.00 10 THOMAS T. HANSON 206 573-5085 .T. 12/24/45 28950.00 9 WINSTON E. LEE 503 365-8512 .T. 05/14/39 34900.00 4 HARRY M. NELSON 315 567-0235 .T. 02/15/48 29000.00 1 JAMES C. SMITH 206 123-4567 .T. 07/04/60 22000.00 7 MARY W. THOMPSON 213 432-6782 .F. 06/18/55 24500.00 2 ALBERT K. ZELLER 212 457-9801 .T. 09/20/59 27950.00 As you can see, the data records have been arranged alphabetically by the employee's last name. MULTIPLE FIELD INDEXING Multiple keys are often used for sorting the data records in a database file. You can achieve that result by using several data fields as a combined key field. Suppose we want to have our employee list sorted by area code and then by phone number. The format of the command would be the following: .INDEX on + + ... TO .use employee .set index to lastnamei .display all Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 5 TINA B. BAKER 415 787-3154 .F. 10/12/56 25900.00 6 KIRK D. CHAPMAN 618 625-7854 .T. 08/04/61 19750.00 8 CHARLES N. DUFF 206 456-9873 .T. 07/22/64 13500.00 3 DORIS A. GREGORY 503 204-8567 .F. 07/04/62 16900.00 10 THOMAS T. HANSON 206 573-5085 .T. 12/24/45 28950.00 9 WINSTON E. LEE 503 365-8512 .T. 05/14/39 34900.00 4 HARRY M. NELSON 315 567-0235 .T. 02/15/48 29000.00 1 JAMES C. SMITH 206 123-4567 .T. 07/04/60 22000.00 7 MARY W. THOMPSON 213 432-6782 .F. 06/18/55 24500.00 2 ALBERT K. ZELLER 212 457-9801 .T. 09/20/59 27950.00 USE INDEX COMMAND This command is used when you want to use a database (to make changes) and you want your already created index files to be automatically updated. Again the first index file listed will be the active file. All others will be opened. REINDEX COMMAND If you did not use the USE INDEX command, and have changed the contents of your database, your index files must be updated to reflect those changes. The REINDEX command will rebuild all active index files. The format of the command is: .REINDEX * Remember, the index files must be opened (or active) in order for them to be reindexed. (SET INDEX TO will open the index files) SEARCHING FOR DATA IN AN INDEXED FILE With an indexed database, you can search quickly for records that share the same content as the index key field. For example, if the database file employee is indexed on the area code, all the records with the same area code are ordered consecutively in 1 block. By positioning the record pointer at the beginning of the data block, you can access each file easily. FIND The FIND command positions the record pointer at the beginning of a data block. The first data record that contains the key string is made the active record. The FIND command only works when locating a record in an indexed database file. The sample below illustrates the Find Command locating the first record with the last name beginning with "N". Remember that dBASE III treats capital and lower case letters as unique characters. Be sure to type in your character string exactly as it appears in your database. An illustration of the FIND command: .use employee .set index to lastnamei .find "N" .display Record # FIRST_NAME LAST_NAME AREA_CODE PHONE_NO MALE BIRTH_DATE ANNUAL_PAY 4 HARRY M. NELSON 315 567-0235 .T. 02/15/48 29000.00 INDEXING OPERATIONS You can use the .DIR command to display a listing of all existing index files. The format of the command is: .DIR *.ndx The * indicates that you want only those files with an extension of .ndx If you want to display all file types, you can enter the following command: .DIR *.* To close all opened index files, enter the following command: .CLOSE INDEX * NOTE: No more than 7 index files can be created for 1 database. COUNTING AND SUMMARIZING DATA One of the most important dBASE III functions is the summarizing of data. For example, you may need to count the number of employees whose annual salary falls within a certain range. Or maybe you need to find the average annual salary for all male employees. For this purpose, dBASE III provides the summary commands of COUNT, TOTAL, & AVERAGE. COUNT & COUNT FOR COMMAND Data records in a database file can be tallied with a count command. You can use a conditional count to count records selectively. The format of the COUNT command is: .COUNT or .COUNT FOR The unconditional COUNT command tallies the total number of data records in the active file. See below for an illustration of the COUNT command as applied to the employee database. .USE EMPLOYEE .COUNT 10 RECORDS . To count records selectively, you can add a qualifier to the command. The qualifier defines the condition to be met for the records to be counted. So to count only those records in our employee database file with an area code of 216, you would use the following command. See below for illustration. .USE EMPLOYEE .COUNT FOR AREA_CODE = "216" 3 records If several conditions need to be specified in the count command, you can define the conditions by using 1 or more of the .AND., .OR., .NOT. relational operators. See below for an illustration. .USE EMPLOYEE .COUNT FOR AREA_CODE = "216" .OR. AREA_CODE = "503" 5 records .COUNT FOR .NOT. MALE .AND. ANNUAL_PAY >= 25000 1 record SUM COMMANDS You can also sum and display the contents of a numerical field in the active database file with a conditional or unconditional SUM command. The format of the command is: .SUM or .SUM FOR The first command computes and displays the sum of all the values in the specified data field. With an unconditional SUM command, all the data records are included in the summing process. The second command shows a conditional SUM command. By placing a qualifier in the command, you can have dBASE III sum only those data records that meet the specified condition. Below are illustrations of both the conditional and unconditional sum command. .SUM ANNUAL_PAY 10 records summed ANNUAL_PAY 243350.00 .SUM ANNUAL_PAY FOR MALE 7 records summed ANNUAL_PAY 176050.00 .SUM ANNUAL_PAY FOR ANNUAL_PAY <=25000 5 records summed ANNUAL_PAY 96650.00 .SUM ANNUAL_PAY FOR .NOT. MALE .AND. ANNUAL_PAY >= 20000 2 records summed ANNUAL_PAY 50400.00 TOTAL COMMAND Data records in an active database file, with the same key field contents, may be defined in groups. Values stored in the numeric fields can be totaled and saved in another database file as summary statistics. The format of the command is: .TOTAL on TO The TOTAL command sums the numeric fields of the active database file, and saves the results to the summary file. The numeric fields of the summary file contain the TOTALs of all the data records that have the same field contents. When you enter the TOTAL command, all the numeric fields in the data base are totaled unless otherwise specified. The structure of the summary file is copied from the database file. * NOTE: 1. If the field is not large enough for the total, an error message is displayed and a * is placed in the data field. 2. Before you enter the TOTAL command, you must use a sorting or indexing command to arrange the database file in ascending or descending order. In order to illustrate the TOTAL command, lets create a new database called Qtysold.dbf. Following is an illustration of the database structure and its contents. .USE QTYSOLD .DISPLAY STRUCTURE Structure for database : B:QTYSOLD.dbf Number of data records : 16 Date of last update : 02/26/86 Field Field name Type Width Dec 1 DATE Date 8 2 MODEL Character 10 3 UNIT_SOLD Numeric 3 ** TOTAL ** 22 .USE QTYSOLD .DISPLAY ALL RECORD # DATE MODEL UNIT_SOLD 1 12/07/84 RCA-XA100 2 2 12/07/84 RCA-XA200 5 3 12/07/84 ZENITH-19P 4 4 12/07/84 ZENITH-21C 3 5 12/14/84 RCA-XA100 2 6 12/14/84 RCA-XA200 4 7 12/14/84 ZENITH-19P 3 8 12/14/84 ZENITH-21C 4 9 12/21/84 RCA-XA100 8 10 12/21/84 RCA-XA200 6 11 12/21/84 ZENITH-19P 6 12 12/21/84 ZENITH-21C 7 13 12/28/84 RCA-XA100 4 14 12/28/84 RCA-XA200 5 15 12/28/84 ZENITH-19P 4 16 12/28/84 ZENITH-21C 2 As you can see, each data record in the database file contains a date (field name called DATE), a model number (field name called MODEL), and the quantity sold during the week (field name called UNIT_SOLD). To generate the weekly totals of TV's sold, you can enter the TOTAL command as illustrated below. .USE QTYSOLD .TOTAL ON DATE TO QTYWEEK QTYWEEK.dbf already exists, overwrite it? (Y/N) Yes 15 Record(s) totalled 4 Records generated .USE QTYWEEK .DISPLAY ALL DATE, UNIT SOLD RECORD # DATE UNIT_SOLD 1 12/07/84 14 2 12/14/84 13 3 12/21/84 27 4 12/28/84 15 All the units sold with the same date have been totaled and saved as 1 data record in the summary file Qtyweek.dbf To use the TOTAL command on a subset of data records, you can add a qualifier that allows the totaling of only those data records that meet certain conditions. The format is: .TOTAL ON TO FOR Below is an example that contains a qualifier that totals only the data records that meet the condition for Nodel < Zenith. .USE QTYSOLD .TOTAL ON DATA TO RCATOTAL FOR MODEL< 'ZENITH' RCATOTAL.dbf already exists, Overwrite it? (Y/N) Yes 8 Record(s) totalled 4 Records generated .USE RCATOTAL .DISPLAY ALL DATE, UNIT_SOLD Record # DATE UNIT_SOLD 1 12/07/84 7 2 12/14/84 6 3 12/21/84 14 4 12/28/84 9 The TOTAL command in the example, totals, by date, all the RCA TV's sold. The qualifier specified that the TOTAL Operation should be carried out for only those records whose model is LESS THAN Zenith. As a result, only those records containing RCA-XA100 or RCA-XA200 were totaled. THE ? COMMAND The ? Command will display the contents of a single memory variable or data field. The format of the command is: .? THE TRIM FUNCTION The TRIM function removes the trailing spaces from a character string. The argument of the function is an alphanumeric expression. The format of the command is: .TRIM () THE VAL FUNCTION The VAL Function converts an alphanumeric string of digits to a numeric value. You specify the alphanumeric string of digits in the argument, and the function returns a numeric value. The format of the command is: .VAL () THE STR FUNCTION To convert numeric data to an alphanumeric string, you use the STR function. When you specify the numeric value as the argument, the STR function returns an alphanumeric string. The format of the command is: .STR () THE DTOC FUNCTION The DTOC function will convert a date into a character string. The format of the command is: .DTOC () PRINT COMMANDS To print directly from the screen, all you need to do is add the phrase "TO PRINT" at the end of a display, list, print command. See the example below: .Display for last_name = 'Smith' to print5 will cause the results to go to print The command to create a label form is: .Create Label To modify the existing label form, the command is: .Modify label To use (or print out) the label form, the command is: .Label form to print The command to create a report is: .Create report To modify an existing report form, the command is: .Modify report To use (or print out) the report form, the command is: .Report form to print