Know and understand characteristics of good form design

18 Databases

Objective

Know and understand the characteristics of good form design and how it integrates with the wider database processes of creating structures, manipulating data and presenting information. This objective covers:

• AO1 – Knowledge: terminology, principles and purposes of database components.
• AO2 – Practical Skills: designing tables, building forms, applying validation, importing/exporting data and generating reports.
• AO3 – Evaluation: assessing the effectiveness of a database solution and suggesting improvements.

1. Database Structure – the foundation for any form

1.1 Core terminology (AO1)

Term (syllabus wording)	Definition	Example (Students & Enrolments)
Table	A collection of related records (rows) stored together.	Students – one record per pupil.
Field (column)	A single piece of data that describes an attribute of a record.	StudentID, FirstName, DOB, Email
Data type (field type)	Specifies the kind of data a field can hold (text, number, date, Boolean, etc.) and any size limits.	StudentID – AutoNumber (numeric, 9‑digit limit) DOB – Date
Primary key	A unique identifier for each record in a table; must contain no null values.	StudentID in the Students table (created as AutoNumber).
Foreign key	A field that links a record to the primary key of another table, establishing a relationship.	StudentID in the Enrolments table.
Relationship type	Describes how two tables are linked: One‑to‑many – one record in Table A relates to many records in Table B (e.g., one student can have many enrolments). Many‑to‑many – many records in Table A relate to many in Table B; usually implemented via a junction table. One‑to‑one – each record in Table A matches exactly one record in Table B.	Students ↔ Enrolments is a one‑to‑many relationship.

1.2 Flat‑file vs. relational databases (syllabus requirement)

Aspect	Flat‑file (e.g., CSV)	Relational (e.g., Access, LibreOffice Base)
Data storage	All data in a single table; repeated information (redundancy).	Data split into related tables; each fact stored once.
Data integrity	Hard to enforce; easy to create duplicate or inconsistent records.	Primary‑key/foreign‑key constraints prevent duplicates and orphan records.
Scalability	Becomes unwieldy as the number of fields grows.	Can handle many tables and complex queries efficiently.
Typical use in IGCSE	Importing a simple list of contacts.	Managing a school database with separate Students, Enrolments and Classes tables.

Why structure matters for form design

• Each form control must map to a field in a table.
• The form must respect primary‑key/foreign‑key rules to avoid duplicate or orphan records.
• Understanding relationships helps decide whether a combo‑box, list‑box or sub‑form is required.

2. Form Design – characteristics of a good form

2.1 What is a form?

A form is a user‑interface object that allows data to be entered, edited or retrieved from a database. In the IGCSE context forms are built with the “Form” object in Microsoft Access, LibreOffice Base or similar software.

2.2 Key characteristics (AO1)

• Clarity and simplicity – concise, unambiguous labels; no superfluous fields.
• Logical flow – arrange fields in the order the user thinks about the data (e.g., personal details → address → contact).
• Appropriate control types – match the control to the data type:
  • Text box for short text.
  • Combo‑box or drop‑down list for a limited set of values.
  • Option (radio) buttons for mutually exclusive choices.
  • Check box for Boolean (yes/no) values.
  • Date picker for dates.
• Consistent layout – uniform alignment, font, colour and spacing.
• Validation and error handling – mandatory‑field checks, format checks, range checks; display clear, field‑adjacent messages.
• Feedback to the user – confirm successful save, indicate required fields (red asterisk *).
• Accessibility – logical tab order, sufficient colour contrast, screen‑reader‑friendly labels.
• Minimise data entry – defaults, auto‑complete, look‑up tables, calculated fields.
• Security considerations – mask sensitive input, limit field length, sanitise input to prevent SQL injection.

2.3 Typical form layout elements

Element	Purpose	Best practice (AO2) – linked to a practical task
Title / Heading	Identifies the purpose of the form	Insert a bold, centred heading such as “Student Registration”. (Task: create a form with an appropriate title.)
Labels	Describe each field	Place to the left or directly above the control and associate it with the control (e.g., label “Date of Birth” linked to the date picker). (Task: add correctly associated labels.)
Input controls	Collect data	Choose the control that matches the field’s data type; set MaxLength and input mode where relevant. (Task: set a 10‑character limit for a “Postcode” field.)
Mandatory indicator	Shows required fields	Use a red asterisk (*) and include a legend “* = required”. (Task: mark “First name”, “Surname” and “DOB” as mandatory.)
Help text / tooltips	Provide guidance	One‑line hint that appears on hover/focus, e.g., “Enter a valid email address”. (Task: add a tooltip to the Email field.)
Submit / Reset buttons	Action controls	Label clearly (“Save”, “Clear Form”) and place at the bottom right. (Task: create a “Save” button that writes the record to the table.)
Error messages	Inform about validation failures	Place immediately below the offending control; use plain language and suggest a correction. (Task: display “Enter a date between 01‑01‑2000 and today” for an out‑of‑range DOB.)

2.4 Design checklist (AO3 – Evaluation)

1. Are all labels clear, concise and correctly linked to their controls?
2. Is the tab order logical for keyboard‑only users?
3. Have the most appropriate control types been selected for each data type?
4. Are required fields clearly marked and explained?
5. Is real‑time validation present for critical fields (e.g., email format, date range)?
6. Do error messages identify the problem and give a concrete remedy?
7. Is the form accessible to users with visual or motor impairments?
8. Are security measures (masking, length limits, input sanitisation) implemented?
9. Is the visual layout balanced, with adequate white space and consistent alignment?
10. Has the form been tested with a representative sample of users and refined accordingly?

2.5 Sample evaluation of a flawed form

Consider a form that omits a mandatory‑field indicator, uses a free‑text box for “Gender” and places the “Submit” button at the top left.

• Usability issue: Users cannot tell which fields are required, leading to incomplete records.
• Data‑type mismatch: A free‑text box allows any spelling of gender, causing inconsistent data (“Male”, “M”, “male”). A radio‑button group would enforce a controlled list.
• Poor layout: Placing the submit button at the top encourages premature submission and forces users to scroll back down to correct errors.
• Suggested improvements: Add red asterisks for required fields, replace the free‑text box with option buttons (Male / Female / Other), and move the submit button to the bottom centre with a clear label “Save Record”.

3. Data‑type limits and validation (AO2)

• Field size limits – e.g., Text fields can be limited to 20 characters; Number fields can be set to a specific range.
• Validation rules – use built‑in properties such as “Required”, “Input Mask” (e.g., 00/00/0000 for dates) and “Validation Text” for custom messages.
• Calculated fields – values derived from other fields using expressions.
  • Example: TotalPrice = Quantity × UnitPrice in an Orders table.

4. Manipulating Data (AO2)

4.1 Sorting

• Ascending or descending order based on one or more fields.
• Typical GUI action: click a column heading or use the “Sort” dialog.

4.2 Searching & Queries

Students should be able to retrieve specific records using criteria. The following examples use SQL‑like syntax, which matches the IGCSE specification.

Operation	Purpose	Example
Simple query	Find all students in Year 10	SELECT * FROM Students WHERE Year = 10;
Combined criteria (AND/OR)	Find Year 10 students who live in “London”	SELECT * FROM Students WHERE Year = 10 AND City = 'London';
Pattern matching (LIKE)	Find surnames starting with “Mc”	SELECT * FROM Students WHERE Surname LIKE 'Mc%';
Range query	Find enrolments between 1‑Sept‑2023 and 31‑Aug‑2024	SELECT * FROM Enrolments WHERE EnrolDate BETWEEN #2023-09-01# AND #2024-08-31#;

4.3 Updating & Deleting Records

• Use form‑based edit mode for updates – the user opens a record, makes changes, then clicks “Save”.
• Confirm deletions with a dialog box (e.g., “Are you sure you want to delete this record?”) to prevent accidental loss.

