Foxpro Tutorial - Lectures Notes PDF

Preview

Summary

Lectures Notes...

Description

Introduction to Visual FoxPro Database This is a collection of electronic and organized information. Database is organized into rows and columns, the rows in a database are called records and columns are called fields (fieldnames). Database models A database model allows the user to arrange information in a database. They show how data is arranged in a database. Types of database models 1. Relational database model Information is arranged in rows and columns. This information is drawn from different tables linked by a common field. 2. File management system This is a traditional method of storing information in a database where data is arranged sequentially. 3. Hierarchical database model This is a model where information stored in a database has a parent child relationship. A pointer (key) is used to access information from a database. 4. Network database model Records and links represent data. It allows modeling of many to many relationships. Designing a database Terms used in FoxPro 1. Data This is the information that goes into a database for processing. 2. Fields These are attributes or categories of information to be displayed in a table. 1

FOXPRO TUTORIAL BY: KABA N. DANIEL

3. Reports This is a summary of information. 4. Field type (data type) Shows the type of information to be stored in a particular field. 5. Width (Field size) This is the number of characters to be entered in a certain field. Phases of database design 1. Data definition Determines what important fields to be included in your database. Determines the exact fields that will be stored in a database. E.g Housing company database * Customer Name * Gender * Age * Room Number etc. 2. Data refinement Review the list of fields on your initial list so that the fields can form an accurate description of the types of data that will be in the database. e.g. * Member Last Name * Member Middle Name * Member First Name * Gender * Room Number * Town Code

2

FOXPRO TUTORIAL BY: KABA N. DANIEL

3. Establishing relationships between fields Create an association between same data in different tables. The relationship and primary key help prevent duplication of records in database. Visual FoxPro commands Field types (Data types) in FoxPro 1. Character Stores any characters including letters, numbers, special symbols and blank spaces. 2. Numeric fields Are those fields that use numbers with or without decimal a point. 3. Float fields Store numeric values with a floating point numbers. 4. Date/Time Are those fields that store and time formats. You must include leading zeros for the day and month. 5. Logical fields They represent a single teller describing True and False values. 6. Memo Fields Store large blocks of text for each record. 7. General fields Used to store pictures, sound word processing documents, spreadsheets and any other type of data that can be pasted from windows application.

3

FOXPRO TUTORIAL BY: KABA N. DANIEL

Creating a database There are two ways to create a database in FoxPro: 1. Using command window by entering Create Filename 2. Using File Menu The command window The command window is used to allow users create database objects and display information they want from the database. Example 1 Creating Housing Company database Items table * Stock No * Description * Cost Orders table * CustNo * stockNo * Quantity * Date Customers table *

LastName

*

MiddleName

*

FirstName

*

Gender

*

Address

*

City

*

Citycode

*

CustNo

4

FOXPRO TUTORIAL BY: KABA N. DANIEL

