Data Science

Quantitative and Qualitative Data

🎯 (EC-12-02, EC-12-04)

📌 Explore the difference between quantitative and qualitative data

Data are the raw facts an enterprise collects. Before you can store, analyse or present them, you need to know what kind of facts they are, because the kind decides which calculations, charts and tools make sense. The first split is between quantitative and qualitative data.

Two kinds of quantitative data

Discrete data are counted in whole steps (the number of trips in a day, free docks at a station). Continuous data can take any value in a range (the exact time of a trip, distance, temperature). This difference affects how you round, store and chart the values.

Numbers that are really labels

A value made of digits is not automatically quantitative. A postcode, a phone number, a student ID or a TripID such as T018 identifies something; adding two postcodes together means nothing. The test is would arithmetic on this value mean anything? If not, treat it as qualitative (an identifier or a category).

Using both together

Most enterprise questions need both. Bellbird’s trip log (quantitative) might show that trips from Hospital station are always short. That tells the manager what is happening. A short survey or interviews (qualitative) are then needed to find out why: perhaps staff ride only to the shops next door, or the docks are often full. Using two types of data to answer one question is sometimes called mixed methods.

Data Types for Representing Data

🎯 (EC-12-02, EC-12-04)

📌 Determine which data types are used to represent quantitative and qualitative data

A data type tells the system how a value is stored and what can be done with it. Choosing the wrong type is one of the most common causes of spreadsheet and database errors. NESA’s data dictionary example uses the types Text, Date, Currency, Real and Boolean; whole numbers are usually called Integer. The full data dictionary layout is in the Toolkit.

Types used for quantitative data

• Integer for whole-number counts, such as the number of docks or trips.
• Real (decimal or floating-point) for measurements with fractions, such as 4.6 km.
• Currency for money, so that dollars and cents are displayed and rounded correctly.
• Date (and time) for points on a calendar or clock. Dates are stored as numbers behind the scenes, so you can subtract them to find a duration, but they are formatted for people to read.

Types used for qualitative data

• Text (character or string) for names, categories and identifiers, and for numbers you will never calculate with, such as a phone number or a postcode.
• Boolean for a two-way category such as Y or N, True or False. It records a quality (has the fee been paid?) rather than an amount.
• Long text for free comments.
• Image, audio and video (binary objects) for rich qualitative content such as a photo of a damaged bike.

Choosing the type: Bellbird fields

What goes wrong with the wrong type

• Numbers stored as text cannot be added: =SUM(range) ignores them, so a total quietly comes out too small.
• Dates stored as text sort alphabetically, so 10/09/2026 sorts before 9/09/2026.
• Identifiers stored as numbers lose leading zeros: a code 0412 becomes 412.
• Categories stored as codes (1 = Member, 2 = Casual) are still qualitative even though the type is Integer. The mean of a set of codes is meaningless. The next section explains why.

Levels of Measurement

🎯 (EC-12-02, EC-12-05)

📌 Explore nominal, ordinal, interval and ratio levels of measurement applied to data

The level of measurement (or scale) of a variable says how much information its values carry, and so which calculations are valid. There are four levels. Each one has all the properties of the levels before it, plus one more.

Telling them apart

Ask three questions in order. Can the values be put in a sensible order? Are the gaps between values equal? Does zero mean “none of it”? Each “yes” moves the variable one level up. The decision tree below draws these three questions in NESA’s vertical decision-tree style.

The day of the week has no natural starting point in the data, so treat it as nominal (or, if you want Monday to Sunday in order, ordinal). A calendar date has equal gaps between days but no true zero, so it is interval.

Why the level matters

The level decides which spreadsheet functions give a sensible answer. =AVERAGE(range) makes sense for ratio data such as trip minutes, but the mean of a column of station codes (1, 2, 3) is meaningless. A count or the most common value is the honest summary for nominal data. Averaging ordinal data such as star ratings is common but needs care, because the gap between 1 and 2 stars may not equal the gap between 4 and 5.

Quick check

Classify each variable, then check your answers. The feedback explains each one.

Data Sampling and Collection

🎯 (EC-12-03, EC-12-04)

📌 Investigate data sampling, including manual and computerised methods of active and passive data collection

A population is everything you want to know about, such as all 400 casual riders. A sample is the part of it you actually collect data from. Enterprises sample because measuring everyone costs too much, takes too long, or is impossible (a factory cannot test every battery to destruction). NESA’s glossary describes data sampling as analysing a subset of a larger dataset to find meaningful information such as patterns and trends. A good sample is representative: it has the same mix of people or items as the population, so what you learn from it applies to the whole.

Sampling methods

A bigger sample reduces random error (the luck of who was chosen), but it does not fix bias. Ten thousand answers from a convenience sample are still biased.

Worked example: choosing 60 riders to survey

1. Bellbird has 600 registered riders: 200 members and 400 casual riders. A random sample of 60 could, by chance, contain very few members.
2. Use stratified sampling. Members are one third of riders, so they should be one third of the sample: 20 members and 40 casual riders.
3. Within each stratum choose randomly. In a spreadsheet, a random rider position from 1 to 200 can be produced with =INT(RAND()*200)+1. INT is a function in NESA’s list; RAND is an extension, so software may spell it differently.
4. Record which method you used and why. Anyone reading the results can then judge how far they can be trusted.

Manual and computerised collection

Manual collection needs a person to observe, record or key in the data (paper forms, tally sheets, a clipboard). It is flexible and needs no infrastructure, but it is slow and prone to transcription errors. Computerised collection captures data using software or devices (online forms, sensors, scanners, system logs). It is faster, consistent and can be validated as it is entered, but it needs equipment and can only record what it was built to record.

Active and passive collection

In active collection the person knows they are providing data and does so deliberately, for example by answering a survey or entering their details. In passive collection the data is captured as a by-product of what people do, often without them doing anything extra and sometimes without them thinking about it: a GPS trail, a tap-on record, browsing history. Passive collection produces a lot of data with little effort, and raises the largest privacy and consent questions.

Where the data is about people, passive collection should still be disclosed in a collection notice and privacy policy (see the Software features and Legal issues sections).

Choosing a method

Match the method to the question. To find out how many trips happen at each station, passive computerised logging is the accurate answer. To find out why people avoid Hospital station, an active method such as a short survey or interviews is needed, because the log cannot record motives. Enterprises often combine them and then check whether the results agree.

Assessing Primary and Secondary Data

🎯 (EC-12-02, EC-12-10)

📌 Assess the relevance, accuracy, validity and reliability of primary and secondary data

Before relying on data, ask whether it is good enough for the job. NESA names four qualities to assess. They are related but not the same, and a dataset can be strong on some and weak on others.

A kitchen scale that always reads 200 g too heavy is reliable (it gives the same answer every time) but not accurate. A survey asking “how many trips do you take?” that riders read as “how many trips do you want to take?” is not valid, however carefully it is analysed.

Primary and secondary data

Primary data is collected first-hand by the enterprise for its own purpose (Bellbird’s trip log, its rider survey). Secondary data was collected by someone else, usually for a different purpose, and is reused (weather observations from the Bureau of Meteorology, population figures from the Australian Bureau of Statistics, a council’s open data, a published research report).

A checklist for judging a source

• Who collected it, and could they gain from a particular result (funding, bias)?
• How was it collected: sample size, sampling method, instrument, and were the questions fair?
• When was it collected, and is it still current?
• What exactly does each field mean (units, definitions, coding), and is there a data dictionary or other metadata?
• Can it be checked against another independent source?

Worked assessment

Bellbird wants to know whether rain reduces trips. It has its own trip log (primary) and can download daily rainfall from a Bureau of Meteorology station (secondary).

Judgement: the trip log is strong on every quality and can be trusted. The rainfall data is reliable but only moderately relevant, so any conclusion should be worded cautiously (“trips fell on days when rain was recorded nearby”) and, if the question matters, checked with a rain gauge at a Bellbird station.

Informatics and Understanding Data

🎯 (EC-12-02, EC-12-04)

📌 Investigate how informatics supports the development of a deeper understanding of data

Informatics is, in NESA’s glossary, the science of processing data for storage and retrieval. In practice it covers how information is represented, collected, organised, processed and communicated using computers. Applied to an enterprise, it is the discipline of turning raw data into something people can understand and act on. It gives a deeper understanding of data because it does not stop at storing facts: it structures them, links them, adds context and finds meaning.

From data to insight

A common model shows understanding building up in levels, often drawn as a pyramid: data, then information, then knowledge, then wisdom or insight. Each step adds context and meaning, and informatics supplies the tools for each step.

How informatics deepens understanding

