The ICT Lounge
 
Section 5.3:
Database Structures
 

This section focuses on databases. You will see examples of databases and understand their purpose.

We will discuss terminologies (words) associated with databases and also the different ways in which they are structured.

You will also take a look at the different ways in which databases organise data to make it easier to search through.

Key Concepts of this section:
#

#

#
Understand database key-words including record, file, table, field, data and key-field.
Understand the ways in which databases are organised and structured.
Understand how databases can be sorted in both ascending and descending order.
What is a Database?
Key Words:
Computer Data, Stored, Organised
#

#

A database is store of data that has been organised (sorted) in some way.

Databases of data are held on a computer.

Example of a database with stored data.
The database below shows you an example of stored medical patients data:
NOTE!
The database has been sorted into order of Patient Id Number.
Breakdown of the medical patients database.  
#

#

Medical Centres, Doctors Surgeries and Hospitals hold lots of data about patients.

The image below shows you examples of the data they might hold. Notice how the information stored about each patient is similar (Name, Gender, D.o.B etc).

REMEMBER!
Databases can store data in an organised way. This makes searching, updating and outputting the data very easy.
Database Structures (Files/Tables, Records, Fields, Data)
Key Words:
Database Structure, File, Table, Record, Field, Data
#

#


#
Databases are so useful because they store data in a structured way.

When data is structured (organised) it can be manipulated easily and then output in different. ways

Databases consist of the following:
1. Files / Tables
2. Records
3. Fields
4. Data
Example of the structure of a database.
The image below shows how a database is structured into Files/Tables, Records, Fields and Data:
NOTE!
# Files / Tables hold Records.
# Records consist of Fields.
# Fields hold Data.

Files/Tables, Records, Fields and Data are explained in more detail below
Files/Tables

#


#


#


A collection of records and fields is called a file.


Database files are viewed as tables. The medical patients database is holding its data in a table. Lets have another look at it.

The patients table is made up of 8 columns and 5 rows. Column headings are known as fields and one complete row is a full record of data.


NOTE!
Tables allow you to see all of the data at once rather than individually.
NOTE!
Tables are made up of columns and rows.

In the example to the left the green box shows a column and the red box shows a row.

Database Records

#


One single row of a database table represents ONE Record:

 

For example:- I have highted in red, record number 2 in our patient database:

NOTE!
In this particular example there are 5 records.
 
What is a record?
#


A database record shows all the data about one person or object.

For example:- the image above shows the entire record for the patient named David.

NOTE!
The headings for each piece of information (Name, Gender etc) are known as fields.
 
The record contains the following information:
#
The records for the other patients are shown below.
Record 1
Record 3
Record 4
Record 5
NOTE!
Each record has the same data structure (Id, Name, Gender etc).

The only things that change are the individual pieces of information (Actual Names, Actual Genders etc)


Database Fields

#

#


Each cell of a table indicates a single field. A single field can hold one piece of data.

 

Fields are given field names so we know what kind of data they hold. Field names are found at the top of each column.

 

For example:- I have highlighted in green all of the field names in the patient database and in red a single field holding individual data:

NOTE!
In this particular example fields include Name, D.o.B, Gender, Doctor etc
.
 
#
Fields are made up of 2 parts:-
Part 1: The Field Part 2: The Field Name
What is a field?

#

Database fields hold/store the single items of data that make up a record.

 
For example:- each of our records consist of 8 fields:
1. Patient Id
2. Name
3. D.o.B
4. Gender
5. Phone
6. Doctor Id
7. Doctor
8. Room
What is a field name?
#
Fields names are used to describe the data that is to be stored inside
the field.
For example:- D.o.B: is the field name used to describe the Date of Birth field. It helps the user decide what data to enter.
Database Data
What is data?
#
Data is the individual pieces of information that get stored in a field.
REMEMBER!
The data that is stored inside fields can change from record to record.

For example: The names 'David', 'Lisa', 'Frank', 'Jeff' and 'Rachel' can all be stored in the Name Field.

 
For example:
In the table below I have listed each field in our patient database and an example of data that could be stored inside it (E.g. 'David' is stored in the 'Name' field):
Fields, Field Names and Data example
#
The image to the right shows you a working example of fields, field names and data being used on an actual database:
   

Sorting Data (organising)
Key Words:
Ascending, Descending
#


As mentioned before, one of the uses of storing data on a database is that they allow data to be sorted very easily.

Data can be sorted in two ways:
  # 1. Ascending sorting
# 2. Descending sorting
Ascending Sorting (lowest to highest)
#
Sorting data in 'ascending order' means to start with the lowest value and move through it in order until you reach the highest value.

Think of these 3 examples:
# 1. Text data would be sorted from A to Z
# 2. Number data would be sorted from 0 - whatever (or even negative numbers)
# 3. Dates would be sorted from the earliest dates to the most recent.

#
Take a look at our patients database again. At the moment it is sorted in Ascending Order of the Patient Id field:
NOTE!
Only Patient Id is in order. None of the other fields are sorted at all.
Descending Sorting (highest to lowest)
#
Sorting data in 'descending order' means to start with the highest value and move through it in order until you reach the lowest value.

Think of these 3 examples:
# 1. Text data would be sorted from Z to A
# 2. Number data would be sorted from whatever - 0
# 3. Dates would be sorted from the most recent date to the earliest.

#
Lets sort the patient database in Descending Order of the Patient Id field:
NOTE!
Only Patient Id is in order. None of the other fields are sorted at all.
How to remember the difference between ascending and descending.
1.

2.

Climbers who 'ascend' a mountain start from the bottom and climb to the top.

Climbers who 'descend' a mountain start at the top and climb to the bottom.

Key Fields (unique data)
Key Words:
Key Field , Unique, Non-Repeating.
#

#
Key fields are individual pieces of data that are unique (only appear once).

Key fields are used to differentiate one record from another.



For example:-
In our patients database the key field is the Patient Id. Every patient has a different Id Number. This is vital so we can identify each patient uniquely and correctly. Imagine if the doctor mixed two patients up and gave them the wrong medicine!!

The Patient Id Number Field contains non-repeating data that identifies individual records. We call this the Key Field.

Why is Patient Id suitable for the Key Field?

#

Only the Patient Id is suitable for use as the unique key field. Remember the key field MUST contain only data that CAN NEVER REPEAT. Look at the table below and think about it:

Name
Two patients can have the same name.
D.o.B
Two patients can have the same birthday (Like Rachel and David in our table).
Gender
Two patients can be the same gender.
Phone
Although the phone numbers are all unique, phone numbers can be changed or even swapped. This makes them unsuitable for key fields.
Doctor Id
The doctor Id can not be used to uniquely identify a patient..... just each doctor.
Doctor
Doctor's names cannot be used to identify each patient as two or more patients can be assigned the same doctor (like in our table).
Room
Multiple patients are sent to the same room. This is not a good key field.
Patient Id
Each patient is given a different Id number which they keep for life. It is used to identify their medical history and any medicines they need.
Activity!

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