DATA INPUT: DATA COLLECTION AND

VALIDATION
Computing 5
 KEYWORDS

▪ Data model - A digital picture of a real situation (broken into small data items).
▪ Simulation - A computer program that shows real-life behaviour and predicts outcomes.
▪ Data - Raw facts and numbers.
▪ Spreadsheet - Rows + columns used to organize data and calculate.
▪ Database - Used to store, organize and access data electronically.
▪ Data-capture form - A ready-made form to collect correct data.
▪ Data item - One small piece of data (like name or age).
▪ Validation - Automatic check to reduce mistakes.
▪ Input mask - A rule that controls how data must be entered.
▪ Data requirements - List of data needed to make a correct model or simulation.
▪ What-if - Change one value in a spreadsheet to see how it affects other values.
 WARM UP
Difference between spreadsheet & database

Spreadsheet
Used for calculations and charts.

Database
Used for storing and searching lots of data.
 PART 1 – SIMULATION VS DATA MODEL

Task 1
Which one is a simulation? Which one is a data model?

▪ Image 1 (car graphic) → Simulation

Shows behaviour like movement.

▪ Image 2 (cars sold table) → Data model

Shows real-life data using numbers.
 TASK 2
Key differences
▪ Simulation shows behaviour
▪ Data model stores facts
▪ Simulation predicts outcomes
▪ Data model helps understand information
▪ Simulation uses formulas
 PART 2 – APPLICATIONS
DATABASE FEATURES
▪ Stores large amounts of data
▪ Searches and filters quickly
▪ Uses validation to reduce errors
▪ Can link tables together
▪ Example: Microsoft Access
 SPREADSHEET FEATURES
▪ Uses formulas for calculations
▪ Makes charts and graphs
▪ Supports “what-if” changes
▪ Organises data in rows and columns
▪ Example: Microsoft Excel
 PART 3 – CHOOSING THE RIGHT TOOL

1. Library book loans 3. Exam scores + averages

Use database Use spreadsheet
Reason: needs organised records + searching. Reason: calculations needed.

2. Student contact details 4. Athletics timing

Use database Use spreadsheet
Reason: large data + updates needed. Reason: comparing times & formulas.
 SCENARIO – MUSIC DEPARTMENT
Data currently recorded
▪ Lesson/exam dates & times
▪ Instrument + current grade
▪ Assigned tutor
▪ Exam grade level
▪ Payment received
PROBLEMS WITH THE CURRENT SYSTEM

▪ Missing data
▪ Wrong data entered
▪ Tutors double-booked
▪ Wrong exam grades entered
▪ Poor table structure
 WHAT YOU WILL HELP WITH
Improving the database
▪ Add correct data fields
▪ Add validation
▪ Add input masks
▪ Create proper table links
▪ Stop double-booking
▪ Store exam grades correctly
CREATING A SPREADSHEET

▪ To calculate fee discounts

▪ To track scholarship amounts
▪ To help with budgeting
▪ To calculate tutor payments
FESTIVAL TICKET MODEL
▪ Organise ticket distribution
▪ Avoid duplicates
▪ Track who gets which ticket
▪ Manage seat availability
FINAL OUTCOME

▪ Clean, correct data

▪ No booking mistakes
▪ Easy fee calculations
▪ Smooth music department work
 WHAT ARE USER REQUIREMENTS?

▪ User requirements - What the user expects the data model to do.

▪ They tell what the database must do.

▪ They help designers understand what data is needed.
▪ They guide how the model should work.
▪ They make sure the final database meets real-life needs.
 A GOOD DATABASE SHOULD…
▪ Store all the needed data items
▪ Make sure required data is entered
▪ Avoid storing unnecessary data
▪ Reduce chances of errors during data entry
▪ Give an easy way to add data
▪ Give an easy way to use and view data
▪ Match the structure of the real-life problem
▪ Produce accurate results (because data is complete)
 WHY STRUCTURE MATTERS

▪ Missing data → incomplete model

▪ Incomplete model → wrong results
▪ Correct structure → correct processing
 HOW DATA IS STORED
▪ Data is kept in fields (small information pieces).
▪ Each field has a heading (label)
▪ Good headings help users understand the data.
▪ Together, fields create the structure of the database.
 ENTITIES AND RECORDS
Entities
People or things stored in a database.

▪ An entity = one type of thing (like a student).

▪ A record = all information about one entity.
▪ Example:
Entity → Student
Record → Name, age, class, contact
BEST PRACTICE: BREAK DATA DOWN

▪ Data should be stored in the smallest possible units

▪ This helps make the model complete.
▪ Smaller units = fewer mistakes + better accuracy.
DATABASE EXTRACT
Student Name Address Age Stage

Keirin Giza 13 8

Sameera Khan 10 Elmessaha Street, Cairo 13 8

Anand Adel Cairo 13 8

 PROBLEMS IN THE DATABASE EXTRACT

Identify issues for each student

▪ Student names stored as one long text (hard to search)
▪ Spaces/inconsistent formatting in names (e.g., Sameera Khan)
▪ Addresses stored inconsistently (full address vs. city only)
▪ Age and stage data may not be properly formatted
▪ Could cause errors when searching or sorting
 BREAKING DOWN DATA ITEMS