• Structure. Organising data into tables with defined fields and relationships lets a computer answer precise questions in seconds.
• Integration. Linking data from different sources (trip log, weather, events calendar) reveals relationships that no single source shows.
• Context. Metadata, units and definitions stop numbers being misread.
• Pattern finding. Statistics, data mining and machine learning find trends, groups and unusual values a person would miss in thousands of rows.
• Communication. Graphs, dashboards and reports put the finding in front of the people who decide, in a form they can question.

Informatics is a field in its own right, with branches named for the area they serve, such as health informatics (patient records and outcomes) and business informatics (decision support for enterprises). The four kinds of analytics also map onto the pyramid: descriptive (what happened?), diagnostic (why?), predictive (what is likely next?) and prescriptive (what should we do?).

Interpreting and Presenting Data

🎯 (EC-12-05, EC-12-11)

📌 Interpret and present data using graphs, infographics, dashboards, reports, network diagrams and maps

Presenting data is a two-way skill. You interpret what a display is really saying (read it critically) and you present your own findings so that a specific audience can understand and act on them. The form you choose depends on the question, the data and the audience.

Interpreting a display

Before you trust a chart, check it in this order:

1. Title and source. What is it about, who produced it, and when?
2. Axes and units. What is measured, in what units, and does the vertical axis start at zero? A bar chart that starts at 40 instead of 0 makes small differences look large.
3. Sample and timeframe. How much data lies behind it, and over what period?
4. Pattern. What is the trend, the biggest and smallest values, and any outlier?
5. Limits. What does it not show? Correlation on a scatter graph is not proof of cause.

Railway is connected only to Market Square, so it would lose its main route (38 trips), and Library depends on Market Square too (17 trips). Riverside Park and Hospital would still be joined by their own link (21).

Choosing a form for an audience

A council committee that meets monthly needs a report with a clear recommendation. A station manager checking each morning needs a dashboard. The public needs an infographic or a map with plain wording. The same data may be presented in all three forms; the message and the level of detail change. The Data visualisation page covers design choices in depth.

Structured and Unstructured Datasets

🎯 (EC-12-02, EC-12-04)

📌 Investigate structured and unstructured datasets

Datasets differ in how organised they are. The difference decides how easily they can be stored, searched and analysed.

Turning unstructured data into structured data

Much of the value in unstructured data is unlocked by extracting structured facts from it. Software can tag a photo with objects it recognises, detect the topic and mood of a comment, or convert a spoken message into text. The extracted values are then stored in fields that can be counted and charted. The results depend on the accuracy of the software, and they lose nuance (sarcasm, for example).

Once the comments are structured, Bellbird can count faults by station and show the trend on a dashboard. This is one reason enterprises invest in machine learning for text and images (see the Machine learning section).

Likes, Emoticons and Memes as Data

🎯 (EC-12-04, EC-12-06)

📌 Explore the use of likes, emoticons and memes as forms of alternative data as sources of feedback

Alternative data is data from non-traditional sources, used in addition to the usual records such as sales, surveys and logs. Likes, reactions, emoticons and emoji, shares, comments and memes are forms of alternative data. They are given freely by people as they use social media, so enterprises use them as quick feedback on how a product, message or event is being received.

As a source of feedback

Traditional feedback (a survey, a complaints form) is slow and only collects answers to the questions someone thought to ask. Alternative data is immediate (the reaction appears within minutes), large-scale, cheap to collect, and unprompted, so it can surface issues the enterprise did not expect.

Limitations

• Ambiguity. A like can mean agreement, sympathy or “seen it”. Emoji and memes change meaning between groups and over time, and sarcasm defeats simple scoring.
• Bias. Only people who use that platform, and who choose to react, are counted. This is a self-selected sample, not a representative one.
• Manipulation. Bots and fake accounts can inflate or attack counts, and posts can be boosted by paid promotion.
• Passive collection. People may not realise that their reactions are collected and analysed. Platform terms and privacy laws still apply.
• Reliability. Social media trends are short-lived, and the platform’s algorithm decides what is seen, which influences the reactions.

Because of these limits, alternative data is best used alongside other sources: for example, a spike in negative reactions is checked against the fault log and a survey before deciding.

Errors, Uncertainty and Limitations

🎯 (EC-12-02, EC-12-10)

📌 Examine the impact of errors, uncertainty and limitations in data

• data sources
• raw data versus processed data
• data bias

No dataset is perfect. Errors are mistakes in the data (a wrong value, a missing value, a duplicate). Uncertainty is how much doubt remains about a value or a conclusion, even when nothing is wrong. Limitations are the boundaries on what the data can support: how much of it there is, what it covers and when. A good analyst finds and reports all three, because decisions made on data that looks more certain than it is can be costly or unfair.

Where errors and uncertainty come from

• Measurement error: a faulty or poorly calibrated sensor (a dock that logs a trip of −3 minutes) or the limits of the instrument (GPS is accurate only to a few metres).
• Human error: typing mistakes, misreading a form, or ticking the wrong box.
• Sampling error: a sample that, by chance or design, does not match the population.
• Processing error: a wrong formula, a mismatch of units (miles and kilometres) or a bad join between tables.
• Missing and duplicate data: blank fields, or one trip recorded twice.
• Rounding and precision: results shown to more decimal places than the measurement can support give a false sense of precision.

Uncertainty is usually reported as a range or a margin of error rather than a single number: “between 40 and 48 per cent”. Larger samples and better instruments narrow the range.

Data sources

Every source has its own error profile, so it matters where data comes from. Primary sources (dock sensors, your own survey) let you control and document the method. Secondary sources (a public dataset, a purchased list, a web scrape) come with definitions, gaps and errors you did not choose. Records of the source, its date, its method and its meaning (metadata) let you judge how far to trust it. Combining sources multiplies the risk: the errors add up, and the definitions may not line up.

Raw data versus processed data

Good practice is to keep the raw data untouched, do the cleaning as recorded steps, and note what was removed and why. That way a mistake can be traced and fixed. The old saying “garbage in, garbage out” applies: no analysis can make poor data reliable.

A spreadsheet can help flag errors before they spread. If Minutes is in column E, =IF(E2<=0,"Check","OK") marks impossible values, and conditional formatting can shade them red. Validation rules on data entry (minutes must be a whole number greater than 0) prevent many errors in the first place.

Data bias

Bias is a systematic tilt in the data or how it is used, so that results consistently lean in one direction. It differs from random error, which scatters both ways. Some common forms:

• Selection or sampling bias: the people or items included are not representative. If Bellbird asks for feedback only through its app, riders without smartphones are missing.
• Measurement bias: the instrument or question favours an answer, such as a leading survey question.
• Survivorship bias: only the things that “survived” are counted. Analysing only registered riders ignores people who tried the scheme once and left.
• Confirmation bias: the analyst looks for, or believes, results that agree with what they already think.
• Algorithmic bias: a model trained on biased data repeats and amplifies that bias.

Where docks already exist, trips are recorded. Where there are none, no trips are recorded, so those suburbs look as if they have no demand and never get docks. The data then “confirms” the original decision.

Blockchain

🎯 (EC-12-03, EC-12-06)

📌 Explain how blockchain technology is used to manage and verify data

• online voting
• online identities
• tracking items of value
• recordkeeping

NESA describes a blockchain as a growing list of records, known as blocks, which are linked using cryptography, and which can record transactions between two parties in a verifiable and permanent way. It is a way of recording data so that anyone can check it has not been changed, without having to trust a single owner of the records. It combines four ideas: hashing, blocks linked into a chain, a distributed ledger held by many computers, and consensus among those computers about what is true. This section explains each and then applies them to NESA’s four uses.

The problem it addresses

Records of value (who owns a title, who paid whom, who voted) are normally kept by one trusted party such as a bank, a registry or an electoral commission. Everyone must trust that party not to make errors, be hacked or alter entries. A blockchain spreads the record across many computers and makes any change to it visible, so trust can come from the system rather than from one organisation.

Hashing

A hash function takes any input (a word, a file, a block of records) and produces a fixed-length output called a hash or digest. A widely used one, SHA-256, always outputs 256 bits, written as 64 hexadecimal characters. Good hash functions have four properties:

• Deterministic: the same input always gives the same hash.
• Fixed length: a one-word input and a whole film give the same length of output.
• Sensitive: changing a single character changes the hash completely and unpredictably.
• One-way: it is practically impossible to work backwards from a hash to the input, or to find two different inputs with the same hash.

So a hash acts as a fingerprint of the data. Hashing is not encryption: nothing is hidden and nothing can be decrypted. Its job is to prove that data has not changed.

Blocks and the chain

A block is a bundle of records (for example, a set of transactions) with a timestamp, the hash of the previous block and its own hash, calculated from everything inside it (including the previous hash). Because each block contains the hash of the one before, the blocks form a chain back to the first block. If anyone edits an old block, its hash changes, and the next block’s stored “previous hash” no longer matches. The mismatch runs through every later block, so the chain is tamper-evident.

