Apply validation to fields in a database

Databases – Topic 9 (IGCSE 0478): Building a Single‑Table Database and Applying Validation

1. Designing a Single‑Table Database (9.1)

When a specification tells you what information must be stored, the first step is to convert it into a table design. A table consists of:

• Fields (columns) – the individual pieces of data to be recorded.
• Data type for each field – determines the kind of values that are allowed.
• Constraints – rules that enforce data integrity (e.g., NOT NULL, UNIQUE).

Example specification: “A school needs a register of students. For each student the register must store a unique student ID, the full name, age, gender and e‑mail address.”

2. Choosing Suitable Basic Data Types (9.2)

The IGCSE syllabus recognises five basic data‑type categories. The table below shows the typical SQL type used in MySQL/PostgreSQL and an example field from the student register.

3. Identifying a Primary Key (9.3)

• A primary key uniquely identifies each record in the table.
• Criteria for a good primary key:
  • Unique for every record.
  • Never NULL.
  • Short, stable and not likely to change.
• In the example, student_id CHAR(8) meets all criteria, so it is declared as the primary key.

4. Simple SQL Queries (9.4)

Students must be able to write and interpret basic SELECT statements using the following clauses:

• SELECT – list the required fields.
• FROM – specify the table.
• WHERE – filter rows; combine conditions with AND, OR.
• ORDER BY – sort the result set.
• Aggregate functions – SUM(), COUNT().

Example queries based on the Students table:

-- 1. List names and ages of students older than 10, sorted alphabetically
SELECT name, age
FROM Students
WHERE age > 10
ORDER BY name ASC;

-- 2. Count how many students are registered
SELECT COUNT(*) AS total_students
FROM Students;

-- 3. Total salary of all IT‑department staff
SELECT SUM(salary) AS total_it_salary
FROM Employees
WHERE department = 'IT';

5. What Is Data Validation? (Enrichment)

Data validation checks that data entered into a database satisfies all required rules before it is stored. Validation protects data integrity and reduces the amount of later cleaning.

5.1 Reasons for Using Validation

• Prevents incorrect or inconsistent data.
• Ensures mandatory information is not omitted.
• Reduces later data‑cleaning work.
• Increases user confidence and system credibility.

5.2 Common Validation Types

1. Data‑type validation – value must match the defined type (INTEGER, DATE, …).
2. Length validation – limits the number of characters or digits.
3. Range validation – numeric value must lie within a specified interval.
4. Format validation – value must follow a pattern (e.g., e‑mail, phone number).
5. Mandatory (NOT NULL) validation – field cannot be left empty.
6. Uniqueness validation – no duplicate values in the column.

5.3 Implementing Validation Directly in SQL

5.4 Using Triggers for Complex Validation

When a rule cannot be expressed with a simple CHECK constraint, a trigger can examine the data before it is inserted or updated.

CREATE TRIGGER trg_check_salary
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END;

5.5 Validation in Application Forms

• Client‑side validation – JavaScript or HTML5 attributes give instant feedback (e.g., required, pattern, type="number").
• Server‑side validation – the server must re‑check every rule before executing SQL, because client‑side checks can be bypassed.

5.6 Example: Student Registration Form

HTML5 form fields (client‑side):

<input type="text" name="student_id" pattern="[A-Za-z0-9]{8}" required>
<input type="text" name="name" maxlength="30" required>
<input type="number" name="age" min="5" max="20" required>
<input type="email" name="email" required>

SQL table definition (server‑side constraints):

CREATE TABLE Students (
    student_id CHAR(8) PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    age INT CHECK (age BETWEEN 5 AND 20),
    email VARCHAR(255) NOT NULL,
    UNIQUE (student_id)
);

6. Common Pitfalls

• Relying only on client‑side validation – users can disable or bypass it.
• Using patterns that are too restrictive and reject legitimate data.
• Forgetting that CHECK constraints treat NULL as “unknown”, so a NULL value can bypass a range check.
• Not updating validation rules when business requirements change.

7. Summary Checklist (All Syllabus Points)

1. Translate the specification into a single‑table design (fields, data types, primary key).
2. Choose appropriate basic data types (INTEGER, REAL, CHAR, STRING, BOOLEAN).
3. Identify and declare a primary key.
4. Write simple SELECT queries using SELECT, FROM, WHERE, ORDER BY, AND, OR, SUM, COUNT.
5. Apply validation:
   • SQL constraints (NOT NULL, UNIQUE, CHECK).
   • Triggers for rules that cannot be expressed with CHECK.
   • Client‑side HTML5/JavaScript checks.
   • Server‑side re‑validation before database operations.