Be able to use formulae and functions to perform calculations at run time including addition, subtraction, multiplication, division, sum, average, maximum, minimum, count

Topic 18 – Databases (Cambridge IGCSE ICT 0417)

Learning Objectives

By the end of this topic you should be able to:

• Recall the key concepts of database design and manipulation (AO1).
• Create tables, define fields, import/export data, sort, search and write simple queries (AO2).
• Use formulae and functions to perform calculations at run‑time, and evaluate the suitability of a database design (AO3).

1. Designing a Database Structure

1.1 Tables and Fields

• Table – a collection of related records (rows).
• Field – a column that stores a single type of data for each record.

1.2 Choosing Data Types

1.3 Primary Keys, Composite Keys & Foreign Keys

• Primary key (PK) – a field (or combination of fields) that uniquely identifies each record; it must be unique and not null.
• Composite primary key – when two or more fields together form a unique identifier (e.g., OrderID + ProductID in an Order‑Details table).
• Foreign key (FK) – a field that references the primary key of another table, establishing a relationship.
• Referential integrity – the DBMS prevents orphan records by ensuring that every FK value matches an existing PK value.

1.4 Naming‑Convention Checklist

• Use a single word or camel‑case (e.g., CustomerID, OrderDate).
• Avoid spaces and special characters.
• Prefix ID fields with the table name (StudentID, CourseID).
• Keep names short but meaningful.

1.5 Relationships, Referential Integrity & Normalisation (1NF)

• One‑to‑many – e.g. one Customer can have many Orders.
• Many‑to‑many – resolved by a junction table that contains two foreign keys.
• Referential integrity rules – cascade update/delete, restrict delete, etc.
• First Normal Form (1NF) – each field contains atomic values; no repeating groups or multiple values in a single field.

2. Manipulating Data

2.1 Import / Export

• Common formats: .csv, .txt, .xlsx.
• Use the DBMS “Import Wizard” to map each column to the correct field type.
• Export is useful for creating evidence files for the exam (e.g., sales.csv).

2.2 Sorting

• Single‑field sort: ORDER BY UnitPrice ASC.
• Multi‑field sort: first by Category, then by Quantity DESC.

2.3 Searching & Query Design

• Record‑level filtering – enter criteria in the “Criteria” row of a query design grid (e.g., > 100 to show sales above $100).
• Operators: =, <>, >, <, >=, <=, LIKE, NOT, AND, OR.
• Wild‑cards: * (any number of characters), ? (single character).
• Full SELECT‑FROM‑WHERE syntax (useful for exam questions):

  SELECT CustomerName, OrderDate, Total
  FROM Orders
  WHERE Total > 100 AND OrderDate BETWEEN #01/01/2024# AND #31/12/2024#;

• Queries can be saved as views for reuse in forms, reports or other queries.

2.4 Calculated (Formula) Fields

A calculated field stores an expression that is evaluated each time the record is displayed. The result is not saved permanently.

TotalPrice: [Quantity] * [UnitPrice]

In SQL‑like syntax the same calculation could be written as:

SELECT Quantity, UnitPrice, Quantity * UnitPrice AS TotalPrice
FROM Sales;

3. Performing Calculations at Run‑time

3.1 Arithmetic Operators

3.2 Aggregate Functions

Aggregate functions work on a set of records and return a single value.

3.3 Date‑Time Functions (selected)

• TODAY() – returns the current date.
• NOW() – returns the current date and time.
• DATEDIFF('d', StartDate, EndDate) – number of days between two dates.
• Example: DATEDIFF('d', OrderDate, TODAY()) gives the age of an order in days.

3.4 Text Functions (selected)

• LEFT(Field, n) – first n characters.
• RIGHT(Field, n) – last n characters.
• LEN(Field) – length of the text.
• CONCAT(Field1, Field2) or Field1 & Field2 – join two strings.
• Example: CONCAT(FirstName, " ", LastName) creates a full name field.

3.5 Combining Arithmetic with Aggregate Functions

You can embed arithmetic inside an aggregate function, e.g.:

SELECT SUM(Quantity * UnitPrice) AS GrandTotal
FROM Sales;

This returns the same $100 as SUM(Total) in the example below.

3.6 Example – Sales Table

3.7 Using Functions on the Sales Table

1. Total sales value: SUM(Total) → 100
2. Average unit price: AVG(UnitPrice) → 3.00
3. Highest transaction total: MAX(Total) → 25
4. Lowest transaction total: MIN(Total) → 16
5. Number of transactions: COUNT(TransactionID) → 5

3.8 Common Pitfalls

• Division by zero – test the divisor before using /.
• Data‑type mismatches – arithmetic requires numeric fields; text will cause errors.
• NULL values – most aggregate functions ignore NULL, but any arithmetic expression that includes NULL returns NULL.

4. Presenting Data – Reports

4.1 Report Wizard Overview

1. Select the source (table or saved query).
2. Choose the fields to display.

3. Pick a layout (columnar, tabular, justified).
4. Add a Report Header (title, date, author) and a Page Footer (page number, total records).
5. Finish and switch to Design view for fine‑tuning.

4.2 Formatting Essentials

• Set numeric fields to Currency or Number with two decimal places.
• Align text left, numbers right, headings centre.
• Use bold or colour to highlight totals.

4.3 Grouping & Summary Fields

• Group records by a field (e.g., Category) to produce a separate section for each group.
• Add a Group Footer with summary functions such as SUM([Total]) to show a subtotal for each group.

4.4 Page Layout Tips

• Portrait orientation works for most tables.
• Switch to Landscape when the report contains many columns or wide fields.
• Check “Print Preview” to avoid truncated columns.

4.5 Exporting

Use the DBMS “Export” feature to create a PDF or XPS file – essential for exam evidence.

5. Evaluation & Analysis (AO3)

During the evaluation stage of the systems life‑cycle, consider the following checklist.

• Field‑type suitability – Are numeric values stored as numbers? Are dates stored in a date field?
• Redundancy – Does any information repeat in more than one table? If so, normalise.
• Primary key choice – Is the key unique, stable and as short as possible?
• Composite keys & referential integrity – Are junction tables using composite PKs? Are cascade rules appropriate?
• Relationship design – Are one‑to‑many relationships correctly implemented with foreign keys?
• Usability – Are field names clear? Are drop‑down lists or look‑ups used where appropriate?
• Security – Are passwords required? Is sensitive data encrypted or stored in a protected table?
• Performance – Do queries run quickly? Could indexes improve speed?
• Naming conventions – Do field and table names follow the checklist in 1.4?

Write a short paragraph (≈80 words) evaluating a given design and suggest at least two improvements.

6. Safety & Security (e‑Safety)

• Use strong passwords for the database file and change them regularly.
• Back up the database to an external drive or cloud service at least once a week.
• Beware of phishing emails that ask for database passwords.
• Enable the DBMS’s built‑in encryption if confidential data are stored.
• Limit user permissions – give read‑only access where editing is not required.

7. Audience & Communication

• Documentation – Write a brief user guide for the intended audience (teacher, manager, peer). Include screenshots of forms, queries and reports.
• Copyright – Use only data that you have created or that are clearly marked as free‑to‑use. Cite any external sources.
• Email etiquette – When sending database files, describe the purpose, file size, and any required passwords.

8. File Management

9. Linking to Other Syllabus Components

The same data can be used in other parts of the ICT syllabus. For example, a database can serve as the data source for a simple website (Section 21 – Website Authoring) using a server‑side script or by exporting the data to CSV and importing it into a web‑page table.

10. Quick Reference Cheat‑Sheet