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
- Turn in a diskette, together with your homework. It MUST contain:
- README file to describe contents of the diskette;
- relevant information and files from which you get the result of the homework.
The relevant files may include database files (DBF, MDX, NDX, etc.),
view queries (QBE), and program file (PRG).
- Hard copies of these files SHOULD also be available.
The hard copies of database files should consist of both structure and data.
- Put everything in a big manila envelope and write your name on the envelope.
Two Ways to Use dBase IV
- Menu-driven Mode (Control Center)
- Command-driven Mode (dot prompt)
Control Center
At DOS, input "dbase" to enter the control center.
Control center:
There are six panels in the control center:
- Data panel
- Queries panel
- Forms panel
- Reports panel
- Labels panel
- Applications panel
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:
- Character
- Numeric
- Float
- Date (mm/dd/yy)
- Logical (T/F)
- Memo (point to a file)
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.
- Specify a query
- Select fields
Query design screen:
Specify a Query
- Exact Match Searches
- Soundex Searches
- Contains Match Searches
- Pattern Match Searches
(wildcard character *, ?)
- Condition Match Searches
(operators =, <> or #, >, <, <=, >=)
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]
- verb: command
- scope: Indicates which record(s) will be affected
- ALL
- NEXT number
- REST
- RECORD number
- expression: a list of the fields or other parameters of the command
- condition: =, <> or #, >, <, <=, >=, .T., .F., .NOT., .AND., .OR.
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:
- character
- numeric
- logical
- date
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:
- interactive mode (dot prompt)
- embedded mode (programming)
Enter the Interactive Mode:
- SET SQL ON
- enter SQL language processor
- SET SQL OFF
- leave SQL language processor