The ICT Lounge
 
Section 5.4:
Types of Databases
 

This section focuses on different types of databases, in particular, flat-file and relational databases.

We will discuss the benefits of using relational databases and how tables can be joined together to make entering and updating data more efficient .

Key Concepts of this section:
#

#
Understand the difference between flat-file and relational databases.
Understand the uses of primary keys and foreign keys when linking two database tables together.
The two different types of databases
#

 

There are two main types of computer databases:
1. Flat-File
2. Relational
Flat-File Databases

#


#

#


Flat-File databases hold all of their data in one table only.


They are only suitable for very simple databases.

The patient database is an example of a flat-file as all of the information is stored in one single table:

Key Words:
One Table, Data Redundancy.

NOTE!
In your Section 11 exam (databases) you would only be expected to produce a flat-file.
Limitations of a Flat-File Database
#
The problems with using a flat-file databases are as follows:
  • Duplicated Data is often unnecessarily entered.
  • Database space is wasted with this duplicated data.
  • Duplicated Data takes a long time to enter and update (unnecessarily).
THESE PROBLEMS CAN BE SUMMARISED WITH TWO WORDS: DATA REDUNDANCY!
What is Data Redundancy?
#


#
Data Redundancy is where you store the same data many times (duplicate data) in your table.

This repeated data needs to be typed in over and over again which takes a long time.

For example:-
The patients database contains several entries of duplicate data:

  • Doctor Id
  • Dr Hyde
  • Room 03
NOTE!
Doctor Id 02, Dr Jekyll and Room 06 are also duplicates and therefore the descriptions here also apply to those data items.
 

REMEMBER!
Data that is duplicated unnecessarily within a database is bad practice. If we had 100 patients who were all assigned Dr Hyde. His Doctor Id, Name and Room Number would have to be entered 100 separate times.

Also if Dr Hyde left the doctors surgery, we would have to update the new doctors details for every patient in the database.

 
What is the solution to Data Redundancy?
#
The way to avoid the data redundancy problems that come with flat-file databases is to create a relational database.
 
Relational Databases

#


#

#


Relational Databases use two or more tables linked together (to form a relationship).


Relational Databases do not store all the data in the same table.

Repeated data is moved into it's own table as shown in the image below:

Key Words:
Relationships, Primary Key, Foreign Key, Common Field

NOTE!
Here I have moved all the repeating data into a table of its own.

Now I have a patient table (for patient details) and a doctor table (for doctor details)
What is a relationship?
#

#


#
A relationship is formed when our two tables are joined together.

Relationships make use of key fields and primary keys to allow the two tables to communicate with each other and share their data.

Key fields are identified using a primary key as shown in the image below:

 

NOTE!
Notice how I have only entered the Doctor Name and Room Numbers once.

 

REMEMBER!
The key field is used to hold unique (one of a kind) data.

 

#

#
Once the tables are linked together each one can read data from the other.

This means that we only need to enter the details of each doctor once instead of many separate entries.
 
NOTE!
When the link (relationship) is created, we will be able to match each patient with the correct Doctor and Room Number in the doctor table.
How do you form the relationship? (link the tables)
#

#

#

In order to link the tables we need to use a common field.

A common field is data that appears in BOTH tables.

If you look at the image below you will see that the common field in the patient database is Doctor Id:

 

NOTE!
The primary key (doctor Id) in the doctor table is being used to link to the doctor Id in the patient table.

 

#

 


#

At this stage you may have noticed the following:
  • Doctor Table - The common field (Doctor Id) is set as a key field
  • Patient Table - The common field is not set as a key field.

Common fields that are not being used as key fields are known as foreign keys.

 
What is a foreign key and what are they used for?
#

#

A foreign key is a regular field in one table which is being used as the key field in another table.

Foreign keys are used to provide the link (relationship) between the tables.

For example:-
In our patient database, Doctor Id is a key field in the Doctor Table but is also being used in the Patient Table as a foreign key:

 

 

NOTE!
Without the common field (Doctor Id) and the use of primary keys and foreign keys, data cannot be shared between tables .

 

#
The foreign key (Doctor Id in the patient table) can then be used to match to the primary key (Doctor Id in the doctor table) and share the correct data.

For example:-
A patient with a Doctor Id of 01 will be automatically assigned to Doctor Hyde and Room 03.

 
REMEMBER!
Now that we have linked our two tables we can update our doctor information very quickly.

For example:-
If Dr Hyde changed his room number to 02, we only need to change this information once in the doctor table. The new room would automatically be assigned to every patient who was under the care of Dr Hyde.
 
Benefits of using a Relational Database
#
The advantages of using a relational database instead of a flat-file database are as follows:
  • Duplicated Data is reduced.
  • Database space is not wasted (due to unnecessary duplicated data.
  • Quicker to enter Data as there are less duplicates.
  • Quicker to update Data
Activity!

Click the above task and follow the instructions given on the sheet. Make sure that you complete all tasks on the sheet.