IB COMPUTER SCIENCE | A3.2.3 Relational Database Data Types

Lesson objective

Outline common relational data types, the importance of consistency and the effects of inappropriate type choices.

Learn

A3.2.3 | DATA TYPES IN RELATIONAL DATABASES

01 | WHAT DOES THE VALUE MEAN?

A school database records a student ID, club capacity, membership fee and meeting date. They may all appear as characters on a screen, but the database needs to handle them differently. You calculate with a capacity, compare dates chronologically and preserve the leading zeros in an identifier.

A data type specifies the kind of value a column represents and helps determine its permitted values, representation and supported operations. It is a rule about meaning and handling, not just what a value looks like when displayed.

For example, 00127 can be an identifier rather than a quantity. Storing it numerically may keep the value 127 while losing the original five-character representation. Conversely, storing quantities as text can make arithmetic and ordering harder.

This lesson outlines common relational data types, explains why consistent choices matter and traces the effects of choosing incorrectly. Exact type names, sizes and conversion behaviour vary between DBMSs.

02 | THE MAIN TYPE FAMILIES

FamilyTypical SQL-style examplesUseful purpose
Whole numbersINTEGER, SMALLINT, BIGINTCounts and suitable numeric identifiers
Exact decimalsDECIMAL(p,s), NUMERIC(p,s)Exact decimal amounts and quantities within chosen precision
Approximate numbersREAL, DOUBLE PRECISIONMeasurements and scientific calculations accepting approximation
Characters/textCHAR(n), VARCHAR(n), TEXTNames, codes, contact details and descriptions
Logical valuesBOOLEAN or a supported equivalentA true/false fact
Dates and timesDATE, TIME, TIMESTAMP or equivalentsCalendar values and events
Binary contentBLOB, BYTEA or equivalentsFile bytes and other binary values

These are families, not one universal product specification. A particular system may use different names or have different typing rules. Start with the required meaning, then select a supported implementation.

Types can be combined with constraints. An integer type does not by itself mean a capacity must be positive or that a row identifier must be unique.

03 | INTEGER: WHOLE-NUMBER QUANTITIES

An integer represents a whole number without a fractional component. Club capacity, number of copies and an item count are natural examples when fractional values have no meaning for the requirement.

Integer variants provide different supported ranges. Selecting too small a range can reject future valid values or cause overflow-related problems under the relevant system’s rules. Choosing a very large representation unnecessarily can also have storage or processing implications.

Do not assume a negative number is impossible merely because a type is integer. A separate constraint may require capacity to be at least zero. Nor does an integer column automatically generate unique IDs; identity generation and key constraints are separate choices.

A quantity requiring a fraction should not be forced into an integer without an explicit representation rule, such as deliberately storing money in a defined smallest currency unit.

04 | EXACT DECIMAL: PRECISION AND SCALE

An exact decimal type represents decimal values within its declared precision and scale without binary floating-point approximation of those stored decimal values. It is commonly useful for amounts where decimal rounding must follow specified rules.

In a common DECIMAL(p,s) convention, precision p is the total number of decimal digits and scale s is the number after the decimal point. DECIMAL(8,2) therefore permits six digits before the point and two after it in this conventional example.

The positive maximum in that example is 999999.99. The sign and decimal separator do not count as extra precision digits. This is not the same as allowing eight digits before the point.

Input with more fractional digits may be rounded or rejected according to the DBMS and operation, and arithmetic results need appropriate rounding policy too. Exact decimal storage does not make every financial calculation correct automatically.

05 | APPROXIMATE NUMBERS: REAL AND FLOATING POINT

Floating-point types represent numbers approximately using a finite set of values. They are useful for scientific and measured data where an appropriate approximation and range suit the requirement.

Many decimal fractions have no exact finite binary floating-point representation. An expression involving 0.1 and 0.2 can therefore reveal a tiny representation difference. This is not a claim that every floating-point value is inaccurate: many values are exactly representable.

Use an exact decimal representation, or another deliberately designed exact approach, when exact decimal amounts are required. For sensor measurements, approximate types may be appropriate, with suitable precision and comparison tolerances.

