Technologies K–10 · Years 9–10
Querying a relational database of the first 20 elements
Computing Technology 7–10 (NSW, 2022); Digital Technologies: Processes and production skills, Acquiring, managing and analysing data (ACARA v9)
This Lab page has no on-screen model. Where a step or a material names a model, simulation or tool, it was planned when the practical was written and is not on this site; an external simulation a step names (for example PhET) is not part of this site.
The idea
Data about entities and their relationships sits in linked tables, and SQL queries and joins answer questions that a single flat list handles badly.
What you need
- Computer with Python 3 (sqlite3) or DB Browser for SQLite
- The IUPAC periodic table for checking atomic numbers and groups
How to do it
- Create table element (atomic_number primary key, symbol, name, group_number) with a CHECK that group_number is from 1 to 18, and table family (group_number primary key, family_name).
- Insert the first 20 elements with their IUPAC group numbers, and family names for groups 1 (alkali metals), 2 (alkaline earth metals), 17 (halogens) and 18 (noble gases).
- Query: SELECT symbol FROM element WHERE group_number = 18 AND atomic_number < 20.
- Query with a join: count the elements in each group and show the family name where there is one.
- Try to insert an element with group_number 19 and record what happens.
- Find the modelling problem: hydrogen is in group 1 but is not an alkali metal. Change the design so each element can have its own classification.
What you should see
The first query returns He, Ne, Ar. The counts by group are group 1: 4 (H, Li, Na, K), group 2: 3, groups 13 to 17: 2 each, group 18: 3. The insert with group 19 is refused by the CHECK constraint. Joining 'alkali metals' to group 1 wrongly labels hydrogen, which shows why the classification belongs to the element, not to the group. The learner knows it worked when the query results match these values checked against the IUPAC table.
What changes
This practical lists no variables to change, measure and keep the same.
Common misconceptions
Each of these ideas is wrong, and the practical is a chance to test it.
- A spreadsheet row per element is a database (a relational design links separate tables by keys).
- Every element in group 1 is an alkali metal (hydrogen is in group 1 but is not a metal).
- Constraints are optional extras (they stop invalid data getting in).
Safety card
Hazards
- None beyond normal computer use
Controls
- Not applicable
Note
No chemicals or heat.
Curriculum references
The NSW syllabus outcomes and Australian Curriculum v9 codes this practical supports. They are references, not a verified or complete curriculum alignment.
- Computing Technology 7–10 Syllabus (2022), NESA. Current elective syllabus. Code read from the outcomes page on 2026-09-22.CT5-DAT-02
- Australian Curriculum v9AC9TDI10P03AC9TDI10P01
Sources
The pages the author read to write this practical.