# Database Terminology: Tables, Records & Field Data Types

- **Track:** Cambridge IGCSE™ Computer Science (0478 / 0984) (CAMBRIDGE IGCSE 0478)
- **Module:** Section 9: Relational Databases & SQL
- **Estimated Duration:** 10 minutes
- **Access:** Open Theory (All-Access for Interactive IDE & Autotests)

---

## Theory & Conceptual Background

A table holds records of the same kind. A record is one row; a field is a named category of information represented by a column. Consistent field names and appropriate types make data easier to query and validate.

## Worked single-table design

A lending club needs MemberID, Name, PostalCode, BooksBorrowed and FeesPaid. MemberID identifies one record. Name and PostalCode use text: a postal code can contain letters and leading zeroes, so it is not a quantity for arithmetic. BooksBorrowed is an integer. FeesPaid is Boolean.

Other useful types include real for fractional numbers, date/time for dates or times, and character for a single character. Select types according to use, not just whether a sample contains digits.

## Independent design

Design a single table for equipment loans, with one record per loan. Choose fields for the borrower, item, loan date and return status. Name a unique key and justify each type. A well-designed relational database can reduce duplication; it does not automatically eliminate duplicate facts or update problems.

---

## Interactive Exercises & Autotest Specifications

### Exercise 1: Exam-Style Check: Selecting Appropriate Database Field Data Types (Multiple choice, 25 XP)

**Instructions:**
A database administrator is designing a patient table for a medical clinic. The table includes a field for the patient's mobile telephone number (e.g., `"07123456789"`).

Which data type should be selected for the `TelephoneNumber` field, and what is the primary technical rationale?

**Options:**
- **A**: Integer, because telephone numbers consist purely of numerical digits.
- **B**: Text / Alphanumeric, because it preserves leading zeros and the values are not involved in mathematical calculations.
- **C**: Currency, because phone numbers have a fixed number of digits.
- **D**: Boolean, because each digit is a binary signal.


**Autotest Expectations:**
- **Correct field data type and rationale selected**: `selection_match` -> Expected: `opt_text_zeros`

### Exercise 2: Exam-Style Question: Identify Database Records and Field Types (Fill in blank, 35 XP)

**Instructions:**
A table named `StudentScores` contains the following structure:

- `StudentID` (e.g. 'ST1042')
- `Score` (e.g. 88.5)
- `Passed` (e.g. TRUE)
- `ExamDate` (e.g. '2026-06-15')

The table currently stores details for 25 individual students.

1. The number of records in this table is: [blank_1]
2. The most suitable Cambridge data type for `Score` is: [blank_2]
3. The most suitable Cambridge data type for `Passed` is: [blank_3]


**Starter Code:**
```text
{"prompt":"Identify the database properties:","template":"1. Number of records: [blank_1]\n2. Data type for Score: [blank_2]\n3. Data type for Passed: [blank_3]","word_bank":["25","4","REAL","INTEGER","BOOLEAN","TEXT","DATE"],"blanks":[{"id":"blank_1"},{"id":"blank_2"},{"id":"blank_3"}]}
```

**Autotest Expectations:**
- **25 records**: `text_exact` -> Expected: `25`
- **Score is REAL**: `text_exact` -> Expected: `REAL`
- **Passed is BOOLEAN**: `text_exact` -> Expected: `BOOLEAN`

### Exercise 3: Independent Practice: Design fields (Fill in blank, 30 XP)

**Instructions:**
Work out each answer before submitting. A postal code may contain letters and leading zeroes. Choose TEXT or INTEGER: A whole number of borrowed books uses INTEGER or BOOLEAN? A paid/not-paid flag uses BOOLEAN or TEXT?


**Starter Code:**
```text
{"prompt":"Independent Practice: Design fields","template":"1. A postal code may contain letters and leading zeroes. Choose TEXT or INTEGER: [blank_1]\n2. A whole number of borrowed books uses INTEGER or BOOLEAN? [blank_2]\n3. A paid/not-paid flag uses BOOLEAN or TEXT? [blank_3]","blanks":[{"id":"blank_1"},{"id":"blank_2"},{"id":"blank_3"}],"word_bank":["TEXT","INTEGER","BOOLEAN","REAL","DATE","CURRENCY"]}
```