Suppose a school stores information about its students on record cards. Each student has their own card; this is their record.
This video looks at databases
The individual pieces of information recorded on each card, such as name and date of birth, are called fields.
- Information in computer-based filing systems is stored in data files. A file is a collection of related records.
- Related records means that each record in a file will contain the same sort of information as all the other records.
- A record must have at least one field. A field contains one individual item of data.
- The arrow below is pointing to the fields, there are four fields in each record of this file
ISBN is the key field. This is the field that has a different value in every record. It is used to distinguish one record from another. A key field uniquely identifies an individual record.
- A fixed length record is one where the length of the fields in each record has been set to be a certain maximum number of characters long.
- A set amount of storage space is set aside for each field. If the contents of a field don’t fill the space completely it is remains empty and is wasted.
The problems with fixed length records are:-
- Fields very rarely contains the maximum number of characters allowed which wastes space;
- Values sometimes can’t be entered because they are too large to fit inside the allowed space in a field.
The advantage of fixed length records is that they make file processing much easier because the start and end of each record is always a fixed number of characters apart. This makes it much easier to locate both individual records and fields.
A variable length record is one where the length of a field can change to allow data of any size to fit.
- A special marker (# in this example) indicates where each field ends.
- The length of a field depends upon the data that is placed in it. Only the space needed for a field is ever used — so none is wasted.
- The advantage of variable length records is that space is not wasted, only the space needed is ever used.
- The main problem with variable length records is that it is much more difficult to locate the start and end of individual records and fields.
- To separate variable length records each field has a special character to mark where it ends — called an ‘end-of-field marker’. When records need to be located the computer must count through the end-of-field markers to locate individual records and fields.
- A database is a structured collection of related data.
- It can be a single file that contains a large number of records or a collection of files.
- Many modern databases are described as being relational.
- A relational database stores data in tables that are linked together using common fields.
Linked data tables in a relational database
- File operations are the different things that can be done to a computer file.
- The main types of file operation are searching, sorting, updating and merging.
Searching
- Searching, or interrogating a file, involves looking for an individual record or group of records that match a certain condition.
- Searches are also called queries.
- To search a database the user must enter a query. The query tells the software which fields to look at in each record and what to look for.
Sorting
- Sorting involves putting the records in a file into a particular order, such as alphabetical order.
Merging
- Merging involves combining two files to produce one new file. This can be done by merging a file of new records to be added with another file that contains all of the existing records — called the master file.
Updating
- The information stored in computer files must be kept up-to-date or it will cause problems for the business or organisation that’s using it.
- To keep a file up-to-date it must be regularly updated. This involves inserting, deleting and amending records.
- When a new record needs to be added to a file, it is inserted. Records are deleted when they are no longer needed. Records are amended when the data in one or more of the fields needs to be altered for some reason.
- Details of all the changes that need to be made to a master file are often collected together in a transaction file.
- The master file is updated by comparing it with the transaction file and making changes to any records that appear in both files. Normally at least three ‘generations’ of a master file are kept for backup purposes. If the latest version of the master file is damaged it can be recreated by re-running the previous update using the old master and transaction files.
- The grandfather-father-son method of updating can be seen below.
An on-line database is constantly being updated. To make sure no data is lost in the event of hardware failure special back-up methods are used.
Two commonly used methods are:-
- Transaction logging;
- RAID (Redundant Array of Inexpensive Disks)
RAID is a technology that connects two or more hard drives to improve storage performance and/or to protect against hardware failure. This video explains what RAID is all about.
Most databases are set-up using a database package.
A typical database package will allow a user to:
- Create a file by entering their own field definitions;
- Specify automatic validation checks for fields;
- Add new fields to records or delete fields that are no longer needed;
- Add, edit and delete records in a file;
- Perform simple searches and complex searches using more than one condition;
- Import data from other applications software;
- Export data in standard file formats to other applications software;
- Create customised report forms for output;
- Create customised data entry screens;
- Create customised menu screens and link them menu together;
- Link files together using common fields.