Be able to set sub-types of numeric data including percentage, number of decimal places

Topic 18: Databases – Setting Numeric Sub‑types

Learning Objective

Be able to set sub‑types of numeric data, including percentage and the number of decimal places, when designing or modifying a database table.

Why Sub‑types Matter

Sub‑types control how numeric values are stored, displayed and validated. Choosing the correct sub‑type ensures:

• Consistent data entry (e.g., users cannot enter “abc” in a percentage field).
• Accurate calculations (e.g., rounding to the required number of decimal places).
• Clear presentation of reports and forms.

Common Numeric Sub‑types in IGCSE ICT

• Integer – whole numbers without decimal places.
• Decimal (Fixed‑point) – numbers with a defined number of decimal places.
• Floating‑point – numbers that can have a variable number of decimal places.
• Percentage – values displayed as a percent sign, often stored as a decimal fraction.

Setting a Percentage Sub‑type

Most database packages (e.g., Microsoft Access, LibreOffice Base) allow you to specify a field as a Number with the sub‑type Percentage. The field stores the value as a decimal fraction but displays it with a percent sign.

1. Open the table in Design \cdot iew.
2. Select the field you want to store percentages.
3. In the Data Type column choose Number.
4. In the Field Size (or Sub‑type) dropdown select Percentage.
5. Optionally set the Format property to control the number of decimal places (e.g., 0.00%).
6. Save the table design.

Example

If a user enters 85 in a percentage field, the database stores it as 0.85. When displayed, it appears as 85%. Calculations use the stored decimal value, so a query that multiplies a price by a discount percentage works correctly:

\$\text{Discounted Price}= \text{Original Price} \times (1 - \text{Discount\%})\$

For a price of $120 and a discount of 15%, the calculation is:

\$120 \times (1 - 0.15) = 120 \times 0.85 = 102\$

Specifying the Number of Decimal Places

When a field must retain a fixed number of decimal places (e.g., monetary values), set the sub‑type to Decimal and define the format.

1. Open the table in Design \cdot iew.
2. Select the numeric field.
3. Set Data Type to Number.
4. Choose the sub‑type Decimal (or Fixed‑point depending on the software).
5. In the Format property, enter a pattern that includes the required decimal places, e.g., 0.00 for two places.
6. Save the design.

Example

A field for “Unit Price” is set to two decimal places. Entering 3.5 is automatically displayed as 3.50. Calculations such as total cost use the stored value 3.50:

\$\text{Total Cost}= \text{Unit Price} \times \text{Quantity}\$

If the quantity is 4, the total cost is:

\$3.50 \times 4 = 14.00\$

Comparison of Numeric Sub‑types

Practice Questions

1. In a table, you need a field to store exam scores out of 100 and display them as percentages with one decimal place. List the steps to set this field correctly.
2. A field for “Tax Rate” must allow values like 5.5% and 7.25%. What sub‑type and format should you choose?
3. Explain the difference between how a Decimal sub‑type and a Floating‑point sub‑type store the value 123.456.
4. When a field is set to the Percentage sub‑type, what value is actually stored in the database if the user enters 12.5%?