Databases

PipeDream enables you to store a database of records and provides facilities to sort your records on one or more fields. You can save and print out those records which satisfy one or more conditions which you specify. PipeDream also provides several database functions which enable you to produce statistics about your records.

The best way to become familiar with using PipeDream as a database is to load a database of records and try out the various facilities upon it. An example is a pupil records database called Markbook, which is provided in the Examples archive, in the directory called Tutorial.

Loading the Example (Markbook) Database

To load the example database:

  1. Download the PipeDream Examples archive onto your disc drive.
  2. Make a writable copy of the contents of the archive on your disc drive. Open the Examples archive with SparkFS, then simply drag the contents of the Example archive to a different location on your disc drive. Close the Examples archive directory viewer to avoid confusion!
  3. Double-click Select on the Tutorial directory's icon.
  4. Double-click Select on the icon of the file called 'Markbook' to load it into PipeDream.
  5. When the file has been loaded into PipeDream, use the toggle size icon if you need to change the size of the window.

The database consists of a list of pupils together with their classes, subjects (one of history or physics, indicated by H or P), continuous assessment marks, examination marks and total marks. Each record is stored on one row and each field is in a separate column.

If you scroll down through the database, you will find that the column headings, which let you know which field is in each column, disappear from the top of the window.

Fixing Rows

It is possible to fix rows so that the column headings always remain at the top of the window. To do so:

  1. Position the caret on row two.
  2. Give the Fix Row command:
Menu Command Ctrl-sequence Key
Layout Fix row LAF

The borders of rows one and two change colour to show that the rows are fixed.

Try scrolling down through the database. You will see that rows one and two remain at the top of the window.

Sorting the Database

When you first load the database it is sorted on column C, ie on class. You will see that all the pupils in 1A are first, followed by those in 1B, then 2A, then 2B.

If you would rather have the pupils listed in alphabetical order of surname, you need to sort the database on column B, the column containing the surnames.

To sort on column B:

  1. Mark the block of records you want to sort by dragging Select down the borders of rows 3 to 39. Dragging Select down the row border marks all of the columns in these rows.

Note that dragging Select beyond the bottom of the window has the effect of scrolling downwards so that you can see and mark data which was not visible at first.

  1. Give the Sort command:
Menu Command Ctrl-sequence Key
Blocks Sort BSO
  1. When the Sort dialogue box appears, delete A with the Backspace key and type B since this is the column you want to sort on.
  2. Click the OK button or press Enter.
  3. Click Select on the mark block box to clear the marked block. The records will be sorted in alphabetical order of surname.

PipeDream also enables you to sort on more than one column at once.

If you want to list pupils in classes 1A, 1B, 2A and 2B in order of the total marks they gained in history and physics, you need to sort first on column C, which contains the classes, then on column D, which contains the the subjects, and finally on column G, the one containing the total marks.

Mark rows 3 to 39 by dragging Select down the row borders.

Give the Sort command.

Type C on the first row of the dialogue box, D on the second row and G on the third row. To move through the dialogue box, press or or click Select in the field you want.

Click the OK button or press Enter.

The records are now arranged in order of class, then subject, then total mark, enabling you to see who was first in history in class 1A, who was bottom in physics in class 2A, and so on.

Click Select in the mark block box to clear the marked block.

Searching for Records

If you want to find a particular pupil's record quickly, you can use Search to find him or her.

Give the Search command:

Menu Command Ctrl-sequence Key
Blocks Search BSE F4

When the Search dialogue box appears, type in the name you want to find.

Try typing Quinn.

Click the OK button or press Enter.

The caret will be positioned next to the name Quinn.

If you want to find all pupils called John, give the Search command again.

When the Search dialogue box appears, delete Quinn and type John.

Click the OK button or press Enter.

The caret will be positioned next to the first occurrence of the word John.

To find the next occurrence of the word, give the Next Match command:

Menu Command Ctrl-sequence Key
Blocks Next match BNM F6

Searching can be especially helpful if you have a very large database.

Selecting Records

One of the most useful aspects of database use is the ability to save and print records which satisfy certain conditions.

If you need to predict your pupils' GCSE results, for example, you might want to know which pupils in class 1A who study history got a total mark higher than 100.

Or you might want to know which pupils got either a continuous assessment mark lower than 50 or an examination mark lower than 45 so that you can give them extra attention next term.