5. Importing and Exporting Data (AO2)

• Import formats – CSV, TXT, Excel. Typical steps:
  1. Choose “External Data → Import” in the database program.
  2. Select the source file and indicate whether the first row contains field names.
  3. Map each column to the appropriate field type and set any size limits.
  4. Run the import and check for errors.
• Export formats – CSV, PDF, HTML.
  1. Open the table, query or report to be exported.
  2. Select “Export” and choose the desired format.
  3. Specify the destination folder and any formatting options (e.g., include field headings).

6. Presenting Data (AO2 & AO3)

6.1 Reports – purpose and key features

• Structured, printable outputs that can include grouping, totals, page headers/footers and charts.
• Typical report elements:
  • Header – title, school name, date.
  • Group header/footer – e.g., group by class and show a subtotal of attendance.
  • Detail section – the individual records.
  • Page footer – page numbers, confidentiality notice.

6.2 Report‑design checklist (AO3)

1. Is the report title clear and positioned at the top centre?
2. Are records grouped logically (e.g., by class or year) and are group totals displayed?
3. Do column headings repeat on each page if the report spans multiple pages?
4. Is the numeric formatting appropriate (currency, decimal places, percentages)?
5. Are page headers/footers consistent and do they contain page numbers?
6. Has the layout been tested for readability when printed (adequate white space, legible font size)?
7. Is any sensitive data masked or omitted from the public version of the report?

6.3 Example report

Student Attendance – Term 1
Header: “ABC Academy – Attendance Summary – Term 1”
Grouped by Class, showing total days present per student and a class subtotal.
Footer: “Page 1 of 3 – Confidential – Generated 04‑Jan‑2026”.

7. Cross‑Topic Reminders (linking to other syllabus areas)

Topic	Relevant Database Issue	Practical tip for IGCSE
File Management	Choosing the correct file format for the database (e.g., .accdb, .sdb, .csv).	Store the master database in a protected folder; back‑up regularly.
E‑safety & Data Protection	Handling personal data (names, DOB, contact details) in forms and reports.	Include a privacy notice; restrict file access with passwords; avoid printing unnecessary personal details.
Security	Preventing SQL injection or accidental data corruption.	Validate all input, limit field lengths, and never concatenate raw user input into SQL statements.

8. Mapping Content to Assessment Objectives

AO	What the student must demonstrate	Relevant note sections
AO1	Recall terminology (table, field, primary key, validation, relationship types, flat‑file vs. relational, etc.) and describe the purpose of each component.	Section 1 (Database Structure), Section 2 (Form Design Characteristics)
AO2	Design a table, create a form that reflects that table, apply appropriate validation, import a CSV file, run a query, and produce a simple report.	Sections 1–6 (structure, form design, data‑type limits, import/export, queries, reports)
AO3	Evaluate the effectiveness of the database solution (form usability, data integrity, security, accessibility) and suggest concrete improvements.	Section 2.4 (Design Checklist), Section 2.5 (Sample evaluation), Section 7 (Cross‑topic reminders), Section 8 (AO mapping)

9. Suggested Diagram (description for the teacher)

A typical form layout: a centred bold title at the top, followed by grouped sections such as “Personal Details” and “Contact Details”. Within each group, labels are left‑aligned and input controls are right‑aligned in a two‑column grid. Mandatory asterisks appear in red beside required labels. At the bottom centre sits a “Save” button; a “Clear Form” button is placed to its left. The tab order follows the visual order from top‑left to bottom‑right.

Summary

Good form design is one pillar of a functional database system. By first establishing a clear table structure with appropriate primary‑key/foreign‑key relationships and understanding the differences between flat‑file and relational databases, students can build forms that capture high‑quality data. Adhering to the characteristics of clarity, logical flow, validation, accessibility and security ensures reliable data entry. Combined with skills in importing/exporting, querying, sorting, updating, and reporting, learners can meet all three assessment objectives of the Cambridge IGCSE ICT 0417 syllabus.