The demonstration uses the browser’s binary floating-point arithmetic, not a query against a particular database. It shows the idea rather than claiming all DBMS result formatting is identical.

Check how representation differs from the intended decimal arithmetic.

06 | CHAR AND VARCHAR: TEXT IS NOT JUST LETTERS

CHAR(n) commonly represents fixed-length character data, with padding behaviour depending on the system. VARCHAR(n) commonly permits variable-length text up to a stated limit. Other text types can support larger descriptions under their own rules.

A name, email, postal code or telephone number can contain digits without being a numeric quantity. Telephone values can also need a plus sign, separators or an extension. A text representation can preserve the required form.

Choose length from the actual requirement and expected data. A short field can reject a valid name; some conversions or systems may truncate excess input, losing information. Do not assume every database silently truncates or every database always raises the same error.

Character support also depends on encoding and collation settings. Text length in characters and storage in bytes are not necessarily identical, especially with Unicode.

07 | PRESERVE THE IDENTIFIER YOU ACTUALLY NEED

If StudentID is defined as exactly five digits and the leading zeros are meaningful, 00127 is a five-character identifier. Storing it as a number ordinarily preserves only 127 as the numeric value. Display formatting can add zeros later, but that requires a known rule and does not preserve every arbitrary original input.

If a future ID can contain letters, a numeric-only representation becomes an even poorer fit. A text type plus an appropriate format constraint can express that requirement.

The activity below uses fixed examples to show the representation difference. It does not assign IDs, edit records or determine uniqueness.

08 | BOOLEAN: A DEFINED TRUE/FALSE FACT

A Boolean type represents logical true/false values. HasParentalConsent can be suitable if the requirement genuinely asks for a binary fact. Accepted literal spellings and storage details vary by DBMS.

A nullable Boolean may also be unknown or absent through NULL. That is different from false: “consent is not recorded” is not necessarily the same state as “consent was refused”. Decide whether the application needs to distinguish them.

Do not use one Boolean for a status with several meaningful states such as Pending, Approved and Withdrawn. A suitable constrained text/status representation or related status table can model that richer requirement.

BOOLEAN does not automatically mean one physical bit of database storage. The logical values and the product’s storage implementation are separate questions.

09 | DATE, TIME AND TIMESTAMP

A DATE represents a calendar date without requiring a time of day. A TIME represents a time of day. A TIMESTAMP or similar datetime type represents a date and time under the system’s supported semantics.

BirthDate is normally a date, while a recorded booking event may need a date/time. Storing an event across time zones also requires a deliberate policy about instants, offsets and display zones; a timestamp type does not universally store the original named zone.

Typed values enable appropriate date operations and checks. Free-form text such as 03/04/2026 can be ambiguous without a format rule. A standardised representation such as 2026-04-03 improves clarity for an example.

A displayed format is not the same as internal storage. Changing a date’s screen format does not necessarily change the stored date value or type.

10 | BINARY DATA AND SPECIALISED TYPES

A binary type stores byte sequences, such as the content of an image or document. Names such as BLOB or BYTEA depend on the DBMS. It is not simply a column containing the text “0101”.

A system may instead store a file reference in a text column and keep the bytes in a separate storage service. These are different designs with different backup, access and consistency responsibilities. A reference is not the image itself.

Some relational systems provide specialised types such as UUIDs, enumerations, spatial values and JSON. JSON is typically semi-structured and needs suitable validation and querying; availability and behaviour vary by product.

For this objective, know the main families and why a type fits a value. You do not need to memorise every vendor’s specialist catalogue.

11 | NULL IS NOT A DATA TYPE OR A DEFAULT ZERO

NULL represents missing or unknown information under database rules. It is not itself the general data type “unknown”, nor automatically the same as zero, false or an empty string.

If Fee is NULL, its amount has not been supplied under that interpretation. If Fee is 0.00, a known zero amount has been recorded. Confusing the two can alter summaries and decisions. SQL expressions and aggregates have particular NULL rules that later query lessons develop.

A NOT NULL constraint requires a value. A default can supply a chosen value when the applicable insertion rules use it, but an inappropriate default may hide genuinely missing information.

