9 Databases

DATABASE
Syllabus 2023-2025
34
Databases
Single databases
A database is a structural collection of data that allows people to extract information in a way
that meet their needs. The data can be include text, numbers, and pictures, anything that can
be stored in a computer.
A single-table database contain only one table.
Why are databases useful?
Databases prevent problems occurring because:
1. If any changes or addition are made it only has to be done once – data is consistent.
2. The same data is used by everyone
3. Data is only stored once in relational database which means no data duplication
What are databases used for?
To store information about people, for instance:
1. Patients in a hospital
2. Pupils at a school
To store information about things, for instance:
1. Cars to be sold
2. Books in a library
To store information about events, for instance:
1. hotel bookings
2. result of races
Fields and records – the building blocks for any databases
Inside a database, data is stored in tables, which consist of many records. Each record consist
of several fields. The number of record in a table will vary as new records can be added and
deleted from a table as required. The number of fields in a table is fixed so each record
contains the same number of fields.
An easy way to remember this is: each record is a row in the table and each field is a column
in the table.
35
A table contains data about one type of item or person or event, and will be given a
meaning name, for example:
1. a table of patients called PATIENT

2. a table of book called BOOK
3. a table of doctor’s appointment called APPOINMENT
Each record in a table contain data about a single item, person or event, for example:
1. Michael (a hospital patient)

2. IGCSE computer science (book)
3. 15:45 on January 2021 (an appointment)
As every record contain the same number of fields, each field in a record contains a specific
piece of information about the single item, person or event stored in that record. Field will have
a meaningful name to identify the data stored in it, for example:
1. For a hospital patient the fields could include:

*The patient’s first name field called first name
*the patient’s family name field called family name
*the patient’s data of admission field called DATE OF ADMISSION
*the name of the patient’s consultant field called consultant
*the patient’s ward number field called ward number
*the patient’s bed number field called bed number etc.
36
The patient’s table structure could look like this:
2. For the table called book the fields could include:

*title of the book called TITLE
*author of the book called AUTHOR
*ISBN,etc
Validation
Some validation checks will be automatically provided by the database management

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.
For example, the DATE OF ADDMISSION field 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.
37
Basic data types
There are six basic data types that you need to be able to use in a database:
1. Text/alphabetic
2. Character
3. Boolean
4. Integer
5. Real
6. Date/time
38
What is a data type?
Each field will require a data type to be select. A data type classifies how the data is stored,
displayed and the operation that can be performed on the stored value. For example, a field
with an integer data type is stored and displayed as a whole number and the value stored can
be used in calculation.
Primary keys
As each record within a table contain data about a single item, person, or event, it is important
to be able 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 field that uniquely identifies the item. This fields is
called the primary key.
A field that is a primary key must contain data values that are never repeated in the table.
The primary key can be a field that is already used, provided it is unique, for example the ISBN
in the book table. The patient table would need an extra field for each record as all of the
existing fields could contain repeated data. To create a primary key, we could add a new field
to each record, for example a unique number could be added to each patient’s record. The
extra field is: Primary key field called hospital number
SQL
Structed Query Language (SQL) is the standard quary language for writing scripts to obtain
useful information from a database. We will be using SQL to obtain useful 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 find where they are in the hospital. Whereas a consultant
could need a list of the names of all the patients that they care for.
SQL SCRIPTS
39
An SQL script is a list of SQL commands that perform a given task, often stored in a file so
the script can be reused.
You will need to be able to understand and identify the output from the following SQL
statement.
40
41
42

9 Databases

Uploaded by

Copyright:

Available Formats

9 Databases

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

9 Databases

Uploaded by

Copyright:

Available Formats

DATABASE

A single-table database contain only one table.

Why are databases useful?

Databases prevent problems occurring because:

What are databases used for?

To store information about people, for instance:

To store information about things, for instance:

To store information about events, for instance:

Fields and records – the building blocks for any databases

1. a table of patients called PATIENT

1. Michael (a hospital patient)

1. For a hospital patient the fields could include:

2. For the table called book the fields could include:

Some validation checks will be automatically provided by the database management

You might also like