Database Fundamentals · Cambridge syllabus 9.1, 9.2 · Lesson 1 of 2
Database Concepts
A table organises data into records and fields. Each row is one record and each column is one field with a defined meaning and type. Choose a structure that stores one kind of entity consistently, and give fields validation, such as a range or presence check, so that only sensible values can be entered.
By the end, you can
- Understand the purpose of a database.
- Understand records and fields.
- Represent data in a single database table.
- Identify suitable fields for a given data-storage requirement.
- Apply validation to the fields of a table.
Single-table databases
A database is an organised collection of data, structured so that it can be searched, sorted and updated reliably. The alternative — the same data spread over separate files and spreadsheets — leads to the same fact being stored in several places, and to those copies disagreeing.
A single-table database is one table. A record is one row: everything known about one item — one pupil, one book, one product. A field is one column: one property, held for every record — surname, or price, or date of birth. One value sits where a record meets a field.
Designing a table means deciding what one record represents and then which fields it needs. For a library, one record is one book, and the fields might be an identifier, title, author, category, publication year, whether it is on loan and the due date. Every field must be a property of that one thing: a borrower’s home address is not a property of a book.
Each field should hold one indivisible piece of data. A single field holding Ahmed Khan, 14 Market Street cannot be sorted by surname or searched by street; separate fields for forename, surname and address can be. This is the most common design fault, and it cannot be repaired by a query afterwards.
The table is described by a structure — the field names and their data types — which is fixed, and the records, which are added, changed and deleted while the structure stays the same.
Fields can also be given validation, so that the database rejects unreasonable data when it is entered. A presence check makes sure a field such as Title is never left empty; a range check keeps a publication year between 1450 and the current year; a length check limits a code to the right number of characters; a format check makes an ISBN follow its pattern. Validation is set when the table is defined, along with each field’s data type.
Worth remembering
- A record is a row, one item; a field is a column, one property.
- Every field must be a property of the thing the record represents.
- Keep each field indivisible, or it cannot be sorted or searched on.
- Fields can be given validation, such as presence, range and format checks.
Worked example
A Students table might contain StudentID, Name and Score; each row describes one student.
Practice
Warm-up · not marked
Check your understanding
Answer in your own words, then compare with the worked answer. Nothing here is saved or scored.
Worked answer
A field whose value in each record gives that book’s title.
Check your method, units and explanation against it.
Marked practice
Set 1 of 5 · easiest first · Checked as you go, with the worked answer. Sign in or create a free account to save your progress.