Databases
Z in Merch nt
a
a
Single table databases
A database is a structured collection of data that allows people to extract information in a
Zain Merchant
way that meets their needs. The data can include text, numbers, pictures; anything that
can be stored in a computer. Relational databases will be studied at A Level but for IGCSE
only single-table databases will be studied.
A single-table database contains only one table.
Why are databases useful?
Databases prevent problems occurring because:
• if any changes or additions are made it only has to be done once – data is consistent
• the same data is used by everyone
• data is only stored once in relational databases which means no data duplication.
What are databases used for?
To store information about people, for instance:
• patients in a hospital
• pupils at a school.
To store information about things, for instance:
• cars to be sold
• books in a library.
2
To store information about events, for instance:
Zain Merchant
• hotel bookings
• results of races.
Fields and records – the building blocks for any database
Inside a database, data is stored in tables, which consists of many records. Each record
consists of several elds. The number of records in a table will vary as new records can be
added and deleted from a table as required. The number of elds in a table is xed so
each record contains the same number of elds.
An easy way to remember this is: each record is a row in the table and each eld is a
column in the table.
Note: while databases can contain multiple tables, all the databases considered in this
chapter will contain a single table.
3
fi
fi
fi
fi
fi
A table contains data about one type of item or person or event, and will be
Zain Merchant
given a meaningful name, for example:
• a table of patients called PATIENT
• a table of books called BOOK
• a table of doctor’s appointments called APPOINTMENT.
Each record within a table contains data about a single item, person or event, for
example:
• Winnie Sing (a hospital patient)
• IGCSE Computer Science (a book)
• 15:45 on January 2020 (an appointment).
4
As every record contains the same number of elds, each eld in a record contains a
Zain Merchant
speci c piece of information about the single item, person or event stored in that record.
Each eld will have a meaningful name to identify the data stored in it, for example:
For a hospital patient the elds could include:
• The patient’s rst name eld called FirstName
• The patient’s family name eld called FamilyName
• The patient’s date of admission eld called DateOfAdmission
• The name of the patient’s consultant eld called Consultant
• The patient’s ward number eld called WardNumber
• The patient’s bed number eld called BedNumber, etc.
5
fi
fi
fi
fi
fi
fi
fi
fi
fi
fi
fi
fi
The PATIENT table structure could look like this:
Zain Merchant
For the table called BOOK the elds could include:
• Title of the book called Title
• Author of the book called Author
• ISBN, etc.
6
fi
V lid tion
Some validation checks will be automatically provided by the database management
Zain Merchant
software that is used to construct and maintain the database. Other validation checks
need to be set up by the database developer during the construction of the database.
The practical use of a database management system is strongly recommended
for all students. The database management software used is Microso Access 365 as
Microsoft Access.
For example, the DateOfAdmission eld will automatically be checked by the software
to make sure that any data input is a valid date before it can be stored in the PATIENT
table.
7
a
a
fi
ft
Basic data types
There are six basic data types that you need to be able to use in a database:
Zain Merchant
• text/alphanumeric
• character
• Boolean
• integer
• real
• date/time.
What is a data type?
Each eld will require a data type to be selected. A data type classi es how the data is
stored, displayed and the operations that can be performed on the stored value. For
example, a eld with an integer data type is stored and displayed as a whole number and
the value stored can be used in calculations.
8
fi
fi
fi
Primary keys
As each record within a table contains data about a single item, person, or event, it is
Zain Merchant
important to be able to uniquely identify this item. In order to reliably identify an item
from the data stored about it in a record there needs to be a eld that uniquely identi es
the item. This eld is called the primary key.
A eld that is a primary key must contain data values that are never repeated in the table.
The primary key can be a eld that is already used, provided it is unique, for example the
ISBN in the book table. The PATIENT table would need an extra eld for each record as
all of the existing elds could contain repeated data. To create a primary key, we could
add a new eld to each record, for example a unique number could be added to each
patient’s record. The extra eld is:
Primary key eld called HospitalNumber
9
fi
fi
fi
fi
fi
fi
fi
fi
fi
fi
SQL
Structured Query Language (SQL) is the standard query language for writing scripts to
Zain Merchant
obtain useful information from a database. We will be using SQL
to obtain information from single-table databases. This will provide a basic
understanding of how to obtain and display only the information required from a
database. SQL is pronounced as es-queue-el.
For example, somebody needing to visit a patient would only require the ward number
and the bed number of that patient in order to nd where they are in the hospital.
Whereas a consultant could need a list of the names of all the patients that they care for.
10
fi
SQL scripts
An SQL script is a list of SQL commands that perform a given task, often stored in a le
Zain Merchant
so the script can be reused.
In order to be able to understand SQL and identify the output from an SQL script, you
should have practical experience of writing SQL scripts. You can write scripts using SQL
commands in Access. There are many other applications that also allow you to do this –
MySQL and SQLite are freely available ones. When using any SQL application, it is
important that you check the commands available to use as these may di er slightly from
those listed in the syllabus and shown below.
You will need to be able to understand and identify the output from the following SQL
statements.
11
ff
fi
An SQL command:
Zain Merchant
Only the SELECT and FROM commands are mandatory in an SQL script. All other
commands are optional.
A SELECT statement takes the form:
SELECT Field1, Field2, Field3, etc. – this speci es the individual
elds (columns) to be shown.
SELECT * – this speci es that all elds (columns) are to be shown.
A FROM statement takes the form:
FROM TableName – this speci es the table to use.
A WHERE statement takes the form:
WHERE Condition – this speci es the condition to apply.
12
fi
fi
fi
fi
fi
fi
Zain Merchant
Conditions also require operators to compare values from elds.
13
fi
An ORDER BY statement takes the form:
Zain Merchant
ORDER BY Field1, Field2, etc. – this speci es a sort in ascending or
alphabetical order starting with the rst eld.
ORDER BY Field1, Field2 DESC – this speci es a sort in descending or reverse
alphabetical order starting with the rst eld.
A SUM statement takes the form:
SELECT SUM (Field) – this speci es the eld (column) for the calculation.
The eld should be integer or real. A COUNT statement takes the form:
SELECT COUNT (Field) – this speci es the eld (column) to count if the given criterium
is met.
14
fi
fi
fi
fi
fi
fi
fi
fi
fi
fi
fi
Ex mple
Using the single table database PATIENT you have created.
Zain Merchant
1. Write an SQL query to list all Mr Jones’ patients.
2. Write an SQL query to list all the patients not in ward 6.
3. Write an SQL query to list all the patients who arrived on 12/11/2022.
15
a
Pr ctic l use of d t b se
As an OL Computer Science student you need to be able to do the following:
Zain Merchant
• de ne a single-table database from given data storage requirements
• choose a suitable primary key for a database table
• read, complete and understand SQL scripts.
16
fi
a
a
a
a
a
a