Document the meaning of missing values rather than filling every blank with zero just to make the table look complete.

12 | DATA TYPE CONSISTENCY ACROSS RECORDS AND RELATIONSHIPS

A consistent column represents the same kind of fact under compatible rules for every row. Storing one fee as a decimal and another as free-form “about twenty” makes reliable calculation difficult.

Referencing and referenced key columns also need compatible definitions for the DBMS to enforce relationships correctly. Mixing a five-character code with an incompatible numeric reference can lose zeros or require conversion rather than express the intended identity.

Consistency does not mean every column has the same type. StudentName and Capacity should differ. It means each attribute and relationship uses a suitable coherent representation.

Import processes and applications must respect the schema too. Implicit conversion may hide mistakes, reject values or add query cost; do not rely on undocumented coercion to repair inconsistent design.

13 | SORTING CHANGES WHEN MEANING CHANGES

For a numeric quantity, 2 is less than 10, which is less than 100. In a simple character-by-character text ordering, the strings “10” and “100” can appear before “2” because their first character is compared as text. Actual text order depends on collation.

Dates stored as inconsistent day/month strings can likewise sort in a way that does not match calendar order. A consistently formatted ISO-style text representation can have useful ordering properties, but that does not provide every operation and constraint of a date type.

The comparison below states the ordering rule explicitly. It illustrates a type-related mistake without pretending every DBMS collation behaves identically.

14 | TYPES AND VALIDATION ARE COMPLEMENTARY

A type rejects or interprets values according to its own rules, but often does not express the full requirement. INTEGER permits whole numbers; a check constraint may be needed to require Capacity from 1 to 40.

VARCHAR(5) can hold many five-character strings, not just the intended two uppercase letters and three digits. A format constraint or server-side validation adds that requirement. A primary key separately enforces unique non-null identification.

A syntactically valid date can still be the wrong birth date for a person. Verification and domain-specific checks may be needed. Type consistency improves usable structure without proving every fact true.

The next lesson combines type decisions with keys and well-defined relational tables. Keep each rule’s purpose distinct.

15 | CHOOSING TYPES FOR THE CLUB SYSTEM

AttributeSuitable starting choiceReason and additional rule
StudentIDText identifier, for example VARCHAR(5)Preserve required code form; add key/format constraints
StudentNameSuitable Unicode-capable character/text typeAllow realistic names; choose length from requirements
CapacityINTEGERWhole-person count; add permitted range
MembershipFeeExact decimal, for example DECIMAL(8,2)Chosen decimal range/scale; add currency and rounding policy
MeetingDateDATEA calendar date with appropriate operations
BookedAtSuitable timestamp/datetime representationRecord event time with an explicit time-zone policy
HasConsentBOOLEAN where binary meaning fitsDecide whether unknown is permitted
PhotoContentSupported binary type, or a designed external-file referenceDistinguish file bytes from a reference and protect both

There can be more than one valid implementation. For example, deliberately storing amounts as integer minor units can also be exact if the unit is explicit and sufficient. Justify the choice rather than treating one label as mandatory in every system.

16 | EFFECTS OF A POOR TYPE CHOICE

Poor choicePossible effect
Quantity stored as arbitrary textAwkward arithmetic, lexical ordering or conversion failures
Meaningful zero-prefixed code stored numericallyOriginal identifier representation can be lost
Too-short text or too-small numeric rangeRejected valid data, failed imports or lossy conversion under applicable behaviour
Approximate float used where exact decimal rules are requiredUnexpected rounding/comparison results
Ambiguous free-form date textMisinterpretation, invalid values or incorrect chronological processing
Incompatible related key representationsFailed constraints, conversion problems or incorrect matches
Zero used instead of genuinely missing informationMisleading calculations and business decisions

The exact failure mode depends on the DBMS and operation. A good outline identifies the risk and its cause rather than asserting one universal conversion behaviour.

17 | MATCH THE TYPE TO THE REQUIREMENT

Choose the type family that best fits the stated meaning. The questions specify the requirement so that a justified choice is possible; additional constraints and implementation details would still be needed.

18 | OUTLINE THE TYPE AND ITS PURPOSE

