Databases in ClarisWorks

ET 605

Dr. Marcovitz

Contents

Fields

The first step in creating a database is deciding what fields you want. Fields are the kinds of information you want to store. For example, if your database is students, you might want to store each student's name, birthday, and grades. You might also want to store a final grade that the computer automatically calculates from the other grades. You might have the following fields: Name, Birthday, Grade 1, Grade 2, Grade 3, and Final Grade.

Next you have to decide what the field type will be for each of these fields. The most common field types are: Text, Name, Number, Date, and Calculation. If you put the names together (first name last name), you will want to use the type Name (you could use text, but when you want to sort the names they will be sorted by first name not last). The birthday will be a date so that should be of type Date if you are going to use a complete date (including the year). If you are using just the month and day, the birthday should be of type Text. The three grades are numbers, so you should use type Number. Finally, the Final Grade field is going to be calculated based on the grades, so it should be of type Calculation.

When you start a new database file in ClarisWorks, the first thing you will have to do is define the fields. You can always change the fields (including the field names and field types) later, but it is helpful to have the fields set up in advance. Once you have entered some data, you might have problems if you try to change fields.

ClarisWorks will first present you with the following screen, asking you to define the fields that you want:

In the Field Name box, type the name of the first field (in this case, use the name Name). For the Field Type, use the pull-down menu to select Name as the type. Then click on the Create button. Your screen should look like this:

Next, you can enter the remaining fields by entering the field name and choosing the appropriate type from the Field Type pull-down menu. When you have entered all the fields except for the Final Grade, your screen should look like this:

Be sure you have entered the correct names and field types for each of the fields. If you made a mistake, don't worry because it is easy to fix. To change the name or field type of any of the fields, click once on the field so it is highlighted, make the necessary changes, and click on the Modify button.

Calculations

Now for the Calculated field. To begin with, you should do the same thing as the other fields: enter the name of the field (Final Grade) and select the type from the pull-down menu (Calculation). When you click on Create, you will be asked to create a formula for your calculation. Your screen should look like this:

At the top of the screen, you will see, three sections: Fields, Operators, and Function. The Fields section contains the names of all of the fields you have created. Whenever you want to use a field in your formula, double-click on the field name in the Fields list. NEVER TYPE THE NAME OF THE FIELD! ClarisWorks wants fields to be in a special format (enclosed in single quotes with spaces exactly as they are in the field name). While it is possible to type the field names just the way ClarisWorks wants them, it is easy to make a mistake. Instead, double-click on field names so they will be just the way ClarisWorks wants them. The Operators and Functions are mathematical and logical operations you can perform. You can do just about any calculations short of calculus. Scroll through the Function list, and you will see a many simple and complex mathematical functions you recognize and many you don't.

For this database, we want our Final Grade to be the average of the three other grades. If you scroll through the Function list, you should see the Average function. Double-click on it, and your screen should look like this:

This automatically puts the average function in your Formula with some information to tell you how the average function works. The average function will average whatever is between the parentheses. If you put AVERAGE(6,8), you would get 7; if you put AVERAGE(10,12,17), you will get 13. But that is not very useful, unless you average your fields.

You want to average Grade 1, Grade 2, and Grade 3 (which will give a different number for each of your students). The first thing you want to do is eliminate the sample information between the parentheses: number1, number2, .... That is just there to tell you how your formula should look. Drag the mouse across that to highlight it and double-click on Grade 1 from the Fields list. Notice that ClarisWorks automatically puts single quotes around and keeps the spelling and spacing exactly how you typed the name of the field. Type a comma and then double-click on Grade 2. Type another comma and double-click on Grade 3. Note that the only thing you have typed from the keyboard is the comma. Your screen should look like this:

If it doesn't look like that, you can try to fix it. The easiest thing to do is probably to highlight and delete what is between the parentheses and try again.

Once you get your formula correct, click OK. ClarisWorks might complain, telling you there is a problem with your formula. If it does, it will force you to try to fix it. Look carefully at your formula and make corrections as needed. If ClarisWorks did not complain, your screen should look like this:

