01.1 Tables, records, fields and data types

01 | WHY A DATABASE?

A school runs several clubs. Someone needs to record students, club details and who has joined each club. A list can work initially, but repeated names, changing details and growing membership soon make it difficult to keep consistent.

A database is an organised collection of data. A database management system , or DBMS, is the software used to store, retrieve and manage it. SQLite is one example of a database engine; SQL is the language we use to give it instructions.

You do not need prior database knowledge for this section. The first two pages focus on understanding the design; page 01.3 shows how to run the SQL.

02 | ONE TABLE, ONE CLEAR SUBJECT

A relational database organises data into related tables. Our Students table stores students; a separate Clubs table will store clubs.

Ask what a single row means. Here, one row describes one student. It does not describe a whole class, all the clubs or an arbitrary collection of facts.

The headers name the properties we store. The values below them describe individual students. The two Mayas are different students, even though their names and year levels match.

03 | RECORDS ARE ROWS; FIELDS ARE COLUMNS

A record is one row: a collection of values about one item. A field is a named property represented by a column, such as first_name . A field value is the particular value in that column for a record.

For student 101, the first_name value is Maya . The table has three records and three fields; the headers are not an extra student record.

04 | A SCHEMA IS THE PLAN

A schema describes structure: table names, columns, declared data types and rules. The data are the actual values stored within that structure.

Adding another student changes the data. Adding a new preferred_name column changes the schema. Keep this distinction in mind when you meet CREATE TABLE and INSERT .

Use clear identifiers such as year_level . Consistent names without spaces make beginner SQL easier to read and reduce quoting problems.

05 | CHOOSE A TYPE FOR THE MEANING

A data type expresses the kind of value intended. Year level is a whole number; a name is text. The choice affects storage, comparisons and the rules we can apply.

A telephone number can contain digits without being an arithmetic quantity. Storing it as a number may lose leading zeros.

06 | SQLITE TYPES ARE FLEXIBLE

SQL is used by several database products, and details differ. These lessons use SQLite syntax. Ordinary SQLite tables use type affinity, which influences conversion and storage rather than enforcing every declared type rigidly.

Declaring INTEGER alone is therefore not a complete validation rule for every ordinary SQLite column. Later we add constraints, including a check for whole-number values where needed.

Other systems may use types such as VARCHAR , BOOLEAN or DATE with different behaviour. SQLite also offers STRICT tables, but these foundations use ordinary tables and explicit rules.

07 | MISSING IS NOT ZERO

NULL represents a missing or unknown value. It is not the same as zero, the text 'NULL' , or an empty string.

If an optional nickname is unknown, NULL can represent that situation. A capacity of zero means a known numeric value, even if our club rules later reject it. An empty string is a text value containing no characters.

Design which fields may be missing. A required first name should not silently be treated like an optional nickname.

08 | KEEP ONE VALUE PER CELL

Putting Robotics, Game Design inside one student field makes each membership harder to search, count or change. It also becomes unclear how to attach a role to each club.

Do not create club1 , club2 and club3 columns either: the design would impose an arbitrary limit and require structural changes for another club.

We will store membership as separate records in a related table. Separating related facts is more useful than squeezing a whole list into one cell.

09 | READ YOUR FIRST SQL STATEMENT

SELECT first_name, year_level
FROM Students;

SELECT says which columns to retrieve; FROM names the table. The semicolon ends the statement. This is a preview: section 02 teaches retrieval properly.

SQL keywords are often written in uppercase to stand out. Column and table names must refer to the actual schema. Retrieving rows does not change the table.

10 | DESIGN CHALLENGE

Design the fields for a Clubs table. Store an identifier, a name and a maximum membership capacity. For each field, write its intended type, whether it is required and an example value.

11 | CHECK BEFORE MOVING ON

You should be able to identify a record, a field and a field value; distinguish schema from data; explain NULL; and justify why a numeric-looking identifier might need TEXT.

Next, we will give records stable identities and connect students to clubs without copying club details into every student row.

KEEP LEARNING

01.2 Primary keys, foreign keys and relationships

Reference: SQLite table constraints · SQLite data types · SQLite foreign keys · Python SQLite interface