Identify fields and records in a single-table database

IGCSE Computer Science (0478) – Databases: Identifying Fields and Records (Single‑Table Databases)

Learning Objectives

• Define a single‑table database and explain why the IGCSE syllabus limits design to one table.
• Identify fields (columns), choose the most appropriate basic data type for each, and state a simple validation rule.
• Identify records (rows) and describe how they relate to fields.
• Explain the purpose of a primary key and select a suitable one.
• Write basic SQL SELECT statements to read, filter, sort and summarise data from a single‑table database.
• Apply common validation rules to keep data reliable.

1. What Is a Single‑Table Database?

A single‑table database stores all information about one entity (e.g., students, books, products) in a single rectangular grid. The grid consists of:

• Fields (columns) – vertical slices that represent attributes of the entity.
• Records (rows) – horizontal slices that hold a complete set of values for one instance of the entity.

The Cambridge IGCSE specification restricts the design to one table so that candidates first master the fundamentals of data organisation before tackling relationships and multiple tables.

2. Fields (Columns)

• A field describes one attribute of the entity (e.g., StudentID, FirstName).
• All values in a field must be of the same basic data type. The data type determines the amount of storage required and the operations that can be performed.
• Choosing the correct data type and applying validation prevents entry errors.

2.1 Basic Data Types (Cambridge)

3. Records (Rows)

• A record is a complete set of field values that together describe one instance of the entity.
• Every record contains exactly one value for each field (unless the field permits NULL – “no data”).
• Records are added, modified, or deleted as whole rows.

4. Primary Key

• A primary key uniquely identifies every record in the table.
• Good primary‑key criteria:
  • Unique – no duplicate values.
  • Not NULL – every record must contain a value.
  • Stable – the value never changes.
  • Preferably a single field of a simple data type (e.g., INTEGER).

5. Example – Student Information Table

The table below stores details of three students. The first row (header) defines the fields; each subsequent row is a record.

5.1 Field‑to‑Data‑Type Mapping & Validation

6. Basic SQL for a Single‑Table Database

All queries use SQL (Structured Query Language). The table name in the examples is Students.

6.1 Retrieve All Records

SELECT * FROM Students;

6.2 Retrieve a Specific Record Using the Primary Key

SELECT FirstName, LastName, Score
FROM Students
WHERE StudentID = 102;

6.3 Filter, Sort and Use an Aggregate Function

SELECT FirstName, Score
FROM Students
WHERE Score > 80
ORDER BY Score DESC;

6.4 Count the Number of Records

SELECT COUNT(*) AS TotalStudents
FROM Students;

6.5 Calculate an Average

SELECT AVG(Score) AS AverageScore
FROM Students;

7. Simple Validation Rules (Optional but Useful)

• Numeric fields – accept only digits; optionally enforce a range (e.g., Score 0‑100).
• Date fields – enforce a valid calendar date and a sensible range (e.g., birth dates not in the future).
• Text fields – limit length, restrict to alphabetic characters, or require capitalisation.
• Validation can be implemented:
  • Directly in the database (CHECK constraints).
  • In the front‑end program that collects the data.

8. Practice Questions

1. Identify the primary key in the example table and explain why it is the best choice.
2. How many records are stored? List all fields.
3. Assign an appropriate data type (from Section 2) to each field and give one validation rule for each.
4. Write an SQL statement that lists StudentID and Score for all students who scored less than 80, ordered from lowest to highest score.
5. Using SQL, calculate the average score of all students and display it with the column heading AverageScore.

9. Common Mistakes to Avoid

• Mixing up fields and records – fields are vertical columns; records are horizontal rows.
• Choosing a non‑unique primary key – duplicates break the uniqueness rule.
• Storing mixed data types in one field – e.g., numbers and text together.
• Neglecting validation – leads to impossible data (e.g., a score of 150).
• Omitting the WHERE clause when only a single record is required – this returns the whole table.

10. Review Checklist – Single‑Table Database (IGCSE 0478)

11. Summary

• Fields define the attributes of the entity; each field has a single, appropriate data type.
• Records store one complete set of values for those attributes.
• The primary key uniquely identifies every record and must be stable and non‑NULL.
• Basic SQL SELECT statements let you read, filter, sort, and summarise data from a single‑table database.
• Applying validation rules keeps the data reliable and prevents entry errors.