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...
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.
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....
Why would you want to have more than one database table?
Take a look at the student database example....
Notice that the table contains several items of data that are repeated over and over again:
Repeated data in a database is generally considered a bad thing:
- Class (5B)
- Tutor (Mr noggin)
- Room (56)
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)
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...
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:
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
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.
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...
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.
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.
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.
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.
For complex databases, it can take a lot of skill to plan what tables and what relationships are required.