You now see your Final Grade field listed with all the other fields. The only difference is that instead of a Field Type, you see the beginning of the formula for Final Grade.

You now have all of your Fields defined, so you can now click on the Done button to tell ClarisWorks you are done creating your fields. If you hit the Done button before you were finished, or if you decide you want to change some of the fields or add more fields, you can always go to the Layout menu and select Define Fields to take you back to your list of fields.

Records

You have done a lot of work to set up your database, but you do not have any information. You have defined the fields, telling ClarisWorks what types of information you want to store, but you have not entered any information. The information that you enter is stored in records. A record is all the information about one item (in our case, one student). That is, the specific name, birthday, grade 1, grade 2, grade 3, and final grade for one student is that student's record. When you start manipulating your data (sorting and searching) the records stay together. That is, you can enter the data in any order and alphabetize the data based on the students' names, and all the information for Joe Smith, for example, will stay together because Joe Smith's information is tied together in a record.

Entering Data

Now that you have defined your fields, you are ready to start typing in your data. There are three ways to enter your data: in a Layout, in Browse view, and in List view. You can use layouts to set up your data to look however you want. This can make it easier to enter data and make data look nice for printing. This document will not discuss creating layouts.

Browse view is useful for looking at all the data from one record at a time, but it will not show you all (or several) of the records at once. ClarisWorks automatically puts you into Browse view. Your screen probably looks like this:

You see the names for each field on the left and next to each field you see a box for entering the data. If you don't see the boxes, click once where the boxes should be, and they will show up. Notice that there is no box for Final Grade, just the number 0. That is because Final Grade is a calculated field, so you cannot type any information into it. Right now, it has 0 because the average of nothing, nothing, and nothing is zero. When you enter some numbers for the grades, the 0 will change.

Also, notice on the far left of the screen that there is a little flip book with a 1 in the lower right corner. This flip book can be used to move from record to record. The 1 tells you that you are currently on record 1. Below the flip book is the word Records and below that is the number 1. This 1 tells you that you have a total of 1 record. As you add data for more students, the 1 will change.

Browse view is nice for looking at one student at a time, but I prefer List view, so I can see all (or several) of my students at once. If you choose List from the Layout menu, you will see your database in a grid or table format:

Now you see your field names at the top. Everything else looks about the same, but when you start to enter data, you will see several records in the table at the same time.

To enter data in List view, click in the first box under name and type the name of the first student. Since we made our Name field to be of type Name, you should type the first name first. If you made the Name to be of type Text, you should type the last name first. After typing the name, either hit TAB or click under birthday and type the birthday, hit TAB or click under Grade 1 and type the first grade, etc. If you hit TAB after typing Grade 3, you will notice that the Final Grade is automatically calculated based on the three grades you typed.

If you made a mistake with any of the information you typed, you can always click in the place where you made the mistake and make your correction.

In many database systems, if you hit TAB at the end of the last field for a record, they will automatically create a new record for you. Unfortunately, in ClarisWorks, you have to explicitly tell it that you want a new record. This can be done by selecting New Record from the Edit menu. For those of you who don't like to take your hands off the keyboard while you are typing, you can use the keyboard shortcut command-R (hold down the command key, that's the one with the apple on it next to the space bar, and hit R).

Formatting

Once you have typed several records, your database will look like this:

This is ok, but it could be improved. First, notice that the name field is not wide enough. Several of the students only have their first names showing. You can make a field wider or narrower, but moving your mouse cursor after the field name (right on the line between fields) and dragging the line to the right or the left. You'll know your mouse is in the right spot when it turns into a vertical bar with arrows pointing to the right and left . Change the width of the Name field and the three Grade fields so they are just wide enough to hold the data:

Next, you can adjust the way the birthday looks. Double-click on the title of the Birthday field. You should see the following dialog box:

You can choose one of the other date formats by clicking on another format and then clicking on the OK button. By changing the date format, you are not changing anything except how the date is presented to you. You can enter dates in any of the formats and display them in of the formats. If you changed the format of the date, you might have to make the Birthday field bigger so you can see the full date.

