Introduction to Databases

ET 605

Dr. Marcovitz

Contents

 

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.


Return to ET605 Home Page.


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

Last Updated: June 12, 1998