Learn
9.2 | DATA TYPES
01 | THE SAME DIGITS CAN MEAN DIFFERENT THINGS
A school database stores the student code “0072” and the attendance count seventy-two. They use similar digits but do different jobs. The code identifies a student; the count measures how many sessions were attended.
A data type describes the kind of value a field stores and the operations appropriate for it. Choosing a type requires understanding the field’s meaning, not simply inspecting its first example value.
The Cambridge database topic includes text/alphanumeric, character, Boolean, integer, real and date/time. This lesson uses those basic categories rather than the exact product-specific types of a particular DBMS.
02 | ONE FIELD HAS A CONSISTENT TYPE
A field is one named kind of information, represented by a column. Its type should suit the values expected across all records, not just the current record.
If HeightMetres may contain 1.72, choosing INTEGER because one student’s height happened to be 2.0 would lose the requirement for fractional values. If a category uses one code character, a general text field may be less precise than the stated CHARACTER requirement.
Different fields in one record may have different types. That differs from an array, whose elements share one declared type.
| Field | Example | Suitable basic type |
|---|---|---|
| StudentCode | 0072 | Text/alphanumeric |
| StudentName | Maya Chen | Text/alphanumeric |
| TutorGroupCode | A | Character |
| PermissionReceived | TRUE | Boolean |
| SessionsAttended | 72 | Integer |
| HeightMetres | 1.72 | Real |
| DateOfBirth | 2009-05-14 | Date/time |
03 | TEXT OR ALPHANUMERIC STORES CHARACTERS
Text/alphanumeric stores character sequences, including letters, digits, spaces and permitted symbols. It suits names, addresses, titles and identifiers.
A book code B003 contains a letter and digits, but text can also contain digits only. StudentCode “0072” should retain its leading zeros, which are part of its identifier. Storing it as an ordinary number would not preserve that representation automatically.
A telephone number is also usually text: it may include a plus sign, leading zero or spaces, and it is not an amount you should add to another telephone number. Data types follow purpose, not the label “number” in everyday speech.
04 | CHARACTER IS FOR ONE CHARACTER
Character stores one character, such as a single category code A, B or C. A character may be a letter, digit or symbol; it is not limited to alphabetic letters.
If TutorGroupCode is always one letter, CHARACTER suits the requirement. If the group is “A1” or “Blue”, a text/alphanumeric type is needed instead.
CHARACTER does not prove the chosen character is permitted. The code X is one character, but a separate allowed-value rule may reject it if only A, B and C are valid.
| Requirement | Choice | Reason |
|---|---|---|
| One letter identifying a group | Character | Exactly one character |
| Two-character code A1 | Text/alphanumeric | More than one character |
| Full group name Blue | Text/alphanumeric | A sequence of characters |
05 | BOOLEAN STORES TWO STATES
Boolean stores TRUE or FALSE. It suits a genuinely two-state fact such as whether permission has been received.
A database interface might display a checkbox or a Yes/No label, but the stored logical meaning is the two states. Do not confuse the BOOLEAN value TRUE with the text “TRUE”.
A field for an approval process with Pending, Approved and Rejected has three states. One BOOLEAN cannot distinguish all three without an additional design convention or extra information. Use a suitable categorical text field for that requirement.
06 | INTEGER STORES WHOLE-NUMBER AMOUNTS
Integer stores whole numbers. Zero and negative values are included in the type; an application’s validation rules decide which are allowed in a particular field.
CopiesOwned, SessionsAttended and PointsEarned commonly use INTEGER because fractional counts do not suit their stated meaning. A copy count 2.5 should not be accepted as an ordinary whole-number count.
Choosing INTEGER does not make every integer sensible. CopiesOwned equal to minus three is still an integer but breaks a non-negative-stock requirement. Add a range check where needed.
07 | REAL STORES NUMERIC VALUES THAT CAN BE FRACTIONAL
Real stores numeric values that may have a fractional part. It suits measurements such as HeightMetres and calculated values such as AverageScore.
A real field can also hold a whole-number-valued amount such as 2.0. You choose REAL because fractions are permitted by the requirement, not because every stored value must contain a nonzero fractional part.
Do not choose INTEGER for a mean merely because the first example mean is five. Another valid set of scores may produce 4.5.
| Field | Expected values | Choice |
|---|---|---|
| CopiesOwned | 0, 1, 2, 3 | Integer |
| TemperatureCelsius | -2.5, 0.0, 18.7 | Real |
| AverageScore | 5.0 or 4.5 | Real |
08 | PRICES AND REPRESENTATION
For Cambridge’s basic database categories, REAL is the suitable choice for a price that can include fractions, such as 4.50. The price is an amount used in calculations, unlike an identifying code.
Displaying 4.50 rather than 4.5 is formatting: both represent the same numeric value. A formatting rule does not change the field from REAL to text.
Real-world database products may offer fixed-precision decimal or currency types for exact monetary calculations. That implementation detail does not change the basic-type answer required here: choose REAL when the question’s allowed categories are the syllabus list.
09 | DATE/TIME STORES A CALENDAR OR CLOCK VALUE
Date/time stores dates, times or combined timestamps according to the field’s requirement and the database system. It suits DateOfBirth, BookingDate and EventStartTime.
A suitable date/time field lets the system compare chronological values and perform supported date operations. Text containing a date-looking string is still text unless the system interprets and stores it as a date/time value.
The displayed format may differ from the stored representation. May 14, 2009 and 2009-05-14 can display the same date. Use clear formats when communicating dates; 03/04/2026 is ambiguous without a convention.
10 | DATE VALIDITY IS MORE THAN A STRING PATTERN
A value following a pattern such as YYYY-MM-DD may still be an impossible date. 2026-02-30 has the expected character arrangement but no such calendar day.
The database’s date parsing and validation should establish a valid calendar value. An application may also need extra rules: a date of birth should not be in the future, while a booking may have to be within an allowed term.
Neither a date type nor a valid calendar date proves the date matches the real person or event. Accuracy still depends on correct collection and verification.
11 | TRY CHOOSING TYPES FROM REQUIREMENTS
Select a field requirement and propose its type. Read the reason after checking. The choices are based on the requirement, so a digit-only code can correctly use text while a measured amount uses a numeric type.
12 | TYPE AND VALIDATION WORK TOGETHER
A type supplies a broad category. Validation adds the application’s restrictions. INTEGER CopiesOwned might require range zero to twenty; text BookID might require B followed by three digits.
Character can hold one symbol, but a permitted-value check may allow only A, B or C. Boolean gives two logical states, but the application must decide exactly what TRUE means.
A field can have the right type yet contain incorrect information. A plausible height 1.72 can be stored as REAL even if the actual measurement is 1.68.
| Field type | Additional requirement | Useful check |
|---|---|---|
| Integer count | 0 to 20 inclusive | Range |
| Text identifier | B plus three digits | Format and presence |
| Character group | A, B or C | Allowed values |
| Date/time birth date | Valid date, not in future | Calendar validity and contextual limit |
13 | BUILD A DATA DICTIONARY
A data dictionary describes the fields and their properties. It records consistent meanings so later users and programmers do not need to guess what a column stores.
The outline below fits a simple school club catalogue. It states names, meanings, types and additional rules. Sample values help illustrate a field but do not replace its full requirement.
| Field | Meaning | Basic type | Extra rule |
|---|---|---|---|
| ClubID | Identifying code such as C01 | Text/alphanumeric | Required; stated format |
| ClubName | Display name | Text/alphanumeric | Required; length up to 40 |
| CategoryCode | One category letter | Character | Allowed A, B or C |
| MaximumPlaces | Capacity count | Integer | 1 to 30 inclusive |
| Fee | Numeric fee amount | Real | Non-negative |
| MeetingDate | Scheduled date | Date/time | Valid permitted date |
| Active | Whether currently active | Boolean | TRUE means active |
14 | JUSTIFY YOUR CHOICE
A good answer names the type and links it to the required data. “INTEGER because copies are counted in whole units” is more useful than “INTEGER because it is a number”.
For an identifier: “Text/alphanumeric preserves the required leading zeros and symbols because this value is a code, not an amount used in arithmetic.”
For BOOLEAN: “PermissionReceived has only two defined states, received or not received.” For date/time: “BookingDate needs chronological comparison, so a date/time field represents the calendar value appropriately.”
When requirements are incomplete, state a reasonable assumption. One-letter grades suit CHARACTER; multi-character grades such as A* need text/alphanumeric.
15 | CHECK THE MEANING BEFORE THE APPEARANCE
Ask whether the field is a character sequence, one character, a two-state fact, a whole-number amount, a possibly fractional amount or a calendar/clock value. Then add relevant validation rules.
Avoid treating phone numbers as arithmetic amounts, treating every Yes/No-looking display as arbitrary text, or assuming every status has only two states. Do not confuse formatted appearance with the underlying type.
The next lesson covers primary keys: choosing a field that identifies each record uniquely.