QCE Digital Solutions Unit 2 — Prototype Data Solutions

1 Proof-of-Concept, Prototype and Product 2 What is a relational database? 3 Entity 4 Attributes become fields 5 Database field data types 6 SQL column data types and sizes 7 Primary key 8 Foreign key and table relationships 9 One-to-many relationships between tables 10 One-to-one and many-to-many relationships 11 Referential integrity 12 Entity relationship diagram: entities and relationship lines 13 Crow's foot notation for one and many 14 Drawing an ERD from a scenario 15 Data dictionary 16 Naming a database and its tables 17 Naming columns, primary keys and foreign keys 18 SQL constraints for validation 19 The repeated-data problem in one big table 20 Insert, update and delete anomalies 21 First normal form (1NF) (Prototype Data Solutions) 22 Second normal form (2NF) (Prototype Data Solutions) 23 Third normal form (3NF) (Prototype Data Solutions) 24 Evaluating a database against the normal forms 25 Modifying a database to 3NF 26 What SQL is 27 Running an SQL query in a database tool 28 SQL SELECT and FROM 29 SQL WHERE conditions 30 Boolean operators AND, OR and NOT in a WHERE clause 31 BETWEEN and IN in a WHERE clause 32 SQL ORDER BY 33 SQL aggregate functions: COUNT, SUM, AVG, MIN, MAX 34 SQL COUNT function 35 SQL GROUP BY 36 SQL HAVING 37 Clause order in a full SQL query 38 Why a query over two tables needs a join 39 Writing the ON condition: foreign key equals primary key 40 Table-qualified field names in a join 41 Table aliases in a join 42 A join with WHERE and ORDER BY 43 Joining three tables 44 A join that forgets the ON condition 45 Sub-query in a WHERE clause with IN 46 Sub-query returning a single value 47 Writing an SQL query from a requirement 48 CREATE TABLE and INSERT INTO 49 FOREIGN KEY ... REFERENCES in CREATE TABLE 50 SQL UPDATE and DELETE 51 CREATE VIEW 52 Changing data across related tables 53 Input-process-output (IPO) chart 54 Writing pseudocode from an IPO chart 55 if-then block 56 else if: chained selection 57 Repeat-Until loop 58 while loop 59 define: defining a function 60 return keyword 61 input() function 62 Type Conversion 63 If Statements 64 elif Statements 65 While Loops 66 For Loops 67 Functions – Defining 68 Functions – Parameters 69 Functions – Return Values 70 Error Handling – try/except basics 71 F-String Formatting 72 Tuples – Operations 73 Program structure with a main() function 74 Connecting Python to a SQLite database 75 Running a SELECT from code and fetching rows 76 Displaying query results 77 Running a join query from code 78 Parameterised INSERT with commit 79 Parameterised UPDATE and DELETE from code 80 A menu loop that controls user interaction 81 SQL injection 82 Validation versus verification 83 Existence checking in code 84 Type checking in code 85 Range checking in code 86 Input validation with a while loop 87 Combining validation checks in a validation function 88 Validating data before it is stored 89 Handling database errors in code 90 Useability principles for an interface 91 UX characteristic: usability 92 Accessibility features in an interface 93 Feedback and error messages in an interface 94 Navigation and user flow 95 Applying UI design principles to an interface mock-up 96 Test table layout 97 Choosing a range of test data 98 Constructing test data for validation checks 99 Recording actual results and a pass or fail verdict 100 Testing that invalid input is rejected 101 Testing a query against the data 102 Working out the output of an SQL statement 103 Common mistakes in SQL scripts 104 Testing that stored data changed correctly 105 Testing a function with assert 106 Testing one module in isolation 107 Test coverage of branches and loops 108 Debugging a program with print() and trace tables 109 Retesting everything after a fix 110 Reliability of a solution 111 Readability improvements in code 112 Maintainability improvements in code 113 Python snake_case naming 114 Docstrings for functions and classes 115 Refactoring code to improve it 116 Efficiency of a solution 117 Code optimisation 118 Efficient SQL queries 119 Effectiveness of a solution 120 Modifying an existing program 121 Data quality: accuracy 122 Data quality: completeness 123 Validation: why data is checked on entry 124 Checking data quality with SQL queries 125 Evaluation criteria 126 Checking a solution against the requirements 127 Applying evaluation criteria to a data solution 128 Evaluating your own solution

