NOS : Certificate in Computer Applications
| Home | Table of Contents |

Up: Online Course Material

Organizing a database means arranging individual records so that they appear in a sequence that makes a proper sense and helps you to work with database efficiently. The concept can be appreciated by taking an example of telephone directory. Imagine a situation where names in the directory are not arranged alphabetically. This will create a mess and take much longer time to find a particular name in the directory.

At the end of this lesson you would be able to

• use the SORT and INDEX commands to organise records of a database
• differentiate between FIND and SEEK commands
• count the number of records in a database
• modify a database
• DELETE records in a database
• RECALL and REPLACE records in a database

Information must be organized before you can make much sense of it. The elements of a database structure - field name, type, and size are all intended to organise within each record. There are two ways to organize records in database. One is INDEXING and the other is SORTING. Before sorting a database you make sure that the database you want to sort is current:

The SORT command gives us the ability to copy the record and structure of a database to another database. Files can be arranged in either ascending order or descending order by using SORT command. Sort command can be used in multiple fields for data arrangement.

This SORT command creates a new database file. But the new file contains records of the original database file only the order is different. The following examples show the forms of the SORT command. Let us consider a database file BIODATA, whose structure is shown in Fig. 21. 1

Suppose you want to sort the records in this file in increasing order of the AGE field. You have to give the command at the dot prompt:

.USE BIODATA and press ENTER

. SORT ON fieldname TO new filename. Thus you have to give the command

. SORT ON AGE TO BIO and press ENTER

Here field name is AGE and the sorted file name is BIO.DBF

To see the contents of the sorted file give the following commands:

. USE BIO.DBF

. LIST

The sorted file BIO. DBF looks like as shown in Fig. 21.2.

. use biodata

. sort on age to bio

100% sorted 5 records sorted

. use bio

. list

Here you notice one thing that BIO.DBF is a new file. In this new file, the record numbers given by database is in a new order. But Age field is in sequence, because you have sorted the BIODATA.DBF to increasing order of the AGE field. Suppose you want to sort the file in descending order of the AGE field. Then you have to give the command SORT ON AGE/D TO TEMP.DBF. The new file TEMP looks like as shown in Fig. 21.3

. use biodata

. sort on age/d to temp

100% sorted 5 Records sorted

. use temp

. list

Index command can also be used to organise a data file. When you index a database, you ask dBASE to create a separate and smaller file based upon designated fields in the database. The index command does not create a new database file. It creates an index file for the database with a default extension (.NDX). Unlike SORT command however INDEX does not permit the user to arrange the data in descending order. It can also be used to arrange the records in multiple fields. The command structure for index command is

.USE BIODATA

. INDEX ON field name TO file name

For example, if we index the file biodata.dbf according to field age to the file bioage then the records in the file biodata.dbf are arranged in ascending order as given in Fig. 21.4

. use biodata

. index on age to bioage

100% indexed 5 Records indexed

. list

The FIND and SEEK commands both are used in indexed database. FIND operates with character strings, while SEEK operates with either character string or numeric values. If SEEK operates with a character string the string must be enclosed in a single or double quotes or square bracket Let us take some examples

. USE BIODATA

. INDEX ON AGE TO BIOAGE

. USE BIODATA INDEX BIOAGE

. FIND 30

. DISPLAY

The screen will look like this

. use biodata

. index on age to bioage

. use biodata index bioage

. find 30

. display

Record#                       NAME             SEX          AGE

3                                  Manju                  f                30

The FIND command will move the record pointer to the first record which matches with find query and the DISPLAY command will display it on the screen.

The SEEK command like FIND command also works only with an index file. When you SEEK command with strings, you must use quotes. Consider the previous example, to SEEK the NAME Manju, you must give the command

. USE BIODATA

. INDEX ON AGE TO BIOAGE

. USE BIODATA INDEX BIOAGE

. SEEK 30

. DISPLAY

The screen will look like this

.use biodata

. index on age to bioage

. use biodata index bioage

. seek 30

. display