How to split names & addresses
▪ Student Name → First Name, Last Name
▪ Address → Street, City, Postal Code
▪ This creates atomic data: smallest pieces of data
▪ Easier for searching, sorting, and validating
 SEARCHING PROBLEMS
Searching students with Age = 13 or Stage = 8
▪ Inconsistent data → may miss some students
▪ Text stored in number fields → calculations fail
▪ Mixed formats (like “8” vs “Stage 8”) → errors in filtering
 RECOMMENDATIONS FOR AGE & STAGE

▪ Use correct data types:

Age → Number (Integer)
Stage → Number (Integer)

▪ Add validation rules to prevent wrong entries

▪ Avoid storing numbers as text
 COMMON MS ACCESS DATA TYPES
Data Type Use

Text Mix of letters & numbers; not for calculations

Number For calculations; e.g., age, stage

Date/Time Record dates & times

Currency Store money values

Autonumber Unique number automatically assigned to each record

Yes/No Only two values; e.g., True/False

Lookup Choose value from a list

 PRIMARY KEY FIELD

What makes a suitable primary key?

▪ Should uniquely identify each record
▪ Example: Autonumber field
▪ Ensures no two students have the same ID
▪ Cannot be reused even if a record is deleted
 IMPORTANCE OF CORRECT DATA
TYPES
▪ Wrong data type → processing errors
▪ Example: Text instead of Number → cannot calculate totals
▪ Correct data type → smooth calculations, searches, and reports
▪ Use data dictionary to describe each field before creating table
 EXAMPLE DATA DICTIONARY
Field Name Data Type Field Length

StudentID (Primary Key) AutoNumber

StudentSurname Text 25

StudentFirstName Text 25
 KEYWORDS

▪ Atomic data structure - Data broken into the smallest possible units.
▪ Data type - Type of data stored, e.g., number, string, date.
▪ Primary key field - Unique field to identify each entity.
▪ Integer - Whole numbers only.
▪ Lookup - Provides a list of options for users to choose from.
▪ Process - Operation on data, e.g., query database or calculate in spreadsheet.
▪ Data dictionary - Table showing field headings, data types, and key features.
 PRACTISE – USER REQUIREMENTS
What must the database record?
▪ Full name and Stage of every student
▪ Type of instrument & current grade
▪ Date & time of lessons or exams
▪ Whether slot is lesson or examination
▪ Exam grade if slot is examination
▪ Full name of the tutor
▪ Whether lesson is online or in-person
▪ Fee for each lesson/exam
▪ Whether payment has been made
 SCHOOL’S CURRENT DATABASE
Exami
Current Online/In- Fee
Name Stage Instrument Lesson Time Examination Time nation Tutor Name
Grade Person Payable
Grade

S. Kahn 8 Drums 3 12 Jan, 9 AM 4 Uma Fouad In-Person $20.00

Anand Amali 8 Guitar 4 12 Jan, 6 PM 5 Ur Youssef Online $20.00

Satyakar Karr 8 Voice 4 13 Jan, 10 AM Omari Fawzy In-Person $15.00

Reem Ragab 8 Drums 5 13 Jan, 11 AM 6 Dalila Atef In-Person $20.00

Ali Hassan 8 Voice 1 13 Jan, 1 PM Dalila Atef In-Person $15.00

Dina Amar 8 Guitar 6 14 Jan, 9 AM Uma Fawad In-Person $15.00

 RECORDING LESSON/EXAM TIMES

▪ Use a single “Session Type” field: Lesson or Exam

▪ Date and Time stored in separate fields
▪ Helps avoid empty rows and confusion
▪ Makes the database atomic (smallest unit of data)
▪ Easier to sort and search by Lesson or Exam
 PART 1 – BREAKING DOWN FIELDS

Are all fields atomic?

No – some can be broken down further:
▪ Name → First Name, Last Name
▪ Address → Street, City
▪ Tutor Name → First Name, Last Name
▪ Lesson/Exam info → Date, Time, Session Type
 ADDING NEW FIELDS & HEADINGS
▪ StudentFirstName, StudentLastName
▪ TutorFirstName, TutorLastName
▪ LessonDate, LessonTime, SessionType
▪ ExamGrade, CurrentGrade
▪ Instrument, Online/InPerson, FeePayable, PaymentStatus
 FIELDS WITH MISSING DATA
▪ Empty rows cause errors in searching & reports
▪ Solution: split fields into atomic units and use mandatory fields/validation
 RECORDING GRADES
▪ Students must progress sequentially (1 → 8)
▪ CurrentGrade shows the student’s current progress
▪ ExamGrade shows the grade for this session
▪ Both are needed to track progress accurately
 RESTRUCTURING THE DATABASE
▪ Break all fields into atomic units
▪ Use lookup lists for fields with limited options, e.g.:
▪ Instrument → Guitar, Drums, Voice, Piano
▪ Online/In-Person → Online, In-Person

▪ Ensure primary key exists (LessonID)

▪ Use correct data types for each field (Number, Text, Date/Time)
 Field Name Data Type Notes

LessonID (Primary Key) Autonumber Unique ID for each session

StudentFirstName Text 25 characters

NEW FIELDS & StudentLastName Text 25 characters

HEADINGS (SUGGESTED) Stage Number Integer

Instrument Lookup Guitar, Drums, Voice, Piano

CurrentGrade Number Integer 1–8

