QCE Digital Solutions Unit 2 — Prototype Data Solutions Hangman

The 111 key terms

1Proof-of-concept, Prototype, Product

Three progressive stages of solution development: proof-of-concept tests feasibility, prototype demonstrates function, and product is the final polished release

2relational database

A database that stores data in separate tables of fields (columns) and records (rows), with tables linked by shared values such as keys.

3entity

A type of thing a database stores data about, such as a student, book or order. Each entity becomes one table, and its name is a singular noun.

4attribute

An attribute is one property of an entity, like a student's name or year level. Each attribute becomes one field (column) in the entity's table, and each instance of the entity is one record.

5data type

The kind of value a database field accepts, such as text, number, date or yes/no. It stops the wrong kind of data being entered, like words in a number field.

6VARCHAR(50)

A column's data type and size set in CREATE TABLE. VARCHAR(50) holds text up to 50 characters. Other types: INT, DECIMAL(8,2), DATE, BOOLEAN. They control what a column stores.

7PRIMARY KEY

A field whose value is unique for every record in a table, such as a student ID number, so each record can be identified exactly. Names make poor keys because they can repeat.

8FOREIGN KEY

A field in one table that holds the primary key value of a record in another table, linking the two tables together so related data is stored once and not repeated.

9one-to-many relationship

One record in a primary table matches many records in a related table. The foreign key sits on the many side, e.g. one class has many students, so class details are not repeated.

10junction table

A table that splits a many-to-many relationship into two one-to-many links by holding two foreign keys, such as Enrolment with studentId and subjectId.

11referential integrity

Every foreign key value must match an existing primary key in the related table, so no record points to something missing. The database blocks changes that break this.

12ERD

An entity relationship diagram draws each entity as a labelled box and each relationship as a line joining two boxes. Boxes list attributes, with PK and FK marked.

13crow's foot

ERD notation where a single bar at a line's end means one and a three-pronged crow's foot means many. The crow's foot end is the foreign key side of the relationship.

14ERD from a scenario

Find the entities, list their attributes, pick primary keys, then work out each relationship and draw it, with the foreign key on the many side.

15data dictionary

A table listing each data element with its name, data type, size or format, description and validation rule, so everyone uses the data the same way when coding and testing.

16naming convention

A consistent rule for naming a database and its tables, such as a singular noun in lower_case with no spaces or special characters, e.g. student, applied in the same style for every table.

17student_id

A key column name in lowercase with an _id suffix. A primary key uses it in its own table, and a foreign key reuses the exact same name in a related table.

18CHECK (age BETWEEN 0 AND 120)

A constraint is a rule on a column. This CHECK rejects any record with an age outside 0 to 120. NOT NULL (must have a value) and UNIQUE (no repeats) are constraints too.

19data redundancy

The same details stored again and again in one big table. It wastes space and risks typos or missed updates, like a customer's address copied onto every order.

20anomaly

A problem caused by repeated (redundant) data: adding, changing or deleting a record leaves data inconsistent or lost. The three types are insert, update and delete anomalies.

24normal form check

Test a table against 1NF, then 2NF, then 3NF, naming each dependency problem found (repeating group, partial or transitive) and backing it up with an example row.

26SQL

Structured Query Language, the standard language for asking a database to return data (SELECT) or to add, change or remove it (INSERT, UPDATE, DELETE).

27SELECT * FROM students;

Type an SQL statement in the tool's query window, run it, then read the results table. If an error shows, read the message and fix the statement.

28SELECT name, age FROM students;

SELECT lists the fields to show and FROM names the table. This returns the name and age columns for every record in students. SELECT * shows all columns. End with a semicolon.

29WHERE

An SQL clause that keeps only the rows meeting a condition, such as WHERE age >= 15 or WHERE town = 'Perth'. Text goes in quotes, and AND or OR can combine conditions.

30AND, OR, NOT

Combine WHERE conditions: AND needs both true, OR needs at least one true, NOT reverses a condition. Brackets set the order and decide which rows are returned.

31BETWEEN low AND high

Tests if a value is inside a range, including both limits. Its partner IN (a, b, c) checks a value against a list, replacing repeated OR. NOT IN excludes the list.

32ORDER BY

