From paper to a working table
Oakfield Youth Club is made up. The design is on paper. Now the technician has to build it.
Building a table is quick when the plan is good. Each line of the data dictionary (a table that describes each field) turns into one line of the table. This lesson shows how, using SQL (a language for working with tables of data).
By the end of this lesson
Note
I can build a table from a data dictionary, with rules, and add records to it.
Key words
| Word | What it means |
|---|---|
| Constraint | a rule the table enforces on its data |
| CREATE TABLE | the SQL words that build a new table |
| INSERT | the SQL word that adds a record to a table |
| Import | to bring data in from another file |
| CSV file | a text file with one record per line and commas between fields |
| Integer | a whole number |
| Range check | a check that a number is between two limits |
| Primary key | a field that is different for every record |
The cells students fill in are left blank in this preview.
Starter: what you already know
Have a go
Starter: what you already know
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Each dictionary row becomes a line of SQL
In CREATE TABLE, you name the table and then list its fields. After each field name you write its type and its rules.
A constraint is a rule the table enforces. If a new record breaks it, the table refuses the record. The refusal is validation (checking entered data) at work.
Worked example: dictionary to SQL
| In the data dictionary | In the SQL |
|---|---|
| Primary key, whole number | INTEGER PRIMARY KEY |
| Presence check (must not be empty) | NOT NULL |
| Range check, 11 to 19 | CHECK (age BETWEEN 11 AND 19) |
| List check (a set of days) | CHECK (weekday IN ('Monday', 'Tuesday')) |
| Default value of 0 | DEFAULT 0 |
| No two the same | UNIQUE |
The cells students fill in are left blank in this preview.
Try it: build the activities table
-- The members table is already built. Here we build activities.
CREATE TABLE activities (
activity_id INTEGER PRIMARY KEY,
activity_name TEXT NOT NULL UNIQUE,
weekday TEXT NOT NULL CHECK (weekday IN ('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday')),
fee REAL NOT NULL DEFAULT 0 CHECK (fee >= 0),
places_left INTEGER NOT NULL CHECK (places_left BETWEEN 0 AND 30)
);
INSERT INTO activities (activity_name, weekday, fee, places_left) VALUES
('Football', 'Tuesday', 2.5, 12),
('Chess', 'Friday', 0, 8);
-- Remove the two dashes on the next line to see the table refuse a record.
-- INSERT INTO activities (activity_name, weekday, fee, places_left) VALUES ('Art', 'Funday', 1, 5);
SELECT * FROM activities;
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.