Database Structure A database is a system for organising and storing data Databases are made up of one or more files Files are a collection of records, and records are made up of fields.

Database Structure A database is a system for organising and storing data Databases are made up of one or more files Files are a collection of records, and records are made up of fields.

Database Structure Each column represents a field

Each row represents a record

Flat File Database A flat-file database is a database structure which consists of only a

single table.

While this is a simple way to store files, a flat-file system becomes increasingly inefficient as more data is added.

Database AnomaliesWhilst the flat-file database might seem to be a good solution to begin with, there are actually a number of problems which are linked with this approach.

These are known as anomalies.

Database AnomaliesAnomaly 1:Mr O'Hara is a teacher in the English department and he teaches in English Room 4. Add Mr O'Hara's details to the database.

Database AnomaliesAnomaly 1:Mr O'Hara is a teacher in the English department and he teaches in English Room 4. Add Mr O'Hara's details to the database.

This is known as an insertion anomaly.

Database AnomaliesAnomaly 2:The art department is reorganising rooms and Miss Dewar is moving from Art Room 3 to Art Room 1. Update the database to show this.

Database AnomaliesAnomaly 2:The art department is reorganising rooms and Miss Dewar is moving from Art Room 3 to Art Room 1. Update the database to show this.

This is known as an update anomaly.

Database AnomaliesAnomaly 3:John McGinn has left school. His record should be deleted from the database.

Database AnomaliesAnomaly 3:John McGinn has left school. His record should be deleted from the database.

This is known as a deletion anomaly.

Relational DatabasesA better approach to database design is known as the Relational Database Model. In the relational model, the data is stored in multiple tables or relations.

However, in this example we have lost the link between pupil and their tutor teacher.

Relational DatabasesWe create linked tables by introducing keys.

Relational DatabasesWe create linked tables by introducing keys.

Atomic DataData items are said to be atomic if they cannot be broken down into smaller data items.

In this example, Forename and Surname fields are atomic because they cannot be broken down into smaller data items:

Atomic DataData items are said to be atomic if they cannot be broken down into smaller data items.

In this example, the Pupil Name field is not atomic because it could be further broken down into fields - Forename and Surname: