Be able to set appropriate data types to fields including text, numeric (integer, decimal, currency), date/time, Boolean/logical

Cambridge IGCSE ICT 0417 – Databases: Data Types & Related Skills

Objective

Be able to set appropriate data types to fields (text, numeric – integer, decimal, currency – date/time, Boolean/logical), justify the choice, and apply the decision throughout the database life‑cycle (analysis, design, testing, implementation, evaluation). Use the chosen types correctly when creating tables, forms, queries, calculations, reports and when exporting data.

1. Context – How Data Types Fit into the Wider ICT System

• Hardware & storage – The size of a data type determines how many bytes each record occupies. Smaller types mean less disk space, faster read/write on hard‑disk or SSD and lower memory usage.
• Network bandwidth – When tables are shared over a network (e.g., in a school intranet) compact data reduces transfer time and the risk of time‑outs.
• Security & e‑safety – Correct data types help prevent:

  • SQL injection (e.g., forcing a numeric field to accept text).
  • Overflow errors (e.g., storing a very large number in a SMALLINT).
  • Data‑type mismatches that could expose sensitive information.

2. Why Data Types Matter

• Storage efficiency – The right type uses the minimum number of bytes.
• Data integrity – Inappropriate types allow invalid entries (e.g., letters in a numeric field).
• Correct calculations – Only numeric types can be used in arithmetic or aggregate functions.
• Sorting & searching – Works as expected when the type matches the content.
• Performance – Smaller, well‑chosen types make queries, forms and reports run faster.
• Security – Proper typing reduces the chance of injection, overflow and other errors.

3. Data Types Overview

3.1 Text (String) Fields

3.2 Numeric Fields

3.3 Date/Time Fields

3.4 Boolean / Logical Fields

4. Choosing the Right Data Type – Checklist

1. Will the field ever be used in calculations? → Choose a numeric type.
2. Does the value need fractions? → Use DECIMAL or CURRENCY.
3. Is the length of the text fixed? → CHAR else VARCHAR.
4. Do you need to store large blocks of text? → TEXT.
5. Is the data a calendar date, a time of day, or both? → DATE, TIME, or DATETIME.
6. Is the field a simple yes/no flag? → BOOLEAN.
7. Consider storage size – smaller types improve performance and reduce file size.
8. Validate against business rules (e.g., “price cannot be negative”).
9. Check security implications – avoid overly permissive types that could allow injection.

5. Systems Life‑Cycle Integration

6. Table Design & Keys

6.1 Primary Keys

• Uniquely identify each record.
• Should be simple, stable, short, and never NULL.
• Typical choices: INTEGER AUTOINCREMENT, CHAR(8) student code, or a UUID.

6.2 Foreign Keys

• Link a child record to the primary key of a parent table.
• Enforce referential integrity – the DBMS prevents orphaned records.
• Example: Enrolments.StudentID (FK) references Students.StudentID (PK).

6.3 Relationship Types

6.4 Field‑Naming Conventions (IGCSE Best Practice)

• Use singular nouns (e.g., StudentID, not Students).
• Start with a capital letter; avoid spaces and special characters.
• Optional type hint (e.g., QtyInt, PriceCur) can aid readability.
• Keep names under 30 characters for compatibility with most DBMS.

6.5 Justifying Data‑Type Choices (mini‑case study)

Field: TuitionFee
Chosen type: DECIMAL(10,2)
Why: Monetary value – requires exact two‑decimal precision; range up to 99 999 999.99 covers all fees; prevents rounding errors.

7. Form Design & Data Validation

• Layout – place labels left of input controls; group related fields using frames or tabs.
• Input controls – use text boxes for VARCHAR/TEXT, spin boxes for SMALLINT/INTEGER, date pickers for DATE, check boxes for BOOLEAN.
• Validation rules:

  • Field size (e.g., Length ≤ 50 for VARCHAR(50)).
  • Range checks (e.g., Age BETWEEN 5 AND 120).
  • Format checks (e.g., email must contain “@”).
  • Required/Not Null – ensure essential fields are filled.

• Double‑entry verification – for critical data (e.g., passwords, bank account numbers) ask the user to type it twice and compare.
• Document all validation rules in the design stage; test them during the testing phase.

8. Queries – Retrieving & Manipulating Data