Sorts the rows a SELECT query returns by a chosen field. It sorts A to Z or lowest to highest by default (ASC), or the reverse if you add DESC after the field name.

33SELECT AVG(score) FROM results;

An aggregate function works out one value from many rows. COUNT, SUM, AVG, MIN and MAX give the count, total, mean, lowest and highest. This query returns the mean score.

34COUNT(*)

SQL function that counts records. SELECT COUNT(*) FROM Students; outputs one row with the number of records in the table. It counts rows, whereas SUM adds up values.

35GROUP BY

An SQL clause that sorts rows into groups sharing the same field value, then applies an aggregate like AVG or COUNT to each group, giving one result row per group (e.g. average score per class).

36HAVING

Filters groups after GROUP BY using an aggregate condition, such as HAVING COUNT(*) > 3. WHERE filters rows before grouping.

37SELECT FROM WHERE ORDER BY

The order of clauses in a full SQL query: SELECT, FROM, WHERE, then ORDER BY. WHERE filters the rows first, then ORDER BY sorts the rows that are left.

38JOIN

A join matches each foreign key value in one table to the primary key value in another, so rows from both tables show together. Rows with no match are left out.

39ON orders.customer_id = customers.customer_id

The join condition: it matches the foreign key in one table to the primary key in the other. The = sign compares the two values to find matching rows. It does not assign a value.

40table.field

Names the table a field comes from, like students.name. Needed in a join when both tables share a field name, or SQL gives an ambiguous column error.

41FROM students s

Gives the students table the short name s after FROM, so the rest of the query can write s.firstName instead of students.firstName.

42JOIN ... ON ... WHERE ... ORDER BY

After JOIN ... ON links two tables, WHERE filters the joined rows and ORDER BY sorts them. Clause order: SELECT, FROM, JOIN ON, WHERE, ORDER BY.

43JOIN subjects ON results.subjectId = subjects.subjectId

A second JOIN line chained after the first to reach a third table. It follows a foreign key in the junction table to the primary key of the next table.

44Cartesian product

A join with no ON condition pairs every row of one table with every row of the other, so 3 customers and 4 orders give 12 rows, most of them wrong.

45IN (SELECT studentId FROM results)

A sub-query is a SELECT inside brackets in a WHERE clause. It returns a list of values, and IN keeps only the rows whose value is in that list. NOT IN keeps the rows that are not in it.

46WHERE mark > (SELECT AVG(mark) FROM results)

A sub-query in brackets returns one value, like the average. The outer WHERE compares each row's field to it. MIN or MAX work too.

47SELECT fields FROM table WHERE condition ORDER BY field DESC;

Turn a request into one SQL statement: pick fields after SELECT, the table after FROM, conditions in WHERE, sort with ORDER BY, and use SUM or COUNT for totals and how many.

48CREATE TABLE

SQL command that builds a new table by naming its fields and their data types, such as a PRIMARY KEY. INSERT INTO ... VALUES then adds one record to that table.

49FOREIGN KEY (field) REFERENCES other_table(field)

Used in CREATE TABLE to link a column to another table's key. The database then only accepts values that match an existing key, so bad links are rejected.

50UPDATE table SET field = value WHERE condition;

UPDATE changes field values and DELETE FROM removes records. Always add WHERE so only the chosen records change. Without it, every record in the table is changed or deleted.

51CREATE VIEW

Saves a SELECT query under a name so you can query it like a table. A view stores the query, not a copy of the data, so it always shows current rows. Remove it with DROP VIEW.

52INSERT parent, DELETE child

Add the parent row before its child rows, and delete child rows before their parent. Otherwise the foreign key rule is broken and SQL gives a foreign key error.

53IPO chart

A three-column table listing a solution's inputs (data entered), the processes applied to that data, and the outputs it produces, such as a total or report.

54IPO to pseudocode

Turn each IPO input into an INPUT statement, each process into assignments or IF selection, and each output into a PRINT statement, in that order.

55end if

The closing keyword that ends an if-then block in VCAA pseudocode. Every if block must be closed with end if at the same indentation level as the opening if keyword

56ELSE IF

A keyword that adds another condition to check if the previous IF or ELSE IF condition was false