SessionType Lookup Lesson / Exam
LessonDate Date/Time Date of lesson/exam

LessonTime Date/Time Time of lesson/exam

ExamGrade Number Integer 1–8 (if applicable)

TutorFirstName Text 25 characters

TutorLastName Text 25 characters

OnlineInPerson Lookup Online / In-Person

FeePayable Currency e.g., $15, $20

PaymentStatus Yes/No Paid / Not Paid
 NOTES ON FIELD LENGTH
▪ Keep field lengths reasonable (e.g., 25 characters for names)
▪ Longer fields → larger file size → slower processing
▪ Smaller fields → faster processing and storage optimization
 ADVANTAGES OF LOOKUP LISTS
▪ Prevents typing mistakes
▪ Ensures consistency in data entry
▪ Useful for fields with limited options, e.g.:
▪ Instrument
▪ SessionType (Lesson/Exam)
▪ Online/In-Person
 PART 2 – USING THE
MUSICSCHOOL DATABASE
▪ Open [Link] provided by your teacher
▪ This database helps monitor lesson and exam bookings for the year
 STEP 1 – OPEN TABLE
▪ Open bookingsTBL in Design View
▪ To do this: click View → Design View
▪ This lets you amend data types and field sizes
 STEP 2 – SET DATA TYPES
Change each field’s data type using the dropdown list:
▪ SessionID → AutoNumber
▪ StudentInitial → Short Text
▪ StudentSurname → Short Text
▪ Stage → Number
▪ CurrentGrade → Number
▪ Lesson/Exam → Lookup Wizard
▪ SessionDate → Date/Time
▪ FeePayable → Currency
▪ FeePaid? → Yes/No
▪ TutorSurname → Short Text
▪ Online/InPerson → Lookup Wizard
 STEP 3 – SET FIELD SIZES
▪ Use Field Size to match your data dictionary
▪ Example:
▪ Short Text → 25 characters for names

▪ Proper field size → better storage, faster processing

 STEP 4 – CREATE LOOKUP LIST
FOR INSTRUMENTS
▪ Select Instrument field → choose Lookup Wizard
▪ Select “I will type the values I want” → Next
▪ Enter possible instruments:
▪ Guitar, Drums, Voice, Piano

▪ Adjust column widths if needed

▪ Click Finish to create the dropdown list
 STEP 5 – USING THE LOOKUP LIST

▪ Switch from Design View → Datasheet View

▪ Click on Instrument for any student → see the dropdown list
▪ Choose from the available instruments
 STEP 6 – LOOKUP
LISTS FOR OTHER FIELDS
Field Heading Lookup Values

CurrentGrade 1, 2, 3, 4, 5, 6, 7, 8

Lesson/Exam? Lesson, Exam

▪ Ensures consistent and accurate data entry

 NOTES
▪ Lookup lists prevent typing mistakes
▪ Easy for users to select correct values
▪ Makes the database more user-friendly and accurate
 COMPUTATIONAL THINKING –
ABSTRACTION
▪ Compare bookingsTBL with the original database in the Practise box
▪ Discuss how the new structure helps the music department monitor bookings
easily
 IMPORTANCE OF PRIMARY KEY
▪ Each session has a unique ID → SessionID
▪ Prevents confusion or duplicate records
▪ Makes it easy to connect tables in larger databases
 NAMES BROKEN DOWN
▪ Students’ names → Initial + Surname
▪ Tutors’ names → Initial + Surname
▪ Helps with sorting, searching, and avoiding errors
 RENAMED FIELD HEADINGS
▪ Headings now clearly show what data is recorded
▪ Example: SessionDate, SessionTime, SessionType
▪ Easier to understand and enter data correctly
 LESSONS & EXAMINATIONS
RECORDED

▪ Lesson and Exam data separated into date, time, and type
▪ Makes it easy to track progress and avoid double-booking
▪ Updated headings prevent confusion from the old structure
IMPROVEMENTS IN
[Link]
▪ Atomic fields → smaller, manageable units
▪ Primary key ensures unique records
▪ Lookup lists prevent typing mistakes
▪ Data validation ensures reasonable, sensible entries
 DATA VALIDATION
▪ Automatically checks entries when data is added
▪ Rejected entries show error messages
▪ Good error messages:
▪ Alert user something is wrong
▪ Explain what went wrong
▪ Help user fix the mistake
 DID YOU KNOW?
▪ Large databases often use multiple tables connected by primary keys
▪ Example: Medical practice database:
▪ One table for patients
▪ Another table for appointments

▪ Improves efficiency and organization

 VALIDATION CHECKS IN [Link]

▪ Validation checks ensure data is entered correctly

▪ Prevent errors and make the database reliable

Presence Check
▪ Ensures important fields are not left blank
▪ Example: All data items must be completed when making a booking

Length Check
▪ Ensures a field has the correct number of characters
▪ Example: Student or tutor initials must be 1 character
 Range Check
▪ Ensures data is within a specific range
▪ Example: Student grades can only be 1–8

Format Check
▪ Ensures data matches a fixed pattern
▪ Example: Surnames start with an uppercase letter, then lowercase letters

Type Check
▪ Ensures data matches the assigned data type
▪ Example: Fee payable must be in currency format
 WHY VALIDATION MATTERS

