Flat-File Databases

A 'flat-file' database is one that only contains a single table of data.

All of the data in the database is stored in this one place. The student database example that we looked at in the previous section was a flat-file database...
The database work that you have to do for the practical exam always uses flat-file databases.
Stacks Image 3684

Relational Databases

A 'relational' database is one that contains two or more tables of data, connected by links called relationships.

Why would you want to have more than one database table?

Take a look at the student database example....
Stacks Image 3697
Notice that the table contains several items of data that are repeated over and over again:
  • Class (5B)
  • Tutor (Mr noggin)
  • Room (56)
In fact, every student in class 5B will have these items of data.

Repeated data in a database is generally considered a bad thing:
  • It wastes space in the database
  • It takes time to input, typing the same data over and over (and mistakes may be made)
  • It is a pain to update (if class 5B gets a new tutor, we have to find every 'Mr Noggin' and change it to the new name)
So how do we avoid repeated data?
You have to understand the concept of relational databases, but you will not be required to use/create them in the practical exam!

Multiple Tables

The solution is to split the data: The repeating data is removed from the main table, and placed in a table of its own...
Stacks Image 3712
Note: we need to leave the Class field in the main table as we still need to know which class each student belongs to , but the data relating to each class (Tutor, Room) can be removed.

So, now the main Student table just contains data directly related to students, whilst the new Class table contains data directly related to classes.

Note that both tables are independent, and each one has its own key field / primary key:
  • Student table key field is student ID number
  • Class table key field is class code
Ok... so we've solved the repeating-data problem, but we seem to have created a new problem: how do we know the name of each student's tutor - it's no longer in the Student table?
Now imagine that class 5B has a new tutor... How much data would you need to update?

That's correct: only one item!

Remember that, with a flat-file, we had to find every student in class 5B and update the tutor field.

Linking Tables - Relationships

We need to link the table together so that we can connect a student to a specific tutor and room.

The common field in both tables is the Class field.

We use this field to create a relationship (link) between the two tables...
Now imagine that class 5B has a new tutor... How much data would you need to update?

That's correct: only one item!

Remember that, with a flat-file, we had to find every student in class 5B and update the tutor field.
Stacks Image 3738
Note that to create the relationship, we are using the key field (primary key) from one table to link it to another.

When a key field from one table appears in a different table (e.g. the Class field in the Student table), we call this a foreign key.
Database design is a very complex business. It's a career for some people.

For complex databases, it can take a lot of skill to plan what tables and what relationships are required.
Stacks Image 3751

Sorry to ask you this, but...

 

This site took quite a bit of time and effort to create, and it costs me money to keep it up and running. If the site has helped you, please consider showing your appreciation by donating a little towards the site's running costs.

Thank you!

Sponsored Links...