The next thing to do for the birthday field is to center. Click once on the word Birthday to highlight the field. If you accidentally click twice and get the Date Format dialog box, click Cancel. Now, with the Birthday field highlighted, go to the Format menu, choose Alignment, and choose Center from the flyout menu. Do the same thing for the Grade 1, Grade 2, Grade 3, and Final Grade fields, except choose right alignment (because numbers look better when lined up on the right edge. Your database should now look like this:

The last thing we need to do is fix the Final Grade field. We want to get rid of some of the decimal places. Double click on the field name Final Grade to bring up the following dialog box:

In this menu, you can choose how you want your numbers to look. General will try to figure out the best way to display the number for you. Currency will put a dollar sign in front. Percent will display it as a percentage (.5 would be displayed as 50%). Scientific would display it in scientific notation. Fixed will display the number with a fixed number places after the decimal point; that's the one we want. Click on Fixed. Now, you can tell it how many places you want after the decimal place by setting the number in the Precision box. Change that number to 1. Click OK and your screen should look like this:

Keep in mind that you can always change the formatting of your fields, and you can always enter more data.

Tools

On the left side of your screen, you have four tools: the Layout tool, the Sort tool, the Search tool, and the Report tool:

These tools allow you to manipulate your data. Layouts can be useful for displaying data in exactly the way you want, but this paper will not discuss them.

Sorting

When you think about the records in your database, you should not think of them as existing in any particular order. Right now, they are in the order in which you typed them, but that might not be the most useful order. Sorting allows you to put your records in any order you choose. For a database of ten students, this might not be particularly useful, but imagine you are a high school teacher with 150 students. Suddenly, automatic sorting becomes a lot more useful. Imagine you are your school's registrar with information from 1000 students. Now, sorting (and searching which we'll explore next) becomes very important.

Sorting is very simple. You have to tell ClarisWorks which field or fields upon which to base the sort and in which order (ascending or descending). For text and names, ascending is alphabetical order and descending is reverse alphabetical order. For dates, ascending puts the oldest date first and descending puts the newest date first. For numbers, ascending puts the numbers in order from smallest to largest and descending puts the numbers in order from largest to smallest.

Hold the mouse down on your Sort tool and choose New Sort from the menu.

On the left, you will see your list of fields and on the right, you will see the sort order you have chosen. Click on the Name field and then click the Move button. Name will appear in the Sort Order list with lines going up, indicating ascending order. Where the dialog says Sort 1, type a name for your sort that makes sense to you. Alphabetical might make sense for this sort.

If you want to make the sort reverse alphabetical order, click on Name in the Sort Order and click the Descending order button below it.

If you want to sort based on two fields, you can add a second field to the sort order. The first field will be the primary sort key, and the second field will be used to break any ties. For example, if you had separate fields for First Name and Last Name, you would want to sort on Last Name, but how would the computer know to put John Smith before Timothy Smith? To break the tie, you would use First Name as the second field on which to sort.

When your sort is set up with at least one field and a name (always name your sorts), click OK. Look at your data. What happened?

Absolutely nothing happened because all you did was define your sort. You didn't tell ClarisWorks to perform your sort. To perform your sort, hold down the mouse on the Sort tool, and choose your sort from the menu (any sorts you define will show up in the sort tool menu). Now your database should look like this:

If your data is not in alphabetical order, you might check to see if you defined your Name field to be of type Text, instead of type Name. If you did, you can go to the Format menu and choose Define Fields to change it. Once you change it, your sort will work.

You can define several different sorts because sometimes you will want to look at the records in one order, and sometimes you will want to look at them in another order. For example, you might want to look at your records in alphabetical order when taking attendance, but you might want to look at your records in order of Final Grade from best to worst to decide who should get the Outstanding Student award for your class, and you might want to look at your records in order of Final Grade from worst to best when deciding who should get a deficiency report, and you might want to look at your records in order of birthday when deciding who gets to cut the class birthday cake, and ...

Searching

Sometimes you will only want to see certain records. For example, you might want to pick out the students who are doing poorly to set up a conference with their parents. With only 10 students, this is easy without a database. With 150 students, this is more difficult. If you are a school registrar trying to pick out the students who are listed as seniors but do not have enough credits to graduate, this could be very tedious without a search.

