FoxPro Worksheet 1

D.Brettell 11 September 1999
Modified by Jutiphan (23-01-2002, 03-09-2005)

What is a database?

A database is a collection of records where each record represents the data of one item. For example, for a library database of books, each book would be a record in that database. Each record would contain information about each book, for example the author, book title, price and perhaps a brief description about the book. The information stored in a record is stored in fields. The author would be one field, the title another and so on. We could design the books database like this:

Field Name	Field Type	Field Size
Author	Character	10
Title	Character	25
Price	Currency	8
Description	Memo

                                                        

Every field name has to be of a certain type. If it will be text that is stored then the type is character. If you are storing a price then it would be currency, and so on.

There are many field types in Foxpro. Some of them are:

Field Type	Use when storing:	Example
Character	Text and numbers	A name or address - Fred
Currency	Monetary amounts	A price of something - 12.52
Numeric	Integers or fractions	12 or 12.345
Date	Date: month/day/year	12/30/1999
Integer	Whole numbers only	1, 2, 3, 4... 10255 etc.
Logical	True or False	.T. or .F.
Memo	A block of text	Used for a long description

The ones above are the ones you will use most of all. FoxPro has other field types that can be used for storing special types of data. Use the help system if you wish to find out about these.

Creating a database

You are going to create a new database table called emplist, which stands for employee list, which will store information about people who work in a company. Follow these steps

1. Load FoxPro
2. Go to File and then New
3. Select Table
4. Click on the New file button
5. FoxPro wants to know where you wish to store this new database table. Select your account (P: Drive)
6. The table name will be called emplist. Type emplist in the “Enter table” box.
7. Click on Save.
8. FoxPro displays the Table Designer window. We use this to define our database.
9. In the little box under Name type fname. As soon as used started typing FoxPro displayed the other boxes.
10. Press the key on the keyboard to move to the Type box
11. The field type for fname is Character and so we do not need to change this. Press again to move to the Width box.
12. Set the width to 15. This means that we can store a first name up to 15 characters big.
13. Don’t worry about the other boxes (Index and Null). Press until you get to the box under fname.
14. Type the lname the press
15. Continue entering the field names, types and sizes. See the Emplist Database Structure page for details. Your database will have 11 fields. They are: fname, lname, address, apt_no, city, state, zip, date_hired, wage, probation and notes.
16. When you have finished entering the field names click on OK
17. FoxPro asks if you wish to Input data records now. Click Yes

Entering Data

FoxPro displays the Edit window so that you can start typing in data in to your new database.

1. Type Audrey in the Fname box then press
2. Type Levy in the Lname field and press
3. Type 318 B. 31 St. in the Adress field and press
4. Press to skip the Apt_no field
5. Type Far Rockaway in the City field and press
6. Type NY in the State field. Because the data fills the entire field, FoxPro moves on to the next field automatically. (Remember that we defined the States field to be width 2)
7. Type 11600 in the Zip field
8. Type 07/16/81 in the Date_hired field
9. You need to enter 8.00 in the Wage field. Just type 8 and press
10. Type n in the Probation field. FoxPro replaces your n with F. This means False.
11. Since the Notes field is a memo, click twice on it to open the Memo window, Emplist.notes. Type: Vacation time now totals more than three weeks per year.  Works on annula Christmas fund-raising drive.
12. Click on the window close button (the X button at the top right) to save and close the memo window. You can click twice on the memo field to check that the text got saved.
13. Enter the following records:

    Fname	Edna
    Lname	Chang
    Address	2710 Addison Way
    Apt_no	2B
    City	Berkeley
    State	CA
    Zip	94706
    Date_hired	09/07/88
    Wage	9.75
    Probation	T
    Notes	(none)

    
    

    Fname	Charlotte
    Lname	Sagorin
    Address	2203 Shady Circle
    Apt_no	(none)
    City	East Orange
    State	NJ
    Zip	07000
    Date_hired	06/06/82
    Wage	22.75
    Probation	F
    Notes	On Xmas fund raising committee

    
    
    

    Fname	Charlotte
    Lname	Sagorin
    Address	2203 Shady Circle
    Apt_no	(none)
    City	East Orange
    State	NJ
    Zip	07000
    Date_hired	06/06/82
    Wage	22.75
    Probation	F
    Notes	On Xmas fund raising committee

     

    Fname	William
    Lname	Johnson
    Address	523 East 21 St.
    Apt_no	F-23
    City	New York
    State	NY
    Zip	10022
    Date_hired	04/23/76
    Wage	6.25
    Probation	F
    Notes	Long term and loyal employee

     

    Fname	William B.
    Lname	Johnson
    Address	1701 Albemarle Rd.
    Apt_no	D-14
    City	Brooklyn
    State	NY
    Zip	11226
    Date_hired	12/15/89
    Wage	19.7
    Probation	T
    Notes	(none)

14. As you enter data into the database FoxPro saves it automatically. You do not need to click on save. Close the data entry window.
15. The database is still open in FoxPro. To see the data go to View and then Browse
16. Foxpro displays the Edit window. Go to View and Browse to see all of the records. You can switch between the Edit view and the Browse view.
17. Change to the Browse view and move down to the last record.
18. Click on View and the Append Mode so that we can enter a new record. Enter the following data:
    

    Fname	Nancy
    Lname	Nixon
    Address	1124 Grant Ave
    Apt_no	(none)
    City	Palo Alto
    State	CA
    Zip	94300
    Date_hired	02/01/84
    Wage	18.25
    Probation	N
    Notes	(none)

    As you can see, it is just as easy to enter data in using the Browse view as it is with the edit view.

19. Close the database window.

Looking at our Database

1. In the Command Window type clear and then press to clear the screen
2. Type disp struc and then press
3. disp struc is short for display structure. FoxPro should have displayed the structure of your emplist database.
4. Type clear
5. Type list to see a very quick list of all of your database records.

Creating a New Database

You are to create a new database called personal, which will store every person’s details in your class. The database will have 10 fields, some of which are defined below. You must think of another 4 fields that you wish to store. You must also consider what field types to use. Complete the following table in your exercise book before starting.

I have used the field name  birth to store the person’s birth date. The field house will be used to store what house they are in and eye I will store the persons eye  colour. You must think of 4 more things to store about a person and complete the table, filling in all field types and widths. I have put a width of 10 for the first name, fname. Do you think that is big enough? Who has the longest first name? If it is bigger than 10 characters then make this filed bigger.

When you have completed the table in your worksheet, ask the teacher to see it. When the teacher is happy that your database structure is okay you may create the database table and enter in the data for all of the students in the class.