CHAPTER 9 DATABASE
SINGLE TABLE DATABASE
A database is a structured collection of data. It allows people to organise, extracts specific
information in a ways that meets their needs. The data can include text, numbers, pictures;
anything that can be stored in a computer.
We also use Relational Databases where two or more tables are linked together. This
through will be studied at A level or IB, for IGCSE only single-table databases are linked
together. This through will be studied at A Level or IB, but for IGCSE only single databases
are need to be studied.
A single-table database contains only one table of information.
The two types
of database:
Relational
database
Flat-file
database
Why are databases useful?
Because they can store lots of information e.g records of how many students are
registered in a school or how many different items are sold in a shop.
Data is usually collected through a form on a computer. If any changes or addition are
made it only has to be done once- data is consistent.
The same data can be viewed any used by everyone who has access, only one file is
needed.
The same data can be viewed and used by everyone who has access, only one file is
needed.
Data is only stored in a database which means no duplication of information.
What are databases used for?
To store information about people:
Patients in a hospital
Pupils at a school
To store information about things
cars to be sold
Books in a library
To store information about events
Hotel bookings
Results of races
1
DATABASE STRUCTURE
A database is organized using a set of key components. These include:
Entities - each record item
Attributes - details about the entity
Field - columns used to capture attributes
Record - one row of details about an entity
Table - a set of fields and records
Primary key - unique number for an entity
This is another example table of a flat-file( single table) database. The entities are students
and the attributes are details about the students.
VALIDATION AND VERIFICATION
Validation - this is checking that the correct type of data is entered
Verification - this checks that the data is actually the data you want.
For example, validation checks that a postcode has been entered in the correct
format( depending on the country, the right combination of letters and number) and
verification checks the postcode being entered is the correct database.
It is good to do verification checks with two people
entering the same data. A verification check will see if both
sets are the same. If there are differences, the check will
bring up an error message and ask for the data to be re-
entered.
ACTIVITY:
State what field would you expect to find
in each record for the doctor’s
appointment and given field a suitable
name.
Note; filed names should be single word,
which should not contain any spaces, for
example :
’’ BedNumber’’.
2
DATA TYPES AND PRIMARY KEYS
THE BASIC TYPES OF DATA
There are 6 basic data types that can be assigned to a database.
Here is an example of a database table with rows and columns with different data types in
each.
PRIMARY KEY
Here is an example of a database tables with rows and columns with different data types in
each.
Each record
within a table
contains data
about a person,
a single item, or an event, and it is important to be able to uniquely identify
it, especially when you have a very large database and you need to find
something quick, e.g a car parts database.
For this reason we add another field to the database that uniquely identifies
the item in the row. This field is called primary key. This field must contain data values
that are never repeated in the table, e.g. a unique ID Number.
3
KEY DATABASE TERMS;
Data - information within database
Table - a structure in which data is stored
Flat - file database- a single table database
Record - a collection of data about a single item
Field - one piece of data
Primary key - a unique identifier for each record
Index - a list of values or items
Query - a method of searching a database
Range - start/ End
Presence - checks if the data is entered in a specific filed or not
Validation - this is checking that the correct type of data is entered
Verification - this checks that the data is actually the data you want
SEQUENCE QUERY LANGUAGE ( SLQ) used to
query data stored in a single table database.
STRUCTURE QUERY LANGUAGE( SQL)
SQL is a types of query language for writing
scripts to obtain useful information from a
database.
We will use some of the SQL statements to
extract information from databases. This will
provide basic understanding of how to obtain
and display only information from a database.
For example, somebody needing to visit a patient would only require word number and the
bed number of the patient in order to find where they are in the hospital. Whereas a
consultant need a list of the names of all patients that they care of.
QL SCRIPTS
An SQL script is a list of SQL commands that perform a given task, often stored in a file so
the script can be re-used.
WHAT IS SQL?
Databases use their own type of programming language. This language is known as
structured language, or SQL.
4
Data can be retrieved using the commands, SELECT, FROM and WHERE, for example:
SELECT * FROM ‘’ doctordatabase’’ where ‘’ Allergies’’ = ‘’Yes’’
Note- *stands for wildcard, which means all records.
SQL CODE TO BUILD A DATABASE
5
6
EXAM STYLE QUESTION
A motorcycles manufacture offers various combinations of;
>> seat colour >> engine size >> paint colours
A tables, MyMotobike was set up as single-table data base to help customers which seat
and paint combination were possible.
Question no 1
1. State the number of record shown in the table
6
2. State the number of fields shown in the table.
9
3. State, with reason, if any of the field are suitable to use as primary key.
I would chose the CODE field because the information in each cell is unique.
Question no2
The following SQL command was used:
SELECT code
FROM MyMotobike
WHERE Sea Colour= ‘’ White’’
AND ( 50cc OR Blue)
Show what will be displayed
MK203
MK204
Question 3
A customer wanted to know all possible combinations for a motorcycle with brown seats
and either black paint colour.
Complete the SQL command required.
SELECT*
FROM MyMotobike
WHERE Seat Colour = ‘Brown’
AND( ‘ Black ‘ OR ‘ Blue ‘ )
7
8
