What Is a Database?

A database is a collection of related information. The information
is stored as a series of records. Each record is all the information
stored about one item (such as a person, an item in a store's
inventory, a student, an employee, a patient, an animal in the
rainforest, etc.) A database program helps to organize this information
and lets you manipulate it to answer questions.

Records and Fields

The kinds of information stored in a database are known as
fields. For example think about the kind of information you might
want to know about your students: name, address, phone number,
ID number, math group, reading group, grade on test 1, grade on
test 2, etc. Each of these kinds of information could be a field
in the database. It is often helpful to picture a database as
a table with each row representing a record and each column representing
a field:

The above example has seven fields: First Name, Last Name,
Birthday, Grade 1, Grade 2, Grade 3, and Final Grade. It also
has ten records, one for each of the ten students listed. The
information in a record is tied together because it is all about
one item (a student in this case). If a record gets deleted, all
the information for that record gets deleted. If a record gets
moved to another location, all the information for that record
gets moved.

Field Types

The information in a field is also tied together because it
is all the same kind of information. Everything in the birthday
field is a date, for example. Fields can be of different types
(exactly which types are available varies based on which database
program you are using). Some of the most common types include:

text

Used to store letters and numbers. While many complex functions
can be applied to text, the most common thing is to sort text
alphabetically (e.g., put the records in order based on the students'
last names).

number

Used to store numbers. You would store numbers in a number
field so you could perform mathematical functions on them (e.g.,
calculating average grades). Some fancier database programs have
several types of number fields for storing integers, real numbers,
long integers, etc. Numbers for which you do not want to perform
calculations (such as phone numbers and ID numbers) are usually
better left in a text field.

date

Used to store dates. This is a very convenient type of
field because dates can be formatted in many different ways (5/1/98;
May 1, 1998; Friday, May 1, 1998; 5/1/1998, etc.). Generally,
databases will allow you to enter information in any of these
formats and display the information in any format (regardless
of which format you used to enter the information). In addition,
if you want to sort a database by dates (e.g., list your students
in order of their birthdays), you can do that with a field of
type date.

name

Some databases allow for a special type of field for names.
This field allows you to store a students first and last name
in one field (instead of having separate first name and last
name fields as in the example of above). When you sort your database
on a name field, the database will sort based on the last name.
If you put both the first and last name in one text field and
tried to sort, the database would sort based on the first name.
For example, the text "Sam Jones" comes after the text
"Kim Smith" because "Sam" is alphabetically
after "Kim," but the name "Sam Jones" comes
before the name "Kim Smith" because "Jones"
is alphabetically before "Smith." If you really want
to put first and last names in a single text field, you should
enter them as last name, first name.

calculation

Some databases, such as ClarisWorks, treat calculations
as regular fields. For simple databases, this is very convenient
because it allows you to treat all of your information in the
same way with the exception that calculated fields will automatically
calculate their values. In the example above, final grade was
a calculated field. It is the average of the three grades. In
ClarisWorks, this is represented with the formula: AVERAGE('Grade1','Grade2','Grade3').
Some databases, such as Microsoft Access, don't have calculated
fields (fields are reserved for information you are going to
enter). Instead, calculations are performed in reports, the place
where the information is set up to look nice.

In the above example, First Name and Last Name are name text
fields; Birthday is a date field; Grade 1, Grade 2, and Grade
3 are number fields; and Final Grade is a calculation field.

Manipulating Fields

There are two things that you will want to do to fields in
a database: format them to look nice and base calculations on
them to extract the information you want.

Formatting Fields

For all your fields, you can do the basic formatting things
you do with text in a word processor: you can changes styles (bold,
underlined, italic), fonts and font sizes, justification (left,
right, center justified). In ClarisWorks, you can do this by clicking
on the field name and making selections from the Format menu.
In Microsoft Access, you are likely to do most of your formatting
in forms and/or reports, but you can manipulate tables directly
in datasheet view by clicking on the field name and either making
a selection from the Format menu or the Format toolbar.

Calculations

