Back to Curriculum

9. Databases

SECTION 2: ALGORITHMS, PROGRAMMING AND LOGIC

Important Note

These notes cover the main points for revision. They are great for reviewing key concepts, but for in-depth understanding, always keep your textbook nearby for reference.

💡 Exam Tip: Try not to use * in SQL queries. In IGCSE exams, they usually ask for specific columns, so always mention them. For example, instead ofSELECT * FROM Students;, useSELECT First_Name, Last_Name, Class FROM Students;

Databases are structured collections of data that allow efficient storage, retrieval, and manipulation of information. This chapter covers database structure, data types, primary keys, SQL queries, and validation techniques.

Table of Contents

9.1 Database Fundamentals

9.1.1 Introduction to Databases

A database is a structured collection of data that allows efficient storage, retrieval, and manipulation of information. It is a structured collection of data that allows people to extract information in a way that meets their needs.

  • • It can store various types of data such as text, numbers, pictures, dates, and Boolean values
  • • Single-table databases (also called flat-file databases) contain only one table
  • • Relational databases (covered at A-Level) consist of multiple interconnected tables

9.1.2 Importance of Databases

Databases prevent problems such as:

Data Inconsistency

Changes need to be made only once

Data Duplication

Data is stored efficiently in relational databases

Easy Retrieval

Users can extract information in an organized way

9.1.3 Uses of Databases

Databases are used to store information for:

People

E.g., hospital patients, school students

Things

E.g., books in a library, cars for sale

Events

E.g., hotel bookings, race results

9.1.4 Structure of a Database

Table/Relation

The fundamental unit of a database storing data in rows and columns

Record

A row in a table, representing one item or person

Note: Records/Rows/Tuples are all interchangeable. In exams, if not mentioned, use Records (Horizontal)

Field

A column in a table, representing a specific attribute (e.g., Name, Date of Birth)

Note: Fields/Columns/Attributes are all interchangeable. In exams, if not mentioned, use Fields (Vertical)

Key Terminology

FIELDS/COLUMNS/ATTRIBUTES are all interchangeable

  • • In exam if not mentioned, use Fields
  • • Vertical

Records/Rows/Tuples are all interchangeable

  • • In exam if not mentioned, use Records
  • • Horizontal

Example Table:

FirstNameLastNameDateOfBirthEmail
JohnDoe12/01/1995john@example.com

Validation in Databases

Validation is the process of ensuring that data entered into a database is correct, sensible, and usefulbefore it is processed or stored. It helps reduce errors but does not guarantee that the data is 100% accurate.

See the Validation section below for detailed validation types and examples.

9.1.5 Data Types

A data type classifies how the data is stored, displayed and the operations that can be performed on the stored value. Each field in a database must have a data type:

Data TypeDescriptionAccess Data Type
Text/AlphanumericStores text, names, or descriptionsShort Text/Long Text
CharacterStores a single character (e.g., 'M' or 'F' for gender)-
BooleanStores True/False or Yes/No valuesYes/No
IntegerStores whole numbersNumber, Large Number
RealStores decimal numbers-
Date/TimeStores date and time valuesDate/Time

9.1.6 Primary Keys

A Primary Key is a field in database which uniquely identifies each record.

  • • Ensures no two records have the same value in that field

Examples:

  • • Student ID in a school database
  • • ISBN for books in a library database
  • • Patient Number in a hospital system

Key Points: A single database table contains one primary key. Each table consists of many records. Every record has the same number of fields. Every field is given a data type. Examples of data types are text, char, and Boolean. Some fields will have validation rules.

Database Structure Summary

A single database table contains one primary key.
Each table consists of many records. 
Every record has the same number of fields.
Every field is given a data type.
Examples of data types are text, char, and Boolean.
Some fields will have validation rules.

9.2 SQL and Validation

9.2.1 SQL (Structured Query Language)

SQL is standard query language used to query and manipulate data in databases.

9.2.2 SELECT Commands

Retrieving Data

Filtering Data (WHERE Clause)

Not Equal
Date Comparison

Sorting Data (ORDER BY)

Ascending Order (Default)

💡 Note: By default, ORDER BY is ascending

Descending Order
Multiple Columns

Counting Records

Aggregate Functions

COUNT()

Counts the number of non-null values in a column

AVG()

Calculates the average of a numeric column

SUM(), MIN(), MAX()

SUM: Returns the sum of values. MIN: Finds minimum value. MAX: Finds maximum value

