SS 2 First Term

Learning Objectives

• Define a database and state why databases are used.
• Describe the parts of a database.
• Distinguish between a table, a record and a field.
• Create, sort and filter a table in a database program.

Meaning and Reason for Databases

A database is an organised collection of related data stored so that it can be searched, sorted and retrieved quickly. A file system stores data in separate files, which leads to duplication and to data that disagrees with itself. A database stores the data once in a controlled structure.

A school keeps a register of pupils. In files, each pupil would have a separate file, and a change of address would have to be made in several places. In a database there is one pupil record, so the change is made once and every report that uses it is correct.

A database is used when there is a large amount of related data that must be searched quickly or reported on in different ways. Schools, banks, hospitals, shops and government offices all use databases.

Parts of a Database

A database system has three parts. The database itself is the collection of stored data. Database software is the application used to enter, search and report on the data. The hardware is the machine and the storage that holds it.

Within the database, data is held in tables. A table holds one kind of record, such as all the pupils or all the staff. A record is one row and holds the fields of one item. A field is one column and holds one kind of information about every record, such as the surname of every pupil.

Each table has a key. The primary key is the field or combination of fields that makes every record unique, and no two records may share the same key. A pupil number makes a good primary key because it is unique and does not change, while a name does not, because two pupils may have the same name.

A table and a record are not the same. A table holds many records, and a record is one row.

A field is not a column label only. The label is the field name, while the field holds the value in each row.

A primary key must be unique, so a name is a poor choice because two people may share it.

Keys, Relationships and Design

A foreign key is a field in one table that refers to the primary key of another table, and the link is called a relationship. A pupil table holds the name and address, an marks table holds the scores, and the pupil number links them.

The design is called normalisation. Splitting the data so that each fact is stored once avoids repetition. The pupil table holds the address once and the marks table holds the scores, and neither repeats the other. This is called first normal form, and the goal is that a fact is entered once and used everywhere.

A relationship may be one to one, where each record in the first table matches exactly one in the second, or one to many, where one record matches many in the second. One pupil has many results, so that is a one to many relationship.

Validation means checking data as it is entered.

Redundancy means the same fact stored more than once.

Using a Database Program

A database program is used to create a table, define the fields and their data types, and then enter the records. The data type is set for each field, such as text, number or date, and it decides what may be entered.

Records are sorted by choosing a field and the sort order, which arranges the records alphabetically or by value. Filtering shows only the records that meet a stated condition, such as all the pupils in a given class, while the rest are hidden but not deleted.

A search finds a particular value. A report is a formatted output produced from the data, such as a list of pupils in a class or a record of attendance. Reports are generated from the stored data, so they are up to date without being typed.

Filtering hides records without deleting them, so a filtered result is not a reduced database.

Advantages and Precautions

The advantages of a database are that data is stored once, searches are fast, several users may work with the same data at once, security can control who sees what, and reports can be produced in any required format.

Precautions are needed because a database holds a great deal in one place. Access is given only to those who need it, a password protects it, a backup copy is kept separately, and the data is validated on entry so that a date of birth cannot be entered as a word.

Practical Work

1. Create a database with one table of pupils holding the fields pupil number, surname, forename, class and date of birth.
2. Set the data type of each field and set the pupil number as the primary key.
3. Enter ten records, then sort the table by surname and confirm the order.
4. Use a filter to show only the pupils in a given class and record the number shown.
5. Create a second table of results holding the pupil number and three scores, and link it to the first table using the pupil number.
6. Produce a report listing the pupils in one class with their scores.

Scenario-Based Questions

1. Explain why storing pupil records in separate files leads to errors that a database avoids.
2. A school wants to use names as the primary key of a pupil table. Give two reasons this is a poor choice.
3. Explain the difference between a field and a record, using a library catalogue as an example.
4. Describe how splitting the address from the marks table reduces redundancy.
5. Explain what a filter does and state what it does not do.
6. A database holds confidential results. Explain two precautions that should be applied.
7. Explain the difference between a one to many relationship and a many to many relationship.