▪ Prevents mistakes
▪ Keeps data accurate and consistent
▪ Helps users enter data correctly
 BOOKINGSTBL EXAMPLE

▪ SessionID, StudentInitial, Stage, Instrument, CurrentGrade, SessionDate,

StudentSurname, Tutor, Online/InPerson, FeePayable, FeePaid?
▪ Example rows:
▪ Kahn, Amall, 8, Guitar, 4, 12 Jan, …
▪ Karr, Rageb, 8, Drums, 5, 13 Jan, …
▪ Amer, Amall, 8, Piano, 2, 14 Jan, …
▪ Osman, Amall, 8, Drums, -2, 15 Jan, …
 IMPORTANT NOTE
▪ Validation checks reduce errors but cannot guarantee correctness
▪ Example: CurrentGrade = -2 → invalid, but might still appear if validation is
missing or bypassed
 KEY TAKEAWAYS
▪ Always use correct data types, ranges, and formats
▪ Validation helps catch errors, but users must still enter sensible data
▪ Regular checks and corrections are important for accurate database records
 INVALID DATA EXAMPLE

▪ A new record added for A Amall

▪ CurrentGrade entered as -2
▪ Grades must be 1–8
▪ Validation checks help reduce errors but cannot catch everything
 KEYWORDS
▪ Validation: automatic check to reduce entry errors
▪ Validation rule: defines what makes data valid
▪ Error message: hints or instructions to fix wrong entries
▪ Invalid data: data that does not follow rules
▪ Condition: set of rules to test a data item
▪ Validation text: message shown when data breaks a rule
 DID YOU KNOW?
▪ Users can ignore a lookup list and type their own data
▪ Validation checks are still important even with lookup lists
▪ Clean data → accurate and reliable
▪ Example: schools need clean data to decide reward trips or assessments
 PRACTISE – ADDING VALIDATION
▪ Open [Link] in MS Access
▪ Open bookingsTBL → Design View
▪ Add these validation checks to fields:
▪ Presence check
▪ Length check
▪ Range check
▪ Format check
▪ Type check

▪ Use Validation Rule + Validation Text properties for each field

 NOTE

▪ Some fields like Autonumber do not need validation

▪ Data is entered automatically by MS Access
 PRACTISE 2 – PRESENCE CHECK

▪ A presence check ensures a field cannot be left blank

▪ Example: StudentInitial field
 STEP 1 – SET PRESENCE CHECK

▪ Select StudentInitial field

▪ Scroll to General Properties
▪ Set:
▪ Validation Rule: Is Not Null
▪ Validation Text: “Please enter a value from A-Z as the student’s initial”
 WHAT THIS DOES
▪ Prevents blank entries
▪ Shows error message if user tries to leave field empty
▪ Message helps correct the mistake
STEP 2 – REPEAT FOR TUTORINITIAL

▪ Apply the same validation rule for TutorInitial

▪ Add a message: “Please enter a value from A-Z as the tutor’s initial”
 STEP 3 – TEST THE VALIDATION
▪ Try adding a new record in bookingsTBL
▪ Enter StudentSurname first but leave StudentInitial blank
▪ Error message appears
▪ Repeat with TutorSurname and TutorInitial → error appears again
 KEY POINTS
▪ Presence check ensures important data isn’t missed
▪ Error messages guide users to enter correct information
▪ Helps keep the database accurate and reliable
 DID YOU KNOW?

▪ In General Properties, a field can be set as Required

▪ This is similar to Is Not Null
▪ Advantage of Is Not Null: you can add your own error message to guide the user
 PRACTISE 3 – LENGTH CHECK
▪ A length check ensures the correct number of characters is entered
▪ Can set:
▪ Minimum number of characters
▪ Maximum number of characters
▪ Exact number of characters
 EXAMPLE – STUDENTSURNAME
▪ Open bookingsTBL → Design View
▪ Select StudentSurname field

Update General Properties:

▪ Validation Rule: Len([StudentSurname])<=25
▪ Validation Text: “Please enter no more than 25 characters for surname”
 TEST THE RULE
▪ Switch to Datasheet View
▪ Try editing a student’s surname longer than 25 characters
▪ Error message appears
▪ Prevents invalid data entry and keeps database clean
 KEY POINTS
▪ Length checks ensure data fits the field properly
▪ Helps avoid errors and long entries
▪ Works best with other validation checks like presence and range checks
COMPUTATIONAL THINKING – LOGIC & EVALUATION

▪ Operators are used to compare values or combine conditions

▪ Examples include <, >, =, >=, <=, <>

▪ Logical operators: AND, OR, NOT

COMPARISON OPERATORS
Operator Meaning

= Equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> Not equal to

 LOGICAL OPERATORS
Operator Meaning

AND Both conditions must be TRUE for output to be TRUE

OR Only one of two conditions must be TRUE for output to be TRUE

NOT Output is the opposite of the input →

 EXAMPLE – CHECK OUTPUT
Given: A = 7, B = 10, C = 2, D = FALSE

Statement Output
A=C FALSE
B>C TRUE
C<A TRUE
B >= C TRUE
C <= A TRUE
A <> B TRUE
A < B AND C < A TRUE
A < B OR C < A TRUE
NOT D TRUE
 KEY POINTS