57REPEAT ... UNTIL condition

A loop that repeats its instructions and checks the condition after each repetition, so the body always runs at least once

58WHILE condition DO

A loop that repeats its instructions for as long as the given condition remains true, checked before each repetition

59define

A keyword that starts a function definition, naming the function and listing the parameters it accepts

60return

A keyword that sends a value back from a function to where it was called, ending the function's execution at that point

73def main():

Defines a function called main that holds the main steps of your program. You call main() once at the end of the file, after the other functions are defined, to start the program.

74sqlite3.connect()

Opens a SQLite database file from Python and returns a connection. Call conn.cursor() on it to get a cursor for running SQL, and conn.close() when finished.

75cursor.fetchall()

Returns every row from the SELECT that cursor.execute() ran, as a list of tuples. An empty result gives an empty list []. fetchone() returns just one row.

76for row in rows: print(f"{row[0]:<10}{row[1]}")

Loops over each fetched row and prints its fields with an f-string, padded into columns so results are readable. Check for an empty list to show a no records message.

77cur.execute(join_sql)

Runs a JOIN query stored in one string. Each row fetched back is a tuple holding fields from both tables, which you can unpack into variables.

78cursor.execute("INSERT INTO products (name, price) VALUES (?, ?)", (n, p))

Adds a new record using ? placeholders filled safely from a tuple of values. The new row is only saved to the database file once conn.commit() is called.

79DELETE FROM students WHERE studentId = ?

A parameterised DELETE or UPDATE run from code: the ? in WHERE is filled by a value so only that record changes. cursor.rowcount of 0 means record not found.

80while choice != "q":

A menu loop repeats: show the menu, read a choice, call that option's function, and stop only when the user picks quit. Other input gets an invalid choice message.

81SQL injection

An attack where SQL commands typed into an input box run because the program joins the input straight into a query. Parameterised queries stop it by treating input only as data.

82validation vs verification

Validation checks entered data is reasonable and complete. Verification checks it matches the source data, for example by proofreading.

83if value.strip() == '':

An existence check tests that a value was actually entered. strip() removes spaces, so this is True for empty or space-only input, and an error message can be shown.

84type check

A check that input is the expected data type, using .isdigit(), try/except around int() or float(), or isinstance(). input() is always text, so check before converting.

851 <= age <= 120

A range check: tests that a number lies between a minimum and a maximum (inclusive here). Values outside the limits, such as 0 or 121, are rejected as invalid.

86while not valid:

A loop that keeps asking for input again until the answer is valid, so bad data (like an age of -5) can't get into your program. Show an error message each time.

87def is_valid_age(text):

A validation function that runs the existence, type and range checks in order and returns True or False, so the same checks can be reused for many inputs and tested.

88if is_valid(points):

Run the validation function first and only execute the INSERT or UPDATE when it returns True, so bad data is rejected before SQL runs and never reaches the database.

89except sqlite3.Error:

Catches a failed database call, such as an IntegrityError from a broken constraint, so the program shows a friendly message instead of crashing. Call rollback() to undo.

90useability principles

Six criteria for judging an interface: accessibility, effectiveness, ease of learning, efficiency, error tolerance and engagement. A good interface does well on all of them.

91usability

How easily, quickly and accurately users can learn and use software to achieve their goals, including ease of learning, efficiency of use, and error prevention and recovery.

92accessibility features

Design choices like alt text, captions, keyboard-only controls, adjustable text size and not relying on colour alone, so people with different abilities can use a system.

93feedback

A response from the interface that tells the user what happened, like a confirmation message, or an error message that says what went wrong and how to fix it.

94user flow

The path of screens a user follows to finish a task, like logging in or buying a ticket. Navigation controls such as back and home buttons help them move along it in as few steps as possible.

95Annotated design review

Checking a mock-up against each UI principle in turn (alignment, balance, contrast, space, text), annotating the improvements needed, then revising the design.

96test table

A table with one row per test case. Its columns are test number, purpose, input, expected result, actual result and pass or fail.

97normal, boundary and invalid data

A good test set uses normal data (typical values), boundary data (values at the edges of a range) and invalid data (values that should be rejected), so every path in an algorithm is checked.

98test data