NULL Values

Check for NULL
Check for NOT NULL

Show Unique Values (DISTINCT)

AND / OR Conditions

AND Condition
Multiple Conditions with AND

LIKE Clause (Pattern Matching)

Names starting with "am"
Names ending with "a"
Names containing "a" anywhere
"a" in the second position
"a" in the third-last position
Starts with "a" and ends with "b"

IN Clause

Include specific values
Exclude specific values

BETWEEN Clause

Using >= and <= Operators
Using BETWEEN for a Range

💡 Note: In the above, 3000 and 5000 are inclusive

Excluding a Range

9.2.3 INSERT, UPDATE, DELETE

Inserting Data

Updating Data

Deleting Data

9.2.4 Validation in Databases

Validation is the process of ensuring that data entered into a database is correct, sensible, and usefulbefore it is processed or stored. It helps reduce errors but does not guarantee that the data is 100% accurate.

Validation vs Verification: Validation ensures the data is reasonable and follows the correct format. Verification ensures the data is correct (e.g., checking spelling manually).

Types of Validation Checks

1. Presence Check

Ensures that a required field is not left blank

Example: A student database must have a Student ID; it cannot be left empty

2. Length Check

Ensures that data entered does not exceed a certain number of characters

Example: A password must be at least 8 characters long

3. Format Check

Ensures data follows a specific pattern

Example: An email must be in the format username@example.com

4. Range Check

Ensures numeric data falls within a set range

Example: A student's age must be between 5 and 100

SQL Example:

5. Type Check

Ensures data is of the correct type (e.g., number, text, date)

Example: A phone number should contain only digits

6. Lookup Check

Ensures data entered matches predefined values

Example: A gender field may allow only "Male" or "Female"

7. Check Digit

A special digit added to numeric data to detect errors

Example: Barcode numbers use a check digit to ensure they are entered correctly

8. Input Mask

Forces users to enter data in a specific format

Example: A date of birth must be entered as DD/MM/YYYY

Example: Student Database Validation

StudentID: Presence Check, Length Check, Format Check

Date of Birth: Format Check, Range Check

Date of Entry: Format Check, Range Check

Current Class: Format Check

Current School Year: Range Check, Format Check

Email Address: Format Check

9.3 Exam Practice

Practice Questions

1. Define a single-table database.

2. Why is a primary key necessary in a database?

3. What are the differences between Boolean, Integer, and Real data types?

4. Write an SQL query to select all students aged 18 or above.

5. Explain the purpose of validation rules in a database.

💡 Exam Tip: Counting Fields and Records

Don't count the columns! Fields are vertical (columns), Records are horizontal (rows). For example: Fields: 6, Records: 23

Example SQL Queries

Query 1: Order by Family Name
Query 2: Order by Class and Family Name
Query 3: Filter by School Year and Order by Class
Query 4: Count Records with Condition
Query 5: Filter by Consultant
Query 6: Filter by Ward Number (Not Equal)
Query 7: Filter by Date of Admission
Query 8: Date Range Filter
Query 9: Count Records (Not Equal)

Verification vs Validation Examples

Example: Student Database Fields

Family Name: Verification (checking spelling manually)

Other Name: Verification (checking spelling manually)

StudentID: Verification and Validation (format check, presence check)

Date of Birth: Validation (format check, range check)

DateOfEntryToSchool: Validation (format check, range check)

Current Class: Validation (format check)

Current School Year: Validation (range check, format check)

Email Address: Verification and Validation (format check)

Validation Rules for Student Database

StudentID: Presence Check, Length Check, Format Check

Date of Birth: Format Check, Range Check

Date of Entry: Format Check, Range Check

Current Class: Format Check

Current School Year: Range Check, Format Check

Email Address: Format Check

Example: StudentID Format

Format: 4 Characters Followed by 3 Digits

Example: ABCD123

Counting Fields and Records

💡 Important: Don't count the COLUMNS! Fields are vertical (columns), Records are horizontal (rows).

Example: Fields: 6, Records: 23

Key Points:
  • • Fields/Columns/Attributes are vertical
  • • Records/Rows/Tuples are horizontal
  • • In exams, if not mentioned, use "Fields" for columns and "Records" for rows

Using SUM Function

💡 Note: The number of cans can be different for each type of soft drink – that's why the SUM function is better suited than COUNT.

Example:
Get in Touch
CodeHaven - Master Computer Science