I85PREP
⌘ K
GCE A/L General Information Technology study materials
GCE A/L General Information Technology study resources

Library Cards That Begin With Zero

GCE A/L General Information Technology · Put your knowledge to work in context.

All case studies

Library Cards That Begin With Zero

A school library issues borrower cards printed with a 10-digit number, for example 0045123789. The barcode encodes all ten digits. When a card is scanned at the desk, the library software reads the barcode, converts the text to a number, and stores it in a 32-bit signed integer column of the borrower table. The software then searches for that integer and displays the borrower. Since an upgrade, cards beginning with 0 sometimes open the wrong borrower record, and a few newer cards are rejected with 'invalid number'. The vendor says the column type is the problem but proposes simply changing it to text. The librarian has 1,850 active borrowers and cannot reprint all cards this term. Some older records were entered manually, without leading zeros. The school also wants to print a short code on each card for offline backup.

1. Explain why the current storage produces wrong borrower matches and rejections. Use 0045123789 in your explanation and refer to the signed 32-bit limit.

Read the worked answer

Treating a card number as an integer loses information and imposes a numeric range.

  • Leading zero lost: the barcode text 0045123789 becomes the integer 45,123,789. An older manual record for a different printed number such as 045123789 also becomes 45,123,789, so the lookup can return the wrong borrower.
  • Rejections: a signed 32-bit integer holds at most 2,147,483,647. Any card number above that is outside the column's range and is rejected. Since 10-digit numbers run up to 9,999,999,999, most numbers beginning with 2 to 9 exceed the limit; only about one fifth of all 10-digit strings are representable.
  • Not an arithmetic value: the card number is an identifier, so addition, subtraction and numeric ordering are meaningless. The conversion is the root cause, not a data-entry error.

2. Evaluate the vendor's proposal to change the column to text. What representation and constraints would you recommend, and why?

Read the worked answer

Changing to text removes the range limit and preserves leading zeros, but plain text alone is not enough.

  • Text will store 0045123789 and 45123789 as different values, so old manual records still will not match unless they are normalised to the same 10-character form.
  • Without a length check, 0045123789 , 0045123789X or 45123789 can enter and create near-duplicates. Comparisons and indexes then depend on padding and collation rules.
  • Recommended: a fixed-width CHAR(10) column (or VARCHAR(10) with a check) with CHECK that the length is 10 and every character is a digit, plus a UNIQUE constraint. Pad all values to ten digits on input and strip spaces before validation. If future cards may contain letters, use VARCHAR(12) and a suitable pattern check instead.
  • If a check digit is added later, keep it in the same code or a separate validated column; do not encode it into an integer. The short offline backup code should be a separate unique column so it can be printed without changing the main identifier.

3. Outline a migration plan that avoids reprinting cards and preserves existing loans.

Read the worked answer
  1. Freeze card issue and edits. Export the current table and count the 1,850 active borrowers.
  2. Add a new nullable card_no CHAR(10) column; do not drop the integer column.
  3. Build the mapping from a source of truth: scan the barcodes or use the card printer's file. Do not rely only on LPAD(integer,10,'0'), because a value may correspond to more than one printed card. Where no source exists, generate the padded value only after checking it is unique.
  4. Detect collisions before updating: group by the old integer and by the padded value, and list any group with more than one borrower. Resolve these manually against physical cards or loan records.
  5. Load into a staging table, then update in one transaction. Run validation queries: no nulls, all values 10 digits, count still 1,850, and each active loan links to one borrower.
  6. Switch the application to read card_no, test scan/search/return, then add the unique constraint and keep a read-only copy of the old column for one term. Reprint only damaged or missing cards.