Block 4 (if there is one) would still hold the old hash of block 3, so the attacker would have to redo every later block too. And on a real network the other computers still hold the original chain.

Distributed ledger

A ledger is a record of transactions. In a distributed ledger every participating computer (a node) holds its own full copy, and there is no single master copy. New blocks are shared with all nodes, and each node checks them before adding them. A public blockchain, such as Bitcoin or Ethereum, lets anyone join. A private or permissioned one is limited to approved participants, such as a group of banks, a supply chain or a government agency and its partners.

Consensus

When nodes disagree, or when many people want to add records at once, the network needs a rule for agreeing on the next block. This is a consensus mechanism. Two well-known kinds are:

• Proof of work: nodes (miners) compete to solve a hard puzzle, which involves trying many values until the block’s hash meets a target. The winner adds the block and is rewarded. Cheating would mean redoing all this work, which costs huge computing power and energy.
• Proof of stake: validators put up their own cryptocurrency as a deposit, and are chosen to check and add blocks. A validator who cheats can lose the deposit. It uses far less energy than proof of work.

In each case the rule is effectively that the majority (or the most work) wins. This is why altering the ledger requires controlling most of the network at once, the so-called “51 per cent attack”. Participants also sign their transactions with a private key (digital signature), so the network can check who authorised each transaction. A smart contract is a program stored on some blockchains that runs automatically when its conditions are met.

Online voting

A voting system needs eligible voters only, one vote each, a secret ballot, and a way to show the count is right. A blockchain could give a shared, tamper-evident record of the ballots, and each voter could check that their vote was recorded. However, the difficult parts of voting lie outside the chain. Voters’ devices can be infected with malware, the register of eligible voters must be correct, a person can be pressured to vote in a particular way when not in a polling place, and the system must keep ballots secret and verifiable at the same time. Many security experts caution against relying on online voting for major elections. Systems already used in Australia, such as NSW’s iVote, have been examined by independent security researchers, and a blockchain does not by itself remove those risks. For this reason, paper ballots counted in public remain the norm for parliamentary elections.

Online identities

Today, identity is checked by large organisations that store copies of your documents, which makes them attractive targets. A blockchain-based approach, often called self-sovereign or decentralised identity, lets the person keep their credentials (a degree, a licence, an age check) in a digital wallet. The issuer signs the credential, and a hash or reference is recorded on a ledger so anyone can verify it is genuine and unaltered, without the verifier contacting the issuer. The person can share only what is needed (“over 18” without a birth date). Limits include loss of the private key (no one can reset it), the need for trusted issuers, and the fact that personal data must not be written to a ledger where it cannot be corrected or deleted. Compare this with a government-accredited digital identity such as myID, where accredited providers verify identity and hold the relevant details.

Tracking items of value

Each time an item changes hands or condition (a diamond, a shipment of frozen food, a medicine, a carbon credit) the event is added to a shared ledger. Producers, carriers, inspectors and buyers each hold a copy, so no one party controls the history. A buyer can trace where the item came from and whether it stayed within temperature limits. For Bellbird, the service history of each bike could be recorded on a ledger shared by the council, the maintenance contractor and the insurer, so a bike’s repairs cannot be quietly changed after an accident. The chain proves the record has not changed since it was entered; it cannot prove the entry was true, so sensors, scanning and audits are still needed at the point of entry. This is called the garbage in, garbage out problem. Tracking platforms also need enough participants to be worthwhile. Some well-known trade-tracking projects have been wound down when partners did not join.

Recordkeeping

Land titles, academic results, medical consent records, contracts and audit logs must be kept safely and shown to be unaltered years later. A ledger gives a time-stamped, tamper-evident history: it shows who recorded what, and when, and any later change would break the chain. The tension is with privacy and law. Recordkeeping law often requires records to be corrected, archived, or destroyed after a set time, and individuals may have the right to correct personal information (Australian Privacy Principle 13). A ledger that cannot be edited makes that hard. Designers therefore keep the sensitive data in a normal database and record only a hash (a fingerprint) on the chain, which proves the record is unchanged without revealing it.

Limitations

Try it: edit a block

This chain uses real SHA-256 hashes (the first eight characters are shown). Edit any data box and watch what happens to the links. Then let the “attacker” recalculate the later blocks and see why the rest of the network still rejects the copy.

Software Features and Data Privacy

🎯 (EC-12-03, EC-12-07)

📌 Examine software features that affect the privacy and security of data

• autofill
• public or private connections
• checkbox
• terms of agreement

Ordinary software features can quietly change how much data is shared and how safe it is. Each of the four NESA features below helps the user, and each also creates a risk. Examining a feature means looking at both sides and at what the user or the enterprise should do about it. The wider privacy and security principles are in the Cybersecurity page.

Autofill

Autofill is a browser, device or password-manager feature that stores names, addresses, phone numbers, payment cards and passwords, and fills them into forms automatically.

• Benefits: speed, fewer typing mistakes, easier for people who find typing hard, and (with a password manager) unique, strong passwords for every site.
• Privacy and security risks: anyone who uses an unlocked device can fill in your card details. A malicious page can include hidden form fields that autofill also fills in, sending details you never saw. Stored data is a target for malware and, if synced, for anyone who takes over the account.
• Controls: lock the device, protect the password manager with a strong master password and multi-factor authentication, switch off autofill for payment cards on shared computers, and review what is saved.

Public or private connections

The connection used decides who else could see the traffic. Operating systems even ask whether a network is public or private, and adjust the firewall and sharing settings to match.

The Networking page explains securing a smart home network, and the Cybersecurity page covers the evil twin attack in detail. A VPN encrypts the traffic between your device and the VPN provider, but it does not make a device or an account safe on its own: you now trust the provider.

Checkbox

A checkbox on a sign-up form records consent to something, such as receiving marketing or sharing data with partners. What matters is how it is designed. In Australian privacy guidance, valid consent must be informed, voluntary, current and specific, and given by a person with the capacity to consent.

• Opt-in (the box starts empty and the user ticks it) is the better practice, because it shows an active choice. Opt-out or pre-ticked boxes rely on people not noticing, which is a “dark pattern”.
• A separate box for each purpose lets the user say yes to one and no to another. A single box that bundles “terms, marketing and sharing with partners” is not specific.
• Marketing messages are also regulated by the Spam Act 2003, which requires consent to send commercial electronic messages and a working way to unsubscribe.

Terms of agreement

Terms of agreement (terms and conditions, terms of service, end-user licence agreement) form a contract between the user and the enterprise, and are usually accepted with a click. A privacy policy or collection notice explains what data is collected and why. They are long and written by lawyers, and most people accept without reading. Things worth checking:

• What personal data is collected, including in the background (location, contacts, device data).
• Who it is shared with, and whether it is sold or used for advertising.
• How long it is kept, whether it can be deleted, and where it is stored (overseas?).
• Whether the enterprise can change the terms, and what you give up (for example, a licence to use the photos you post).

Under the Privacy Act 1988, organisations covered by it must keep a clear, up-to-date privacy policy and give a notice when they collect personal information (Australian Privacy Principles 1 and 5). Unfair terms in standard consumer contracts can also be challenged under the Australian Consumer Law. Clicking “I agree” does not give an enterprise unlimited rights.

Big Data and Data Warehousing

🎯 (EC-12-04, EC-12-06)

📌 Explore the use of big data and data warehousing, considering volume, variety and velocity

NESA’s glossary describes big data as extremely large datasets that may be analysed computationally to reveal patterns, trends and associations. They are so large, varied or fast-moving that ordinary tools (a single spreadsheet, a single database server) cannot store or process them well. NESA asks you to consider it through three properties, often called the three Vs, and then to see how enterprises store it for analysis in a data warehouse.

Volume, variety and velocity

Other “Vs” are often added, such as veracity (how trustworthy the data is) and value (whether it is worth the cost of keeping). They are a useful reminder that having more data is not the same as having better data.

Data warehousing

An enterprise’s operational systems, such as the dock software that records each trip, are built for fast, small transactions. Running a huge analysis on the same database would slow the live service. A data warehouse is a separate, central repository that collects data from many operational systems, cleans it, organises it around business subjects, and keeps years of history so it can be analysed. It is used for reports, dashboards and data mining.

Data reaches the warehouse through a process known as ETL: extract the data from each source, transform it (clean it, convert units and formats, remove duplicates, combine sources) and load it into the warehouse.

Analysing the warehouse keeps heavy queries off the live system, and the data there has already been cleaned and joined with other sources.

A data mart is a smaller warehouse for one department (finance, for instance). A data lake holds raw data of every format as it arrives and imposes structure only when it is used. Warehouses give clean, trusted answers; lakes give flexibility but need good management (see data swamps in the Curated data section).