Values chosen to test each validation rule: empty for existence, text for type, and values just inside and just outside range limits, each with an expected result.

99Pass or Fail

Copy the actual result exactly as the program gave it, then mark Pass only if it matches the expected result, otherwise Fail. Never change the expected result to fit, and add a note for fails.

100assert not is_valid(-5)

Tests that bad input is rejected: call the validator with an invalid value and assert it returns False. If an error is expected, use try/except; no error means the test fails.

101Test a query

Predict the rows a query should return from a small sample, run it, then compare the actual result with your prediction. Check row counts, boundary values and JOIN matches.

102WHERE, ORDER BY, SELECT

To find a query's output, first keep the rows that pass WHERE, then sort them with ORDER BY, then show only the SELECT fields or the SUM or COUNT result.

103SQL error

A mistake in an SQL script, such as text without quotes, > instead of >=, AND instead of OR, clauses in the wrong order, a missing table after FROM or a missing semicolon.

105assert

Checks a condition is True, like assert add(2, 3) == 5. If the result is not what you expected, it raises an AssertionError, so test cases can run automatically.

106unit testing

Testing one function or class on its own, with its own test table, before joining it to the rest of the program, so a failing test points to that module.

107test coverage

Testing so that every if/elif/else branch runs at least once and every loop is tried running zero times, once and many times. Any code never run is untested.

108print(variable)

Debugging tool: print a variable at key points, then compare each value with your trace table of what it should be. The first mismatch shows where the bug is.

109regression testing

After fixing a bug, rerun the failed test and all earlier tests, because a fix can break code that used to pass.

110reliability

A reliable solution gives correct results every time and keeps working when input is bad or an error occurs, so no data is lost.

111readable code

Code that people can quickly understand, using clear names, consistent indentation and spacing, short functions with no repeated code, and helpful comments.

112maintainable code

Code organised so it can be fixed or extended later without breaking other parts, using small functions, classes that hide detail and constants instead of repeated values.

113snake_case

A naming style where all letters are lowercase and words are joined by underscores, like student_score. Python uses it for variables and functions, and it should be used consistently.

114"""docstring"""

A triple-quoted string placed first in a function or class that describes its purpose, parameters and return value. It is internal documentation.

115refactoring

Rewriting working code to make it clearer or simpler (e.g. renaming variables, moving repeated lines into a function) without changing what it does, then re-running tests.

116efficiency

How well a solution uses resources such as time, processing, memory, storage and user effort to do its job. An efficient solution is fast, light on memory and storage, and saves the user time.

117optimisation

Changing code so it runs faster, uses less memory or is easier to read, without changing what it does. For example, removing repeated code or using a built-in function.

118SELECT name FROM products WHERE stock > 0

An efficient query asks only for the fields and rows it needs: name fields instead of SELECT *, WHERE to filter early, and an index or key to find records fast.

119Effectiveness

How well a solution does what it was meant to do, giving correct, complete and usable results that meet the requirements and suit its purpose.

120modify code

Read and trace the code first to see what it does, then change one small part at a time and re-run your test cases to check the effect of that change.

121accuracy

Data is accurate when it is correct and free from errors, so it matches the real-world fact it records. Typos, faulty measurements and out-of-date values make data inaccurate.

122completeness

Data quality where every record has a value in each required field and no needed records are missing. In SQL, a missing value shows as NULL.

123validation

An automatic check that data is reasonable and in the right form when entered. It blocks bad data early, but it cannot prove the data is true (a valid age of 15 could still be wrong).

124IS NULL

Tests whether a field has no value. Use WHERE field IS NULL, often with COUNT(*), to find and count incomplete records when checking data quality.

125evaluation criterion

A measurable standard used to judge a solution, linked to a requirement and written so it can be answered with evidence, such as a target, a yes/no check or a rating.

126requirements checklist

A list of every functional requirement and success criterion. You test the solution against each one, then record yes or no and note your evidence, such as what you saw or a screenshot.

127apply a criterion

Test the solution against a criterion, record the result (met, partly met or not met) and justify the rating with evidence. Recommend improvements if it is not fully met.

128evaluate

To check how well your solution works by testing it against the requirements, asking users to try it, and deciding what to improve. Be honest about any problems.