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
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
- 2018 January Skater
- 2018 June Gill's Fitcamp
- 2019 January Letting Agency
- 2019 Sample Assessment Material: Keep out Beaches Clean (PowerPoint)
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)
- June 2018 Data Type Guide
- January 2019 - Quiz Style
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:
- 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...)
- 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.
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
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.