Benefits: a single trusted source, historical trend analysis, faster reports, and a base for data mining and machine learning. Risks and costs: setup and running cost, complex ETL, data going stale between loads, and a large store of personal information that is an attractive target and must be secured. See also Data warehousing in Data visualisation.

Data Mining

🎯 (EC-12-04, EC-12-07)

📌 Explore the risks and benefits of data mining

Data mining is the process of discovering patterns, relationships and unusual values in large datasets, using statistics, database techniques and machine learning. Its results are often used to predict or to decide. Where a report answers a question you already asked, data mining finds patterns you did not know to ask about.

Common techniques

Data mining is usually a cycle: understand the problem, prepare and clean the data, apply a technique, evaluate whether the pattern is real and useful, then use the result and monitor it.

Benefits

• Better decisions: evidence about customers, demand and costs, rather than guesses.
• Efficiency: fewer empty bikes and fewer breakdowns by predicting where and when demand and faults will occur.
• Personalisation: offers and services suited to different groups.
• Fraud and risk detection: banks detect unusual card activity within seconds.
• Research and public benefit: health researchers find risk factors in large, de-identified records.

Risks

• Privacy: combining datasets can reveal a person even when names are removed, and patterns can expose sensitive facts (health, religion, relationships) that the person never disclosed. In 2016 the Australian Department of Health published a de-identified sample of Medicare and Pharmaceutical Benefits records. Researchers at the University of Melbourne showed that some of the details meant to be hidden could be recovered, and the dataset was withdrawn.
• Function creep: data collected for one purpose gets used for another without the person’s knowledge, which conflicts with the purpose limits in the Australian Privacy Principles (APP 6).
• Bias and discrimination: if past decisions were unfair, a model trained on them repeats the unfairness (for example in credit or hiring).
• False patterns: with enough data, coincidences look like findings. Correlation is not causation.
• Security: the large stores of data needed are attractive targets.
• Manipulation: profiles can be used to target people with messages that exploit their vulnerabilities.

The Impact of Data Scale

🎯 (EC-12-06, EC-12-07)

📌 Analyse the impact of data scale

• volume of raw data
• storage
• real-time and continuous streaming
• opportunities for machine learning (ML)
• changes in human behaviour
• ethical implications, including digital footprints

Scale changes what is possible and what can go wrong. To analyse the impact of data scale, follow each cause to its effects. The NESA list gives six areas, and they are linked: growing volumes need storage; fast arrival needs streaming; large stores let machine learning work; and all of it changes how people behave and what is at stake ethically.

Volume of raw data

Cheap sensors, smartphones, cameras and connected devices (the Internet of Things) create data continuously. Most of it is raw: unfiltered, repetitive and full of noise. The larger the volume, the higher the cost of storing, cleaning and searching it, and the harder it is to find the small part that matters. A bike-share that logs every GPS point produces far more raw data than one that logs only the start and end of a trip. Enterprises must therefore decide what to collect, what to keep and what to summarise.

Storage

More data needs more capacity, and capacity has costs: hardware, power, cooling and the environmental impact of data centres, plus backup copies and security. Enterprises respond with compression, tiered storage (recent data on fast, expensive storage and older data on cheap, slow storage), retention rules that delete data after a set time, and cloud storage that grows on demand. Keeping data longer than needed increases the harm if it is breached; the Australian Privacy Principles require personal information to be destroyed or de-identified when it is no longer needed (APP 11.2).

Real-time and continuous streaming

Traditional batch processing collects data and analyses it later, for example overnight. Streaming processes data as it arrives, continuously, within seconds. It makes new services possible: live dock availability, fraud alerts as a card is used, traffic maps that update, monitoring of patients or machinery. It also demands more: infrastructure that never stops, handling of late or missing data, and decisions made with no time for a person to check them. A wrong alert or an automated decision can cause harm before anyone notices.

Opportunities for machine learning

Machine learning models improve with more, and more varied, data. Large datasets allow models to find patterns, such as predicting demand or detecting fraud, that small samples cannot show. However, more data helps only if it is good data. If it is biased or badly labelled, the model learns the bias at scale. Training also costs computing power and energy, and the results must be tested on data the model has not seen.

Changes in human behaviour

People change what they do when they know they are being measured, and platforms change what people do by what they show. Fitness trackers and streaks encourage exercise; recommendation systems shape what people watch and buy; people may avoid searches, places or opinions they think are recorded. At scale, an enterprise’s design choices affect the habits and wellbeing of millions. Bellbird’s live map of free bikes changes where people walk to pick one up, which in turn changes the data it collects next.

Ethical implications, including digital footprints

Your digital footprint is the trail of data you leave online. An active footprint is what you deliberately share (posts, forms, comments). A passive footprint is collected without you doing anything (location, browsing history, device details, and the metadata about your messages). At scale, footprints raise several ethical questions:

• Permanence: data can be copied and kept for years, so a post or a location trail from your teens may still exist.
• Aggregation: small, harmless facts combine into a detailed profile that can identify you.
• Consent and awareness: people often do not know what is collected or what it is used for.
• Unequal power: an enterprise sees far more than the individual, and may use profiles to price, exclude or target people.
• Children and vulnerable people: extra care is needed, and the eSafety Commissioner publishes advice for young people on managing their online presence.

Methods of Data Storage

🎯 (EC-12-03, EC-12-10)

📌 Evaluate the effectiveness of different methods for data storage

• local storage
• cloud storage
• portable storage media
• data warehouses

To evaluate storage you need criteria, then a judgement about which method (or mix) best fits a purpose. Useful criteria are: capacity and how easily it grows; cost; speed and availability (access when needed); security; reliability and recovery (protection against loss); portability; and legal and environmental factors. The networking page also covers enterprise storage requirements and types of cloud storage.

Local storage

Data held on drives in the computer or on the enterprise’s own premises: internal solid-state (SSD) and hard disk drives (HDD), a file server, or network-attached storage (NAS).

• Strengths: fast access on the local network, works without the internet, the enterprise has full control, and no ongoing subscription.
• Weaknesses: limited by the hardware bought, the enterprise must maintain, secure and back it up, and fire, theft, flood or hardware failure can destroy the only copy.

Cloud storage

Storage rented from a provider and reached over the internet.

• Strengths: scales up or down on demand, access from anywhere, built-in redundancy and backup, and no hardware to maintain.
• Weaknesses: depends on internet access, ongoing cost, less control, and data may be held in another country. For personal information this raises data sovereignty and Privacy Act issues (see APP 8), so many organisations choose a provider region in Australia.

Portable storage media

USB flash drives, external SSDs and hard drives, memory cards and, less often now, optical discs and tape.

• Strengths: cheap, easy to carry, work offline, and useful for transferring files and for offline (air-gapped) backups.
• Weaknesses: easily lost or stolen, small or slow compared with servers, can carry malware, often unencrypted, and easy to forget to back up.

Data warehouses

A data warehouse (see Big data and data warehousing) is a storage design as much as a storage place: a large repository of cleaned historical data from many sources, in the cloud or on premises.

• Strengths: integrated, consistent history that is fast to query for reports and analysis, and it keeps heavy analysis away from live systems.
• Weaknesses: costly to design and run, needs ETL and skilled staff, and is not meant for day-to-day transactions. It concentrates personal data, so security and retention rules matter.

Comparison and judgement

Ethical Use of Data

🎯 (EC-12-07)

📌 Investigate the ethical use of data for social or enterprise research purposes

Ethics asks what we should do with data, not only what we can do or what the law allows. It matters in research, whether the purpose is social (understanding a community or informing public policy) or for an enterprise (understanding customers to improve a product). People who provide data trust researchers to treat it well, and research that breaks that trust harms both the people involved and the future of research.

Principles for ethical research with data

Australian research ethics are guided by documents such as the National Statement on Ethical Conduct in Human Research, which sets out values including research merit and integrity, justice, beneficence (doing good and avoiding harm) and respect for people. Applied to data, they become working practices:

Social research and enterprise research

Social research (universities, government, community groups) is usually reviewed by an ethics committee before it starts. Enterprise research, such as market research or analytics on customer data, may face less formal review, but it must still meet the Privacy Act, the Australian Consumer Law and the enterprise’s own code of conduct. In enterprise research the temptation is to use data because it is available. The ethical test is whether the use matches the purpose the person agreed to, and whether they would be surprised or upset to learn of it.

Worked example: publishing Bellbird trip data

The council asks Bellbird to publish its trip data as open data so that students and researchers can use it. Steps to do this ethically:

1. Check the purpose. Do the terms and privacy notice riders accepted allow this use? If not, get new consent or do not publish.
2. Remove direct identifiers. Delete names and RiderID, or replace them with random codes that cannot be traced back.
3. Reduce indirect identifiers. A precise time plus a unique station could identify a person who rides alone at 5 am. Round times to the hour, publish dates by week, and group rare combinations.
4. Suppress small numbers. Hide any cell that describes fewer than about five people.
5. Test for re-identification. Ask whether a neighbour could pick out a rider by combining this with what they already know.
6. Document and license. Publish a data dictionary, the limitations and a clear licence, and name a contact.

Social, Ethical and Legal Issues

🎯 (EC-12-03, EC-12-07)

📌 Explore social, ethical and legal issues associated with using data

• bias
• accuracy of the collected data
• metadata
• copyright and acknowledgement of source data
• intellectual property and respect for ownership, including Indigenous Cultural and Intellectual Property (ICIP)
• permissions, rights and privacy of individuals, including cultural responsibility
• security

Using data raises issues that are social (effects on people and communities), ethical (what is right) and legal (what the law requires). The seven areas below overlap: a single dataset can have a bias problem, a copyright problem and a cultural problem at once.

Bias

Bias in collection, analysis or presentation leads to unfair or wrong conclusions and can harm groups of people. Social effects include people being wrongly refused a service, or ignored because they are missing from the data. The ethical response is to check who is missing or over-represented, question how the data was labelled, and test results across different groups (see Errors, uncertainty and limitations).

Accuracy of the collected data

Decisions about real people rest on the data being correct and current. An out-of-date address can send a bill to the wrong person, and a mistaken record can wrongly flag someone as a risk. Under the Australian Privacy Principles, organisations must take reasonable steps to keep personal information accurate, up to date and complete (APP 10), and individuals can ask to see and correct their information (APP 12 and 13). Enterprises should validate at entry, review regularly and provide a way to report errors.

Metadata

Metadata is data about data: who created it, when, how, in what units, with what meaning and under what licence. A data dictionary is metadata. Good metadata makes data findable, understandable and trustworthy, and lets others judge its accuracy. It also has a privacy side. A photo can carry the time, the camera and the GPS position where it was taken, and a message can reveal who contacted whom and when, even if the content is private. Check and strip metadata before sharing files, and treat metadata about people as personal information.

Copyright and acknowledgement of source data

In Australia the Copyright Act 1968 gives creators automatic rights over original works such as writing, images, software and databases arranged in an original way. Copyright does not protect bare facts (a temperature reading is free to use), but the report, chart, photograph or dataset compilation that presents them may be protected. To use someone else’s data or work legally and ethically:

• Check the licence. Many government open datasets use Creative Commons licences that allow reuse if you credit the source (attribution).
• Acknowledge the source in your report: the creator, title, date and where you found it. This is honest, lets readers check it, and is usually a condition of the licence.
• Limited “fair dealing” exceptions exist, for purposes such as research or study, criticism or review and news reporting, but they are narrow. They are not a general permission to copy.
• Do not present someone else’s analysis or data as your own (plagiarism).

Intellectual property and respect for ownership, including ICIP

Intellectual property (IP) covers creations of the mind, protected by copyright, trade marks, patents and designs. An enterprise’s datasets, models and reports can be its IP, and it should respect the IP of others. Indigenous Cultural and Intellectual Property (ICIP) refers to Aboriginal and Torres Strait Islander Peoples’ rights in their heritage: traditional knowledge, stories, art, languages, songs, ceremonies, cultural sites and the knowledge of the natural world. ICIP is held collectively and passed down through generations, and Australian law does not fully recognise it, since copyright generally requires an identifiable author and lasts for a limited time. For this reason respect for ICIP depends on ethical practice, which usually includes:

• consulting the relevant people and communities early, and getting their free, prior and informed consent, from those with authority to give it;
• attributing the source correctly and using the names and terms the community prefers;
• agreeing how the knowledge or data will be used, stored and shared, and how the community benefits; and
• not altering, misrepresenting or using it commercially without agreement.

IP Australia publishes guidance on Indigenous Knowledge, and the AIATSIS Code of Ethics for Aboriginal and Torres Strait Islander Research is a key reference. Following the protocols of the specific community involved matters, and these can differ between communities. If in doubt, ask.

Permissions, rights and privacy of individuals, including cultural responsibility

Individuals have rights over information about them: to know what is collected, to consent, to access and correct it, and to complain. Permission is needed to collect, use or share personal data, and for photos and recordings of people. The Privacy Act 1988 and the NSW privacy laws set the legal minimum (see the next section). Cultural responsibility extends this. Some information belongs to a community, not just an individual, and some has particular protocols: for example, images and names of deceased people can cause distress in many Aboriginal and Torres Strait Islander communities, and warnings or permission are usually needed. Data about a small community can identify individuals even when names are removed. Being culturally responsible means asking who has the authority to grant permission and following their advice, respecting cultural, religious and community sensitivities, and avoiding harm or stereotypes.

Security

Ethically and legally, an enterprise that holds data must protect it. Security keeps data confidential (only authorised people see it), correct (integrity) and available when needed. Measures include access controls, encryption, secure disposal, staff training and breach response. APP 11 requires reasonable steps to protect personal information, and eligible breaches must be reported under the Notifiable Data Breaches scheme. The Cybersecurity page covers protecting data in depth.

Legal Issues in Data Handling

🎯 (EC-12-03, EC-12-07)

📌 Investigate the legal issues surrounding data collection and handling

• legislation
• authorities responsible for data protection
• data sovereignty of Aboriginal and Torres Strait Islander Peoples

Collecting and handling data is regulated by federal and state laws, and there are public authorities that oversee them. You are not expected to memorise clause numbers, but you should know the main laws, what they protect, who enforces them, and how they apply to a scenario. This section also covers the data sovereignty of Aboriginal and Torres Strait Islander Peoples, which is a set of principles and frameworks rather than a single Act.

Legislation

Laws from other countries can also apply. An Australian business that serves customers in Europe may need to comply with the European Union’s GDPR. Read the Cybersecurity page for more on cyber laws.

The Australian Privacy Principles in brief

The NDB scheme requires an organisation covered by the Privacy Act to notify affected individuals and the OAIC when a data breach is likely to result in serious harm and the organisation has been unable to prevent that harm with remedial action.

Authorities responsible for data protection

• Office of the Australian Information Commissioner (OAIC): the federal privacy regulator. It gives guidance, handles complaints, receives data breach notifications, investigates and can take enforcement action under the Privacy Act. The Privacy Commissioner is part of the OAIC.
• Information and Privacy Commission NSW (IPC): oversees NSW public sector privacy and information access, led by the NSW Information Commissioner and Privacy Commissioner.
• Australian Competition and Consumer Commission (ACCC): enforces the Australian Consumer Law and oversees the Consumer Data Right together with other agencies.
• eSafety Commissioner: deals with online harms such as cyberbullying and image-based abuse, and educates the public.
• Australian Signals Directorate’s Australian Cyber Security Centre (ASD’s ACSC): gives cyber security advice and responds to incidents.
• Australian Federal Police and state police: investigate cybercrime, along with the courts that decide cases.

Useful starting points are the OAIC’s page on the Australian Privacy Principles and the Privacy Act 1988 on legislation.gov.au. The Information and Privacy Commission NSW website has the NSW guidance.

Data sovereignty of Aboriginal and Torres Strait Islander Peoples

The word data sovereignty is used in two related ways. Nationally, it means data is subject to the laws of the country where it is stored, which is why storing data overseas matters. Indigenous data sovereignty is, in NESA’s words, the right of Indigenous Peoples to determine the creation, collection, ownership and application of data that is for and about Indigenous Peoples. For Aboriginal and Torres Strait Islander Peoples that includes data about their communities, Country and cultures.

Much of the data about Aboriginal and Torres Strait Islander Peoples has been collected by governments, researchers and enterprises, for their own purposes and without community control. It has often focused on disadvantage rather than strengths and been used in ways that did not benefit the people it described. Aboriginal and Torres Strait Islander Peoples and organisations have called for data to be governed by the communities it is about, so that it serves their priorities.

• Maiam nayri Wingara. The Maiam nayri Wingara Aboriginal and Torres Strait Islander Data Sovereignty Collective, working with the Australian Indigenous Governance Institute, set out principles for Indigenous data sovereignty and governance in Australia. In summary, they say that Aboriginal and Torres Strait Islander Peoples have the right to exercise ownership and control over data about them, from its creation and collection through to its analysis, use and storage, and that data should be relevant, contextual, accountable to the people it is about, and protective of their interests. Read the exact wording on the Maiam nayri Wingara website.
• CARE principles. The Global Indigenous Data Alliance’s CARE Principles for Indigenous Data Governance are Collective benefit, Authority to control, Responsibility and Ethics. They sit alongside the FAIR principles (Findable, Accessible, Interoperable, Reusable), which are about making data easy to use but say nothing about who has the authority over it. CARE adds the people-centred side. The Global Indigenous Data Alliance website sets out the principles in full.