▪ Use operators to compare values
▪ Logical operators combine conditions
▪ Helps computers make decisions based on data
 PRACTISE 4 – RANGE CHECK

▪ A range check ensures data falls within specific values

▪ Helps prevent invalid data entries
 EXAMPLE FIELDS FOR RANGE CHECK

▪ Stage: Only students in Stages 7–11 can take lessons

▪ SessionDate: Sessions cannot be booked in the past
 STAGE VALIDATION
▪ Open bookingsTBL → Design View
▪ Set Validation Rule: Between 7 And 11

▪ Set Validation Text: “Stage must be between 7 and 11”

▪ Test values:
▪ 5 → Error appears
▪ 7, 9, 11 → Accepted
▪ 15 → Error appears
 SESSIONDATE VALIDATION
▪ Format SessionDate field → Short Date/Time
▪ Set Validation Rule: >=Date()

▪ Set Validation Text: “Sessions cannot be booked for dates that occur in the past”

▪ Test cases:
▪ Past date → Error appears
▪ Today → Accepted
▪ Future date → Accepted
 KEY POINTS
▪ Range checks limit values to acceptable ranges
▪ Stage prevents invalid grades
▪ SessionDate prevents booking in the past
▪ Keeps data accurate and reliable
 PRACTISE 5 – FORMAT CHECK & TYPE CHECK

▪ Type check: Ensures correct data type is entered

Example: Only numbers in a number field, only text in a text field

▪ Format check: Ensures data is entered in a specific pattern

 MS ACCESS METHODS

▪ Method 1: Use validation rules for specific allowed values

▪ Example: Booking sessions can be either Online or InPerson
 STEPS FOR ONLINE/IN PERSON FIELD

▪ Open bookingsTBL → Design View

▪ Set Validation Rule: Online OR InPerson

▪ Set Validation Text: “Please enter either Online or InPerson”

▪ Test rule:
▪ Typing On-line → Error appears
▪ Typing Online or InPerson → Accepted
 KEY POINTS
▪ Format and type checks reduce errors when entering data
▪ Helps keep data clean and consistent
▪ Boolean operators like OR are used to specify multiple allowed values
 METHOD 2 – INPUT MASK

▪ Input mask: Controls how data is entered into a database

▪ Implements both format and type checks
▪ Defines a particular layout or format for the data
 COMMON INPUT MASK CHARACTERS

Character Formatting Rule

L A-Z accepted; a letter must be entered; uppercase automatically

? A-Z accepted; letter optional; converts to lowercase automatically

1 Only digits accepted

 STEPS FOR TUTORSURNAME FIELD

▪ Open bookingsTBL → Design View

▪ Select TutorSurname
▪ Go to Input Mask row under General properties
▪ Enter the input mask for the field
▪ Set Field Size: 25 characters
 TEST INPUT MASK
Change Attempt Expected Outcome

fouad → Fouad Auto-correct: F uppercase, ouad lowercase

FOUAD → Fouad Auto-correct: F uppercase, ouad lowercase

1234 → Not accepted

>25 letters → Stops accepting letters after 25

 KEY POINTS
▪ Input masks guide users to enter data correctly
▪ MS Access cannot show error messages for input masks
▪ Designers should provide help in forms to avoid mistakes
 COLLECTING THE DATA
▪ Data-capture forms are used to:
▪ Enter new data
▪ Modify existing data
▪ View existing records

▪ Data entered is automatically stored in the correct field headings

 EXAMPLE: CUPCAKE ORDER FORM

Fields include:
▪ OrderID (Autonumber, primary key, Office Use Only)
▪ Initial, Surname (text boxes)
▪ Cupcake Flavour (dropdown list)
▪ Number Ordered, Delivery Date, Paid?

OrderID is shaded differently because it is autonumber and managed automatically

 ELEMENTS OF DATA-CAPTURE FORMS

▪ Labels – Explain what to enter (e.g., Initial, Surname)

▪ Text boxes – For typing data
▪ Dropdown lists – Select from options (linked to lookup lists)
▪ Radio buttons / checkboxes – For YES/NO fields (Boolean data)
 FEATURES OF A GOOD FORM
▪ Clear labels for easy understanding
▪ Atomic fields to collect all required data
▪ Examples and hints to reduce mistakes
▪ Selection options where possible
▪ Correctly sized text boxes for expected input
 PRACTISE: DESIGN A DATA-CAPTURE FORM
▪ Design a form for bookingsTBL in [Link]
▪ Ensure users know which fields are optional (e.g., SessionID / OrderID)
▪ Provide examples for fields requiring a specific format
▪ Include dropdown options where choices are limited
▪ Add instructions to help avoid mistakes and validation errors
▪ Include all form elements:
▪ Labels describing required info
▪ Text boxes for typing data
▪ Dropdown lists for options
▪ Radio buttons / checkboxes
 EVALUATE YOUR FORM
Ask your partner:
▪ Are all fields from bookingsTBL included?
▪ Are labels clear and easy to understand?
▪ Are fields in the correct order?
▪ Are there enough examples and instructions for users?
▪ Do text boxes match the field length in the database?
▪ Make suggested improvements with a different-colored pen
 KEYWORDS
