Be able to perform searches using wildcards

18 Databases – Overview

This section follows the Cambridge IGCSE/A‑Level syllabus (Section 18) and is divided into three parts:

• 18.1 Create a database structure – tables, fields, data‑types, keys, relationships, normalisation, and simple form design.
• 18.2 Manipulate data – sorting, searching, calculations, query operators and the use of wild‑cards in SQL and Access.
• 18.3 Present data – reports, headings/footers, formatting, pagination and exporting.

18.1 Create a Database Structure

Tables, Fields and Data‑Types

Primary and Foreign Keys

• Primary key: a field (or combination of fields) that uniquely identifies each record. Must be unique and not null.
• Foreign key: a field in one table that references the primary key of another table, establishing a relationship.

Database Relationships

• One‑to‑One: each record in Table A matches at most one record in Table B. Rarely needed; often implemented by sharing the same primary key.
• One‑to‑Many: a single “parent” record relates to many “child” records. This is the most common relationship.
• Many‑to‑Many: records in Table A can relate to many records in Table B and vice‑versa. Implemented by a junction (link) table containing two foreign keys.

Diagram description (use in notes): Draw three boxes labelled Students, Enrollments, Courses. Connect Students.StudentID → Enrollments.StudentID (one‑to‑many) and Courses.CourseID → Enrollments.CourseID (one‑to‑many). The Enrollments table is the junction table for the many‑to‑many relationship between Students and Courses.

• Referential integrity: Enforced by the DBMS so that a foreign‑key value must exist as a primary‑key value in the related table, preventing orphan records.

Basic Normalisation (1NF)

First Normal Form requires that:

• Each field contains atomic (indivisible) values.
• Each record is unique.

Example of a non‑1NF table (single table Orders):

OrderID | CustomerName | Product1 | Product2 | Product3
----------------------------------------------------
1       | Alice        | Pen      | Notebook | (null)
2       | Bob          | Pencil   | (null)   | (null)

To achieve 1NF, split the repeating product fields into a separate table:

Orders
-------
OrderID | CustomerName
1       | Alice
2       | Bob
OrderDetails
------------
OrderID | Product
1       | Pen
1       | Notebook
2       | Pencil

Simple Form Design – Checklist

• Labels next to every input control.
• Tab order set so the cursor moves logically (top‑to‑bottom, left‑to‑right).
• Grouping of related fields using section headers or boxed frames (e.g., “Personal Details”).
• Control types:

  • Text box – free‑form text.
  • Combo box – drop‑down list for limited choices.
  • Check box – Yes/No (Boolean) values.
  • Option group – mutually exclusive choices (radio buttons).

• Captions should be clear and concise.
• Control Source must be linked to the appropriate table field.
• Required fields can be indicated with an asterisk (*) and validated.

18.2 Manipulate Data

Sorting and Filtering

• ORDER BY clause in SQL (e.g., ORDER BY LastName ASC, FirstName DESC).
• In Access query design, set the Sort row to “Ascending” or “Descending”.
• Filter records with a WHERE clause and logical operators (AND, OR, NOT).

Comparison & Logical Operators

Query Criteria Syntax (AO 18.2)

In the Criteria row of the Access query‑design grid (or the WHERE clause in SQL) you write expressions such as:

• Country = 'France'
• Score >= 70 AND Score <= 100
• City LIKE 'New*' (Access) or City LIKE 'New%' (SQL)
• Multiple criteria on the same field can be placed on separate rows to represent OR.

Calculated Fields & Aggregate Functions

• Calculated field (SQL example):

  SELECT ProductName, Quantity, UnitPrice,
  Quantity*UnitPrice AS TotalPrice
  FROM Orders;

• Aggregate functions (used with GROUP BY):

  • SUM(column) – total of numeric values.
  • AVG(column) – average.
  • COUNT(column) – number of rows.
  • MIN(column) / MAX(column) – smallest / largest value.

Wildcard Searches

SQL Wildcards (ANSI‑92)

• % – matches any sequence of zero or more characters.
• _ – matches exactly one character.

Access Wildcards (ANSI‑89 – default in Access)

• * – any sequence of characters.
• ? – any single character.
• # – any single digit (0‑9).
• ! – any single character that is not a digit.

Using Wildcards in Queries – Step‑by‑Step

1. Write a SELECT … FROM … WHERE … statement (SQL) or place criteria in the Access query‑design grid.
2. Insert the LIKE operator after the field you wish to search.
3. Enclose the pattern in single quotes (SQL) or double quotes (Access) and include the appropriate wildcard(s).
4. Run the query to view the matching records.

Example Patterns (SQL)

Example Patterns (Access)

Performance Tip

