About dBase IV

To download dBase IV

Please use anonymous ftp to download /afs/cs.pitt.edu/public/chang/db4.zip

Once you unzip it, you can immediately start to use it by typing "dbase".
This version is for exercises only and should be deleted once you have completed the exercises.

You can also use DBASE III or DBASE III Plus for the homework if you already have.

To Use dBase IV in CIS Lab

You can use DBASE IV from Forbes Quadrangle CIS Computing Lab.

Follow the main menu to invoke DBASE IV. Or type "dbase4" directly from DOS prompt.

To use your own diskette, change to A: drive before invoking DBASE IV, or issue the following command in the command mode of DBASE IV, "SET DEFAULT TO A:"

HELP?

Use on-line HELP facility to learn DBASE, or consult any DBASE textbook.


About dBase Homework

  1. Turn in a diskette, together with your homework. It MUST contain:

  2. Hard copies of these files SHOULD also be available. The hard copies of database files should consist of both structure and data.

  3. Put everything in a big manila envelope and write your name on the envelope.


Two Ways to Use dBase IV


Control Center

At DOS, input "dbase" to enter the control center.

Control center:

There are six panels in the control center:


Data Panel

In dBase, every DBF file (*.dbf) is considered to be a seperate database, the relations between databases are linked by programs.
(Note: in SQL, a database is a group of tables, the relationships between tables are considered as a part of the group.)

Design the Database Structure

Click the < create> in the Data panel.
A database is composed of fields.
There are six field types: Database design screen (before):

Database design screen (after):

Enter Records to the Database

Click the database file and select " Use file ".
Press function key < F2> to enter or switch between Edit and Browse screens.
(You can use TAB, PGUP, PGDN keys to move in the screen.)
In Edit screen, you can enter records.
In Browse screen, you can browse records.

Enter data to the edit screen:

Record shown in the browse screen:

Add, Modify, and Delete Record

Enter Edit or Browse screens.
Select Add/Delete option in Records menu in the Edit/Browse screen.
Modify record in the Edit screen.

Change Database Structure

Click the database file and select " Modify structure/order ".


Queries Panel

A query design, or view, can be save as a file (*.QBE).
A QBE file is associated with a DBF file, which is defined in the Data Panel. Query design screen:

Specify a Query

and (in the same row):

or (in different rows, use UP/DOWN keys to move):

Soundex Searches:

Contains Match Searches:

Pattern Match Searches:

Condition Match Searches:

Select Fields

Use function key < F5> to select/erase fields in the query design.

LASTNAME selected into the view:

See the Result

Use function key < F2> to bring up the browse/edit screen and see the searching result of your query.


Using dBase IV Command and Programming

dBase IV Command Mode

Enter and Quit

At the control center, select "Exit to dot prompt" in Exit menu to enter the command mode.
Enter command "ASSIST" to go back to the control center.


Programming Using dBase IV Command Language

A database program is named *.prg
In the control center, click the program and choose "run application" at the Application panel to run a program.


Syntax of a Command Line

The syntax of dBase IV a command line is like:

verb [scope][expression][FOR condition][WHILE condition]

e.g. LIST NEXT 10 Firstname, Lastname, FOR City = "Pittsburgh"
which means that, starting from the current record point, list the next 10 records which match the given condition City = "Pittsburgh".

(Note: keywords are represented by capital letters in this document.)


dBase IV Commands

On-line Help

HELP
invoke the on-line help

HELP command
describe the syntax of the command

Open and Display Database Files

USE database_file
open the database file

DISPLAY STRUCTURE
display the database structure

DISPLAY [scope][expression][FOR/WHILE condition]
display records

LIST [scope][expression][FOR/WHILE condition]
display records

LIST [scope][expression] TO FILE filename
output records to a file

Move Record Pointer

SKIP [number]
positive#: forward;
negative#: backward

GO number
move the record pointer to the record number

GO TOP
move the record pointer to the first record

GO BOTTOM
move the record pointer to the last record

LOCATE [scope][FOR/WHILE condition]
move the record pointer to a record that satisfies the condition

CONTINUE
locate the next record

Edit Records

EDIT [scope][condition]
modify records

BROWSE
browse records

REPLACE [scope] field WITH value FOR condition
change the value of a field

APPEND
add records to the end of a database

DELETE
mark records for deletion

PACK
remove marked records from database file

Set Commands

SET
display a menu for changing the value of many set commands

SET FIELD TO [field list]
set a list of fields tha can be accessed

SET FIELD TO
turn of previous setting

SET FILTER TO [condition]
display only records that meets a condition

SET FILTER TO
turn of previous setting

Note: you can find more SET commands in the on-line help

Indexing and Sorting

INDEX [ON expression] [TAG tag_name]
create an index tag tag_name in which records are ordered alphabetically, numerically, or chronologically; tag_name is saved into an index file *.MDX

?ORDER()
return current tag_name

SET ORDER TO tag_name
set to the index tag tag_name

Searching with an Indexed File

SEEK expression
search a record in an indexed database file (set index tag before use SEEK)

Working with Multiple Database Files

A database file is opened in a work area. You can open up to 10 work areas (named by number from 1 to 10 or alias name) at one time. But only one of the work areas can be active at any time.
The default work area is 1.

USE database_file
open a database file at the current work area

USE database_file ALIAS alias_name
open a database file at the work area alias_name

SELECT number/alias
move to and activate work area number or alias

CLOSE ALL/file_type
close all files/all file type listed

SET RELATION TO expression INTO work_area#/alias_name
link the active database to open a database in another work area

Display Messages and Get Input into a Variable

? expression
display an expression

INPUT [expression] TO variable
display an expression and assign input into a variable.

@ SAY [expression] [GET variable]
display an expression and assign input into a variable.

READ
activate the SAY command; READ follows the SAY command to do so

There are 4 variable types:

Creating a Loop

DO WHILE condition
...
ENDDO

Conditional Statements

IF condition
...
ENDIF

DO CASE
CASE condition
...
CASE condition
...
...
ENDCASE

About Date

var_days = var_date1 - var_date2
returns the number of days between two dates.

Use on-line help to find more commands.


dBase IV Functions

EOF()
return a true value .T. or a false value .F.; decide whether the record pointer is located at the end of the database file

DATE()
return the current date in the computer

DTOC()
transfer a variable of type date into a character string

More functions can be found in the on-line help


A Sample Program Using dBase IV Command Language

CLEAR
CLEAR ALL
   @ 1,1 SAY 'Select:'
   @ 2,1 SAY 'A: First query'
   @ 3,1 SAY 'B: Second query'
   @ 5,1 SAY 'Q: Quit'
   @ 6,1 SAY 'Enter your selection'
   choice = ' '
   DO WHILE .NOT. UPPER(choice) $ 'ABQ'
      @ 7,1 SAY " -> " GET choice
      READ
   ENDDO
   SET COLOR TO W/N
   CLEAR GETS
   choice = UPPER(choice)
   IF choice='Q'
      CLEAR ALL
      CLEAR
      @ 7,1 SAY "See You next time. Good Luck!"
      EXIT
   ENDIF
   IF choice='A'
      DO query1
      choice = ' '
   ENDIF
   IF choice = 'B'
      DO query2
      choice = ' '
   ENDIF
CLEAR ALL
CLEAR
QUIT


Structured Query Language (SQL)

Two Modes Using SQL in dBase IV:
Enter the Interactive Mode:
SET SQL ON
enter SQL language processor

SET SQL OFF
leave SQL language processor