▪ Modify: Update or change
▪ Form element: An object in an electronic data-capture form
▪ Radio buttons: Choose one option from a list
▪ Check boxes: Choose multiple options from a list
▪ Atomic field heading: Describes smallest data items in a database
▪ Linear search: Compare each item in turn to find a value
 COMPUTATIONAL THINKING:
ALGORITHMS

▪ Primary key fields uniquely identify records in a database

▪ Useful for searching and distinguishing records with the same name
▪ Many databases use Autonumber to automatically assign primary key values
▪ Knowing the primary key makes it easy to perform a linear search
 UNDERSTANDING LINEAR SEARCH

▪ Compare the search value with each primary key in turn

▪ Continue until the value is found or all records checked
▪ Return the matching record
▪ Suitable for small databases
▪ Disadvantage: Slow for large databases
 DATA MODELS & SIMULATIONS

▪ Data model: Digital representation of a real-life scenario

▪ Uses individual data items (attributes) to represent important factors
▪ Simulation: Interacting with a data model to predict outcomes
 ADVANTAGES
▪ Safer and cheaper than real-life testing
▪ Easy to test situations before they happen
▪ Supports “what-if” questions for planning and decision-making
 KEYWORDS

▪ Data model: Digital representation of a real-life scenario, broken into attributes

▪ Simulation: Computer program representing a real-life scenario using formulas
to predict outcomes
 DATA MODELS & ADVANCED
SIMULATIONS

▪ Some models use numbers, text, and formulas to represent real-life situations
▪ Others use complex graphics and specialized hardware to create realistic simulations
 AUGMENTED REALITY (AR)
▪ AR simulations superimpose computer-generated images onto the real world
▪ Users can interact with these images in real time as if they are real

Examples of use:
▪ Gaming
▪ Training pilots or doctors
▪ Tourism
 VIRTUAL REALITY (VR)
▪ VR simulations provide a fully immersive 3D experience
▪ Requires special headsets or gloves for interaction

Examples of use:
▪ Gaming
▪ Training in safe environments
▪ Virtual visits to extreme locations
▪ Education, e.g., attending concerts or operas virtually
 KEYWORDS
▪ Hardware: Physical parts of a computer (processor, storage, input/output devices)
▪ Augmented Reality (AR): Digital images overlaid onto the real world
▪ Virtual Reality (VR): Fully immersive digital environment
▪ Immersive: Feeling physically present in a virtual world
▪ 3D image: Image with depth, can be digital and sometimes animated
 PRACTISE: AR & VR SIMULATIONS
Research online:
▪ How businesses use simulations and data models
▪ Examples:
▪ AR: Customers try products at home via phone apps
▪ VR: Walk-throughs of new buildings using VR goggles

Advantages & disadvantages of using AR/VR simulations:

Discuss in pairs and make a list

Data needed for a model:

Example scenarios:
▪ Weather changes over one month
▪ SatNav route from home to school
 COMPUTATIONAL THINKING
– DECOMPOSITION
▪ Databases and spreadsheets can model real-life situations
▪ Spreadsheets allow links between data items using formulas
 CAKE SALE SPREADSHEET EXAMPLE
Scenario: Track cupcakes sold and profit for charity
Formulas to include:
▪ Total Cost per flavour: =NumberMade * CostPerCupcake

▪ Income per flavour: =NumberMade * SalePrice

▪ Total cost of all cupcakes: =SUM(TotalCost column)

▪ Total income: =SUM(Income column)

▪ Overall profit: =TotalIncome - TotalCost

Cost per Sale

Cupcake Flavour Number Made Total Cost Income Profit
Cupcake Price
Vanilla 350 $0.20 Formula $1.60 Formula Formula
Strawberry 225 $0.20 Formula $1.60 Formula Formula

Chocolate 200 $0.20 Formula $1.60 Formula Formula

Lemon 120 $0.20 Formula $1.60 Formula Formula
 KEYWORDS
▪ Client: Person a product is being developed for
▪ What-if you…?: Predicting outcomes by changing values in a model
▪ Data model: Digital representation of a real-life scenario
▪ What-if analysis: Changing input data in a model to see the effect on outputs
▪ Attributes: Individual data items in a model
▪ Formulae: Rules linking data items together in a spreadsheet
 SPREADSHEET DATA MODELS
▪ Spreadsheets can model real-life situations
▪ Example: Students’ assessment scores in a music class
▪ Attributes (data items) are linked using formulae
▪ Formulae can calculate:
▪ Test percentages (%)
▪ Average scores
▪ Maximum and minimum scores
 WHAT-IF ANALYSIS
▪ Changing a value in a spreadsheet updates all linked cells
▪ Allows you to experiment with the data to predict outcomes
▪ Example: Changing S. Kahn's Test 1 score from 68 → 75 updates averages and
linked cells automatically
 PURPOSE OF WHAT-IF ANALYSIS
▪ Helps make decisions using predictions
▪ Uses current data and past experiences

▪ Example (decision making in real life):

▪ Planning a day out with friends:
▪ Biking = $0, Cinema = $10, Amusement Park = $20
▪ Calculate total money → decide feasible option

▪ Organisations use it to predict outcomes and plan

 IMPORTANT NOTES

▪ Data models help experiment safely

▪ Predictions may not consider everything → can be inaccurate
▪ Helps organisations simulate scenarios before taking real actions
 WHAT-IF ANALYSIS IN SPREADSHEETS