To perform a search, hold down the mouse on the Search tool and select New Search. You will be asked to give the search a name. Name the search something that makes sense to (always give your searches names). In this case, we can name this search Doing Poorly. When you click OK, you will see your fields in a format similar to what you saw for Browse view:

If you don't see the boxes next to the field names or if all the fields are highlighted, click where the boxes should be and the boxes should show up.

In a box or more than one box, you will enter your search criteria. The search criteria are logical expressions that tell the computer how to select the records you want. If you want all students whose names are John Smith, type

=John Smith

in the Name box. The equal sign says you want an exact match, so it won't select John A. Smith or Jon Smith. If you want all students whose name contains Smith, type

Smith

in the Name box. This will give you both John Smith and Timothy Smith in our database.

If you want all students whose grades are less than or equal to 75 (those students who are doing poorly), type

<=75

in the Final Grade box. Note that you can use =, <, >, <=, >=, and several other operators. You can play around with these to see how they work. You can also put something in more than one box, so you could get the students who are doing well and are named Smith by putting Smith in the Name box and >90 in the Final Grade Box. With our data, that should only select John Smith, who has a 95.67 average.

Since we want the students who are doing poorly, we'll just put <=75 in the Final Grade box.

Click on the Store button to store your search. Since ClarisWorks likes Browse view, it put you into Browse View. Go to the layout menu and choose List to see your data in the preferred tabular view. You will notice that all your records are still there. That is because, just like what happened with the sorts, you merely defined your search, you did not ask ClarisWorks to perform it. To perform your search, choose it from the Search tool's menu. Your data should now look like this:

Only William Gates and Charles Kim have Final Grades less than 75 so they are the only ones showing up for this search. Notice that on the left side of the screen under the label Records, you see 2(10). This tells you that there are two records showing, but they are 10 records total. From this, you know that you didn't lose any records; you just can't see them all. If you want to see all of your records, go to the Organize menu and select Show All Records.

Searching can be very powerful because you can pick out records based on any criteria you choose. For simple searches, you could perform a sort and look at the appropriate place on the list, but for some searches (especially complex searches with two or more criteria), that will not work.

Reports

The final thing to do with databases is to create reports. Reports combine a Layout, a Search, and Sort. If you have explored Layouts on your own, you can choose a Layout that you have defined. If you have not, then you can choose List View for your layout. You could always select a Layout, a Search, and Sort separately and have the same result as using a report, but if you use certain combinations of Layouts, Searches, and Sorts, reports can be convenient.

To create a report, you must have already defined the Layouts, Searches, and Sorts you want to use. Hold the mouse down on the Report tool and choose New Report from the menu. You should get the following dialog box:

 

Give your report a name by typing a name that makes sense. In this case, we are going to select the students who are doing poorly and put them in alphabetical order, so Poor Alphabetical makes sense.

Next choose List View from the Layout pulldown menu (or choose another layout if you have defined one). Choose Doing Poorly from the Search pulldown menu (unless you have played around and created other searches, it will be the only choice). Finally choose Alphabetical from the Sort pulldown menu. Your screen should look like this:

 

Click OK. You can now choose your report from the Report tool menu. Your screen probably didn't change because we had already chosen all of the same settings individually from the Layout, Search, and Sort tool menus. If you want to verify that your Report worked, you can select Show All Records form the Organize menu and Browse from the List menu. Now, if you choose your Report from the Report tool menu, you should see the data return to alphabetical with only those students with grades less than 75.

Conclusion

Databases are very powerful tools for organizing lots of information. The example used in this paper shows you how to make a simple gradebook (your real gradebook would be more complicated), but you can use databases for any information. If your students are collecting information about insects or rocks, they can put that information into a database and easily find out which insects have wings or which ones have more than 6 legs. For organizing large quantities of data, nothing beats a database.


Return to ET605 Home Page.


This page was prepared by Dr. David M. Marcovitz.

Last Updated: August 20, 1998