Unit 2 - Creating Systems to Manage Information

From January 2020 the assessment of the unit changed to two timed examinations.

  • Part A - a 3-hour exam that covers:
    • Normalisation
    • Table creation
    • Data types
    • Naming of tables and field
    • Relationships
    • Field validation
    • Query design
    • Reports
    • Testing
    • Evaluation
  • Part B - a 2-hour exam that:
    • Provides a partially completed database
    • Form creations that can:
      • Update existing tables
      • Run form level validation
      • Perform calculations using fields
    • Testing
    • Evaluation

The exam board has published two Sample Assessment Material (SAM) papers that demonstrate what the new examination will look like.

Pre-2020: Summary of Skills as seen in examinations before 2020

SAM - "Get our Beaches Clean"

SAM - "Sharebrook Estate"

Jan 2020 - Exam - Notes and Guides

Normalisation

The process of organising data to make it harder for anomalies to be introduced.

Video:How to use highlighters in the Exam

Practice Data Sets:

Past Paper ERD Explanations

Data Types

In this section we give you guides to the June 2018 exam and an interactive quiz based on the January 2019 exam. (Note from 2020 there will be fewer tables and fields)

Of special importance is that the data types of Foreign keys have to match those of the associated Primary Key. (An autonumber is the only special one in that it matches to an ordinary number type)

Naming of tables and fields

The marks in this section are given in two ways:

  1. For using conventional naming, tables should begin tbl (so the Proprty table in the previous section would be called "tblProperty"). This will be similar for queries (qry...), forms (frm...) and reports (rpt...)
  2. For consistency. The use of capital letters and how you deal with spaces. The following are all acceptable so long as you do NOT do some one way and others another!
    • OwnerSurname
    • Owner_Surname
    • Ownersurname
    • Owner_surname

Relationships

Activity 1 asks you to normalise the database but until you have completed Activity 2 this may change. If it does change you could LOSE marks if you do not ensure Activity 1 matches.

Do Activity 2 BEFORE you screenshot the ERD for Activity 1 - this way you will guarantee they match.

Field validation

The source of most errors in data is the human error when someone enters an invalid value.

Validation is about limiting the damage they can do by only allowing entries that follow certain rules. In this exam you will be asked for ONE example of each of the following (you can do more than 1 if you are not sure you have picked the right one to do - but the exam paper does give you some pretty big hints)

  • Presence Check (Is Not Null) - Don't do this on any Primary Key as they already have a built in check (and you may lose the mark!)
  • Length check - field length, pick a sensible length for any TEXT field
  • Value lookup or range check - with value lookups don't forget to set the "Limit to list" to Yes (otherwise that pesky user can still type whatever they like!)
  • Table lookup - make sure you do this with the FOREIGN key...so if the field you are screenshotting has a key symbol next to it you may have got it WRONG. (Unless it's part of a composite key)
  • Format check - the most common checks seen so far have been telephone numbers, post codes or email addresses. Do NOT do DATE or CURRENCY.
One screenshot can show more than one of these.
Form Validation Exercise (Password complexity check)  (Database for the Exercise)

Query design

You have to be able to query the database to find specific information. 

  • Getting fields from related tables – check relationships exist
  • Single line queries (AND)
  • Multiple line queries (OR)
  • Parameter Queries ([Question in square brackets]) or linked to Form
  • Summary Queries (TOTALS button) - Group by, Where, Count, Max, Sum
  • Sub queries - where you do a query on a query
  • Aliases (NewName:FieldName)
  • Calculated fields – e.g. Score:Judge1 + Judge2. DateDiff( ). 
  • Calculated fields including expressions on other fields, e.g. TotalPeople:Sum([NumAdults]) + Sum([NumChild])
  • Date spans
Totals in Query Practice

Reports

  • How to get the fields from a query (you may need a new query)
  • Group and Sort button
  • Use of Group Header section
  • Totals (including conditional use): 
    • =Sum([Field]) 
    • =Sum(IIf([MemberGender]="Male",1,0))
    • =Count(*) 

Placement of totals in different sections on the report gives different totals for those sections

Testing (Part A)

Be very specific about what test data will be used. All fields should have a value even if you are testing just one of them. If a field is to be left empty write down that fact - but don't forget to have data in the others!

Evaluation (Part A)

The evaluation is where you get to use the proper technical terms.

Explain what normalisation is and use examples from the data set given to you for THIS exam to show how your design prevents anomalies.

Explain why relationships are needed and which of yours are one-to-many etc.

Copyright © 2015-present Exam Dividers LLP All rights reserved.