Code Create items Create orders Create customers Alternatively, you can create tables in the Command window or a program file using standard SQL commands like: CREATE TABLE Customers; (CustomerID I, ; Customer C(20), ; Address C(20)) CREATE TABLE Materials; (MaterialID I, ; MaterialName C(20), ; SaleUnit C(10), ; UnitPrice Y) CREATE TABLE Orders ; (OrderID I, ; CustomerID I, ; OrderDate D, ; PayDate D) CREATE TABLE OrderDetails ; (OrderID I, ; OrderDetailID I, ; MaterialID I, ; nitsSold N(10, 2) Adding records to a table (file) This is done using either file menu or command window. Using the command first activate the database by typing USE “ “ command and then type APPEND command. e.g. Use items Append 5

FOXPRO TUTORIAL BY: KABA N. DANIEL

Viewing Database contents Browse Mode This window enables the user to view information in database in tabular form. e.g. Use items Browse lastname, middlename, firstname Records in a database can also be viewed using the command window using DISPLAY and LIST commands. Display is used to e.g. Use items List displays entire contents of a database, while display narrows the search to certain limits. e.g. List stockno Dispay all Changing database This is the process of changing the appearance of records and fieldnames in a database. It also involves changing the structure of a database. Editing a database Enter CHANGE command in command window and press enter key. e.g. Change

Limiting editing in a database 6

FOXPRO TUTORIAL BY: KABA N. DANIEL

Involves preventing editing of records in a database. Use FREEZE command to stop any editing to records.

E.g. Freeze fields Deleting records Use DELETE and PACK commands to prepare records for deletion at a later time. These commands allow the user to mark records for later removal from database. Also uses LOCATE command to find desired records and then use DELETE command to mark records for deletion. E.g. Locate for LastName=” “ Delete Or Delete record 4 To reverse the process use RECALL command. E.g. Recall record 4 or Recall all

Deleting files This is removal of files (database) from memory. Use DELETE command to remove databases from the computer memory. Delete items.dbf Replacing records We use CHANGE command to change or replace records. E.g. Replace all city with”Nairobi” for city=”Kampala” 7

FOXPRO TUTORIAL BY: KABA N. DANIEL

Or Change LastName to “Kennedy” for LastName=”Kenny”

Sorting records This is the process of arranging records in a predetermined sequence.ie ascending or descending order. We use SORT command to sort records in a database. e.g. Use customers Sort on LastName to customers2 Use customers2 List LastName You can also specify the type of sort wanted i.e. Ascending or descending order. e.g. Sort on LastName /D to customers2 Use customers2 List LastName Sorting multiple fields Use customers Sort on lastname, middlename, firstname to customers2 Use customers2 List LastName, middle name, first name

Indexing records 8

FOXPRO TUTORIAL BY: KABA N. DANIEL

Index contains information about the address of individual records in database. Indexing is the allocation of key fields which can be used to retrieve files and records. Types of indexes 1. Compound index Maintains information of different indexes created within a single file. 2. Individual index Information is stored in different files. Creating index Use customers Index on gender tag LastName List LastName, Gender Selective indexing We use FOR command to index a selected group of records with a clause index on e.g. Index on LastName, tag gender for=”Male” Index multiple fields Index on LastName + FirstName tag gender Indexing fields of different types We use DTOS and STR functions to convert the contents of one field to another. DTOS converts date fields to string of characters. STR converts contents of numeric field to strings of characters to avoid operator mismatch.

Use customers Index on gender +dtos(date) to customers2 9

FOXPRO TUTORIAL BY: KABA N. DANIEL

List gender, date Creating Relationships in tables This is a link between one or more tables in a database. There are three major types of relationships 1. One to one relationship 2. One to many relationships 3. Many to many relationships Be4 setting relation is created, set index in the fields. Relating two or more tables A. Items table StockNo

Description

Cost

2001

Leather handbag

1000

2002

Casing

3000

2003

Mirror

4000

2004

Shoes

6000

2005

Books

3500

2006

Soap

1500

B.Orders table

10

FOXPRO TUTORIAL BY: KABA N. DANIEL

CustNo

StockNo

Quantity

Date

9001

2001

10

03/05/2008

9002

2002

12

03/06/2008

9003

2003

8

03/07/2008

9004

2004

14

03/08/2008

9005

2005

5

03/09/2008

9006

2006

6

03/10/2008

9007

2007

15

03/12/2088

C. Customers Table LastName MiddleName Kaba Omar Zablon Kerubo Nyakundi Edward Onkundi Pamela Jennifer Moraa Mary Dennis Kebati Kefah

FirstName Daniel Mary Kenny Barare Bonda Mogere Dancan

Gender Male Female Male Female Female Female Male

Address P.o.Box P.o.Box P.o.Box P.o.Box P.o.Box P.o.Box P.o.Box

3 6 8 9 1 7 4

City Kampala Nairobi Harare Abuja Kola Lusaka Kigali

Citycode 40100 40200 40300 40400 40500 40600 40700

CustNo 9001 9002 9003 9004 9005 9006 9007

Code Close databases Select 1 Use items Index on StockNo to bystock Select 2 Use orders Index on CustNo to Bycust 11

FOXPRO TUTORIAL BY: KABA N. DANIEL

Select 3 Use customers Index on CustNo to Custom Select 1 Set relation to CustNo into customers Set relation to StockNo into items

Visual FoxPro Programming FoxPro command file is made up of FoxPro commands. Each time you use command file FoxPro executes the list of commands in sequential order. Characteristics of command files 1. A series of commands entered from command window can be stored in command file and executed as a group. 2. One command file can call and execute another command file. 3. Information can be transferred between command files. Creating command files We use MODIFY COMMAND to create command files. Syntax Modify command Filename In FoxPro editor type the FoxPro commands you wish to store as command files. Example 1 Modify command Test After pressing the enter key FoxPro will open the command file. Type the code below and save the file with any name. Use customers 12

FOXPRO TUTORIAL BY: KABA N. DANIEL

List LastName, MiddleName, FirstName city Close the command file and open command window and type DO command followed by the name of the command file then press enter key. e.g. DO Test Compiling FoxPro programs FoxPro programs runs on compiled basis rather than on interpreted basis. Interpreters convert line by line in a program into machine code. The compilers translate the entire program into object code once. Constants This is an item which does not change during execution, fixed data items in FoxPro. Variables These memory locations in a computer that store data awaiting execution. They are referred to using their names. Rules for creating variables 1. Should be ten or fewer characters 2. Must consist of only letters, numbers and underscores. 3. Variables names cannot be commands or function names we use STORE command to assign data types to a variable. Syntax Store expression to variable name Example 1 Store 200 to salary Or Salary = 200 Expression 13

FOXPRO TUTORIAL BY: KABA N. DANIEL

A combination of one or more fields, functions, operators, variables and constants to perform an operation. Sum =a + b Net Pay = Gross pay – Deductions Operators These are symbols that are used to work on related values to produce a single value.

Types of operators 1. Arithmetic operators Used to mathematically produce numeric results. * Addition (+) * Subtraction (-) * Multiplication (*) * Division (/) * Exponentiation (^) * Modulus (Mod) 2. Relational operators Used to compare and relate character strings. * * * *

And Not Or Between

3. Logical operators * * * *

Less than () Less than or equal to (=) 14

FOXPRO TUTORIAL BY: KABA N. DANIEL

* Not equal () * Equal (=)

4. String operator Used to combine two or more character usually known as concatenation. * Plus sign (+) e.g. Kaba + Daniel

Calculating sum of two or more numbers in FoxPro Store 0 to a, b, d set clock to 4,25 @5,10 to 7,50 double @6,15 say "sum of two numbers" @8,10 to 15,50 double @10,14 say "Enter num1" get a @12,14 say "Enter num2" get b read d=a+b @14,14 say "sum is" +str(d,4,0) Product of two or more numbers store 0 to a,b,d set clock to 4,25 @5,10 to 7,50 double @6,15 say "product of two numbers" 15

FOXPRO TUTORIAL BY: KABA N. DANIEL

@8,10 to 15,50 double @10,14 say "Enter num1" get a @12,14 say "Enter num2" get b Read d=a*b @14,14 say "product is is" +str(d,4,0)

Area of a circle store 0 to a,d @5,10 to 7,50 double @6,15 say "Area Calculation for Circle" @8,10 to 15,50 double @10,14 say "Enter Radious of Circle" get a read d=22/7*a*a @12,14 say padr("Area of Circle",15) +str (d,6,2) Area Calculation for Triangle store 0 to a,b,d set clock to 4,25 @5,10 to 7,50 double @6,15 say "Area Calculation for Triangle" @8,10 to 15,50 double @10,14 say "Enter Base of Triangle " get a @12,14 say "Enter Height of Triangle" get b read d=1/2*a*b @14,14 say "Area of Triangle" +str(d,6,2) Interest Calculation set clock to 1,28 store 0 to rate,ammount,time,interest,total 16

FOXPRO TUTORIAL BY: KABA N. DANIEL

@2,8 to 4,60 double @3,25 say "Interest Calculation" @5,8 to 20,60 double @7,10 say "Enter Principal Ammount" get ammount @9,10 say "Enter Time

" get time

@11,10 say"Enter Rate of Interest " get rate read interest=ammount*time*rate/100 @13,10 say "Simple Interest: " +(str(interest,10,2))+" Kshs." total=ammount+interest @15,10 say "Total Ammount with Interest: "+(str(total,10,2)) +" Kshs."

Find Sum/Count of Even/Odd numbers from the 10 numbers entered clear i=1 sumeven=0 sumodd=0 te=0 to=0 do while i= 80 @ 14,28 say "A" 19

FOXPRO TUTORIAL BY: KABA N. DANIEL

case score >= 70 @14,28 say "B" case score >= 60 @14,28 say "C" case score >= 50 @ 14,28 say "D" case

score = 70 score=messagebox("GOOD",1+32,"GRADES") case score >= 60 score=messagebox("FAIR",1+32,"GRADES") case score >= 50 score=messagebox("FAIL",1+32,"GRADES") case

score...