Definition:
Using current data and past experiences to investigate different scenarios in a data model

▪ In spreadsheets: Change a value in a cell → see how formula outcomes are affected
▪ Helps predict results and experiment safely
 PRACTISE – PART 1: FORMULAE
Examine formulae used in spreadsheet:
▪ Cells D3, I3, J3

Key concepts:
▪ Absolute cell referencing: Refers to a fixed cell (e.g., $C$3)
▪ Relative cell referencing: Refers to a cell relative to the formula’s location (e.g., C3)

Questions to discuss:
▪ Which type of referencing is used in each formula?
▪ What happens to linked cells if a value changes (e.g., D Amer’s Test 2 score 78 → 82)?
▪ Which other cells are affected by changes?
 PRACTISE – PART 1: EXCEL ACTIVITY

▪ Open Student Test [Link]

▪ Compare formulae in D3, I3, J3 with your answers
▪ List all cells linked by formula to C3
 PRACTISE – PART 2: WHAT-IF
QUESTIONING
Scenario 1: R Rageb scored 71 in Test 1 instead of 66
▪ What is the new percentage (%) for Test 1?
▪ What is the new Average Score and Average % for all three tests?

Scenario 2: A Essan obtained full marks in Test 2

▪ Update affects Average Score and %
▪ How does it affect cell F10?

Scenario 3: Test 3 scores reduced by 3 for all students

▪ How are other values in the model affected?
▪ Use screenshots and annotations to describe the changes
 KEYWORDS

▪ What-if analysis: Changing values in a spreadsheet to explore different outcomes

▪ Absolute cell reference: Fixed reference to a cell ($C$3)
▪ Relative cell reference: Reference that changes relative to the formula’s location (C3)
▪ Linked cells: Cells whose values are connected through formulae
 CHANGING THE SCENARIO
– WHAT-IF ANALYSIS
▪ Manual changes are fine for one-time updates
▪ Organisations often reuse the same model multiple times
▪ Important to save what-if values for repeated use
 MS EXCEL TOOL: SCENARIO MANAGER

▪ Excel allows users to create and save what-if models

▪ Saved scenarios can be applied later without manual changes
▪ Useful for testing multiple conditions or predictions
 PRACTISE
▪ Open Test Score [Link]
▪ Model includes formulae to calculate:
▪ Whether a student passed or failed (based on pass marks in B15, B16, B17)
▪ Number of students who passed Test 1, 2, and 3
▪ Average score for each test
▪ Number of tests each student passed

▪ Go to Data → What-If Analysis → Scenario Manager

▪ Click Add to create a new scenario
▪ Name the scenario (e.g., Test 1% Pass 90)
▪ Click on the spreadsheet cell to change (e.g., B15) and click OK
 KEYWORDS

▪ Scenario Manager: Tool in Excel to save and manage multiple what-if scenarios
▪ What-if model: Spreadsheet setup that allows experimenting with data changes
▪ Pass mark: Minimum score required to pass a test
 ADDING A SCENARIO
▪ Open Scenario Manager in Excel
▪ Click Add to create a new scenario
▪ Scenario name: Test 1% Pass 90
▪ Changing cells: B15
▪ Use Ctrl + click to select non-adjacent cells if needed
▪ Add a comment if required
▪ Click OK
 EDITING THE SCENARIO
▪ Change cell B15 value to 90
▪ The scenario now appears in Scenario Manager
▪ Options in Scenario Manager:
▪ Show: Display scenario results in spreadsheet
▪ Add / Delete / Edit / Merge / Summary / Close
 EXPERIMENTING WITH SCENARIOS
▪ Click Show to see effects of scenario on your model
▪ Run multiple scenarios to explore results
▪ Example goal: All seven students pass all three tests
 KEYWORDS

▪ Scenario: Saved set of changes applied to a model

▪ Changing cells: Cells modified in the scenario
▪ Show: View the results of a scenario in the spreadsheet
 GO FURTHER: DATA
VALIDATION IN DATABASES
▪ Len function and input masks limit characters entered in fields
▪ Input mask example: TutorSurname – uppercase letter followed by lowercase only

[Link] already includes:

▪ StudentInitial: presence check
▪ StudentSurname: length check (≤25 characters)
▪ Stage: range check (7–11)
▪ SessionDate: range check (no past dates)
▪ Online/InPerson?: only “Online” or “InPerson”
▪ TutorSurname: input mask
 ADDITIONAL VALIDATION CHECKS
– [Link]
▪ All fields: required
▪ StudentSurname: must start with uppercase
▪ Instrument: only Guitar, Drums, Piano, Voice
▪ CurrentGrade: 1–8 only
▪ Lesson/Exam?: only “Lesson” or “Exam”
▪ TutorInitial: one character only
▪ FeePayable: between $15 and $20
 ▪ Edit bookingsTBL in [Link]

TEST PLAN ▪ Add designed validation checks and input masks

▪ Test each rule as you implement

Field Name Validation/Input Mask Testing Example

All fields Required field Try adding a new record leaving a field blank

Try entering a lowercase first letter; it should change

StudentSurname Input Mask → L
automatically to uppercase

Instrument Validation rule: Guitar, Drums, Piano, Voice Try entering “Flute”; error should appear

CurrentGrade Range check: 1–8 Try entering 0 or 9; error should appear