18

QCE Digital Solutions Unit 2: Database design, data types, constraints and keys Students learn how a relational database is designed, with entities, fields, data types, primary and foreign keys, relationships, ERDs, a data dictionary, naming rules and validation constraints.

7

QCE Digital Solutions Unit 2: Normalising a database to 3NF Students learn to spot redundancy and anomalies, evaluate tables against 1NF, 2NF and 3NF, and modify a database to third normal form.

12

QCE Digital Solutions Unit 2: SQL SELECT queries, grouping and aggregates Students write SQL SELECT queries that filter, sort, count, group and summarise data using WHERE, IN, ORDER BY, aggregate functions, GROUP BY and HAVING.

10

QCE Digital Solutions Unit 2: SQL joins and sub-queries Students retrieve data across related tables using inner joins and sub-queries, and turn requirements into complete SQL queries.

5

QCE Digital Solutions Unit 2: SQL creating tables, views and changing stored data Students use CREATE, INSERT, UPDATE, DELETE and CREATE VIEW to build tables and views and to change data safely across related tables.

21

QCE Digital Solutions Unit 2: Planning and programming skills for the prototype Students plan with IPO charts and write pseudocode and Python using selection, loops, functions, error handling and a main() structure.

7

QCE Digital Solutions Unit 2: Prototype code that runs SQL and controls the interface Students connect Python to a database, run queries and data changes from code, display the results, and control user interaction with a menu loop.

9

QCE Digital Solutions Unit 2: Validating data and handling errors in the prototype Students protect the database from bad data with SQL injection defences, validation checks run before storing, and error handling for database failures.

6

QCE Digital Solutions Unit 2: Useability principles and interface design Students judge and improve an interface using the useability principles, accessibility, feedback, error messages and navigation.

14

QCE Digital Solutions Unit 2: Testing SQL, algorithms and code Students plan and run tests with test tables and test data, check queries and stored data changes, and use assert, tracing and retesting to find and fix faults.

11

QCE Digital Solutions Unit 2: Reliability, maintainability and efficiency Students evaluate and improve a solution for reliability, readability, maintainability, efficiency and effectiveness, including efficient SQL and refactored code.

8

QCE Digital Solutions Unit 2: Data quality and evaluating the prototype Students check data accuracy and completeness, including with SQL, and evaluate the prototype against criteria and requirements with evidence.

✎

Python Crossword: Getting Started (Modules 1–10) 10 clues · print() function, input() function, Comments, Variables and more

✎

Python Crossword: Control Flow (Modules 11–20) 10 clues · Type Conversion, Comparison Operators, Boolean Logic, If Statements and more

✎

Python Crossword: Functions (Modules 21–30) 10 clues · range(), Error Handling – try/except basics, break and continue, Functions – Defining and more

✎

Python Crossword: Advanced (Modules 51–63) 10 clues · OOP – Methods, OOP – String Representation (__str__), OOP – Practical Application, OOP – Class Variables vs Instance Variables and more

🔎

Python Word Search: Getting Started and Control Flow (Modules 1–20) 22 words to find · print() function, input() function, Comments, Variables and more

🔎

Python Word Search: Functions and Data Structures (Modules 21–40) 20 words to find · range(), Error Handling – try/except basics, break and continue, Functions – Defining and more

🔎

Python Word Search: Advanced (Modules 41–63) 20 words to find · Sets – Operations, Nested Lists, Lists + Dictionaries, File Operations – Open and more

✎

VCE Innovative Solutions Crossword: Impact of Emerging Trends in Digital Systems and more (Part 1 of 2) 10 clues · Impact of Emerging Trends in Digital Systems, Characteristics of Creative and Innovative Solutions, Functions and Capabilities of Digital Systems, Ideation Techniques and more

🔎

VCE Innovative Solutions Word Search: Impact of Emerging Trends in Digital Systems and more (All modules) 20 words to find · Impact of Emerging Trends in Digital Systems, Characteristics of Creative and Innovative Solutions, Functions and Capabilities of Digital Systems, Ideation Techniques and more

✎

VCE Pseudocode Crossword: What is Pseudocode to if-then block (Modules 1–10) 6 clues · What is Pseudocode, Pseudocode Writing Conventions, Variable Assignment: x <- value, input keyword and more

✎

VCE Pseudocode Crossword: if-then-else block to Reading and tracing complex pseudocode (Modules 11–28) 10 clues · if-then-else block, else if: chained selection, for loop: for i from 1 to n, Nested for loops and more

🔎

VCE Pseudocode Word Search: What is Pseudocode to Reading and tracing complex pseudocode (Modules 1–28) 20 words to find · What is Pseudocode, Pseudocode Writing Conventions, Variable Assignment: x <- value, input keyword and more

✎

Years 5–6 Creating Digital Solutions Crossword: Building visual programs and Evaluating digital solutions and their impact (Modules 61–74) 10 clues · Using a variable as a counter in a loop, Decisions inside loops in a visual program, Building a button with 'when this sprite clicked', Switching screens with backdrops and show/hide and more

🔎

Years 5–6 Creating Digital Solutions Word Search: Designing user interfaces to Evaluating digital solutions and their impact (Modules 41–74) 20 words to find · Communicating a design to others, Evaluating an interface design, Modifying an interface design, The visual programming editor: stage, sprites and scripts and more

✎

VCE Data Analysis Crossword: Data types and data characteristics to Privacy and the Australian Privacy Principles (Modules 25–37) 10 clues · Characteristic of data: accessibility, Characteristic of information: clarity, Characteristic of information: context, Data quality: accuracy and more

✎

VCE Data Analysis Crossword: Requirements, constraints and scope and Design tools for databases, spreadsheets and visualisations (Modules 68–80) 10 clues · Scope of a solution, Listing requirements and constraints for a problem, Interpreting teacher-provided requirements for a data solution, Input-process-output (IPO) chart and more

🔎

VCE Data Analysis Word Search: Data types and data characteristics to Ethics and APA referencing (Modules 25–54) 20 words to find · Characteristic of data: accessibility, Characteristic of information: clarity, Characteristic of information: context, Data quality: accuracy and more

🔎

VCE Data Analysis Word Search: Ethics and APA referencing to Design tools for databases, spreadsheets and visualisations (Modules 56–80) 22 words to find · Acknowledging sources and copyright, Purpose of APA referencing, APA in-text citations, APA reference list entry structure and more

✎

VCE Spreadsheets, SQL & Visualisation Crossword: Manipulating, validating and testing spreadsheet data and Relational database structure (Modules 35–44) 10 clues · Pivot tables, What is a relational database?, What a single-table database is, Database field data types and more

✎

VCE Spreadsheets, SQL & Visualisation Crossword: Relational database structure and Querying data with SQL (Modules 45–54) 10 clues · Importing a CSV file into a database table, Validation rules on database fields, SQL constraints for validation, What a database query is and more

✎

VCE Spreadsheets, SQL & Visualisation Crossword: Querying data with SQL to Purposes and types of data visualisations (Modules 55–70) 10 clues · SQL GROUP BY, SQL JOIN across two tables, SQL UPDATE and DELETE, Testing a query against the data and more

🔎

VCE Spreadsheets, SQL & Visualisation Word Search: Manipulating, validating and testing spreadsheet data to Querying data with SQL (Modules 35–54) 20 words to find · Pivot tables, What is a relational database?, What a single-table database is, Database field data types and more

🔎

VCE Spreadsheets, SQL & Visualisation Word Search: Querying data with SQL to Visualisation components and design conventions (Modules 55–86) 20 words to find · SQL GROUP BY, SQL JOIN across two tables, SQL UPDATE and DELETE, Testing a query against the data and more

✎

VCE Software Development U3O1 Crossword: Requirements and design tools to Naming conventions and internal documentation (Modules 10–47) 10 clues · Data dictionary, Writing a data dictionary entry, Designing classes with a class diagram, Object descriptions and more

✎

VCE Software Development U3O1 Crossword: Pseudocode to Data structures (Modules 72–96) 10 clues · Turning pseudocode into Python, Changing a global variable with the global keyword, Functions calling other functions, Program structure with a main() function and more

✎

VCE Software Development U3O1 Crossword: Data sources and files to Graphical user interfaces (Modules 114–124) 10 clues · Reading an XML file with ElementTree, Validation: why data is checked on entry, Types of validation check, Existence checking in code and more

✎

VCE Software Development U3O1 Crossword: Errors and debugging and Testing modules (Modules 168–177) 10 clues · Debugging a program with print() and trace tables, Setting a breakpoint, Stepping through code and watching variables, Tracing nested loops and more

✎

VCE Software Development U3O1 Crossword: Testing modules and Artificial intelligence in programming (Modules 178–193) 10 clues · Testing one module in isolation, Modifying an existing program, Emerging trend: AI code generation tools, Writing a prompt to generate code and more

🔎

VCE Software Development U3O1 Word Search: Requirements and design tools to Pseudocode (Modules 10–71) 21 words to find · Data dictionary, Writing a data dictionary entry, Designing classes with a class diagram, Object descriptions and more

🔎

VCE Software Development U3O1 Word Search: Pseudocode to Data sources and files (Modules 72–113) 21 words to find · Turning pseudocode into Python, Changing a global variable with the global keyword, Functions calling other functions, Program structure with a main() function and more

🔎

VCE Software Development U3O1 Word Search: Data sources and files to Object-oriented programming (Modules 114–141) 20 words to find · Reading an XML file with ElementTree, Validation: why data is checked on entry, Types of validation check, Existence checking in code and more

🔎

VCE Software Development U3O1 Word Search: Errors and debugging to Artificial intelligence in programming (Modules 168–193) 20 words to find · Debugging a program with print() and trace tables, Setting a breakpoint, Stepping through code and watching variables, Tracing nested loops and more

✎

VCE Software Development U3O2 Crossword: Ideation and evaluating design ideas to User experience and software design (Modules 86–109) 10 clues · Mood boards, Design criteria for an interface, Comparing alternative designs with a decision matrix, Efficiency of a solution and more

✎

VCE Software Development U3O2 Crossword: User experience and software design and User interface design principles (Modules 110–119) 10 clues · Accessibility features, How UX characteristics affect software design, UI design principle: alignment, UI design principle: balance and more

🔎

VCE Software Development U3O2 Word Search: Ideation and evaluating design ideas to User interface design principles (Modules 86–119) 20 words to find · Mood boards, Design criteria for an interface, Comparing alternative designs with a decision matrix, Efficiency of a solution and more

✎

VCE Software Development U4O1 Crossword: Classes, objects and access modifiers to Code repositories, APIs, libraries and AI assistants (Modules 82–114) 10 clues · Choosing access modifiers for a class, Readability improvements in code, Built-in functions sum(), min() and max(), List comprehensions and more

✎

VCE Software Development U4O1 Crossword: Code repositories, APIs, libraries and AI assistants to Beta testing (Modules 125–171) 10 clues · Keeping API keys secure, Validation versus verification, Alpha testing, Commenting out code to isolate a bug and more

🔎

VCE Software Development U4O1 Word Search: Efficient and effective solutions and user-centred design to Code repositories, APIs, libraries and AI assistants (Modules 3–114) 20 words to find · User-centred design, Order of operations with brackets in Python, Choosing data types for input, storage and output, Procedures versus functions and more

🔎

VCE Software Development U4O1 Word Search: Code repositories, APIs, libraries and AI assistants to Beta testing (Modules 115–171) 22 words to find · Branches and merging, What a library is: standard vs third-party, Random numbers with import random, The math module and more

✎

VCE Data Analytics U3O1 Crossword: Data types and relational databases to Sourcing, validating and referencing data (Modules 17–49) 10 clues · Field sizes, SQL column data types and sizes, One-to-many relationships between tables, Boolean operators AND, OR and NOT in a WHERE clause and more

🔎

VCE Data Analytics U3O1 Word Search: Data types and relational databases to Statistical analysis of data (Modules 17–91) 20 words to find · Field sizes, SQL column data types and sizes, One-to-many relationships between tables, Boolean operators AND, OR and NOT in a WHERE clause and more

✎

VCE Data Analytics U4O1 Crossword: Data quality and testing visualisations to Recording and assessing project progress (Modules 85–105) 10 clues · Visual inspection of a data visualisation, Confirming a chart represents the data, Evaluation criteria, Time frame of an evaluation strategy and more

🔎

VCE Data Analytics U4O1 Word Search: Audience and developing infographics and dashboards to Recording and assessing project progress (Modules 63–105) 20 words to find · Pagination of an infographic or dashboard, Clarifying data refresh requirements, Visual hierarchy in a visualisation, Clarity of message in a visualisation and more

✎

Years 9–10 Creating Digital Solutions Crossword: Defining and decomposing problems to Evaluating solutions, impact and innovation (Modules 6–132) 10 clues · Decomposing a problem into a hierarchy chart, Prioritising requirements, Truth tables for AND, OR and NOT, Logical operators in a flowchart decision and more

🔎

Years 9–10 Creating Digital Solutions Word Search: Defining and decomposing problems to Evaluating solutions, impact and innovation (Modules 6–132) 20 words to find · Decomposing a problem into a hierarchy chart, Prioritising requirements, Truth tables for AND, OR and NOT, Logical operators in a flowchart decision and more

✎

VCE Applied Computing U1O2 Crossword: Requirements and design tools to Naming conventions and internal documentation (Modules 8–88) 10 clues · Writing an IPO chart for a program, Accessing record fields by index, Abstraction in OOP, Encapsulation in OOP and more

✎

VCE Applied Computing U1O2 Crossword: Naming conventions and internal documentation to Legal requirements and emerging trends (Modules 89–124) 10 clues · Explaining data and code structures with comments, Docstrings for functions and classes, Combining validation checks in a reusable function, Breakpoints in an editor and more

🔎

VCE Applied Computing U1O2 Word Search: Requirements and design tools to Legal requirements and emerging trends (Modules 8–124) 21 words to find · Writing an IPO chart for a program, Accessing record fields by index, Abstraction in OOP, Encapsulation in OOP and more

✎

IGCSE Computer Science Paper 2 Crossword: 9 Databases (Modules 525–538) 10 clues · Field order in the output of a SELECT, WHERE on text, Boolean, character and date fields, Mixing AND and OR with brackets in a WHERE clause, Clause order in a full SQL query and more

✎

IGCSE Computer Science Paper 2 Crossword: 9 Databases and 10 Boolean Logic (Modules 539–551) 10 clues · Common mistakes in SQL scripts, What a logic gate is, Labelling inputs and the output: A, B, C and X, Truth table layout for two inputs and more

🔎

IGCSE Computer Science Paper 2 Word Search: 8.3 File Handling and 9 Databases (Modules 500–538) 20 words to find · Write, close, then read the data back, Copying a line of text from one file to another, Tracing a file handling program, Common mistakes in file handling pseudocode and more

🔎

IGCSE Computer Science Paper 2 Word Search: 9 Databases and 10 Boolean Logic (Modules 539–580) 20 words to find · Common mistakes in SQL scripts, What a logic gate is, Labelling inputs and the output: A, B, C and X, Truth table layout for two inputs and more

✎

ACARA Digital Technologies Years 7–8 Crossword: Analysing and Visualising Data and Structured Data and Queries (Modules 331–360) 10 clues · Describing a trend in a sentence with evidence, Investigating a dataset from question to conclusion, Attributes of an object, Attributes of an event and more

🔎

ACARA Digital Technologies Years 7–8 Word Search: Analysing and Visualising Data to Algorithms, Flowcharts and Pseudocode (Modules 331–411) 21 words to find · Describing a trend in a sentence with evidence, Investigating a dataset from question to conclusion, Attributes of an object, Attributes of an event and more

