Single-table databases and primary keys

Computer Science · Databases. The idea in plain words, the rules to remember, the mistake to avoid and a worked example, written and checked by Mega Lecture.

Test this topic free All Computer Science topics

The idea

A database is an organised store of data. In a single-table database all the data is held in one table. Each column is a field: one item of data, such as a surname. Each row is a record: all the data about one person or thing.

When you define a table you give each field a name and a data type. You can also add validation, which checks that data being entered is sensible, for example a range check on an age.

Every table needs a primary key. This is a field whose value is different in every record, so it identifies exactly one record.

Rules to remember

Field = one column (one item of data). Record = one row (all the data about one item).
Number of fields = number of columns. Number of records = number of items stored.
Data types: text/alphanumeric (letters, digits and symbols), character (one character), Boolean (true/false or yes/no), integer (whole number), real (decimal number), date/time.
Use text/alphanumeric for codes and phone numbers: they are not used in calculations and may start with 0.
Validation checks include range, length, type, presence and format checks.
Primary key: a field that uniquely identifies each record. It must hold a different value in every record.

Common mistake

Students choose a name as the primary key. Two people can share a name, so it is not unique. Choose an ID or code field that is different for every record.

Worked example

A table PET stores, for each of 120 pets: PetID, Name, Species, Weight (for example 4.5) and Vaccinated (yes or no). Which field is the primary key, and which data types suit Weight and Vaccinated?

Name and Species can repeat, so they cannot identify one pet. PetID is different for every pet.
Weight has a decimal part, so it needs the real data type, not integer.
Vaccinated has only two possible values, so Boolean fits.
The table has 5 fields and 120 records.

Answer: Primary key: PetID. Weight: real. Vaccinated: Boolean.

Ask about this

Type what you are stuck on. The helper finds the explanation, worked examples and the notes to read, free and instant.

Keep going

← Arrays and file handling Structured query language (SQL) →Take a Computer Science test