Calculations allow you to enter information into a record and
calculate things based on that information. A calculation can
be as simple as adding two numbers together or as complex as calculating
a letter grade based on several numerical grades. Your primary
use for a calculation is likely to be calculating students' grades.
This can be done as a simple average (easy but not usually realistic)
or some more complicated weighted average (difficult but more
likely to be what you want. See the Weighted Averages handout
for more information.

While formatting is nice, calculations are one of the most
powerful things a database can do. You can enter a formula once
and let the computer do all the work for you. Imagine, for example,
calculating by hand (even using a calculator) each grade for 150
students (a typical load for a high school teacher) compared to
setting up one formula and letting the computer do all the work
for each of the 150 students.

Manipulating Records

There are two kinds of things you will want to do with records:
sort them and select certain records.

Sorting Records

Sorting allows you to put records in whatever order you want
based on the values of certain fields. The most common example
is that you would want to put your students in alphabetical order
(sorting on the name or Last Name fields). When I used a paper
gradebook as a teacher, I would always be annoyed when I got a
new student because I could not put that student in alphabetical
order in my gradebook. A database doesn't care what order the
information was entered, a simple sort will put the new student
in exactly the right place. In addition, you might want to sort
based on the students' current average to see who is doing well
and who is not. You might have a database of animals and you want
to sort based on the kingdom or the habitat of the animals to
group similar animals together.

To sort in ClarisWorks, create a new sort by choosing the Sort
Tool (on the left of the screen), move the fields
on which you want to base your sort from the Field list to the
Sort Order list and choose whether you want ascending or descending
for each sort. Be sure to name your sort for future reference.
Once you have defined your sort, choose it from the Sort Tool
and the order of your records will be changed.

To sort in Microsoft Access, look at a table in datasheet view
and click on the field name for which you want to base your sort.
Click on the sort ascending (A to Z) in the toolbar or descending
(Z to A) in the toolbar. If you want to save a sort, you will
have to create a Query.

Sorting is the another powerful thing that databases can do
for you because trying to sort the data by hand (imagine a database
with hundreds or thousands of records) can be very time-consuming.

Remember that as you sort the records in your database, all
the information for each record stays together. Sally Smith's
grades, for example, will always stay with Sally Smith's name
and ID number, so you don't have to worry about information getting
lost and attached to someone else's information.

Selecting Records

Imagine a database with thousands of animals and you want to
see only those animals who live in salt water. You could manually
look through the list to pick out the animals you want, but that
would be tedious, and you would be likely to make a few mistakes.
Imagine you want to send out deficiency reports to all your students
who are in danger of failing (perhaps those with an average less
than 75). These tasks are very easy in a database. In ClarisWorks,
this process is called searching, and to perform a search, you
use create a new search using the Search Tool on
the left side of the screen. Give the search a name, and you will
see a list of each of your fields, and you can enter search criteria
in any of the fields. For example, to pick out those students
whose average is less than 75, simply enter <75 in the Average
field. To pick out those students who are in the blue math group,
simply enter =blue in the Math Group field. When you have entered
the appropriate criteria, click on store to store the Search.
Now, your search will appear as a choice when you select the Search
Tool. If you select your search, you will only see the records
that fit the criteria you have selected. To see all the records
again, choose Show All Records from the Organize menu.

In Microsoft Access, you will have to create a new Query to
select certain records. Once you have attached your Table to the
Query, you can add whatever fields you want to the Query. You
should add any fields you want to see in the results of the Query
and any fields on which you want to base your selection. You should
see a row in the Query table labeled Criteria. You can enter a
mathematical formula, such as <75, in the criteria row under
the Average field to select those students who have an average
less than 75. You can enter blue in for math group to pick out
those students who are in the blue math group. To see the results
of your Query, go into datasheet view.

Selecting or searching is a very powerful aspect of databases
because it allows you to take a large database and see only the
items you want to see.

Combining Sorting and Selecting

Finally, you might want to select and sort at the same time.
For example, you might want to see all your potentially failing
students in order of current grade. In ClarisWorks, this is done
with a Report. A Report combines a Layout, a Search, and a Sort.
Layouts can be very useful as well, but they are beyond the scope
of this class (exploring them is a good way to exceed the minimum
requirements), generally we will choose List View as our Layout.
The Search and the Sort are things you created. To create a Report,
choose the Report Tool from the left
side of the screen and pick New. Simply, name your report, and
choose a Layout (probably List View), a Search, and a Sort from
the pull-down menus. Save your report and select it from the Report
Tool. No matter what records were showing and in what order, when
you choose a report, ClarisWorks will always show the records
specified in the Search in the order specified in the Sort.

In Microsoft Access, you can combine a Sort with selection
criteria in your Query. In design view of your Query, in the row
above where you put your selection criteria, there is a Sort row.
If you want to Sort based on a field, click in the Sort row for
that field and choose Ascending or Descending from the pull-down
menu (this can be the same field with selection criteria or a
different one).

These methods of combining sorting and selecting are very useful,
but you can always do the same thing in two steps by first doing
a search or query with selection criteria and then doing a sort.
The results will be the same. However, if you want to use those
results over and over again, creating a Report or a Query with
both selection and sorting criteria will save you some time.

Layouts

When you want to present your data neatly, you can create a
layout for the data. In ClarisWorks this is done with the Layout
Tool on the left side of the screen. In Microsoft
Access, this is done with a Form (for a neat way of inputting
data) or a Report (for a neat way of outputting data). With layouts,
you can set the screen up to look however you want, not just as
a simple table. This can be useful and is worth exploring on your
own.

Summary Information

It is often useful to summarize information, such as calculating
an overall class average. This is possible with databases. In
ClarisWorks, you must first create a special Summary type of field
and then create a layout that includes that field. In Microsoft
Access, you can do this in a special section of a Report. This
can be useful, but it is beyond the scope of this class.

Relational Databases

ClarisWorks is a simple but powerful database program. It allows
you to enter your data in one place and manipulate that data as
described above. Relational databases, like Microsoft Access,
are more complex and even more powerful. You can create several
tables to enter different kinds of related data. You can use a
variety of tools to link those tables together, cross-reference
the information on those tables, and verify that information from
one table is acceptable with other tables. For most of your purposes
a simple database (done in ClarisWorks or only using a single
Table in Microsoft Access) will suffice, but if you want to create
a database to keep track of extremely complex and varied information
(such as all the student records at your school), you might need
a relational database.

Conclusion

Database can be powerful tools for keeping track of large quantities
of information. Teachers can use them to keep track of students'
grades and assignments. Students can use them to keep track of
objects being studied (e.g., animals or rocks). Through calculations,
sorting, and searching, databases can make manipulating your data
very easy.