
Getting Started		Database

Database

There are screens to define or modify the overall database information, the information about each record and the information about each variable within a record. These screens can be used to set up any kind of database structure or to modify existing database definitions.

As an alternative to using the interactive screens, there are a set of database definition commands. It is sometimes more convenient to create commands using a text editor and run these.

The database definition is referred to as the schema. There are options on the menus to view the schema and to write out copies of the schema to a file.

Creating a new database

To create a new database, select New from the Database - databases menu.

Specify the database name and, optionally, a password in the dialogue box.

As an exercise:

Go to your directory, start SIR2002
Select New from the Database/Databases menu.
Enter TEST as the database name and do not use a password.
Click Next
Congratulations - you have created a database. However, this database has no records defined and so cannot yet store any data. The screen now lets you enter some description of your database. Click OK, then Done to finish.

Example Database:

The following is a brief illustration of the type of data that might be held in a database.

DATABASE  SURGERY
CASE ID   PATNO
Record Type 1  PATIENT

PATNO	NAME	PHONE	ADDRESS	PCODE	DOB
1	James	425-1234	21 High St.	2056	15JAN65
2	Smith	364-9238	1A The Vale	3458	10FEB77
3	Jones	858-3289	32 Main Road	4754	30DEC54

Record Type 2 VISIT

PATNO	VISDATE	SYMPTOM	ACTION	OUTCOME
1	10JAN92	Headache	Aspirin	No change
1	2FEB92	Headache	Aspirin	Cured
1	12APRIL92	Footache	None	*
2	01JAN92	Joint Pains	Infra-red	*

Record Type 3 GENERAL

PATNO	HEIGHT	WEIGHT	INSURE
1	2.2	155	GA123/7
2	1.9	205	*
3	1.7	*	AE435-32

This example database is called SURGERY. It is a case structured database using the patient number (PATNO) as the case identifier. The data can be linked for any given patient. Data can be analysed by patient or by record type.

There are three record types: PATIENT, VISIT and GENERAL.

Each of these has variables of various types:

PATNO is an integer
NAME, PHONE, ADDRESS, PCODE and INSURE are strings of various lengths
DOB and VISDATE are dates
HEIGHT and WEIGHT are real numbers
SYMPTOM, ACTION and OUTCOME are strings with a defined set of possible values or codes for these variables so it is possible to define these as categorical

All of these types of variables can be defined in the SIR2002 database.

Maintaining Databases

Even the smallest and shortest-lived database needs some maintenance work. As databases grow larger, become more complex and involve more people, their maintenance becomes more and more important.

There are several issues that affect all database users and especially managers of databases that other people use.

Safeguarding Databases

Though computers are becoming more and more reliable, they are still subject to failure. Power failures, electrical storms and human error can happen. A database could be corrupted if the computer "crashes" at the wrong time. There are utilities to check whether a database is corrupt and utilities to attempt to recover from corruptions.

There are several things to do to protect against disaster.

Journalling

When journalling is turned on, the journal file keeps track of every change made to a database. Anytime that the schema or data is modified, a record of the change is written in the journal file.

If disaster strikes (e.g. disk crash, power failure) and a database is damaged, it can be completely reconstructed from backup copies and the journal file. When creating a database, journalling can be turned on or off. Journalling can also be turned on or off at any later time. If a database is easy to re-create from an existing computer file or is only going to be used for a very short time, turning journalling off saves time and disk space. Normally keep journalling turned on.

Backups

The UNLOAD FILE utility backs up a database by copying the database files to a single sequential file. The journal file can be used to recover a database by applying the changes it contains to an earlier version of the database. These files should be copied to external media and archived. How frequently a database should be backed up varies with the amount of update activity.

Always run a verify (from the Database/Recover/Verify Database menu) before taking a backup. This should display a single screen with some summary information and the message "Verification Complete. No errors were found." If any errors were found, use the PATCH option on VERIFY to attempt to correct the problem.

Writing Data

Use any of the following utilities (some from the Database/Backup menu and some from theData menu).

EXPORT creates a machine independent version of a database. This can be transferred and IMPORTed to recreate the database and application on the new machine.
DUMP SIR FILE creates a text file containing the data or a subset of the data formatted according to the specifications in the schema. This is a format suitable for input through the batch data utilities. This can be a way of passing data between systems or a way of saving data for re-input for one reason or another.
UNLOAD backs up a database by copying the database files to a single sequential file. This can then be used to restore the database. How frequently a database should be backed up varies with the amount of update activity.
LIST SIR FILE produces a readable or printable version of the data in the database.
ITEMIZE FILE is used to check what is on a journal or an unload.
PURGE DATABASE deletes the whole database.
DELETE RECORD SCHEMA deletes a record definition.
LIST STATS provides database statistics including important dates, database parameters, numbers of variables, keyfield size, number of schema redefinitions by record type and record counts by record type.
VERIFY DATABASE checks the structure of a database.