8.1 Basic SELECT Syntax (Paper 2 evidence)

SELECT FullName, BirthDate, GPA
FROM Students
WHERE IsEnrolled = TRUE
AND BirthDate BETWEEN '2005-01-01' AND '2006-12-31'
ORDER BY FullName;

When answering Paper 2, capture a screenshot of the query window and the result set as part of the evidence document.

8.2 WHERE Clause – Logical Operators & Wild‑cards

• Comparison: =, <>, >, <, >=, <=
• Range: BETWEEN … AND …
• Set: IN (value1, value2, …)
• Pattern: LIKE 'A%' (starts with A), LIKE '%son' (ends with son), LIKE 'a' (any single character before and after a).
• Logical: AND, OR, NOT

8.3 Calculated Fields

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

8.4 Aggregate Functions (useful for reports)

8.5 Grouping & Having

SELECT Class, COUNT(*) AS StudentsInClass, AVG(GPA) AS AvgGPA
FROM Students
GROUP BY Class
HAVING AVG(GPA) >= 3.0;

9. Sorting & Filtering in Forms & Reports

• Sorting – ORDER BY FieldName ASC|DESC (e.g., alphabetical list of students).
• Filtering – apply criteria in the form’s property sheet or in a query’s WHERE clause.
• Use combo boxes or list boxes on a form to let the user select a filter value (e.g., choose a year group).

10. Reporting – From Data to Presentation

10.1 Creating a Basic Report

1. Open the Report Wizard (or “Create Report” in your DBMS).
2. Select the source table or query.
3. Choose the fields to display – order them logically (e.g., ID, Name, DOB, GPA).
4. Add a group header if you need totals per class or year.
5. Finish and switch to Layout view for fine‑tuning.

10.2 Formatting Essentials (IGCSE expectations)

• Column headings – bold, centre‑aligned.
• Number formatting – two decimal places for currency, one for GPA.
• Date format – DD‑MMM‑YYYY (e.g., 14‑Sep‑2005) for readability.
• Headers & footers – report title, page number, date of generation.
• Conditional formatting – e.g., highlight rows where GPA < 2.0 in red.

10.3 Exporting & Sharing

• PDF – fixed layout, ideal for exam submission.
• CSV – plain‑text, easy to import into spreadsheets or other programmes.
• Linking to other ICT components:

  • Insert a chart from a spreadsheet (e.g., Excel) into a Word document that contains the report.
  • Copy a query result into a PowerPoint slide for a presentation.
  • Publish a query as a web page (HTML) – use SELECT … INTO OUTFILE or the DBMS export wizard.

• Take a screenshot of the final report layout for the evidence document.

11. File Management & Data Export

• Save the database file with a clear, versioned name (e.g., SchoolDB_v1.accdb).
• Regularly back‑up the file to a separate storage medium or cloud drive.
• Export tables or queries:

  • CSV – Export > Text File > CSV. Useful for importing into spreadsheets or statistical software.
  • PDF – File > Export > PDF. Preserves formatting for printed reports.

• When exporting, remember that text fields are quoted, dates are stored in ISO format (YYYY‑MM‑DD), and numeric fields retain their precision.

12. Performance & Security Summary

• Performance – Choose the smallest type that meets the business need; index numeric or short text fields that are used for searching.
• Security – Use appropriate data types to limit input (e.g., BOOLEAN for yes/no), enforce NOT NULL where required, and apply validation rules to stop malicious data entry.
• Audit trail – Add a Timestamp field (auto‑update) to record when a record was last modified.

13. Quick Revision Checklist (Exam‑style)

1. Identify the nature of each piece of information → text, numeric, date/time, Boolean.
2. Select the most suitable data type and note the storage size.
3. Justify the choice in a sentence (e.g., “DECIMAL(5,2) for price – ensures exact cents”).
4. Define primary key(s) and any foreign key relationships.
5. Design a simple form with appropriate controls and at least one validation rule.
6. Write a query that:

   • Uses a WHERE clause with logical operators.
   • Contains a calculated field.
   • Includes an aggregate function and GROUP BY.

7. Create a report that groups by a chosen field, formats numbers/dates, and export it as PDF.
8. Explain one way the chosen data types improve performance and one way they improve security.