Lesson/Exam? Validation rule: Lesson or Exam Try entering “Practice”; error should appear

TutorInitial Length check: 1 character Try entering “AB”; error should appear

FeePayable Range check: 15–20 Try entering $25; error should appear
 DATA VALIDATION IN SPREADSHEETS
▪ Validation reduces data-entry mistakes
▪ Example: Test Score [Link]
▪ Highlight student scores for Test 1
▪ Go to Data → Data Validation
▪ Set rules in Settings, Input Message, Error Alert
▪ Test by entering invalid value (e.g., 900 for S Kahn)

▪ Repeat for Test 2 and Test 3

 KEY TAKEAWAYS

▪ Validation ensures clean, accurate data

▪ Apply rules to databases and spreadsheets
▪ Always test your rules to confirm they work as expected
 CHALLENGE YOURSELF:
VALIDATION ON DATA-CAPTURE
FORMS

▪ Even if a database table has no validation rules, you can still validate data using
a data-capture form
▪ MS Access allows validation rules directly on form fields
 TASK SETUP
▪ Open [Link]
▪ Open the bookingsFRM form
▪ This form has labels and text boxes linked to fields in bookingsTBL
▪ The form allows you to add validation rules to collect clean data
 ADDING VALIDATION RULES
▪ Open the form in Design View
▪ Click on a text box, e.g., StudentInitial
▪ Property Sheet shows options:
▪ Input Mask
▪ Validation Rule
▪ Validation Text

▪ Example: add >L input mask for StudentInitial

 TESTING VALIDATION
▪ Switch to Form View
▪ Try deleting a student’s initial (e.g., S Kahn)
▪ Try typing the initial in lowercase
▪ Observe: does the form enforce rules like the table validation?
▪ Test each validation check using an appropriate data item
 KEYWORDS

▪ database object: a single item inside an MS Access database,

e.g., a table or a data-capture form

▪ Initial requirements: the idea and starting plan for a project

 FINAL PROJECT
– PART 1: DATABASE

▪ Create a database to monitor ticket allocations for the school summer music festival
▪ Festival runs over 3 days
▪ Parents can order tickets for any/all days
▪ Tickets are ordered using the student's name
▪ Each student can order a maximum of 4 tickets per day
▪ Parent/guardian name and phone number required
▪ Improve existing [Link] database
 DATABASE DESIGN IMPROVEMENTS
▪ Ensure each student Stage is between 8 and 11
▪ Add presence checks to important fields
▪ Tickets per day must be between 1 and 4
▪ Phone number must:
▪ Be 11 digits
▪ Start with 01

▪ DateCollected cannot be in the future

▪ Include a primary key field
▪ Make field headings atomic
 DATA DICTIONARY
▪ Create a data dictionary similar to earlier exercises
▪ Include:
▪ Field name
▪ Data type
▪ Field length
▪ Validation check / input mask

▪ Use dictionary to update EventBooking database

 FORM DESIGN
▪ Design a form on paper to collect all data
▪ Include:
▪ Labels and text boxes for each field
▪ Instructions and hints for users
▪ Correct field order and layout
 FINAL PROJECT – PART 2:
SPREADSHEET
▪ Set up a spreadsheet model for the scholarship system
▪ Discounts are based on student's current grade
▪ Budget: $500
▪ Discounts:
▪ Grades 1–4 → 5%
▪ Grades 5–8 → 10%
 SPREADSHEET TASKS
▪ Record number of lessons per student per year
▪ Add validation check: Stage must be between 7 and 11
▪ Use absolute or relative cell references where appropriate
▪ Calculate total fees per student for the year
▪ Calculate discount amount per student
▪ Sum total discounts given
▪ Compare total discounts with available budget
▪ Use what-if analysis in cell K2 to determine maximum lessons under budget
 PART 3: MODELLING AND
SIMULATION IN SCHOOL
▪ Explain the differences between:
▪ Data model: digital representation of real-life scenario, attributes = data items
▪ Simulation: model that can predict outcomes based on rules & data

▪ Key features to include:

▪ Data linkage
▪ Formulae / rules
▪ Ability to test “what-if” scenarios
 RESEARCH TASK
▪ Research 2 other departments in school using:
▪ AR (Augmented Reality)
▪ VR (Virtual Reality)

▪ Explain how AR and VR simulations enhance learning

▪ Provide examples for each department
 PART 1: DATABASE
▪ Swap databases with your partner
▪ Ask your partner to test the validation methods in your table by adding a new
record with invalid data
▪ Your partner should note all invalid entries they were able to add
▪ Update your validation checks to prevent these errors
 DATA-CAPTURE FORM EVALUATION

▪ Use partner feedback to evaluate your form:

▪ Could more guidance be added for users?
▪ Is it clear what data must be entered in each field?

▪ Update your form design to include any new validation checks

 PART 2: SPREADSHEET
▪ Examine your spreadsheet solutions with your partner
▪ Discuss how each solution met the Final Project requirements
▪ Compare field headings and formulae:
▪ Where do they differ?
▪ Which approach is better and why?
 PART 3: MODELLING AND
SIMULATION IN SCHOOL

▪ Discuss recommendations for AR and VR simulations in school

Consider:
▪ Which subjects would benefit most from AR/VR? Why?
▪ Could new subjects be introduced if AR/VR is available?