CHAPTER 9: DATABASES

SYLLABUS FOR CHAPTER 9

(1) Define a single-table database from given data storage requirements

• Including:

➢ Fields
➢ Records
➢ validation

(2) Suggest suitable basic data types

• Including:

➢ text/alphanumeric
➢ character
➢ Boolean
➢ Integer
➢ Real
➢ date/time

(3) Understand the purpose of a primary key and identify a suitable primary key for a given database table

(4) Read, understand and complete structured query language (SQL) scripts to query data stored in a single
database table

• Limited to:

➢ SELECT
➢ FROM
➢ WHERE
➢ ORDER BY DESCENDING
➢ ORDER BY ASCENDING
➢ SUM
➢ COUNT
➢ AND
➢ OR

• Identifying the output given by an SQL statement that will query the given contents of a database table
 9: DATABASES

A database is a well-organized compilation of data that enables individuals to retrieve information according to
their specific requirements. The data contained within a database can encompass various forms such as text,
numerical values, images, or any other type of digital content that can be stored on a computer system.

9.1: WHY DO WE NEED A DATABASE?

❖ To store data about people, things, and events.

❖ Any modifications or additions need to be made only once, ensuring data consistency.

❖ All users access and utilize the same set of data, promoting uniformity.

❖ Relational databases store data in a non-repetitive manner, eliminating duplication.

9.2: WHAT MAKES A DATABASE?

❖ Data is stored in tables in databases. Each table consists of a specific type of data e.g. cars. These tables
HAVE to be named according to what they contain e.g. a table containing patient information will be
PATIENT

❖ These tables consist of records (rows). Each record consists of data about a single entity (a single item, person
or event ) e.g. a single car

❖ These tables also have columns that are known as fields. These consist of specific information regarding the
entities that are written later in records e.g. car name, car manufacturer etc.

Note: In this chapter, skills of dealing with a database are also required so working with Microsoft Access
is needed to understand this chapter better. You have to be able to define a single-table database from given
data storage requirements, choose a suitable primary key for a database table and also be able to read, complete
and understand SQL scripts.
 9.3: VALIDATION IN DATABASES

❖ Database management software automatically provides some validation checks, while others need to be set
up by the developer during construction.

❖ For example; The software automatically validates fields like "DateOfAdmission" in the PATIENT table to
ensure data input is a valid date.

9.4: BASIC DATA TYPES

Each field will require a data type to be selected. A data type classifies how the data is stored, displayed and the
operations that can be performed on the stored value.

The datatypes for databases are quite similar to original datatypes, however, there are a few differences.

DATA TYPE DESCRIPTION ACCESS DATA TYPE

Text / Alphanumeric A number of characters Short text / long text

Character A single character Short text with a field size of one
Boolean One of two values: either True Yes / No
or False, 1 or 0, Yes or No
Integer Whole number Number formatted as fixed with zero decimal places
Real A decimal number Number formatted as decimal
Date / Time Date and / or time Date / Time

Note: Access datatype refers to the software Microsoft Access which is a DBMS (Database Management
System). Here, databases could be worked upon in practical form

9.5: PRIMARY KEY

❖ Each record in a table represents a unique item, person, or event.

❖ To ensure reliable identification of these items, a field called the primary key is necessary.

❖ The primary key is a unique field that distinguishes each item within the data.

❖ In order to serve as a primary key, a field must have values that are never repeated within the table.

❖ An existing field can serve as a primary key if it is unique, such as the ISBN in the book table.

❖ In cases where all existing fields may contain repeated data, an additional field, such as "HospitalNumber,"
can be added to each record to serve as the primary key.
 9.6: STRUCTURED QUERY LANGUAGE - SQL

❖ Structured Query Language (SQL) is the standard language for writing scripts to retrieve valuable information
from databases.

❖ By using SQL, we can learn how to retrieve and display specific information needed from a database.

❖ For instance, someone visiting a patient may only require the ward number and bed number to locate them in
the hospital, while a consultant may need a list of the names of all the patients under their care. This can be
done using SQL

SQL Scripts

❖ An SQL script is a collection of SQL commands that are used to perform a specific task, often stored in a file
for reusability.

❖ To comprehend SQL and interpret the output of an SQL script, practical experience in writing SQL scripts is
necessary.

SQL QUERY DESCRIPTION OF STATEMENT

STATEMENT

SELECT Fetches specified fields (columns) from a table; queries always begin with SELECT
FROM Identify the table to use.
WHERE Includes only records (rows) in a query that matches a given condition.
ORDER BY Sorts the results from a query by a given column either alphabetically or numerically.
SUM Returns the sum of all the values in a field (column). Used with SELECT
COUNT Counts the number of records (rows) where the field (column) matches a specified condition. Used with SELECT

Select Statements:

SELECT (fieldsname)
FROM (tablesname)
WHERE (condition)
ORDER BY (sortingcondition) ;

Selecting Sum OF Values In A Table:

SELECT SUM ( fieldsname )

FROM (tablesname)
WHERE (condition)
ORDER BY (sortingcondition) ;
 Counting The Number OF Records Where The Field Matches A Specified Condition:

SELECT COUNT ( fieldsname )

FROM (tablesname)
WHERE (condition)
ORDER BY (sortingcondition) ;

==ORDER BY Field1, Field2, etc. – this specifies a sort in ascending or alphabetical order starting with the
first field.==

==ORDER BY Field1, Field2 DESC – this specifies a sort in descending or reverse alphabetical order starting
with the first field.==

Note: ORDER BY is not necessary to add. It has to be only added if required!

Conditions often include values from fields, the values need to be stated in a form that matches the data type for
the field.

FIELD EXAMPLE GENERAL NOTES ACCESS NOTES

TYPE VALUE

Text ‘Mr Smith’ Text field values should be enclosed with single Double quotation marks can also be used.
quotation marks.

character ‘M’ Character field values should be enclosed with Double quotation marks can also be used.
single quotation marks.

Boolean TRUE Boolean can be TRUE or FALSE Data type is Yes/No

Integer 12 Integer field values should be whole numbers. Allows integer or decimal values.

Real 12.01 Real field values should be decimal numbers. Allows integer or decimal values.

Date/time ‘22/11/2022’ Date/time field values should be enclosed in Date/time field values must be enclosed in
single quotation marks. hashes (#).
 9.7: OPERATORS

Just like pseudocode, the operators used there can also be used here for conditions, however, a few more
are also used in databases

OPERATOR DESCRIPTION

= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
BETWEEN (×) Between a range of two values
LIKE (×) Search for a pattern
IN (×) Specify multiple values
AND Specify multiple conditions that must all be true
OR Specify multiple conditions where one or more conditions must be true
NOT Specify a condition that must be false