These are not statutes, but they shape practice: research funding, ethics review and many government agencies expect projects that involve Aboriginal and Torres Strait Islander data to follow them, together with the AIATSIS Code of Ethics. In practice this means partnership with the community from the start (co-design), agreement on who owns and can access the data, storing it where the community agrees, sharing benefits and results with the community, and using strengths-based framing.

Curated Data and Social Behaviour

🎯 (EC-12-04, EC-12-07)

📌 Investigate the influence of curated and communicated data on social behaviour

• data literacy
• timeframes
• signals impacting on behaviour
• data swamps
• educating users

Curated data is data that someone (a person or an algorithm) has selected, organised and presented for a purpose. Communicated data is that curated data shared with an audience through news, social media, dashboards, advertising or the app in your hand. Because curation involves choices, what an audience sees is never the whole picture, and it can steer what people believe and do.

Feeds are a familiar example. A recommendation system decides which posts, videos and products you see, based on what you previously clicked. This can create a filter bubble (mostly seeing what fits your past behaviour) or an echo chamber (mostly hearing views similar to your own), and it can make some things look more popular or true than they are.

Data literacy

Data literacy is the ability to read, understand, question and communicate with data. A data-literate person asks:

1. Who collected this data, and why?
2. How was it collected and how big was the sample?
3. What has been left out?
4. Is the comparison fair, and is the chart honest (axes, scale, timeframe)?
5. Does it show a cause, or only two things happening together?

Timeframes

The period chosen can change the story. Bellbird’s daily fare revenue for the week of 7 to 13 September 2026 was $9.35, $10.00, $15.35, $6.95, $16.05, $14.20 and $18.75.

• “Fares rose from $9.35 on Monday to $18.75 on Sunday, up by more than 100 per cent.”
• “Fares fell from $15.35 on Wednesday to $6.95 on Thursday, down by more than half.”

Both statements are true, and both mislead, because a single week of seven points cannot show a trend. Each was chosen to give an impression. Ask whether the timeframe is long enough, whether it covers seasons or unusual events (school holidays, weather, a festival), and whether it was picked to support a conclusion. Compare like with like: a weekend against a weekend.

Signals impacting on behaviour

Data is also communicated through signals: cues in an interface that prompt people to act. Examples are like counts, “trending” labels, notification badges, streaks, leaderboards, countdown timers and “only 2 bikes left here” messages. They work because people copy what appears popular (social proof), fear missing out and dislike losing a streak. Signals can help (a message saying that you are using less power than neighbours can reduce energy use), but they can also pressure people into buying, staying online or comparing themselves with others. Designers should ask whether a signal serves the user or only the enterprise, and whether it is true.

Data swamps

A data lake keeps data of all kinds in its raw form for later use. Without management it becomes a data swamp: a mass of data that nobody can find, understand or trust, because nothing says what it is, where it came from, how good it is or who owns it. Duplicates, old copies and unlabelled files pile up, analysts waste time hunting, and wrong answers are drawn from stale or poor data. Swamps also hide sensitive personal data no one knows they are holding. Prevention comes from governance: metadata and a data catalogue, clear ownership, quality checks, access rules and retention limits so old data is removed.

Educating users

Enterprises, schools and governments share responsibility for helping people to use data critically.

• Enterprises can label the source and date of the data, show uncertainty and limits, explain why a recommendation was made, and let users change or reset personalisation.
• Users can check a claim against other sources, look at the original data, think about who benefits, and adjust their privacy and feed settings.
• Schools and public agencies teach data and media literacy; the eSafety Commissioner provides resources for young people on managing their online experience.

Summarising Data in a Spreadsheet

🎯 (EC-12-05, EC-12-08)

📌 Summarise data using a spreadsheet

A spreadsheet turns a list of records into totals, averages and counts that answer a question. This part uses one worked dataset from start to finish: 18 trips from Bellbird Bikes, 7 to 13 September 2026. The values are fictional but consistent, so you can type the data into your own spreadsheet and check every result on this page. Formulas use the names in NESA’s Course Specifications (SUM, MAXIMUM, MINIMUM, COUNT, MEAN, STANDARD DEVIATION, IF and LOOKUP); the spelling below is the form used by Excel and Google Sheets. The full function list is in the Toolkit.

Step 1: enter the data

Type the headings in row 1 and the 18 trips in rows 2 to 19. Set each column to the right data type: Date for TripDate, whole number for Minutes, decimal for Km, currency for Fare. Column G (Fare) and column H (TripLength) are calculated, so leave them for step 3.

Step 2: put the rules on their own sheet

Prices belong in cells, not inside formulas, so they can be changed in one place. Create a second sheet named Rates. The rider types are in alphabetical order because LOOKUP needs the search column sorted in ascending order.

Step 3: calculate each trip

In G2 the fare is the unlock fee plus the per-minute price times the minutes. Each price is found with LOOKUP on the rider type in D2:

=LOOKUP(D2,Rates!$A$2:$A$3,Rates!$B$2:$B$3)+LOOKUP(D2,Rates!$A$2:$A$3,Rates!$C$2:$C$3)*E2

Fill down to G19. The dollar signs make Rates!$A$2:$A$3 an absolute reference so it does not slide when the formula is copied, while D2 and E2 are relative and move to D3, E3 and so on. Rates! is how one sheet refers to another. Check the first row by hand: T001 is a Member, so 0.00 + 0.10 × 12 = $1.20.

In H2 use IF to label long trips, then fill down:

=IF(E2>=30,"Long","Short")

Step 4: summarise

Put the summary in J1:K8, away from the records. Fixed ranges such as E2:E19 make it clear exactly what is being counted.

COUNT is applied to the Minutes column because COUNT counts numbers: TripID values such as T001 are text, so =COUNT(A2:A19) would wrongly give 0. Other summaries help decisions: =IF(K3>=100,"Target met","Below target") gives “Below target” against a $100 weekly target.

To summarise by group (fares for each station) you need a filter, subtotals or a pivot table, covered in the next sections. Software also offers functions such as SUMIF and COUNTIF for this; they are extensions, not in NESA’s list.

Check your work

• Reasonableness. The mean (23.1) lies between the shortest (7) and longest (55) trips, and total fare is not larger than 18 × the top fare.
• Independent check. Add the three station totals from a pivot table; they must equal K3.
• Formulas visible. Show the formulas (or print them) so a reader can see how each number was produced.

Spreadsheet Analysis Features

🎯 (EC-12-05, EC-12-08)

📌 Collate information using spreadsheet analysis features, including charts, statistical analysis and what-if modelling

“Collate” means gather and organise information so it can be compared. Spreadsheets have three families of analysis features: charts (see the pattern), statistical analysis (measure it) and what-if modelling (test changes). All examples continue with the Bellbird sheet from the previous section.

Charts

NESA lists five chart types: bar, column, line, scatter and pie. Choose by the question:

To make a chart: select the data (including headings), insert the chart type, then configure it: a clear title, labelled axes with units, a legend only if there is more than one series, sensible colours, a vertical axis that starts at zero for bars and columns, and data labels where they help. Then save or export the chart as an image or PDF for use in a report. If you need to make a chart from summarised figures, first build a small table of them (or a pivot table) and chart that.

Statistical analysis

NESA names two statistical functions: MEAN (the average) and STANDARD DEVIATION (how spread out the values are around the mean). Applied to trip minutes:

The mean and standard deviation can flag outliers. The longest trip is 55 minutes: =(E14-K4)/K8 gives (55 − 23.11) ÷ 14.22 = 2.24 standard deviations above the mean, so it is unusual, though not impossible. Investigate before removing it: it might be a real leisure ride (T013 at Riverside Park) or a bike that was not docked properly.

A trend line on the scatter graph adds a fitted straight line. For these 18 trips the software gives roughly km = 0.184 × minutes + 0.255 with R² = 0.996, so it predicts about 5.8 km for a 30-minute trip. Trend lines and the SLOPE, INTERCEPT and FORECAST functions are extensions: use them to describe a relationship but do not read them as proof of cause.

What-if modelling

A what-if model is a spreadsheet in which changing an input (an assumption) updates every result, so you can ask “what would happen if…?”. It works because the fare formulas point to the Rates sheet instead of containing typed prices.

1. Change an input and watch. Change the casual per-minute price (Rates!C2) from 0.25 to 0.30. Every casual fare recalculates and the total fare (K3) rises from $90.65 to $104.00.
2. Goal Seek works backwards. Data > What-If Analysis > Goal Seek: Set cell K3, To value 100, By changing cell Rates!C2. The software finds a casual price of about $0.285 a minute that makes total fare $100.00.
3. Scenarios store named sets of inputs to compare side by side. The Scenario Manager or a small table of alternatives gives:

A data table (one-variable) lists several values of one input and shows the result for each. For the casual per-minute price of $0.20, $0.25, $0.30 and $0.35 the total fare is $77.30, $90.65, $104.00 and $117.35. Each extra 5 cents adds $13.35, because casual riders rode 267 minutes in total.

Filtering, Grouping and Sorting

🎯 (EC-12-05, EC-12-08)

📌 Filter, group and sort data in a spreadsheet to process and display information

• linking multiple sheets to extract data and create summaries
• applying conditional formatting
• making data comparisons
• designing forms and reports

Once data is in a sheet, three operations reshape how it is displayed without changing the underlying records. Sorting reorders rows, filtering hides rows that do not meet a condition, and grouping collects rows with the same value so they can be subtotalled or summarised. Each uses the Bellbird sheet from the previous sections.

Sort

Select the whole table (so rows stay together), then Data > Sort. You can sort on several columns: for example, Station A to Z, then Minutes largest to smallest. The sort order of text is alphabetical and of dates is chronological only if they are stored as real dates. Always include the header row in the selection and keep the records together, or one column will be sorted against the wrong rows.

Filter

Turn on filters and set conditions using the relational operators NESA lists (equal to, not equal to, greater than, greater than or equal to, less than and less than or equal to). Filtering for RiderType equals Casual and Minutes greater than or equal to 30 leaves four rows: T008, T013, T014 and T018. Their fares are $11.50, $14.75, $10.50 and $12.75, totalling $49.50, or 55 per cent of all fares from just four of the 18 trips. The other rows are hidden, not deleted, and the status bar shows how many rows match. Filters combine conditions with AND (all must be true) or OR (either can be true).

Group

To total by station, sort by Station, then use Data > Subtotal (“at each change in Station, use function Sum, for Fare”). The sheet inserts subtotal rows and an outline, so you can collapse to just the totals:

A pivot table does the same job more flexibly and is covered in the dashboard section. The trip explorer there lets you filter, sort and group these 18 trips yourself.

Linking multiple sheets to extract data and create summaries

Real workbooks split data across sheets: the trip records, the Rates table, a Stations list and a Summary sheet. Formulas pull data across sheets by putting the sheet name and an exclamation mark before the reference, such as Rates!B2 or Trips!G2:G19. Because the sheets are linked, changing a value in one place updates every sheet that uses it.

Add a Stations sheet, sorted alphabetically (Hospital 8 docks, Market Square 12, Riverside Park 10), and a Summary sheet with one row per station:

Filled down, the other rows give Market Square 6 trips, $17.60, 12 docks and 0.50 trips per dock, and Riverside Park 7 trips, $62.55, 10 docks and 0.70. Riverside Park is the busiest per dock, which supports adding docks there. COUNTIF and SUMIF are extensions: within NESA’s list you can get the same numbers by filtering by station and reading COUNT and SUM, or from a pivot table.

Applying conditional formatting

Conditional formatting changes how a cell looks when its value meets a rule, so patterns and problems stand out without reading every number. Home > Conditional Formatting:

• Cell value rule: shade Fare cells that are greater than or equal to 10. This highlights T008, T013, T014 and T018.
• Formula rule: to highlight whole rows for casual riders, apply =$D2="Casual" to A2:H19. The $ locks the column, so every cell in the row checks column D.
• Statistical rule: highlight Minutes above the mean plus one standard deviation, =$E2>$K$4+$K$8 (above 37.3 minutes): T008, T013, T014 and T018 again.
• Data bars and colour scales show the size of a value inside the cell.

Use colour sparingly and add a key. Do not rely on colour alone to convey meaning, because some readers cannot distinguish colours.

Making data comparisons

Comparisons turn numbers into judgements. Compare like with like, and express the difference in a way the reader can use:

Average fare per trip is $8.42 for casual riders and $1.66 for members. So although members and casual riders each made nine trips, casual riders produced 84 per cent of the fare revenue. The comparison also shows a limit: members pay a subscription that is not in this dataset, so fares alone understate their value.

Designing forms and reports

A form is a screen for entering or viewing one record at a time. A report is a formatted output of selected data for reading or printing. In a spreadsheet you can build both.

Data entry form. Design it on its own sheet or with a form tool.

• Clear labels and a logical order that matches the paper source.
• Data validation: a drop-down list for Station and RiderType (taken from the Stations and Rates sheets), a whole number between 1 and 180 for Minutes, and a valid date range for TripDate, each with a helpful error message.
• Lock and protect the formula cells (Fare, TripLength) so users cannot overwrite them, and leave only the input cells unlocked.

Report. Choose the rows and columns the reader needs; give it a title, date and source; sort and group logically; format numbers; set the print area, orientation and margins; add a repeating heading row, a header and footer with page numbers, and place page breaks between logical groups (for example, one page per station). Preview before printing.

Developing a Data Dashboard

🎯 (EC-12-05, EC-12-11)

📌 Apply spreadsheet analysis features to develop a data dashboard

• graphs
• pivot tables and slicers

A data dashboard puts the handful of measures that matter on one screen so a decision-maker can see the state of the enterprise at a glance and dig into the detail. It is built from the features you have already used (formulas, charts) plus two that make it interactive: pivot tables and slicers. The audience for this one is Bellbird’s operations manager, who checks it each morning to decide where to move bikes and where to add docks.

Step 1: plan the dashboard

Start with the questions, not the charts. The manager asks: How many trips and how much revenue? Which station earns the most? How does that change through the week? Do casual riders and members behave differently? Choose a small number of key measures (KPIs) and one display for each question. A dashboard with fifteen charts answers none of them.

Step 2: prepare the data

Convert the trip records into a table (Insert > Table). A table has named columns and grows automatically when new trips are added, so the dashboard keeps working. Check that every column has a single data type and no blank headings or merged cells.

Step 3: build pivot tables

A pivot table summarises a table by dragging fields into Rows, Columns and Values areas, with no formulas to write. Insert > PivotTable, then:

1. Rows: Station. Columns: RiderType. Values: Sum of Fare.
2. Add a second pivot: Rows: TripDate; Values: Count of TripID and Sum of Fare.

The same layout with Count of TripID shows Market Square 2 casual and 4 member trips, Riverside Park 5 and 2, and Hospital 2 and 3. Together the tables show that Riverside Park’s revenue comes mainly from casual riders (5 of its 7 trips, $56.75 of $62.55). A pivot table can also show the values as a percentage of the total, count, average or maximum. A pivot table is a snapshot: after the data changes, choose Refresh.

Step 4: add graphs

Create charts from the pivot tables (pivot charts) so they update together. Match each chart to a question:

• Column chart of fare by station, with casual and member as stacked series: which station earns most and from whom.
• Line chart of trips and fares by day: the weekly pattern.
• Scatter graph of minutes against km with a trend line, if the manager wants to know about distance.

Add key figures as cells with a large font: trips =COUNT(E2:E19) (18), total fare =SUM(G2:G19) ($90.65), mean trip =AVERAGE(E2:E19) (23.1 minutes) and longest trip =MAX(E2:E19) (55 minutes). Conditional formatting can colour a key figure against its target.

Step 5: add slicers

Slicers are buttons that filter pivot tables and their charts. Insert > Slicer, choose Station and RiderType (and optionally a Timeline for dates), then connect each slicer to every pivot table (Report Connections) so one click updates the whole dashboard. Select Riverside Park and Casual, for example, and every figure and chart on the dashboard now shows only casual trips at Riverside Park: 5 trips, $56.75.

Step 6: lay out and test

Put the most important figures top left, group related items, use the same fonts and colours throughout, label every chart with a title and units, and leave white space. Then test: click each slicer, add a new trip and refresh, and check that the totals match the source table. Hide the working sheets and protect the dashboard sheet so formulas cannot be overwritten. Design principles for communicating with charts are in Data visualisation.

Try it: pivot table and slicers on the Bellbird trips

The tool below has the same 18 trips. Use the station and rider-type buttons like slicers, set a minimum trip length, click a column heading to sort, and change the pivot table’s rows, columns and values. The query at the bottom shows the same filter and sort as SQL.

The highest mean is Riverside Park casual riders: 5 trips averaging 41.4 minutes (207 minutes in total). It suggests leisure use rather than commuting, so longer-hire options or e-bikes there may suit them.

Developing a Flat-File Database

🎯 (EC-12-03, EC-12-08)

📌 Develop a flat-file database

A flat-file database stores all its data in one table (one file) of records and fields. It is the simplest kind of database and is suitable for a short list, such as a mailing list or a stock list, where each record stands alone. Spreadsheets are often used as flat files, but database software adds stricter data types, validation, queries, forms and reports. NESA’s database software specification asks that flat-file and relational databases can both be created. This section develops a flat file for Bellbird, then shows where the design runs into trouble.