To find out which pupils in class 1A who study history got a total mark higher than 100:

  1. If necessary, shrink the window a little so that you can see the PipeDream icon on the icon bar.
  2. Give the Save command.
  3. When the Save dialogue box appears, click Select on the Save selection of rows option.
  4. Click Select to position the caret in the box to the right of the Save selection of rows option.
  5. Type the following:

C1C39="1A" & D1D39="H" & G1G39>100

The condition is too long to be completely visible in the dialogue box, so the field will scroll as you type.

It tells PipeDream to save all those rows which have 1A in column C, H in column D and a number higher than 100 in column G.

The slot references you have typed, C1, D1 and G1, will be updated automatically for each new line in the database, changing to C2, D2 and G2, then C3, D3 and G3, and so on.

Notice that any text included in your condition must be enclosed within double quotation marks, for example "1A".

The & sign is used to link conditions when there are more than one.

The conditions on both sides of the & sign must be true for the record to be selected.

  1. Drag the PipeDream icon from the Save dialogue box onto the PipeDream icon on the icon bar.

A new PipeDream window will appear on the screen containing the records you selected, ie a list of all pupils in class 1A who study history and got a total mark higher than 100.

You can now save the new file or print it out, depending upon your requirements.

To find out which pupils got a continuous assessment mark lower than 50 or an examination mark lower than 45:

  1. With the pointer in the example database window, give the Save command.
  2. When the Save dialogue box appears, click Select on the Save selection of rows option.
  3. Delete any contents using the Backspace key and type the following:

E1E39<50 | F1F39<45

To type |, hold down Shift and press the \ (backslash) key, which is usually either just above the Enter key or to the left of the Z key on UK PC-style keyboards.

This tells PipeDream to save all those rows which have either a number lower than 50 in column E or a number lower than 45 in column F.

| is used to represent 'or'. Only one of the conditions needs to be true for the record to be selected.

  1. Drag the PipeDream file icon from the Save dialogue box onto the PipeDream icon on the icon bar.

A new PipeDream window will appear on the screen containing the records you selected, ie a list of all pupils who either got an assessment mark lower than 50 or an exam mark lower than 45.

Once again, you can save the new file or print it out, depending upon your requirements.

Entering Records

You can type new records into PipeDream in the same way as you type text. Type each record on a separate row and each field of the record into a separate column.

Try entering a few new records into the Markbook database. Position the caret in slot A15, for example.

Give the Insert Row command:

Menu Command Ctrl-sequence Key
Edit Insert row N F7

Type in the first name of a pupil. Press . You are now in column B, ready to type in the pupil's surname. When you have done so, press again to move to column C, where you can type in the pupil's class. Continue in this way until the pupil's record is complete.

Try adding more pupil records to the database.

Deleting Records

If a pupil leaves the school, you will want to remove his or her record from the database.

Position the caret on the row you want to delete. Give the Delete Row command:

Menu Command Ctrl-sequence Key
Edit Delete row Y F8

Column Widths

Use the Auto Width command to ensure that each column is the correct width for its contents:

Menu Command Ctrl-sequence Key
Layout Auto width LAW

Database Functions

PipeDream provides a number of database functions which enable you to produce statistics about selected records in your database. The records are selected by the condition which forms the second part of the database function.

For example, you can use the function DCOUNTA to find out how many of your pupils study history:

  1. Position the caret in an unused slot such as A40.
  2. Click Select in the formula line.
  3. Type the following:

DCOUNTA(D3D39, D3D39="H")

  1. Press Enter or click Select on the tick box.

The number of pupils who study history appears in the slot containing the DCOUNTA formula.

D3D39 is the range of slots upon which you want to carry out the function ie all slots from D3 to D39 inclusive.

D3D39="H" is the condition. You only want to count the slots in the range D3D39 which contain H.

Note that the parameters of the function are enclosed within brackets and any text, in this case H, within double quotation marks.

If you want to know the average physics exam result:

  1. Position the caret in a blank slot such as F41.
  2. Click Select in the formula line.
  3. Type the following:

DAVG(F3F39, D3D39="P")

  1. Press Enter or click Select on the tick box.

The average physics result appears in the slot.

There are many other database functions which are explained fully in the Database Functions section of the Reference Guide.