Be able to use arithmetic operations or numeric functions to perform calculations including calculated fields, calculated controls

Topic 18 – Databases (Cambridge IGCSE 0417)

1. Database basics (syllabus 18.1)

1.1 What is a database?

• A structured collection of related data that can be stored, retrieved and updated electronically.

1.2 Types of databases

• Flat‑file (single‑table) database – a simple list of records with no relationships. Useful for very small data sets.
• Relational database – two or more tables linked by primary and foreign keys. This is the type expected for all IGCSE coursework and examinations.

1.3 Setting up tables – the essential steps

1. Open the database program (e.g. Microsoft Access) and choose Create → Table Design.
2. Enter a field name and select an appropriate Data Type (see 1.4).
3. Set field properties:

   • Field Size / Length – for Text fields, ≤ 255 characters; for Memo/Long Text, unlimited.
   • Format – e.g. Currency, Short Date.
   • Required – forces a value to be entered.
   • Indexed – set to Yes (No Duplicates) for primary keys, or Yes (Duplicates OK) for foreign keys to speed up searching.

4. Choose a Primary Key (right‑click the field → Primary Key). This uniquely identifies each record.
5. Save the table with a meaningful name (e.g. Students).
6. Repeat for any additional tables, then create relationships (Database Tools → Relationships) by dragging the primary key onto the matching foreign key.

1.4 Common field types (Cambridge “field‑type selection”)

1.5 Primary and foreign keys

• Primary key – a field (or combination of fields) that uniquely identifies each record.

  StudentID (AutoNumber) – Primary Key

• Foreign key – a field that refers to the primary key of another table, creating a relationship.

  Enrolments.StudentID – Foreign Key → Students.StudentID

1.6 Form‑design checklist (AO2 – produce a solution)

• Clear, descriptive labels (e.g. “Unit price (£)”).
• Logical Tab order – set using the Tab Order dialog.
• Appropriate control types:

  • Text box for free‑text or numbers.
  • Combo box or list box for a limited set of choices.
  • Option group for mutually exclusive options.
  • Calculated control for values derived at run‑time.

• Input validation (e.g. Before Update event, InputMask for dates, Validation Rule for ranges).
• Consistent colour, font and alignment – improves readability.

2. Using arithmetic operations and numeric functions (syllabus 18.2 – calculations)

2.1 Arithmetic operators

2.2 Numeric functions supported in Access/Excel (exam‑relevant)

2.3 Calculated fields (stored in a table)

• Definition: a field whose value is the result of an expression that uses other fields in the same record. The value is saved with the record and updates automatically when source fields change.
• General syntax (Access):

  =[Field1] * [Field2] + [Field3]

• Example – total price of an order line

  TotalPrice = [UnitPrice] * [Quantity]

• Real‑world example – net amount after discount

  NetAmount = ([UnitPrice] * [Quantity]) * (1 - [Discount%] / 100)

  If UnitPrice = 15.00, Quantity = 4, Discount% = 10 then NetAmount = 54.00.

2.4 Calculated controls (not stored, shown on forms or reports)

• Created in Design View by setting the Control Source property to an expression.
• Useful for totals, averages, ages, or any value that combines data from several records.

Examples

• Running total on an invoice form

  =Sum([UnitPrice] * [Quantity])

  Updates each time a line is added or edited.

• Age from date of birth

  =Int((Date() - [DateOfBirth]) / 365.25)

  Returns the whole number of years.

• Grand total on a report footer

  =Sum([NetAmount])

  Placed in the report footer to give the total for all printed records.

3. Manipulating data – sorting, searching, queries, filtering, import/export (syllabus 18.2)

3.1 Sorting records

• In Table or Datasheet view – click a column header to sort ascending; click again for descending.
• Using the Ribbon – Home → Sort & Filter → Sort A‑Z or Sort Z‑A. This works for both tables and query results.

3.2 Searching records

• Press Ctrl + F to open the Find dialog; enter the value and choose the field to search.
• For more precise searches, use the Advanced Find (Home → Find → Advanced Find) which allows criteria on multiple fields.

3.3 Filtering records

• Filter button (Home → Sort & Filter → Filter) – tick the check boxes that appear under each column heading to display only matching records.
• Criteria row in Query Design – type conditions under the appropriate field. Example:

  Score >= 80

• Advanced Filter/Sort (Home → Advanced → Advanced Filter/Sort) – lets you build complex criteria using AND/OR, BETWEEN … AND …, LIKE, etc.

3.4 Queries – the backbone of data manipulation

A query extracts, calculates or modifies data without changing the underlying tables.

3.4.1 Basic SELECT query (SQL view)

SELECT StudentID, FirstName, LastName
FROM Students
WHERE Year = 2024
ORDER BY LastName ASC;

3.4.2 Criteria operators (must be known for the exam)

3.4.3 Wild‑cards for text searches (Access)