Step 1: plan the fields and write the data dictionary

List what one record must hold, decide a data type, format and size for each field, and record them in a data dictionary (NESA’s layout is in the Toolkit). A flat file for trips must also hold the rider’s details on every record:

Step 2: create the table and enter the data

1. Create a new database and a table named Trips. Add each field with its data type and size.
2. Set TripID as the primary key so no two records can have the same value.
3. Add validation rules: Minutes must be a whole number from 1 to 180; RiderType must be Member or Casual; TripDate cannot be in the future.
4. Import the trips from the spreadsheet (import and export are part of NESA’s database software list) or type them in through a form.

Step 3: search and sort

Queries in database software combine the relational operators in NESA’s list (EQUALS, NOT EQUAL TO, GREATER THAN, GREATER THAN OR EQUAL TO, LESS THAN, LESS THAN OR EQUAL TO, CONTAINS, DOES NOT CONTAIN) with the logical operators AND, OR and NOT, and sort on more than one field.

Brackets matter. Without them, AND is usually worked out before OR, and the fourth query would return every Market Square trip regardless of length.

Step 4: forms and reports

Create a form for entering trips (labelled boxes, a drop-down for RiderType, a date picker) and a report for a manager: trips sorted by station, with a total fare at the end of each group. The next section shows how to design forms and reports for different users.

Where a flat file struggles

Every trip repeats the rider’s details. Look at Ava Chen’s three trips:

• Redundancy: the name and rider type are typed again for every trip, wasting space and effort.
• Inconsistency: the third record has “A. Chen” and “Mkt Square”, so a search for “Ava Chen” or “Market Square” misses it. This is a data integrity problem.
• Update problems: if Ava upgrades from Casual to Member, every one of her records must be edited, and missing one leaves the file contradicting itself.

These weaknesses are why larger systems use a relational database, described next. A flat file remains a good choice when there is one kind of thing to record, few records, one user, and no need for links between tables.

Designing a Relational Database

🎯 (EC-12-03, EC-12-08)

📌 Apply computational thinking to design a relational database with appropriate user views

• develop a data dictionary
• linking tables via key fields
• sort and search data, including using structured query language (SQL)
• using forms and reports

A relational database splits data into several linked tables so each fact is stored once. It suits Bellbird’s growing needs: many riders, many bikes, many stations, and thousands of trips. This section designs one from the flat file, using computational thinking, and covers the four NESA items: a data dictionary, key fields that link tables, sort and search (including SQL), and forms and reports. The Toolkit explains normalisation and schemas and the SQL syntax in detail; here they are applied.

Computational thinking applied to the design

Develop a data dictionary

Each table gets a data dictionary. The Trips table holds the events and the keys that link to the other tables. Fare is stored on the trip as the amount actually charged: the rate might change next year, and old trips must keep the fare that was charged at the time.

Stations has StationID (Text, SN, size 2, primary key), StationName (Text, 20) and Docks (Integer, NN, 2). Bikes has BikeID (Text, BNN, 3, primary key) and Model (Text, 10: Standard or E-bike). Personal details such as names are kept only in Riders, so they can be protected and controlled in one place.

Link tables via key fields

A primary key uniquely identifies each record in its table. A foreign key is a field that holds a primary key value from another table, which creates the link. One rider takes many trips, so Riders and Trips have a one-to-many relationship, with 1 at the Riders end and ∞ at the Trips end. The same holds for Bikes and Stations. The database can enforce referential integrity: it will refuse a trip whose RiderID does not exist in Riders. The schema, in NESA’s notation, is:

Repairs would hold BikeID as a foreign key, because one bike can have many repairs. The primary key would be a RepairID.

The data in the linked tables (18 trips, six riders, three stations, five bikes) is the same data as the spreadsheet, split so each fact is stored once:

Sort and search data, including using SQL

NESA’s SQL has four keywords: SELECT (what to display), FROM (the tables), WHERE (the search criteria, which can come from several tables) and ORDER BY (the sequence). Tables are joined by listing them in FROM and matching the linked key fields in WHERE. Dates use the same day/month/year format as the data, and the field must have the Date type so that comparisons follow the calendar.

Query 1: list the casual riders in surname order (one table).

SELECT FirstName, Surname
FROM Riders
WHERE RiderType = 'Casual'
ORDER BY Surname ASC

Query 2: trips of 20 minutes or more on or after 10 September 2026, longest first (one table, two conditions).

SELECT TripID, TripDate, Minutes
FROM Trips
WHERE TripDate >= '10/09/2026' AND Minutes >= 20
ORDER BY Minutes DESC

Query 3: the rider, date and length of every trip of 25 minutes or more on an e-bike, longest first (three tables joined by their keys).

SELECT Riders.FirstName, Riders.Surname, Trips.TripDate, Trips.Minutes
FROM Trips, Riders, Bikes
WHERE Bikes.Model = 'E-bike'
AND Trips.BikeID = Bikes.BikeID
AND Trips.RiderID = Riders.RiderID
AND Trips.Minutes >= 25
ORDER BY Trips.Minutes DESC

Notice how the two lines Trips.BikeID = Bikes.BikeID and Trips.RiderID = Riders.RiderID match each foreign key to its primary key. Leaving one out gives every combination of rows, which is wrong.

SELECT Stations.StationName, COUNT(Trips.TripID), SUM(Trips.Fare)
FROM Trips, Stations
WHERE Trips.StationID = Stations.StationID
GROUP BY Stations.StationName

Using forms and reports (appropriate user views)

Different users need to see and do different things, so each gets a tailored user view: a form or report showing only the fields they need. This is also a security measure, because each user sees only the data required for their job (least privilege); NESA lists applying security to the database as a feature.

To design a form: choose the table or query it is based on, place fields in a logical order with clear labels, use drop-downs and validation to prevent errors, and add navigation and save buttons. To design a report: base it on a query, group and sort the records, add totals, and give it a title, date and page numbers. Test both with real users, including a mistake, to check that the design prevents errors.

Machine Learning and Statistical Modelling

🎯 (EC-12-05, EC-12-06)

📌 Explore how machine learning and statistical modelling are used in data analytics to analyse big data, and as a prediction tool

Big datasets are too large to study by eye. Two families of methods help analysts find patterns and make predictions. NESA’s Course Specifications say students should know that machine learning and statistical modelling can be used independently or together to analyse datasets and make predictions, and should be aware of how they are used in the real world. You do not need to know the mathematics inside them.

They overlap. The two differ mainly in emphasis: statistical modelling usually stresses explaining the relationship and its uncertainty (does rain reduce trips, and by how much?), while machine learning usually stresses accurate prediction on new data, even where the reasons are complex. Analysts often use both: a statistical model to understand which factors matter, and a machine learning model, built on those factors, to forecast.

Three kinds of task

Regression: fitting a line and learning it by trial

A regression model finds the line that comes closest to the data. A machine learning method can learn the line by starting with a guess and repeatedly adjusting it to reduce the error, a process called gradient descent. The animation below trains a line on a small set of study hours and exam scores (not the bike data), and shows why the size of each adjustment (the learning rate) matters.

Check the model on data it was not trained on, and check that the relationship is sensible. A line that fits the past well can still predict badly if conditions change.

Classification: deciding by resemblance

One simple classifier is K-nearest neighbour. To classify a new item, it looks at the K most similar known items and takes a vote. The animation uses plant measurements; the same idea would classify a Bellbird trip by comparing its minutes, distance and start time with past trips whose purpose is known.

With two classes and an odd K, a vote cannot tie, so one class always wins.

Clustering: finding groups nobody labelled

In clustering, the algorithm is given no answers. It groups items by similarity, and people then interpret the groups. Bellbird might find one group of short weekday trips from Market Square and Hospital (commuters and staff), and another of long weekend trips from Riverside Park (leisure riders), and could design a pass for each. The algorithm finds the groups, but only people can decide whether they mean anything.

Using big data, and using it well

Machine learning improves with large, varied data, so it is closely tied to big data. Real-world uses include weather forecasting, recommending videos and products, detecting card fraud, planning delivery routes, and helping doctors to read medical images. Cautions:

• Data quality and bias: a model learns whatever is in its training data, including its bias (see the bias cycle in the Errors, uncertainty and limitations section).
• Overfitting: a model that memorises the training data performs poorly on new data. Test on data it has not seen.
• Explainability: some models give an answer without a reason a person can check, which is a problem when the decision affects someone (a loan, a job).
• Correlation is not cause: a prediction does not prove why something happens.
• Human oversight: a person must remain responsible for decisions, and privacy and consent laws still apply to the data used.

The Intelligent systems page covers AI, decision support and expert systems in more depth.