Record#                       NAME             SEX          AGE

3                                  Manju                  f                30

The main difference between SEEK and FIND commands is that SEEK command will not work with macros but FIND command is used in macros.

The COUNT command helps in finding the number of records in the active database that meet a given condition.

. USE BIODATA

. COUNT FOR AGE> 13

The screen will appear as follows:

. use biodata

. count for age >13

3 records

. count

5 records

The COUNT command, without any condition, gives the total number of records in the active database file.

. COUNT

The computer prompts

5 RECORDS

The LOCATE command is used to find the records in a database which satisfy certain conditions. The general form of LOCATE command is

. LOCATE [scope] FOR [condition]

Suppose you give the command

. USE BIODATA

. LOCATE FOR AGE =25

The computer will prompt

RECORD =1

Your screen will appear like this

. use biodata

. locate for age=25

Record = 1

For displaying that particular record in a database give the command DISPLAY and if you want to continue the search give the command CONTINUE along with LOCATE command. Your screen will appear like this

. use biodata

. locate for age=30

Record = 3

. continue

End of LOCATE scope

Command Line          <C:> BIODATA                Rec: EOF/5

Enter a dBASE III PLUS command.

Top

IN-TEXT QUESTIONS 1

1. What is the difference between SORT and INDEX command?
2. What is the usefulness of FIND and SEEK commands?
3. Define the following command with a suitable example

(a) COUNT (b) LOCATE

Top

Modifying a database is almost as easy as creating a database. You begin with an existing structure and want to modify the database by

• Inserting a new field
• Deleting an existing field
• Inserting a new record
• Deleting a record permanently

Let us explain the steps by taking our previous file BIODATA. Begin with opening the file.

. USE BIODATA

. EDIT 5

Bytes remaining: 4000

CURSOR   Char:         Word: Home End Pan:  ^     ^ INSERT Char:        Ins Field:        ^N Help:         F1 DELETE Char: Del Word: ^Y Field: ^U Up a Field: ­       Down a Field:    Exit/Save:           ^End Abort:                   Esc

NAME:      Soma SEX:          f AGE:          28

EDIT                          ¦<C:>¦ BIODATA ¦                Rec: 5/5

After making the desired changes, pressing Ctrl+W saves the changes and exits to the prompt.

(a)  The Record Pointer

How did the EDIT command display only the 5th record? This can be explained by the concept of a record pointer that is maintained for each file opened by dBASEIII+. When the file is open the pointer points to the first record in the file and moves to the other records after a command has been given. For example, with the LIST command, the record pointer moves to the end of the file, since it moves successively from the first record to the last record. The current record (i.e., the record on which the pointer is positioned currently) can be displayed by giving the command. DISPLAY. This command is used to position a particular record, which is to be displayed.

. DISPLAY

DISPLAY [ALL!RECORD<recordno. >]

[<expr_list>] [FOR<expl.>] [TO PRINT]

where:

<expr_list> is a list fields or expressions.

<expl.> is a condition.

.USE BIODATA

.DISPLAY

dBASE will display only the first record along with header which we have discussed earlier.

Let us consider the following example

dBASE will display the 4^th record in the file along with header.

. use biodata

. goto 4

. display

Record#                       NAME             SEX          AGE

4                                  Sibu                   m               12

.

Fig 21.11

The GOTO command followed by the record number moves the record pointer to a specified record.

. GOTO 5

This command will position the pointer on the 5^th record.

. GOTO<expN>

where <expN> is the record number.

 . use biodata

. goto 5

. display

Record#                       NAME             SEX          AGE

5                                  Soma                  f                28

(b) EDIT Command

Similarly, on giving the command EDIT 5, the record pointer moves to the 5^th record and displays it for modification.

To summarise, the steps involved in modifying a database, using the EDIT command are:

• Open the file by the command USE File Name
• Access the record(s) to be modified.
• Make the required changes in the records.
• Save the changes by pressing <Ctrl>+<W>.

This command positions the record pointer at the required record and displays it for modification

