9-10 Digital Technologies: Acquiring, storing and validating data Hangman

The 27 key terms

1primary data vs secondary data

Primary data is data you collect yourself, like a survey. Secondary data was collected by someone else, like data from a website or database.

2survey

A survey collects data by asking everyone the same clear questions, such as closed or open questions on an online form, so the answers can be counted and compared.

3Form responses sheet

A spreadsheet linked to an online form. Each submitted response becomes a new row, each question is a column, and a timestamp column records when it was sent, giving data ready to use.

4CSV file

A plain text file that stores table data with commas between the values. A spreadsheet can open it and show the data in rows and columns.

5open data

Datasets, such as government statistics, that anyone can download from a portal. A licence says how you may reuse them, and you must credit the source.

6data table

A grid that organises data. Each column has one heading, and each row holds one record, like one student or one pet. There are no blank rows.

7Sort

Sorting puts the rows of a table in order using the values in one column, like A to Z or smallest to largest, and keeps each row together.

8filter

A filter shows only the rows that match a value you choose and hides the other rows. The hidden rows are not deleted. Clear the filter to see them all again.

9clean data

Finding and fixing mistakes in data, like typos, spelling variations, duplicate rows, missing values and impossible values, so the results are correct.

10=TRIM(A2)

A text function that returns a tidy copy of the text in A2 with extra spaces removed. UPPER, LOWER and PROPER work the same way to fix capitals, e.g. =PROPER(A2) gives Mia Chen.

12=IF(test, value_if_true, value_if_false)

Checks a condition, like B2>=50, and shows one result if it is true and another if it is false. Text results go in quotes, and you can copy it down a column.

13=COUNTIF(C2:C50,"Yes")

COUNTIF counts the cells in a range that meet a condition. SUMIF works the same way but adds up the values that meet it. Example: count every 'Yes' in C2:C50.

14XLOOKUP

A lookup function that finds a key in one column and returns the matching value from another column, such as a price for a product code. It can show a message if no match is found.

15validation

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).

16validation check

A test that an entry is sensible. Types include presence, data type, range, length, format and list. Each one tests a different property of the entry.

17Data Validation

A spreadsheet tool that limits cells to allowed entries, like a whole number from 1 to 100 or a drop-down list, and shows an error message if someone types anything else.

18relational database

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

19data type

A setting on a database field that decides what kind of value it accepts, such as text, integer, date or yes/no, so wrong entries are rejected. It is a first form of validation.

20PRIMARY 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.

21FOREIGN 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.

22CREATE 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.

23SELECT 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.

24WHERE

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.

25ORDER 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.

26UPDATE 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.

27CHECK (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.

28INNER JOIN t2 ON t1.key = t2.key

Combines rows from two tables where a foreign key matches a primary key, so related data shows in one result. Use table.field names to say which table each field belongs to.