All subjects · Information Technology · Unit 4

WIT14 3 hour practical exam on database solutions

Edexcel IAL Information Technology, Unit 4 (A2 unit, usually Year 13). 27 lessons so far, in the suggested order.

Practise this unit in the app: instant marking and a fix for every mistake. Free, no email needed to start.

Practise in the app

Lessons in WIT14

  1. Why use database software? Features and context
    Database software stores structured data in related tables with data types, validation, queries, forms, reports, automation, import/export and multi-user securi
  2. Analysing user needs and designing for them
    List users, tasks, skills and other characteristics; design for them (novices need guided forms, experts shortcuts, time-pressed staff defaults and automation);
  3. User experience: how design affects the user
    Interface type, appearance, complexity, language and compatibility should suit the users; good design gives ease of use, efficiency, help, compatibility, stabil
  4. Entities, tables, records and fields
    Entity = table, attribute = field, instance = record. Use a naming convention (tblName, no spaces). CREATE TABLE defines fields, data types and rules; ALTER TAB
  5. Choosing data types and formats
    Text for anything not calculated (phones, postcodes, codes keep leading zeros); Number for quantities; Currency for money; Date/Time for dates (sort and compare
  6. Why relational structures? Redundancy and anomalies
    Flat files repeat data (redundancy) and suffer update, insertion and deletion anomalies, which lead to inconsistent data. Relational structures store each fact
  7. Relationships and entity relationship diagrams
    1:1 (shared key), 1:M (foreign key on the many side), M:M (resolve with a link table holding both keys, giving two 1:M). In crow's foot ERDs the many end has th
  8. Primary, foreign and composite keys
    Primary key: unique, never empty, identifies a record (natural or surrogate). Foreign key: holds another table's primary key, sits on the many side, same data t
  9. Referential integrity
    Referential integrity: every foreign key value must match an existing primary key. It blocks orphan inserts, deleting parents with children and changing keys in
  10. Evaluating data and relational structures
    Evaluate structures for completeness, minimal duplication, types, keys, relationships, integrity, naming and future needs. For a new requirement: say what exist
  11. Validation checks and error messages
    Presence, range, list, format, length and lookup checks; put the rule in Validation Rule and a what-is-wrong-and-how-to-fix message in Validation Text; test wit
  12. Improving data entry: help text, masks, controls and automation
    Help text guides; input masks control typing (0 digit, L letter, > upper case); combo boxes and radio buttons limit choices; check boxes for Yes/No; defaults, g
  13. Inserting, updating and deleting data
    INSERT INTO table (fields) VALUES (...); UPDATE table SET field = value WHERE ...; DELETE FROM table WHERE .... Always use WHERE and test it as a SELECT first;
  14. Building a relational structure from a given data set (to 3NF)
    For a supplied file: apply 1NF, 2NF and 3NF (from Unit 3) to find the entities, give each a table and key, add link tables for M:M, set relationships with refer
  15. Select queries: criteria, wildcards and sorting
    SELECT fields FROM table WHERE criteria ORDER BY field ASC|DESC. BETWEEN includes ends; AND on one grid row, OR on separate rows; LIKE with % and _ (Access grid
  16. Queries across tables: joins
    INNER JOIN ... ON fk = pk returns matching rows; go through link tables for M:M; LEFT JOIN plus IS NULL finds records with no matches; always give a join condit
  17. Summary queries: aggregate functions, GROUP BY and HAVING
    COUNT, SUM, AVG, MIN, MAX; GROUP BY gives one row per group (non-aggregated fields must be grouped); WHERE filters rows before grouping, HAVING filters groups a
  18. Calculated fields, conditional logic and parameter queries
    Calculated fields compute from other fields at run time; always name them (HireCost: [DailyRate]*[Days]) and format them. CASE WHEN in SQL, IIf in Access, for c
  19. Reports and charts
    Report = tested query + grouping + summary calculations (=Sum, =Max, =Count in footers) + formatting + title, checked in Print Preview. Group footer totals a gr
  20. Importing, exporting and evaluating your tools
    Import from Excel, CSV and text; export to Excel, CSV, text, HTML and PDF. Inspect the file first, use a holding table for unnormalised data, set delimiter, hea
  21. Data entry and edit forms
    Entry forms open on a new record; edit forms find and change records with IDs locked. Earn marks with a meaningful title, instructions, clear labels, asterisks
  22. Menus, dashboards, buttons and macros
    Menus give one clearly labelled button per task; dashboards add at-a-glance information. Know OpenForm, OpenQuery, OpenReport (Print Preview), MessageBox, GoToR
  23. Outputs, supporting users and evaluating the interface
    Choose outputs (datasheets, reports, charts, error and confirmation messages) for the user's purpose. Support users with field selection, help text, meaningful
  24. Testing a database solution
    Test that each part works (functional) and that the whole meets user needs (fitness for purpose), involving users during development. A test plan records data,
  25. Evaluating a solution and suggesting enhancements
    Evaluate each requirement and user need with evidence, then judge overall fitness for purpose. Enhancements must be specific, feasible and justified. Pattern: c
  26. The Unit 4 practical exam: how it works
    3 hours, 80 marks, all compulsory, set in a context, data files supplied, everything evidenced in the word-processed template. Point-marked practical tasks (sho
  27. Answering the 9-mark analysis questions
    Levels: 1 to 3 superficial, 4 to 6 sound, 7 to 9 sound, developed and logically linked with a workable solution. Structure: requirement, what exists, what is mi

Next exams for WIT14

From Pearson's final timetables. All exam dates. Always confirm your entries with your exams officer.