ISDD2 - Building a Structure | National 3/4 Computing Science

Learning intentions

Create database tables using SQL in DataGrip
Choose suitable field names, data types and primary keys
Populate tables with accurate records

Success criteria

I can create a table structure to store information
I can insert records into the table
I can explain how my table structure links to the assessment standard

Building the Bike Scotland Database

This lesson starts the practical database evidence. You will create a simple bike shop database. National 3 pupils need evidence of creating a structure and populating it with information. National 4 pupils also need evidence of a structure with links, so the database uses two linked tables.

Database design

Bike Scotland linked structure

bike_typestype_id primary key
type_name

→

bikesbike_id primary key
type_id foreign key

The link is made by storing the type table's primary key inside the bikes table.

Table	Purpose	Key fields
`bike_types`	Stores each category of bike	`type_id` primary key
`bikes`	Stores individual bike models	`bike_id` primary key, `type_id` foreign key

The link is made by storing type_id in both tables. In bike_types, it is the primary key. In bikes, it is a foreign key that points to the matching bike type.

Assessment Practice

Practical Task 1 - Create and populate the database

Open a query console in DataGrip and run the SQL below in stages. Read each command before running it.

CREATE DATABASE bike_scotland;
USE bike_scotland;

CREATE TABLE bike_types (
    type_id INT PRIMARY KEY,
    type_name VARCHAR(40) NOT NULL,
    description VARCHAR(120)
);

CREATE TABLE bikes (
    bike_id INT PRIMARY KEY,
    model VARCHAR(50) NOT NULL,
    type_id INT NOT NULL,
    frame_size VARCHAR(20),
    price DECIMAL(6,2),
    in_stock BOOLEAN,
    FOREIGN KEY (type_id) REFERENCES bike_types(type_id)
);

INSERT INTO bike_types VALUES
(1, 'Mountain', 'For rough tracks and off-road cycling'),
(2, 'Road', 'For everyday road cycling'),
(3, 'Racing', 'For speed and competition');

INSERT INTO bikes VALUES
(101, 'Ridge Runner', 1, 'Medium', 425.00, TRUE),
(102, 'City Glide', 2, 'Large', 310.00, TRUE),
(103, 'Sprint Pro', 3, 'Small', 780.00, FALSE);

Then run SELECT * FROM bike_types; and SELECT * FROM bikes; to check the records.

N3 evidence checklist

Screenshot or export showing the table structure created (N3 O1.1)
Screenshot or export showing records inserted into the table (N3 O1.2)
Short note naming fields and records in your database

N4 evidence checklist (builds on N3)

Evidence that both linked tables were created (N4 O1.1)
Identifies the primary key and foreign key that make the link
Explains why the database uses two tables instead of one repeated list

Common mistakes

Forgetting USE bike_scotland;, then creating tables in the wrong database.
Misspelling a table or field name. SQL needs the same spelling each time.
Inserting a bike with a type_id that does not exist in bike_types.

Tip

If an SQL command fails, read the first error line slowly. Most beginner errors are missing commas, mismatched brackets or a field name typed differently from the table definition.

Task Set

1. Which table stores the bike categories? TYPE 1

bikes
bike_types
bike_scotland

2. What field links bikes to bike_types? TYPE 1 N4

price
type_id
frame_size

3. Paste your successful SELECT outputs or describe what they show. TYPE 3

4. Explain why bike_id is a better primary key than model. TYPE 2

bike_id is designed to be unique. A model name could repeat or change, so it is less reliable as the value that identifies one record.

Teacher notes - Shift+T to hide