Cambridge IGCSE ICT 0417 – Databases: Data Types
Cambridge IGCSE ICT 0417 – Databases
Topic: 18 Databases
Objective
Be able to set appropriate data types to fields including text, numeric (integer, decimal, currency), date/time, Boolean/logical.
1. Why Data Types Matter
Choosing the correct data type for each field ensures that:
Data is stored efficiently (minimum storage space).
Invalid data cannot be entered (data integrity).
Calculations and sorting work correctly.
Reports and queries run faster.
2. Text (String) Fields
Used for letters, numbers that are not used for calculation, and any combination of characters.
CHAR(n) – Fixed length; always stores exactly n characters. Good for codes such as “AB12”.
VARCHAR(n) – Variable length; stores up to n characters, using only the space needed. Ideal for names, addresses.
TEXT – Large blocks of text (e.g., comments). No specific length limit in many DBMS.
3. Numeric Fields
Numeric fields store numbers that can be used in calculations. They differ by the kind of number and precision required.
3.1 Integer
Whole numbers without a decimal point.
Typical range: \$-2^{31}\$ to \$2^{31}-1\$ (≈ -2 147 483 648 to 2 147 483 647) for a 4‑byte integer.
Use when the value will never have fractions (e.g., quantity, age, student ID).
3.2 Decimal (Fixed‑point)
Numbers that require a fixed number of decimal places.
Defined as DECIMAL(p,s) where p = total digits and s = digits after the decimal point.
Example: DECIMAL(7,2) can store values from –99999.99 to 99999.99.
Use for precise values such as grades, measurements, or scientific data.
3.3 Currency
Specialised decimal type for monetary values. Often implemented as DECIMAL(p,2) (two decimal places).
Ensures rounding errors are avoided in financial calculations.
Example: DECIMAL(10,2) stores up to 99999999.99.
4. Date/Time Fields
Store calendar dates, times of day, or both.
DATE – Stores a calendar date (YYYY‑MM‑DD).
TIME – Stores a time of day (HH:MM:SS).
DATETIME – Stores both date and time together.
TIMESTAMP – Similar to DATETIME but often includes automatic update features.
5. Boolean / Logical Fields
Represent a true/false condition.
BOOLEAN – Stores values TRUE or FALSE (sometimes 1/0).
Useful for flags such as “IsActive”, “Paid”, “Verified”.
6. Choosing the Right Data Type – Checklist
Is the data ever used in calculations? If yes, choose a numeric type.
Will the value ever contain fractions? Use Decimal or Currency.
Is the length of the text fixed? Use CHAR; otherwise, use \cdot ARCHAR.
Do you need to store dates or times? Use DATE, TIME, or DATETIME accordingly.
Is the field a simple yes/no? Use BOOLEAN.
Consider storage size: smaller types improve performance.
7. Example Field Definitions
Field Name
Sample Data
Recommended Data Type
Reason
StudentID
2023456
INTEGER
Whole number, never fractional.
FullName
Emma Johnson
VARCHAR(50)
Variable length text, up to 50 characters.
BirthDate
2005-09-14
DATE
Stores only a calendar date.
GPA
3.75
DECIMAL(3,2)
Two decimal places required for grade point average.
TuitionFee
1250.00
DECIMAL(8,2)
Currency value with two decimal places.
IsEnrolled
TRUE
BOOLEAN
Simple yes/no flag.
Suggested diagram: An Entity‑Relationship diagram showing entities with fields labelled by their chosen data types (e.g., Student(ID INTEGER, Name VARCHAR, DOB DATE, GPA DECIMAL, Enrolled BOOLEAN)).