For outline, give a brief account of the main points. State the type family, what it represents and one relevant use. Add the consistency or wrong-choice consequence where the question requires it.

For example: “An integer represents whole-number values such as club capacity. An exact decimal type represents chosen decimal amounts such as a fee, while a floating-point type provides approximate numeric representation suitable for some measurements. Text stores names or identifiers whose written form matters.”

Then add: “Date/time types support calendar and event values, Boolean represents a defined true/false fact, and binary types store bytes. Consistent compatible types support correct queries and relationships; poor choices can lose information, misorder results or require unreliable conversion.”

Keep storage details conditional on the implementation. Type names alone do not supply uniqueness, real-world truth, access control or every validation rule.

MATCH THE TYPE FAMILY TO ITS PURPOSE

Terminology

Terminology

Data type

A definition of a value’s kind and relevant representation/operation rules.

Integer

A whole-number representation without a fractional component.

Numeric range

The minimum and maximum values supported by a representation.

Exact decimal

A type representing stored decimal values exactly within chosen limits.

Precision

In common DECIMAL(p,s) usage, the total decimal digits.

Scale

In common DECIMAL(p,s) usage, decimal digits after the point.

Floating point

An approximate numeric representation with finite representable values.

Rounding

Applying a rule to represent a value with limited available precision or scale.

CHAR

A commonly fixed-length character type with product-specific padding rules.

VARCHAR

A commonly variable-length character type with a defined limit.

Text

Character data, with supported limits and behaviour depending on the DBMS.

Unicode

A character repertoire/standard supported through appropriate database encoding and types.

Character encoding

Rules representing characters as stored or transmitted units such as bytes.

Collation

Rules used for text comparison and ordering.

Identifier

A value identifying an instance; its appearance may matter more than arithmetic.

Boolean

A logical true/false type, with missing values handled separately if nullable.

DATE

A calendar date type without a required time of day.

TIME

A time-of-day type under the system’s supported rules.

TIMESTAMP

A date/time representation whose exact zone semantics depend on its type and DBMS.

Time-zone policy

Rules defining how instants, offsets and display zones are handled.

Binary type

A type storing byte sequences rather than interpreting them as ordinary text.

BLOB

A DBMS term for binary large-object content, with implementation-specific behaviour.

File reference

A stored location/identifier for external content, not the content bytes themselves.

NULL

Missing or unknown information under database rules, distinct from ordinary zero/false values.

NOT NULL

A constraint requiring a non-null value.

Default value

A value supplied under applicable insertion rules when no explicit value is provided.

Type consistency

Coherent suitable representations across an attribute and related data.

Type conversion

Changing a value’s representation or interpretation to another type.

Constraint

An additional rule such as a range, reference or key requirement.

Data dictionary

Documentation of attribute meaning, types, limits and related rules.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which match type families and uses?
2. Which describe DECIMAL(8,2) under the stated convention?
3. Which describe floating point?
4. Which describe character choices?
5. Which distinguish Boolean and missing data?
6. Which describe dates/times?
7. Which are consistent design statements?
8. Which are poor-choice consequences?
9. Which distinguish binary content and references?
10. Which make a good outline?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Outline integer and exact decimal types.

2. Outline floating point and a suitable use.

3. Explain DECIMAL(8,2) in the stated convention.

4. Outline CHAR and VARCHAR.

5. Explain why 00127 may need text.

6. Outline Boolean and distinguish NULL.

7. Outline date and time families.

8. Outline binary storage and an external-file alternative.

9. Explain data type consistency.

10. Explain why type choice is not full validation.

11. Describe three consequences of wrong choices.

12. Outline a suitable set of club fields.

OUTLINE IT | TYPES FOR A CLUB DATABASE

Outline suitable types for StudentID, StudentName, Capacity, MembershipFee, MeetingDate and HasConsent. State the required meaning of each, add one relevant constraint, and explain a consequence of one poor alternative. Check that related key columns use compatible definitions and that missing values are not confused with known zero or false values.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for A3.2.3 Relational Database Data Types is coming soon. Complete the matching task and written questions in your exercise book.