✎

ACARA Digital Technologies Years 9–10 Crossword: Entities, Relationships and Queries (Modules 433–451) 10 clues · The repeated-data problem in one big table, Entity, Attributes become fields, One-to-one and many-to-many relationships and more

✎

ACARA Digital Technologies Years 9–10 Crossword: Entities, Relationships and Queries (Modules 459–475) 10 clues · Pattern matching with LIKE, % and _, BETWEEN and IN in a WHERE clause, Why a query over two tables needs a join, Writing the ON condition: foreign key equals primary key and more

✎

ACARA Digital Technologies Years 9–10 Crossword: Algorithms with Logical Operators and Testing and Validating Programs (Modules 536–590) 10 clues · Choosing test data for compound conditions, What it means to validate an algorithm or program, Working out the expected output before you run the code, Edge cases with lists and strings and more

✎

ACARA Digital Technologies Years 9–10 Crossword: Testing and Validating Programs (Modules 591–622) 10 clues · Writing a test plan from requirements, Writing test cases before the code, Testing a program that uses input(), Storing test cases as a list of tuples and more

✎

ACARA Digital Technologies Years 9–10 Crossword: Cyber Security Threats and Vulnerabilities (Modules 782–809) 10 clues · Physical social engineering: tailgating and baiting, Data flow diagram of a system, Mapping STRIDE threats to confidentiality, integrity and availability, Writing a threat statement and more

🔎

ACARA Digital Technologies Years 9–10 Word Search: Analysing and Visualising Data and Entities, Relationships and Queries (Modules 413–451) 21 words to find · Connecting one slicer to several pivot charts, Referencing a cell on another worksheet, Named ranges, A drop-down list as a chart selector and more

🔎

ACARA Digital Technologies Years 9–10 Word Search: Entities, Relationships and Queries and Algorithms with Logical Operators (Modules 459–534) 22 words to find · Pattern matching with LIKE, % and _, BETWEEN and IN in a WHERE clause, Why a query over two tables needs a join, Writing the ON condition: foreign key equals primary key and more

🔎

ACARA Digital Technologies Years 9–10 Word Search: Algorithms with Logical Operators and Testing and Validating Programs (Modules 536–622) 20 words to find · Choosing test data for compound conditions, What it means to validate an algorithm or program, Working out the expected output before you run the code, Edge cases with lists and strings and more

🔎

ACARA Digital Technologies Years 9–10 Word Search: Modular and Object-Oriented Programming (JavaScript) and Cyber Security Threats and Vulnerabilities (Modules 743–809) 21 words to find · One class, many objects, Storing objects in an array, Methods calling other methods, Choosing properties and methods for a class from a scenario and more

✎

QCE Digital Solutions Unit 1 Crossword: Algorithms and Programming Techniques and Programmed Solutions (Modules 158–380) 10 clues · input keyword, if-then-else block, Repeat-Until loop, define: defining a function and more

🔎

QCE Digital Solutions Unit 1 Word Search: User Experiences and Interfaces to Programmed Solutions (Modules 82–380) 20 words to find · Recording pitfalls and useful solutions in an existing interface, Known user, User characteristics, Age appropriateness of information and more

✎

Crossword: Prototype Data Solutions (Modules 305–336) 10 clues · Insert, update and delete anomalies, First normal form (1NF) (Prototype Data Solutions), Second normal form (2NF) (Prototype Data Solutions), Third normal form (3NF) (Prototype Data Solutions) and more

✎

Crossword: Prototype Data Solutions (Modules 337–409) 10 clues · Changing data across related tables, Connecting Python to a SQLite database, Running a SELECT from code and fetching rows, Displaying query results and more

🔎

Word Search: Data and Programming Techniques and Prototype Data Solutions (Modules 234–409) 22 words to find · Reading a relational schema, Unnormalised data and why normalise, First normal form (1NF) (Data and Programming Techniques), Second normal form (2NF) (Data and Programming Techniques) and more