Placing a wildcard at the start of a pattern (e.g., LIKE '%abc') prevents the database engine from using an index, which can make the query noticeably slower on large tables. Whenever possible, anchor the pattern with a leading literal character (e.g., LIKE 'abc%').

Visual Aid – Query‑Design Grid (description)

In Access the grid has columns for Field, Table, Sort, Show, Criteria and Or. Sort determines the order of the output, while Criteria holds the WHERE conditions (including wild‑cards). Placing a value in the Or row creates an OR condition.

18.3 Present Data – Reports

Report Components

• Report Header – title, date, author, logo (appears once).
• Page Header – column headings that repeat on each page.
• Detail Section – the data rows from the underlying query.
• Report Footer – overall totals, summary notes (appears once).
• Page Footer – page numbers, confidentiality statements, etc.

Report Layout Checklist (AO 18.3)

• Insert a Page Header with column headings.
• Insert a Page Footer that contains Page [Page] of [Pages].
• Set Pagination (e.g., 30 records per page) to avoid splitting rows.
• Apply Numeric formatting:

  • Currency – $#,##0.00
  • Percentage – 0.0%
  • Decimal – #0.00

• Group records where required (e.g., by Department) and add a group header/footer with subtotals.
• Use Conditional formatting to highlight values (e.g., totals > £1,000 in red).
• Preview the report, adjust margins, and ensure headings repeat correctly.
• Export to PDF or print directly.

Formatting & Grouping

• Group by a field (e.g., Class) to create sub‑headings.
• Within each group, set a secondary sort order if needed.
• Insert calculated fields in the group footer for subtotals.

Exporting & Printing

• In Access: File → Export → PDF (or Word) to create a printable document.
• In MySQL/PostgreSQL: use \copy (SELECT …) TO 'file.csv' CSV HEADER and open the CSV in a spreadsheet for further formatting before printing.

Step‑by‑Step: From Table to Report (Access example)

1. Create the tables (e.g., Students, Results) and define primary/foreign keys.
2. Design a data‑entry form for Results using the form‑design checklist.
3. Build a query that joins the two tables, calculates the average mark, and (if required) uses a wildcard in the WHERE clause.
4. Run the query to verify correct results.
5. Create a report based on that query, add a report header with the school logo, insert a page header with column titles, group by Class, and place a SUM([Mark]) in the report footer.
6. Preview, format, and export the report to PDF for distribution.

Practical Tips & Evaluation

• Wildcards are case‑insensitive in most DBMSs; case‑sensitivity can be forced with collations (advanced).
• Test a wildcard query on a small sample before running it on the full table.
• Balance flexibility (wildcards) against performance (index usage). Anchor patterns where possible.
• When designing a database, ask:

  • Is the data stored in the correct normal form?
  • Do primary and foreign keys accurately represent relationships?
  • Will the required reports be easy to generate from the current structure?

Practice Questions

1. Write an SQL query to find all customer names that contain the letter “x” anywhere in the name.
2. In Access, create a query that returns all product codes that start with “P” followed by any two digits.
3. Explain why using LIKE '%abc%' might be less efficient than LIKE 'abc%'.
4. Write a MySQL query that finds all email addresses ending with “@example.com”.
5. In PostgreSQL, how would you search for a string that contains an underscore character?
6. Design a simple table called Employees with appropriate fields, data‑types, a primary key, and a foreign key linking to a Departments table.
7. Create a calculated field in an Access query that shows Quantity × UnitPrice as TotalCost.
8. Produce a report layout (list the sections) that would display a class list with a header, student details, and a footer showing the total number of students.
9. Discuss two advantages and two disadvantages of using wildcard searches in a large database.
10. Given a table Sales with fields SaleDate (Date) and Amount (Currency), write a query that returns the total sales per month for the year 2023.

Key Takeaways

• Database design starts with well‑structured tables, appropriate data‑types, and clearly defined primary/foreign keys.
• Relationships (one‑to‑one, one‑to‑many, many‑to‑many) are enforced through foreign keys and referential integrity.
• Normalisation (at least 1NF) eliminates duplicate data and ensures atomic fields.
• Forms provide a user‑friendly way to enter and edit data; follow the form‑design checklist for clarity.
• Queries retrieve, sort, filter, and calculate data. Master WHERE, comparison & logical operators, aggregate functions, and wildcard patterns.
• Wild‑cards (%, _ in SQL; *, ?, #, ! in Access) enable flexible searching but can degrade performance if used at the start of a pattern.
• Reports turn query results into professional output. Use the report‑layout checklist to include headers, footers, pagination, grouping, and proper numeric formatting.
• Always evaluate the efficiency and suitability of your solution – consider data integrity, speed, and ease of use.