• * – any number of characters (e.g. Smith* finds “Smith”, “Smithson”).
• ? – exactly one character (e.g. Jo?n finds “John” or “Joan”).

3.5 Importing and exporting data

3.5.1 Importing a CSV/TXT file

1. File → Import → Text File.
2. Browse to the source file and click Import.
3. Select the delimiter (comma, tab, semicolon, etc.) and indicate whether the first row contains field names.
4. Map each column to an existing field or let the wizard create new fields. Pay special attention to numeric fields – set the correct Data Type.
5. Finish and review the imported table.

3.5.2 Exporting a table or report

1. Select the object (table, query, or report) in the Navigation Pane.
2. File → Export → Text File (or Excel).
3. Choose a destination folder, decide whether to include field names as the first row, and pick a delimiter.
4. Complete the wizard – the file can now be opened in other programs.

4. Presenting data – reports (syllabus 18.3)

4.1 Creating a simple report

1. Report → Report Wizard (or Report → Design View for full control).
2. Select the table or query that supplies the data.
3. Move the required fields to the right‑hand box; use Add All if you need every field.
4. Choose a layout style (Columnar, Tabular, Justified) and any grouping fields.
5. Finish; then switch to Design View to adjust fonts, colours, and add calculated controls.

4.2 Report layout elements

• Page Header – report title, logo, date.
• Page Footer – page number, “confidential” notice.
• Report Header / Footer – overall title or grand total for the whole report.
• Group Header / Footer – subtotals for each group (e.g., per Customer).
• Detail Section – one row per record; place calculated controls here for per‑record values.

4.3 Page setup and pagination

• File → Page Setup – set Orientation (Portrait or Landscape) and Margins (default 0.5 inches works for most exams).
• Use the Page Break tool (Design → Page Break) to force a new page where required.
• Check the Print Preview to confirm that headers, footers and page numbers appear correctly.

4.4 Formatting numeric values in reports

• Select the control, open the Property Sheet → Format tab.

  • Format – Currency, Fixed, Percent, Standard.
  • Decimal Places – set the number of digits after the decimal point.
  • Input Mask – only needed on forms; on reports it ensures consistent display (e.g. 0000-00-00 for dates).

4.5 Printing and exporting reports

• Print Preview → Print to send to a printer or to a PDF driver.
• File → Export → PDF or XPS – creates a portable version of the report that can be submitted electronically.

5. Step‑by‑step: creating a calculated field (IGCSE‑level)

1. Open the required table in Design View.
2. Add a new field name, e.g. TotalCost.
3. Set Data Type to Currency (or Number if no currency symbol is needed).
4. In the Default Value (or Expression) property, type the expression using square brackets around field names:

   =[UnitPrice] * [Quantity]

5. Save the design (Ctrl + S). The field now displays the calculated result for every record.

6. Common pitfalls and how to avoid them

• Division by zero – wrap the expression in IIf:

  IIf([Divisor]=0, 0, [Numerator]/[Divisor])

• Rounding errors with currency – apply ROUND() to two decimal places:

  ROUND([Subtotal], 2)

• Incorrect field references – field names are case‑insensitive but must be spelled exactly; use brackets if the name contains spaces:

  =[Unit Price] * [Quantity]

• Data‑type mismatch – ensure all parts of the expression are numeric. Convert text with VAL() if necessary:

  =[TextNumber] + VAL([AnotherTextNumber])

• Unexpected Null values – use Nz() (or IsNull()) to treat Null as zero:

  Nz([Discount%],0)

7. Evaluation activity (AO3 – analyse/evaluate)

For each scenario, answer the three questions below. Mark the key points that would earn AO3 marks.

1. Scenario – A school wants to store student results for three tests. The current design uses one table with fields StudentID, Name, Test1, Test2, Test3.

   • Is the design likely to cause data‑entry errors or redundancy? Explain.
   • Suggest a more efficient relational design (list the tables and key fields).
   • How would the new design improve the speed of queries that calculate class averages?

2. Scenario – An invoice form shows a calculated control “Running Total”. The form is used by a busy shop assistant who often forgets to press Enter after editing a line.

   • Identify a possible usability problem caused by the current setup.
   • Propose a simple change (e.g., event procedure, control property) to make the total update reliably.
   • Explain how your suggestion benefits the user and maintains data integrity.

8. Practice questions (AO1, AO2, AO3)

1. In a table Students you have fields Mark1, Mark2, Mark3. Write the expression for a calculated field Average that rounds the result to one decimal place.
2. A payroll form contains HourlyRate and HoursWorked. Create a calculated control that shows the gross pay, ensuring the result is displayed as currency with two decimal places.
3. Write a SELECT query that returns the ProductName and TotalSales (quantity × unit price) for all sales where the total exceeds £500. Include an appropriate ORDER BY clause.
4. Explain how you would filter a query to show only records where a student’s Score is between 70 and 85 inclusive.
5. Describe the steps to export a report called “MonthlySales” as a PDF file ready for submission.