EDIT [ALL![RECORD]<record no. >]

[FIELDS<fieldlist>] [FOR<expl>]

Where:

<fieldlist> is a list of fields to be edited

<expL. > is a condition.

Now we open BIODATA by the command USE

.USE BIODATA

. EDIT

The screen looks like this

CURSOR   Char:         Word: Home End Pan:  ^     ^ INSERT Char:        Ins Field:        ^N Help:         F1 DELETE Char: Del Word: ^Y Field: ^U Up a Field: ­       Down a Field:    Exit/Save:           ^End Abort:                   Esc

NAME:      Soma SEX:          f AGE:          28

EDIT                          ¦<C:>¦ BIODATA ¦                Rec: 5/5

Fig. 21.13

(c) REPLACE command

The REPLACE command is used to REPLACE the contents of one or more specified field either the current record or all record, depending on the form of the command.

. REPLACE ALL

Syntax:

REPLACE [ALL] <field> WITH <expr>

[FOR<expl>]

Where:

<field> is the field to be replaced

<expr> is the expression to replace the field

<expl> is a condition.

(d) BROWSE Command

BROWSE command gives an option of adding new records. The BROWSE command also provides the option of displaying and modifying selective fields. Once you make changes through BROWSE command you can save the changes and exit to the dot prompt by pressing Ctrl + W. Consider the following example:

. USE BIODATA

. BROWSE

Immediately dBASE will display a screenful of records as shown in Fig. 21.14

The difference between BROWSE and EDIT Command is that several records are viewed simultaneously using BROWSE, while one record is displayed at a time when EDIT is used.

CURSOR   Char:         Word: Home End Pan:  ^     ^ INSERT Char:        Ins Field:        ^N Help:         F1 DELETE Char: Del Word: ^Y Field: ^U Up a Field: ­       Down a Field:    Exit/Save:           ^End Abort:                   Esc

NAME      SEX       AGE Anant       m               25 Babloo           m               13 Manju        f                30 Sibu                m               12 Soma       f                28

BROWSE                 <C:>    BIODATA                                 Rec: 5/5

View and edit fields.

21. 5 MODIFY STRUCTURE

The MODIFY STRUCTURE command helps to change the structure of an active database file. The command:

. MODIFY STRUCTURE

displays the structure of the database file as shown in figure 21.15 . After adding the field to the file save the modified structure by pressing <Ctrl>+<W>. The MODIFY STRUCTURE command also allows deleting fields or changing the field types and widths. You should keep in mind that if the field widths are reduced, the data in that field may be lost. Hence changes to existing fields must be made with proper care.

Bytes remaining: 3987

CURSOR   Char:         Word: Home End Pan:  ^     ^ INSERT Char:        Ins Field:        ^N Help:         F1 DELETE Char: Del Word: ^Y Field: ^U Up a Field: ­       Down a Field:    Exit/Save:           ^End Abort:                   Esc

Filed Name       Type      Width Dec     Filed Name      Type    Width     Dec 1 NAME Character 10 2 SEX Character 1 3 AGE Numeric 2

MODIFY STRUCTURE           |<C:>|        BIODATA           |Field: 1/3 | |

Enter the field name. Field names begin with a letter and may contain letters, digits and underscores

Fig. 21.15

21. 6 MORE ON QUERIES

Instead of giving the List to Print command with each and every query, the printer can be activated to print the command as well as the output of the command. The command that can be given to activate the printer is as follows:

. SET PRINT ON

. LIST

To deactivate the printer the command is:

. SET PRINT OFF

21.6.1 Deleting Records

The delete command marks the current record for deletion. When the records are marked for deletion they are not transferred during copy and sort operation. Now we will discuss how delete command is used in different ways.

. DELETE RECORD 5 <marks record 5 for deletion>.

1 records deleted

The DELETE command also provides the following options:

 Now delete all the records in the active database file BIODATA. You will find an asterisk (*) against the records marked for deletion.

. use biodata

. delete all

5 records deleted

. list