A First Look at Databases

Tables, records and fields

Read a database table as records and fields, count what it holds, and say what a primary key is doing in it.

A spreadsheet will let you type anything anywhere, which is exactly what you want while you are working something out and exactly what you do not want for a school's list of pupils. A database is the strict version. Its information lives in tables, and a table is a grid with rules: each row is a record, holding everything the table knows about one thing, and each column is a field, which is one particular question asked of every single record. Where a record and a field meet there is one value. Nothing is left out and nothing is put somewhere it does not belong, because the shape of the table decides where everything goes before any of it is typed.

The primary key is the field that can never repeat

Two pupils can share a first name, a house and a birthday, and a table that has to pick out one of them by any of those is stuck. So one field is chosen as the primary key, and the promise made about it is that no two records will ever hold the same value in it — which makes it a name for the record rather than a fact about the thing. That is why schools invent roll numbers when they already know everybody's name. A field that could repeat, even once in a hundred years, cannot be a key, and a key that repeats by mistake has stopped being one: the moment two records hold the same key, there are questions the table can no longer answer.

Worked example

How much does this table hold, and how much of it is not really new?

code   title   shelf   room
101    atlas   red     upper
102    novel   blue    upper
103    diary   red     upper

  1. Three rows of information, so three records; four columns, so four fields. That is twelve values in all.

    Counting a table is always these two numbers multiplied. Neither of them can be read off the other.

Try it together

Now count and reason about one table. A club keeps a table of its members with seven fields, and it currently holds twenty records.

Keep the two numbers apart as you go: one of them is about members and the other is about what is known of each member.

    1.How many single values does the table hold at the moment?

    Have a go

    Have a go on your own. A table holds sixty single values and has four fields. How many records does it hold?

    Ready to practice?

    Eight questions on what you have just read. Nothing is timed, and you can play as many times as you like.

    Print a worksheet