Here is a term paper on the ‘Database Management Package’ for class 11 and 12. Find paragraphs, long and short term papers on the ‘Database Management Package’ especially written for college and IT students.
Term Paper on Database Management Package
Term Paper Contents:
ADVERTISEMENTS:
- Term Paper on the Introduction to Data Base Management Package
- Term Paper on the Data Structure
- Term Paper on Data File Creation
- Term Paper on Entering Data
- Term Paper on Displaying Data
- Term Paper on Modifying Data
- Term Paper on Data Operation
- Term Paper on Sorting & Indexing
- Term Paper on Memory Variable
- Term Paper on Multiple Data Files
- Term Paper on File Operation
- Term Paper on Program Control
1. Term Paper on the Introduction to Data Base Management Package:
A database management package is an application software for executing data based activities under the environment of the MS DOS Operating System. The packages that have been used for discussion is dBASE III Plus, a product of Ashton Tate.
To activate the package, at the DOS prompt, type DBASE and press the Return key. The package concerned gets loaded and in due course displays a dot (.) called dot prompt — which implies “I am ready to execute your instructions.” Typing QUIT followed by the press of the Return key, causes all files to close, the memory to be cleared, and the CPU returns the control to DOS.
ADVERTISEMENTS:
By typing the commands at the dot prompt, which are the instructions of the data base system, you can operate in the direct mode — each command being executed as it is given by typing the correct syntax of the respective commands and pressing the Return key [shown here as <R>], which conveys the fact that you want the instruction to be carried out.
You can also operate in the indirect-mode, called program mode, when all the required commands are stored in sequence in a program file, which are automatically executed one by one, as you will see later. Apart from the commands, there are a number of functions, which are preprogrammed sub-routines, serving as utilities to carry out specific tasks.
Each function is identified by a pair of first brackets () after its name, which may or may not contain arguments inside it, which are passed as parameters and each function returns an appropriate value — the result of its operation on the arguments.
In direct-mode, you can use the question mark “?” to get the return value of a function displayed on the screen, or the result of a valid question asked.
ADVERTISEMENTS:
2. Term Paper on the Data Structure
:
ADVERTISEMENTS:
The data structure of the data file under this system, follows the principles of Relational Data Base System, in which the data are arranged in the form of a table, whose records form the rows [tuples] and the columns contain the fields or the data items under which the attributes of each entity [record] are stored being variously called as data values or data elements. The file structure is standardized and so it is common for all relational data base packages like Clipper, Foxbase, Foxpro, etc., — the default or normal file extension is .dbf.
In the data file, the fields constitute a record and the records constitute a file — the fields are the basic units of data storage which are manipulated to get the desired information. Each field must have a unique name to identify it. The column headings of a data file are the field-names, in which up to 10 characters starting with an alphabet can be used.
The other characters of the field-name can be alphabets, numbers, or an underscore, but nothing else. You cannot use the command names as field names. In these packages, the first four characters of the command name are significant and you can execute a command by using only the first four characters — so these also cannot be used as field names.
ADVERTISEMENTS:
In the data file created under dBASE III Plus, you can have a maximum of 128 fields in a file, but in the total length of a record cannot exceed 4000 characters; actually 1 less than it.
While defining field names, you should do so it in such a way that the names itself convey some meaning about the type of data contained in them. For example, you can use BORNON as a field name to indicate that it contains the date of birth of some persons.
For any record structure to store data, you should apply your mind to decide how the relevant data would be stored in the file, because, the flexibility of your data base system would depend on this. For example, to store the address of a person, you can use one field, wide enough to store the address completely. But then, if you want the address to be sorted by name, by state, or by pincodes, you would not be able to do it. So it will be better to have different fields for each of them — even the name should be broken into first and last name and stored in different fields.
To define a field completely, in addition to its unique name, its type and size are also to be specified.
ADVERTISEMENTS:
The available field types are:
Character:
To store any normal printable character available from the keyboard. It is stored as a string with a maximum length of 254 characters.
Numeric:
To store numeric values including signs and decimal points. Up to 19 digits including the decimal point and decimal places can be stored; out of which 15 digits can be used for decimal places.
Date:
It has a standard 8 character width provided automatically by the system and the date is stored in mm/dd/yy format.
Logical:
To store logical values of Yes or No [also True or False], the standard width being 1 character; fixed automatically.
Memo:
This is a place to store any text you may wish to have as a note, comment, etc. This is not stored in the data file, but in a separate file which is automatically created by the system. The filename is same but the extension used is .dbt.
3. Term Paper on Data File Creation
:
To create a new data file, you use the command- CREATE with or without a valid filename [as per MS DOS specification]. If the filename is not given, a prompt asks for it. If the filename already exists, you are warned and permission is sought to overwrite it. If you respond with Y, most obediently the existing file is destroyed and a new file is created.
A filename as per MS DOS specification, contains up to 8 characters. You are not required to give a filename extension, .dbf being automatically used by the system. You can also give a different filename extension, up to 3 characters, but then you will always have to specify the extension whenever you use the filename; which you do not have to do with .dbf. While giving the filename, you can also specify the drive, otherwise, the default drive will be used for storing the file created.
The CREATE command requires you to define the structure of the record and a screen is offered for that purpose; where you have to specify field-name, field-type, and field-size in that sequence — once a field is defined, another blank form is presented for you to define the next field — it ends only when you press the Return key on a blank field-name and then confirm it again by pressing the Return key.
At the end you are asked whether you would like to add data to the file. If you respond with N, then the empty file is stored. Incidentally, the create command also closes any other file if it was open in the work area.
To open an existing file, which is necessary before anything can be done with the data of the file, the command is:
when the filename stands for an existing file’s name, with dbf extension — otherwise you have to specify the extension also. This causes the existing file to be opened and a position indicator, called record pointer, is positioned at the first record of the file. If you type USE without any filename, the file which was open in the work area is closed. You can also use the commands CLOSE DATABASES or CLOSE ALL to close all the open data base files.
To create backup copies of the data files, you can use the MS DOS commands of BACKUP/RESTORE or the COPY command. Alternately, you can use the data base system command: COPY FILE old-file TO new-file — the full filename with extensions have to be given.
Let us create a sample file with 6 records to explore different commands and functions.
So, to create the file called example.dbf:
CREATE EXAMPLE <R>
With the structure screen presented enter the following:
* width determined automatically by the system
Two fields have been provided for the name. The telephone numbers are numerical digits, but since these are not involved in any mathematical computations, this field has been defined as a character field. Numeric fields have some complications, such as, suppose somebody types a phone number as 42 – 1188, then 1188 will be subtracted from 42 and the result would be stored.
In case of the weight field, which is numerical, the size is 5, but because 2 decimal positions have been provided, the maximum number that can be entered is 99.99. The logical field of sex, as per our condition will be true for male and false for female. We could also use a single character field, but it has been done so to explain handling of logical field data. While entering type of fields, just type the first letter like C, N, D, etc., or use Spacebar to change the type.
4. Term Paper on Entering Data
:
The process of entering data into a data file is called appending and the commands available are:
— to enter data into a file that has been opened
Let us enter some data into our example file:
.USE example <R>
.APPEND <R>
This causes the fields to be displayed on the screen with the width of the fields displayed in a highlighted form, called reverse video. While entering data, normal arrow keys, backspace, del/insert keys can be used for moving the cursor from one position to another and for deleting / inserting characters. The cursor moves automatically to the next field, when the end of a field length is reached or the return key is pressed.
Pressing of the return key on the last field automatically presents the blank screen of the next record for entering data. If the return key is pressed without entering a single character in the first field, the append operation ceases, but the file remains open. To enter data into the memo field, you have to press the Control and PgDn keys simultaneously to enter the text editor screen for entering your text. When typing is over, press Control-End to go back to normal append mode.
Let us enter the following sample data in upper case only:
Note: There is no relation between BORNON and AGE here — the data have been randomly entered to demonstrate the pros and cons of the commands
5. Term Paper on
Displaying Data:
Several command options are available to view the contents of data file — all at a time, selectively, or conditionally. The most common command is LIST, which can be used in many ways.
To see all fields:
The options within the square brackets are optional — may or may not be used. If used, OFF suppresses display of the record numbers; TO PRINT causes the output to be printed. The expression-list means that you can specify the fields to be displayed in any order and in any combination, that is, you can selectively view your data. The scope specifies the records to be shown, the default scope of LIST is ALL.
Other choices are:
* RECORD n — shows a single record of number n
* NEXT n — shows next n records from current position
* REST — shows all the records from current position
* ALL — shows all records from beginning to end
The contents of a memo field is displayed only when its name is specified in the list command. Let us see some displays.
.USE EXAMPLE <R>
.LIST <R>
It will display all fields of all records, excluding the memo field; in the same order these exist in the file, such as:
The spacing of the display will be as per the field width defined, and it will wrap around to the next line if the width exceeds 80 characters including spaces.
.LIST AGE, LNAME, FNAME, COMMENT <R> would display:
The data base system has a record pointer for each data file which has been opened indicating the position of the current record and this pointer moves to different positions as the records are displayed or by using specific commands, such as:
To see where the record pointer is, you have to take help of some functions. The function EOF() returns true, if the record-pointer is at the end of the file, which is one record beyond the last record. Similarly, BOF() returns true if the record- pointer is at the beginning of a file, which is one position above the first record. The function RECNO() returns the current record number as given by the record pointer.
Let’s use these.
.USE EXAMPLE <R>
.LIST FNAME, LNAME, PHONE, AGE * WEIGHT, BORNON OFF <R>
.? EOF() <R> — .T. : yes, went past the 6th record.
.? RECNO() <R> — 7 : the record number is 7
.LIST NEXT 2 FNAME, LNAME, PHONE, AGE <R>
Nothing is displayed, the pointer is at 7th record
.GO TOP <R>
.? BOF() <R> . — .F. : it is not above the first record
.? RECNO() <R> — 1 : the pointer is at the first record
.LIST NEXT 3 FNAME, LNAME, PHONE, AGE <R>
.? RECNO() <R> — 3 : the pointer is at the 3rd record, which has been displayed last.
.GOTO 5 <R> — takes the pointer to the 5th record
.? RECNO() – 5 confirmed.
.SKIP <R> – moves the pointer to next record
.? RECNO() – 6 confirmed.
.SKIP -2 <R> – moves the pointer backward by 2 records
.? RECNOQ <R> – 4 confirmed.
Any sensible software system has to have different operators and the data base system is no exception — it provides for four classes of operators.
Mathematical Operators are: [follows usual priority rules]:
Relational Operators are:
Logical Operators are:
String Operators are:
Let us use the string operators, as given below:
.LIST FNAME + LNAME <R>
.LIST FNAME – LNAME <R>
Earlier, we talked about conditional viewing. The relational operators, which evaluate relations between different data values, can be used for such displays.
To do so, we use another option of the LIST command, which was not shown earlier, it being:
.LIST FOR LNAME = “SEN” FNAME, LNAME, PHONE, AGE, WEIGHT <R>
Well, we wanted to see the SENs, but all other records starting with SEN have also come in. It is because, the command displays all the LNAMEs starting with SEN.
We can cheat the computer by using, a little variation, such as:
LIST FOR LNAME = “SEN” FNAME, LNAME, PHONE, AGE, WEIGHT <R>
By using the blank space after SEN, we have eliminated records 1 and 4, which is an improvement but this is also not correct. A better way to get the correct record is to use the command SET EXACT ON, which ensures that only exact matches are evaluated.
.SET EXACT ON <R>
.LIST FOR LNAME = “SEN” FNAME, LNAME, PHONE, AGE, WEIGHT <R>
Now we have the correct record with the exact match. When you are looking for something which you do not remember correctly, just switch over to SET EXACT OFF, which is the default setting. Let’s have a few examples of conditional viewing.
.LIST FOR FNAME > “D” FNAME, LNAME, PHONE, AGE, WEIGHT <R>
In this kind of comparison, the ASCII values of the characters are compared and so FNAMEs starting with A are eliminated here.
.LIST FOR AGE > 25 FNAME, LNAME, PHONE, AGE, WEIGHT <R>
.LIST FOR AGE > 25 .AND. WEIGHT > 55 FNAME, LNAME, PHONE, AGE, WEIGHT <R>
Since the data stored in the character fields are strings, we have to enclose these in quotation marks, as we have done earlier. No such restrictions operate for numeric values. What about the logical fields?
Let us see:
.LIST FOR SEX FNAME, LNAME, PHONE, AGE, SEX <R>
All the males are displayed/ because the syntax requires only true values of sex to be displayed.
To get the females listed:
.LIST FOR .NOT. SEX FNAME, LNAME, PHONE, AGE, SEX <R>
Here we have asked for the records where SEX is not true, that is, false.
As far as the date fields are concerned, let us postpone it for the time being and let us go in for a bit of diversion.
We can also use the LIST command to view the structure of a data file, such as:
.LIST STRUCTURE <R>
This will display:
It also shows the name of the file, number of records, and the date of the last update. Did you notice a discrepancy? The total of the field size comes to 58, but it is displayed as 59. Why?
When a record is deleted by using the command, whose syntax is:
The record is actually not deleted but marked for deletion and an asterisk is put before the first field name to indicate so. To provide space for that mark of deletion 58 has been increased to 59. The meaning of the options scope arid for are same as before.
A record which is marked for deletion can be “undeleted” by recalling it with:
But if you insist on deleting, then the command is PACK and all records, which are marked for deletion, gets removed from the file.
To delete all the records, normally you will have to use DELETE ALL <R> followed by PACK <R>. But, there is a short cut, just use the command ZAP.
There is an environment command which results in ignoring of all the records which are marked for deletion — it is SET DELETED ON, the opposite effect is caused by SET DELETED OFF, which is the default setting.
There is also a function called DELETED(), which returns _true if a record has been marked for deletion. Let us now explore these commands and functions.
.SET EXACT ON <R>
. DELETE FOR LNAME = “SEN” <R> — marks record 2 for deletion
.DELETE RECORD 6 <R> — marks record 6 for deletion
.LIST FNAME, LNAME, PHONE, AGE, WEIGHT <R>
The asterisks before ASHOK and HEMA indicates that these are marked for deletion.
.SET DELETED ON <R>
.LIST FNAME, LNAME, PHONE, AGE, WEIGHT <R>
The two deleted records, numbers 2 and 6, are not displayed.
.SET DELETED OFF <R>
.LIST FOR DELETED() FNAME, LNAME, PHONE, AGE, WEIGHT <R>
The two records marked for deletion are displayed only. If we use SET DELETED ON, then nothing is displayed. Now if we use PACK, records 2 and 6 will be permanently deleted.
There is an identical command as LIST, for viewing the data whose syntax is:
The only difference between the LIST and the DISPLAY commands are that DISPLAY shows only one record by default and if the scope ALL is used, it works as LIST but pauses after the screen becomes full.
Under the category of relational operators, there is a $ operator which is used for substring search — it can find out characters embedded in the string of a character field.
For example:
.LIST FOR “GUP” $(LNAME) FNAME, LNAME, PHONE, AGE, WEIGHT <R>
It shows records 1 and 3 only, as these have the characters “GUP” embedded in the LNAME field.
Although we have always used double quotes for marking string values, single-quotes can also be used, but these two must not be mixed up. In our data file, we entered all the data in the character fields in upper case, although we could do so in lower case or use a combination of the two. The problem arises with such entry during comparison using relational operators, as the ASCII number of the lower case and upper case alphabets are different.
There are two functions to convert upper case data to lower case and vice versa during execution — the functions being:
.LIST FOR LOWER(LNAME) = ‘SEN’ will not display anything, as it looks for “sen” only. Similarly,
.LIST FOR UPPER(LNAME) = “sen” will be a failure.
To find out the location of a particular data value in any data file, there is a special command, whose syntax is:
The command searches the data file for the value specified, in the records defined by the scope, if used and ceases searching when its first occurrence is detected. The record number is displayed after a successful search and not the values. To see the values, use the DISPLAY command, which shows only one record and, to continue the search, use the command CONTINUE and the search begins again from that record position. When the item being searched in not found, it prompts “End of locate scope”. All the various conditions which have been discussed with the LIST command are also applicable for LOCATE command.
6. Term Paper on Modifying Data
:
To change the data values of an individual record one by one, the editing command is used, whose syntax is:
If you just use the command EDIT without any options, the record where the record pointer is positioned is displayed for carrying out the necessary changes.
You can also specify the fields to be changed with the FIELD option to save a number of keystrokes when only one or two fields’ data are to be changed.
As before, you can get conditional viewing with the for option, such as:
EDIT FOR FIELDS FNAME, LNAME FOR LNAME <> “SEN” <R>
Only these two fields are displayed starting with Record #5 and ending with the next record. You can find the proper record also by using the LOCATE command. The changes made in the data values are saved when you go from one record to another for multiple record editing, or with ^W or ^End. Using Esc key or ^Q abandons the changes made. [The symbol ^means pressing and holding down the Control key, when used with other characters.]
Another editing command is browse, which displays all the records of a data base in a tabular form. To see the portion, if any, which is out of screen, use ^- RightArrow or ^B to pan one field to the right and ^-LeftArrow or ^Z to pan one field to the left. As usual, ^W saves and ^Q abandons changes.
The syntax is:
With the option FIELDS, you can specify specific fields for editing. The option FREEZE is used for editing a single field. During browse operation, you can add records if the cursor is moved beyond the last record — this can be prevented with the option NO APPEND. Here ^U marks record for deletion, which can be packed later.
There is another useful editing command which allows multiple changes of data.
The syntax is:
Taking into account the scope, field list, etc., or the for condition, if used, the specified records are only displayed for editing.
For example:
.CHANGE FIELD AGE FOR LNAME = “SEN” <R>
This will allow editing of the AGE field, which is only displayed, in the records where LNAME field contains characters with first three letters as SEN. The records displayed would be # 1, 2,3, and 4. It is actually identical with the edit command.
A very powerful but dangerous global modification command is REPLACE, which causes all the specified data values to be changed.
The syntax is:
.REPLACE ALL LNAME WITH “Sen” FOR LNAME = “SEN” .
This command will change the SEN to Sen in two records, where SEN exists in the LNAME field.
7
. Term Paper on Date Operation:
A number of functions are available for values of the date fields, which are:
? date() will show the date of the clock in the desired format, the default being mm/dd/yy. If the clock shows a date of 26th January, 1994, date() will show 01/ 26/94.
However, you can change the reporting style with the SET DATE command format, the options being:
With the system date being 01/26/94, let us explore other date commands.
? cdow(date()): name of the day of the week : Wednesday
? cmonth(date()): name of the month : January
? day(date()): number indicating the day of the month : 26
? dow(date()): number of the day of the week : 4
? month(date()): number of the month : 1
? vear(date()): number of the year : 1994
In each case, where the current date function has been used, the name of a date field can be used. It often becomes necessary to convert a date format to a string format and vice versa.
The available functions are:
? dtoc(date()) : date to character : 01/26/94
? ctod(“05/10/92”) : character to date : 05/10/92
The former is a character string, with the latter being in date format. Their differences will be clear when then are used.
Such as:
.USE EXAMPLE <R>
.LIST FOR DTOC (BORNON) = “07/01/68” FNAME,LNAME,BORNON <R>
Since we are using a string on the right side of the equality sign, the date field is converted to a character by using, date-to-character function.
.LIST FOR DTOC(BORNON) < “07/01/68” FNAME,LNAME,BORNON <R>
.LIST FOR DTOC(BORNON) > “07/01/68” FNAME,LNAME,BORNON <R>
Now, we are going to find the difference between two dates/one being a date field and the other a string, which is converted to the date format using the function character-to-date.
. LIST BORNON – CTOD(“01/01/70”) < 60 <R>
LIST BORNON – CTOD(“01/01/70”) > 60 <R>
.LIST FOR CMONTH(BORNON) = “April” <R>
.LIST FOR CMONTH(BORNON) = “July” <R>
String – Numerical Conversion:
Similarly, there are two functions which can be used to convert a string to a numerical value and vice versa.
These are:
STR(N1, N2, [N3]) : it creates a character string of the numerical N1 of digits equal to N2, and with N3 decimal places, if provided.
VAL(STRING); it gives the numerical value of numerical string.
.? STR(25,3) <R> = 25
.? STR(25,3,2) <R> = 25
.? VAL(“122”) <R> = 122.00
.? VAL(“Abc”) <R> = 0.00
8
. Term Paper on Sorting & Indexing:
So far we have dealt with records, as they exist in a file which is determined by the sequence in which these were entered — we dealt with physical records. The order of the physical records can be altered based on the data values of a field, which is called the primary key — the process being called sorting.
The syntax is:
The data file is sorted in ascending order (/A) which is the default or descending order (/D), with /C option causing to ignore the upper-lower cases. The records are physically sorted on the “field” specified, which is called the primary key. The sorting process completely alters the record numbers of the existing file allotting new record numbers on sorted order in the new file created.
The process requires double space as a new file of the same size is created and it is relatively slower than indexing, which we will discuss next. It does not work with an empty file or with a single record. Let us use it in our file.
.USE EXAMPLE <R>
.SORT TO TEMP1 ON LNAME <R>
.USE TEMP1 <R>
.LIST FNAME, LNAME, PHONE, AGE <R>
Note that the record numbers have changed. Incidentally, you can also use the command as:
.SORT ON LNAME TO TEMPI <R>
Let us see another case.
.SORT TO TEMP2 ON AGE/D <R> — descending order.
.USE TEMP2 <R>
.LIST FNAME, LNAME, PHONE, AGE <R>
Instead of physically sorting and thereby creating a new file, the records can be sorted logically by preparing indexes in a file on the basis of a primary key, where no new data file is required and the process is much faster than sorting.
The syntax is:
The option UNIQUE is used when there is a possibility of duplicate value of the key in the records. Let us use our unsorted example.dbf again.
.USE EXAMPLE <R>
.INDEX ON LNAME TO EXLNAME <R>
.LIST FNAME, LNAME, AGE, BORNON <R>
The operation creates an index file with the name provided, which has an .ndx extension in dBASE III Plus and .sbi in Softbase. Let us have another index.
.INDEX ON BORNON TO EXBORN <R>
.LIST FNAME, LNAME, AGE, BORNON <R>
.INDEX ON AGE TO EXAGE UNIQUE <R>
.LIST FNAME, LNAME, AGE, BORNON <R>
In the above case, we indexed on AGE using the UNIQUE option, so the ROHIT has been eliminated, because MALAY and ROHIT have same age. [As earlier, stated the age and boron have no relation in these examples]. Now, without the unique option, we get all the records indexed.
.INDEX ON AGE TO EXAGE <R>
.LIST FNAME, LNAME, AGE, BORNON <R>
To use an indexed file always specify the name of the index file when opening the file, such as:
.USE EXAMPLE INDEX EXLNAME <R>
When a data file has been indexed on the basis of a primary key, it is necessary to make all the index files active while appending or modifying data, otherwise the index will be corrupted and will give wrong results. You can use up to seven active index files at a time. To use the index file names, use them in the order of indexes wanted.
For example:
.USE EXAMPLE INDEX EXLNAME, EXBORN, EXAGE <R>
.LIST FNAME, LNAME, AGE, BORNON <R>
In the above case all the three index files are active. Now you can use the append or edit commands without corrupting the index files.
However, if they get corrupted by mistake, either you will have to prepare fresh index files, or use the command REINDEX, as:
.USE EXAMPLE INDEX EXLNAME, EXBORN, EXAGE <R>
.REINDEX <R>
To change the order of indexes just change their sequence when opening them.
You can also use environmental command, like:
.SET INDEX TO 2 <R>
will cause EXBORN to become the first index. Using SET INDEX TO without specifying order cancels active index.
For the same purpose, you can also use:
.SET INDEX TO EXBORN <R>
You can have multi-index done by combining the fields. In that case all the fields have to be converted to strings, as shown below, where both the 1 name and age are used, the latter being a numeric field.
.USE EXAMPLE <R>
.INDEX ON LNAME+STR(AGE,2) TO LAGE
.LIST FNAME, LNAME, AGE, BORNON <R>
.USE EXAMPLE <R>
.INDEX ON FNAME+STR(AGE,2) TO FAGE
.LIST FNAME, LNAME, AGE, BORNON <R>
.USE EXAMPLE <R>
.INDEX ON STR(AGE,2)+DTOC(BORNON) TO AGEBORN <R>
.LIST FNAME, LNAME, AGE, BORNON <R>
9. Term Paper on
Memory Variables:
Apart from the values which are stored in data files under different field-names, the database system provides for another type of variable called Memory Variable which are temporary storage places created in the primary memory and manipulated by the specific names given to them. The names of the memory variables can be up to 10 characters and 256 numbers of different memory variables can be created at any time.
You can store different types of values in memory variables in two ways:
1. STORE 10 TO PAY: the memvar [memory variable] PAY of a numeric type is created and 10 is assigned to it.
2. PAY = 10: it does the same thing as above.
To store string values, the characters are to be enclosed within quotation marks, like:
STORE “HELLO” TO X or X = “HELLO”
After creating memvars you can see how these are stored in memory by using the command DISPLAY MEMORY
You can also create memvars by using the commands ACCEPT and INPUT, the former being for character variables and the latter for any type.
The syntax are:
INPUT [prompt] TO memvar: keyboard input of any data type. The prompt, if used, is displayed and it has to be a string.
ACCEPT [prompt] TO memvar: same as above but restricted to character variables only.
Let us try them out.
.ACCEPT “Enter your name: ” TO NAME <R> —> Akbar <r>
.INPUT “You bank balance in Rs: ” TO BBAL -—> 50 <r>
.DISPLAY MEMORY <R> will show the existence of the memvars, as:
NAME pub C “Akbar”
BBAL pub N 50
The word pub denotes that these memvars can be used in all programs and commands. When a memvar is prefixed by an ampersand “&”, it becomes a macro and replaces its position by the value it contains, wherever it is used.
For example:
.? “&name” <R> —> Akbar
.? &bbal <R> —> 50
With this additional knowledge at our disposal, we can use the powerful commands to locate or confirm the existence of a value in the primary field in the file which has been indexed.
The commands are:
— gets the first record matching the expression.
— same searching with the character string which need not be within quotes.
.USE EXAMPLE INDEX EXLNAME <R>
.FIND SEN <R>
.? FOUND0 <R> — > .T.
.DISPLAY <R>
.STORE “SEN” TO LOOKFOR <R>
.SEEK LOOKFOR <R>
.? FOUND() <R> —> .T.
.DISPLAY <R>
.FIND LOOKFOR <R> —> no find
.FIND &LOOKFOR <R>
.? FOUND() <R> —> .T.
.DISPLAY <R>
You should carefully note the difference between the uses of find and seek. The function to check whether the search was successful or not is FOUND(), which report true, if found.
The memory variables created in a program can be saved in a file and later restored for use, the commands are:
You can use wild cards in specifying the skeleton to save selective memory variables.
For example:
SAVE TO mefile ALL m* <R> will cause all memvars starting with m to be saved.
When the last option is used, the existing memvars are not overwritten when restoring, provided the names are different.
erases the memory variables specified.
The commands LIST STRUCTURE, DISPLAY STRUCTURE, AND MODIFY STRUCTURE show the structure of the data file in use, but only the last one will allow to change the structure. You can change the name, size, or type of any field, but do not change the name and size simultaneously — do it one at a time, after the changes have been saved with ^W or ^End.
If you want to add a field in between the existing fields, use ^N. Use ^Q to abandon changes, ^W to save, and ^U to delete a field.
There are a number of mathematical functions which provide various mathematical operations, returning the result.
These are:
ABS(expN) returns the absolute value.
.? ABS(5-10) <R> —> 5, ignores the sign.
INT(expN) returns the integer portion of the numeric expression.
.? INT(73.56) <R> —> 73
LOG(expN) returns the natural logarithm.
MAX(expN1, expN2) returns the maximum of the numbers N1 and N2.
.?MAX(23,12) <R> —> 23
MIN(expN1, expN2) returns the minimum of the numbers N1 and N2.
.?MIN(23,12) <R> —> 12
MOD(expN1, expN2) returns the remainder by dividing N1 by N2.
.?MOD(23,12) <R> —> 11
ROUND(expN1, expN2) returns value of N1 rounded to N2 decimal places.
.? ROUND(23.12,0) <R> —> 23.00
.? ROUND(23.54,0) <R> —> 24.00
.? ROUND(23.54,1) <R> —> 23.50
.? ROUND(23.54,2) <R> —> 23.54
SQRT(expN) returns the square root of number N.
.?SQRT(20) <R> —> 4.47
The string functions covered are:
ASC(expC) returns the ASCII number of the leftmost character of the character expression. Quotes are required for the character expression.
.?ASC(“computer”) <R> —> 99
CHR(expN) just does the opposite of the previous function. It indicates the ASCII equivalent of the numeric expression.
.?CHR(99) <R> —> c
AT(expC1, expC2) returns a number which indicates the position of the expression C1 in expression C2, 0 if not found.
. ?AT( “p”,”computer”) <r> —> 4
LEN(expC) returns the length of the character expression.
.ab = “computer”
.?len(“computer”) <R> —> 8
.?len(“&ab”) <R> —> 8
REPLICATE(expC, expN) repeats expression C by N times.
.?REPLICATE(“computer”,3) <R> —> computercomputercomputer
SPACE(expN) creates N blank spaces, wherever used.
SUBSTR(expC, expN1 [,expN2]) returns a string which is N2 long starting at N1 in the character expression C.
.? SUBSTR(“computer”,2,3) <R> —> omp
.? SUBSTR(“computer”,3,2) <R> —> mp
Some other functions relating to status of files, cursor position, etc. are:
ROW() returns the row position of the cursor where it is located.
COL() returns the column position of the same.
DBF() returns the name of the data file open in the current work are.
.?DBF() <R> —> example.dbf
FIELD(expN) returns the name of the field in the data file corresponding to the numeric expression N, which can be between 1 and 128. Invalid numbers return a null string. With our example file.
.?FIELD(2) <R> —> LNAME
.?FIELD(6) <R> —> BORNON
.?FIELD(9) <R> —> (blank)
FILE(expC) returns true if the filename specified exists in the default directory. Naturally the extension is also to be given.
.? FILE(“example.dbf”) <R> —> .T.
RECCOUNTO returns the number of the records in the open data file.
.? RECCOUNTO <R> —> 6
RECSIZE() returns the size of the records in the open data file.
.?RECSIZE() <R> —> 59
LUPDATE() returns the date of the data file as recorded last.
PROW() returns the row position of the printer head during printing.
PCOL() returns the corresponding column position.
Normally, the field lengths are so specified as to accommodate the largest string or values. In case of character fields, the remaining spaces are filled with blank spaces.
These can be used by using the trim function, which has three variations, such as:
TRIM(expC) removes blank spaces from right side of the string.
RTRIM(expC) also does the same thing.
LTRIM(expC) removes blank spaces from the left side of the string.
Let us see some listings of names.
. use example <r>
. list fname, lname <r>
Record# FNAME LNAME
1 MALAY SENGUPTA
2 ASHOK SEN
3 ANIMA SEN GUPTA
4 ROHIT SENSHARMA
5 SHILPA CHAKRAVARTY
6 HEMA BOSE
. list fname+lname <r>
Record# fname+lname
1 MALAY SENGUPTA
2 ASHOK SEN
3 ANIMA SEN GUPTA
4 ROHIT SENSHARMA
5 SHILPA CHAKRAVARTY
6 HEMA BOSE
. list trim(fname)+lname <r>
Record# trim(fname)+lname
1 MALAYSENGUPTA
2 ASHOKSEN
3 ANIMASEN GUPTA
4 ROHITSENSHARMA
5 SHILPACHAKRAVARTY
6 HEMABOSE
. list trim(fname)+” “+lname <r>
Record# trim(fname)+” “+lname
1. MALAY SENGUPTA
2. ASHOK SEN
3. ANIMA SEN GUPTA
4. ROHIT SENSHARMA
5. SHILPA CHAKRAVARTY
6. HEMA BOSE
10
. Term Paper on Multiple Data Files:
The data base package provides for 10 different work areas in each of which a file can be opened; however, only one work area can be active at a time. The work areas are identified by the letter A to J or numbers 1 to 10. The command SELECT is used to go to a work area and open a file or refer to a file which is already opened.
When the file is opened using its name, an ALIAS can also be specified, giving it a second name for handling. When relations are set between files in different work areas, moving the record pointer of the file in the active work area also moves the record pointer of the related file. In dBASE III Plus, only one such parent-child relationship can be established at a time. Before going into the commands available, let us open another file, with some numeric fields.
.CREATE COMPU <R>
The value in the amount field is computed by multiplying box with price.
A number of commands which operate on the numeric fields are:
It computes the arithmetic mean (average) of the numeric fields specified in the expression list and saves it in memory variables; different conditions and scopes can be specified. The default is all.
It counts the number of records for the scope and condition specified and stores in the memory variables.
It totals the values of the numeric variables and stores them in the memory variables specified. Let us see some examples.
.USE COMPU <R>
.AVERAGE box, price TO mbox, mprice <R>
box price
32 358
With display memory you will see:
32 (32.50000000)
358 (358.25000000)
Decimals ignored as the fields are so defined, but the values stored contain them.
.AVERAGE box, price FOR code = “F312” TO mbox, mprice <R>
box price
30 444 ( 3 averaged)
.AVERAGE box, price FOR code = “F514D” TO mbox, mprice <R>
box price
56 230 ( 2 averaged)
.COUNT FOR CODE = “F514D” <R>
2 records
.COUNT FOR CODE = “F514H” <R>
3 records
.SUM box, price to mbox, mprice FOR code = “F312” <R>
box price
91 1332
.SUM <R>
.8 records summed
box price amnt
260 2866 86534
.AVERAGE <R>
8 records averaged
box price amnt
32 358 10817
.COUNT <R>
8 records
There is an extremely important command for summarizing values of similar records. It creates a new file containing the summary and requires the data file to be indexed or sorted on the primary key, which is used for identifying the records uniquely.
The syntax is:
Let us use this without indexing our compu file, as:
.TOTAL ON code TO nfile <R>
8 records totalled
7 records generated
.LIST <R>
If you carefully compare this file with the original file, you will find that the first two records, having same codes, have been totalled, but nothing has been done for the others. The pdate of the totalled record is same as that of the first entry. Let us now do it properly.
.USE COMPU <R>
.INDEX ON CODE TO COMPU <R>
TOTAL ON CODE TO NFILE <R>
8 records totalled
3 records generated
Now the totaling is perfect. To link up different data files, one being the parent with the other as its child, the command available is given below. The alias can be a second name given to the child file or the name of the work area where it is opened; the two files requiring two different work areas for keeping them open simultaneously. Both the files must be indexed.
Once the relation is set, the data can be updated using the following command.
UPDATE ON <key field> FROM <alias>
REPLACE <field> WITH <expression> [,<field2> WITH <exp2>…]
The replace command is also separately available as:
The replace command when used with the update command, it updates the record in the active file by taking the values of the corresponding fields from the child file (non-active but open). In the second case, when the replace command is used alone, it affects the current record of the active file — there is no need to establish any parent child relationship in the second case. Let us first update. For demonstration purpose, we will create a second file identical in structure with the nfile created earlier.
.use nfile <r>
.index on code to nfile <r>
.copy to nfile2 <r>
.use nfile2 <r>
.index on code to nfile2 <r>
.SELECT B
.USE NFILE2 INDEX NFILE2 <R>
.SELECT A
.USE NFILE INDEX NFILE <R>
.SET RELATION TO CODE INTO NFILE2 <R>
.UPDATE ON CODE FROM NFILE2 REPLACE BOX WITH BOX+B->BOX <R>
.LIST <R>
The symbol B-> indicates the value in the box field of the child file, which is nfile2. By this command, the values in the box field for all the codes in the nfile are replaced by the total of the values in both the files. You can use other numeric operators to update the active file from the data of the non-active file.
With the above relation being set, we can display values from the two files, as shown below:
.LIST CODE, PROD, BOX, B->BOX <R>
The last column displays the value of the box field from the file nfile2.
The replace command is also used to change the field values of a data file with values which are input be the user. Let us take a simple example, where we use three other commands. These are however used in program files only.
To open a program file, the syntax is:
MODIFY COMMAND filename:
It results in a word processor being made available, where you type the instructions one after another; the file having a .prg extension. To execute the program, just use the command DO filename. Let us create a program file called test.
.MODIFY COMMAND TEST <R>
^W will close and save the file test as test.prg, which can be run as do test.
The line numbers are given for reference only not to be included in the actual program. Line 1 clears the screen. In line 2 and 3 two memvars are created which will be used to get input from the keyboard. Line 4 uses the say command which displays the string after it at the coordinates of row 10 and column 20. The get command at line 5 waits for the user to type the input.
Line 6 also does what lines 4 and 5 do in a different form. Ultimately the input is recorded at line 7 with the read command. Thereafter, the file example is opened, a blank record added at the end, in which the code and box fields are replaced with the values contained in mcode and mbox respectively This is how the experts enter values in data file instead of using the append command. At line 11, the example file is closed. The return is used to go back to the dot prompt.
The set commands, which generally affect the environment, are of two types. One set is used as a toggle switch, which move between on and off conditions, there being a default condition, which is shown in upper case.
These are:
SET BELL ON/off: controls the beep sound made
SET CONFIRM on/OFF: requires pressing of return key when moving from one field of get command to another.
SET CONSOLE ON/off: controls display on the screen. With off, nothing is shown on the screen.
SET EXCLUSIVE ON/off: controls sharing of files in a network.
SET HELP ON/off: when on, asks whether you need help, in case of mistakes.
SET PRINT on/OFF: the printer is made on or off.
SET SAFETY ON/off: seeks confirmation before overwriting files.
SET TALK ON/off: it displays command results on the screen.
SET UNIQUE on/OFF: excludes duplicate records during indexing.
The other group of set commands are used to control different states in a different manner.
These are:
SET ALTERNATE TO [<filename>]: it is used to specify a file where the output of all the commands are recorded as a text file. After specifying the filename as above, it is switched on as:
SET ALTERNATE on/OFF
SET ALTERNATE TO: starts recording when on. The command will close the file in which recording is made.
SET COLOR TO [<standard>] [,<enhanced>] [,<border>] [,<background>] is used to change the colour of the display. Simply using SET COLOR TO returns the screen to the default colour.
SET COLOR TO ON/OFF switches between colour and B&W monitors.
SET DEFAULT TO <driveletter> is used to change the default drive for all dbase operations.
SET DEVICE TO SCREEN/print: toggles between screen and printer for sending output. It is necessary when say commands are used to control display positions.
SET FIELDS TO [<field list>/ALL]: it restricts operation to the list of fields, if specified, providing the field list in the commands where these are required.
After the choice has been specified, to activate:
SET FIELDS on/OFF: whether the list is to be ignored or used.
SET FILTER TO [<condition>]: causes an active data file to appear to contain only the records which meet the specified condition.
SET PATH TO [<path list>] can be used to specify additional file searching path for files. The paths are separated by , or ;. Using SET PATH TO returns to default path setting.
SET PRINTER TO [LPT1/LPT2..] it selects the printer port, where the printer is connected.
11. Term Paper on File Operations
:
A number of operations can be performed on data and program files, which are:
RENAME <oldfile> TO <newfile> changes the name of a closed file to the new name specified. The file extension has to be provided.
COPY FILE <sourcefile> TO <targetfile> copies files completely.
COPY TO <newfile> [<scope>] [FIELDS <fieldlist>] [FOR <condition>] [WHILE <condition>] [DELIMITED [WITH BLANK]/<delimiter>]
As you can see from the above command, you can exercise different choices during copying operation. You can even create text files by specifying delimiters, which are symbols used to separate data values — you can use comma, semi-colon, etc., as delimiters.
COPY STRUCTURE [EXTENDED] TO <newfile> [FIELDS <fieldlist>] This command in simplest form creates a duplicate structure of the open data file, where you can specify the fields to be included, the default being all. The option EXTENDED creates a new data file with 4 fields, which contain the field name, type, length, and number of decimals of the active data file. Let us use our example file.
. use example <r>
. list <r>
. copy structure extended to text1 <r>
. use text1 <r>
. list <r>
JOIN WITH <alias> TO <newfile> FOR <condition> [FIELDS <fieldlist>] This command creates a new data file by combining records and fields from two open data files which are in two work areas. The alias gives the name of the non-active data file.
EXPORT TO <file> [TYPE] PFS copies the data file is use to a file which is in PFS format.
IMPORT FROM <file> [TYPE] PFS does the just opposite, creating a dbase file from PFS file format.
TYPE <file> [TO PRINT] displays the contents of a text file, like program file. It is like equivalent DOS command.
ERASE <file> or DELETE FILE <file> deletes closed files, where the full name with extension is to be provided.
12. Term Paper on Program Control
:
As you know, the instructions of a program are executed in sequence unless some conditional branching or looping takes place. Dbase provides a number of commands for conditional testing, which are used only in program files.
These commands are:
If the condition tested returns true, instructions at commands1 are executed and the control returns after endif. If the else clause is provided, it is executed with commands2 when the condition returns false.
Here there are a number of paths which are taken depending on the condition fulfilled. When none of the conditions specified are met, the commands under otherwise are executed.
This is a looping sequence, where as long as the condition is true, the commands up to enddo are repeatedly executed. The exit causes the control to move to the command after enddo, the looping ends. The loop causes control to return to the first command after do while; the commands after “loop” are not executed.
Let us create a small program to add, change data of our example file.
We start with:
MODIFY COMMAND textex <R>
This will open the word processor, where we enter the following:
textex.prg
* It is used for recording comments clear
set talk off
if .not. file(“example.dbf”)
@10,30 say “Data File Missing!”
clear
return
endif
use example
store 0 to choice
ok = “Y”
do while ok = “Y”
clear
@10,40 say “1. Adding Data ”
@12,40 say “2. Changing Data ”
@14,40 say “3. Exit Program ”
@20,40 say “Enter choice: ” get choice
read
do case
case choice = 1
append
case choice = 2
go top
edit
otherwise
clear
return
endcase
enddo
use
clear
set talk on
return
Since all the commands used have been explained earlier, the outcome should be obvious to you. In the program, a wrong statement was made initially as store ok = “Y”.
During execution, the system complained as:
Variable not found.
?
store ok = “Y”
Called from – C:textex.prg
Cancel, Ignore, or Suspend? (C, I, or S) Cancel
Do cancelled
The program file was again opened and the syntax changed as in the file now and it worked. When a mistake is reported, you can also suspend or ignore it. But ultimately you will have to cancel it with cancel command for incorporating the modifications. From the suspended state, use command resume to continue again.
A number of commands for different purposes are explained here.
The LIST command can be used for several other purposes, such as:
LIST MEMORY [TO PRINT] shows the contents of the memory, which is also obtained by display memory. The TO PRINT option prints the output.
LIST STRUCTURE [TO PRINT] displays the structure of an open file.
INKEY() function returns the scan code of the key pressed, which is between 0 and 255.
IIF(<expL>, <exp1>, <exp2>) this function evaluates expL and if it is true, exp1 is displayed, else exp2. Both the exp1 and exp2 must be of same data type.
WAIT [<prompt>] [TO <memvar>] it causes the execution to pause displaying the prompt, if included. The code of the key pressed is returned to the memvar, if included. As it is, without options, it pauses and displays “Press any key to continue”.
TRANSFORM(<expN>/<expC1>, <expC2>) it creates a character string from either expN or expC1 in the form of expC2.
CLEAR [ALL/FIELDS/GETS/MEMORY/TYPEAHEAD] this, as the name suggest — ALL closes all open files, releases memory variables and selects work area 1; FIELD releases the set field list; GETS releases the current @…GET variables from read accessibility; MEMORY releases all memory variables; TYPEAHEAD cledrs the typing buffer; without option the screen is cleared.
EJECT causes the printer to set the next page during printing.
INSERT [BEFORE] [BLANK] adds a record after or [before] the current record.
CANCEL stops execution of a program, closes all open program files and returns to dot prompts.