Font size
WorksheetsUnit 2 – Distinction-Level Scenario-Based Quiz (Access 365)
Total questions: 50
Worksheet time: 25mins
In a college student management system, what must a Relationship diagram in Access show in the Relationships window?
All entities with enforced referential integrity and correctly typed 1–many joins
Some related tables linked by matching field names
Only primary keys with no foreign keys shown
A single table with all attributes combined
How should a many-to-many relationship between Students and Modules be implemented in Access?
Via a junction table (e.g. Enrolments) with composite FK fields and enforced referential integrity
By adding a multi-value field to the Students table
By duplicating module columns in the Students table
By creating a calculated field that concatenates student and module IDs
Which change most strongly evidences full normalisation (no redundancy) in the database?
Moving tutor contact details from Modules to a separate Tutors table linked by TutorID
Adding a lookup field for module names inside Students
Copying student emails into every related table to speed up reports
Storing both TutorName and TutorID in every child table
In Access, which action confirms correct 1–many relationships for an ERD?
Enable 'Enforce Referential Integrity' and 'Cascade Update Related Fields' where appropriate
Disable referential integrity to avoid input errors
Use text fields for all keys to keep joins flexible
Set all joins to many-to-many to avoid duplicates
Enable 'Enforce Referential Integrity' and 'Cascade Update Related Fields' where appropriate
A student sets StudentID (Short Text) in Students and StudentID (Number) in Enrolments. What must be corrected?
Match the data types and field sizes for PK/FK pairs across tables
Rename the fields to identical names only
Change both fields to AutoNumber in both tables
Permit nulls in foreign keys to avoid join failures
Match the data types and field sizes for PK/FK pairs across tables
Which description best matches a high-quality ERD outcome?
All tables fully normalised (3NF), no repeating groups, and all relationship types accurately shown
A partially normalised set with minimal duplication allowed for convenience
A single wide table to avoid complex joins in forms
Two tables with many lookups and no enforced links
All tables fully normalised (3NF), no repeating groups, and all relationship types accurately shown
Which combination is correct in Table Design View?
Data Type: Date/Time; Required: Yes;
Data Type: Short Text; Required: No; no
Data Type: Date/Time; Required: No; Allow
Data Type: Number; Input Mask applied for
Data Type: Date/Time; Required: Yes;
Which validation rule and text should be used for Dates of Birth in Students in Access?
Validation Rule: <=Date(); Validation Text explaining future dates are not allowed
validation
Zero Length: Yes
DD/MM/YYYY
Validation Rule: <=Date(); Validation Text explaining future dates are not allowed
How should a Grade field (A–F only) be validated in Access?
Use a Validation Rule: In("A","B","C","D","E","F") with clear Validation Text
Use a Short Text field with no validation (teacher checks later)
Use a Number field with an Input Mask
Store grades as free-text notes in a memo field
Use a Validation Rule: In("A","B","C","D","E","F") with clear Validation Text
Which naming approach best meets consistency for tables and fields?
tblStudents/StudentID, tblModules/ModuleID, tblEnrolments/EnrolmentID
STUDENTS/ID, MODULES/ID, LINKS/ID
Table1/Field1, Table2/Field2
StudentsTable/PrimaryKey
tblStudents/StudentID, tblModules/ModuleID, tblEnrolments/EnrolmentID
What is required of PK/FK data types in Access?
PKs and their corresponding FKs must use the same data type and compatible field size
PKs must be AutoNumber and FKs must be Short Text
PKs may vary; Access will coerce during joins automatically
FKS should be optional text fields to prevent errors
PKs and their corresponding FKs must use the same data type and compatible field size
How should you prevent negative attendance in Access?
Validation Rule: >=0 and appropriate Validation Text
Use an Input Mask only
Store attendance as Short Text and check manually
Set Default Value to 0 without validation
Validation Rule: >=0 and appropriate Validation Text
In the Property Sheet, which setting supports high data quality for a Postcode field?
Input Mask for UK postcodes plus Validation Rule to ensure NOT NULL
Allow Zero Length set to Yes and no mask
Data Type: Number with Decimal Places: 0
Format set to General Number
Input Mask for UK postcodes plus Validation Rule to ensure NOT NULL
A report must summarise attendance by module. Which approach is best in Access?
Create a totals query grouped by ModuleID and bind a grouped report with clear headers and a grand total
Export to Excel to calculate totals and paste back
Add a calculated control directly to a blank report with no query
Build a crosstab without grouping then filter manually
Create a totals query grouped by ModuleID and bind a grouped report with clear headers and a grand total
A query to list students who have not submitted an assignment by a due date should use which criterion?
Submission Date Is Null OR Submission Date > [Enter Due Date]
Submission Date < [Enter Due Date]
Submission Date = ""
Submission Date Like "*"
Submission Date Is Null OR Submission Date > [Enter Due Date]
Which calculated field best fits the requirement?
FinalMark: ([ExamMark]*0.6)+([CourseworkMark]*0.4)
FinalMark: [ExamMark] + [CourseworkMark]
FinalMark: CStr([ExamMark]) & CStr([CourseworkMark])
FinalMark computed in the form only, not the query
FinalMark: ([ExamMark]*0.6)+([CourseworkMark]*0.4)
What is the accuracy in a query for final marks?
kMark]*0.4) with data types ensured numeric
kMark]
workMark]
query
kMark]*0.4) with data types ensured numeric
How should fields be selected for a report presented to faculty?
Include only relevant fields; remove redundant columns; apply consistent alignment and formatting for readability
Include all fields in case staff want more detail
Include keys only to keep it short
Include screenshots of tables instead of fields
Include only relevant fields; remove redundant columns; apply consistent alignment and formatting for readability
Which design choice most supports readability in Report Layout View?
Use grouping, page headers, calculated totals, and consistent alignment/format
Random font sizes and colours to highlight values
Single-column layout with no grouping and no totals
Rely on default auto-generated report with no edits
Use grouping, page headers, calculated totals, and consistent alignment/format
A parameter query prompts for Module Code. Which prompt practice is best?
Use square brackets with a clear prompt (e.g. [Enter Module Code]) and validate inputs
Use ambiguous prompts to allow flexible entry
Rely on form filters only
Type the module code directly into SQL view
Use square brackets with a clear prompt (e.g. [Enter Module Code]) and validate inputs
Which prompt practice is best?
Normal (valid), Erroneous
Only normal data because users should
Only extreme data to
Erroneous data only to trigger error
Normal (valid), Erroneous
Which test set is most appropriate for the Students table in structure testing?
(invalid), and Extreme (boundary) test data with expected results specified
enter valid values
stress-test the system
messages
(invalid), and Extreme (boundary) test data with expected results specified
Which evidence best proves structural robustness?
A completed test plan table showing test data, expected vs actual results, and fixes applied
A verbal statement that 'it works'
A screenshot of one successful data entry
A printout of table design only
A completed test plan table showing test data, expected vs actual results, and fixes applied
During testing, a FK allows a value not present in the parent table. What should you evidence?
A) Relationship updated to enforce referential integrity and re-tested with results recorded
B) A note explaining this is acceptable for speed
C) Change the FK to Short Text to allow any value
D) Remove the relationship to avoid errors
E) Relationship updated to enforce referential integrity and re-tested with results recorded
Which documentation detail aligns with structure testing?
Specific expected results tied to each identified test input, not generic statements
Generic expected results such as 'should work'
No expected results; only actual outcomes
Only screenshots without descriptions
Specific expected results tied to each identified test input, not generic statements
A boundary test for a mark field (0–100)
–1, 0, 100, 101 with clear expected outcomes
10, 50, 90 only
100 only because it is the maximum
Any random number
–1, 0, 100, 101 with clear expected outcomes
Which inputs should be included?
A) and validation messages
B) A list of tables only
C) A description of how to open Access
D) An opinion that '3NF is always best' with no evidence
E) and validation messages
Which explanation best exemplifies evaluation of structure?
A) A fully supported justification of 3NF design choices linked to the college scenario (e.g. separation of Tutors, Modules, Rooms)
B) A list of tables only
C) A description of how to open Access
D) An opinion that '3NF is always best' with no evidence
E) A fully supported justification of 3NF design choices linked to the college scenario (e.g. separation of Tutors, Modules, Rooms)
What language style is appropriate for technical evaluation?
Fluent and accurate technical vocabulary (e.g. 1-many, junction table, surrogate key, referential integrity)
Casual language with minimal terminology
Acronyms without definitions
No technical vocabulary to keep it simple
Fluent and accurate technical vocabulary (e.g. 1-many, junction table, surrogate key, referential integrity)
Which link most clearly ties the solution to scenario requirements?
Explaining how enrolment rules enforce one student in many modules via Enrolments with referential
Saying 'It meets requirements' without detail
Showing a screenshot of the Navigation Pane
Referencing a different project's needs
Explaining how enrolment rules enforce one student in many modules via Enrolments with referential
Which metric strengthens a justification of structure?
Explaining how normalisation reduces update anomalies and improves query performance in Access
Counting total number of tables only
Stating that forms look neat
Saying 'performance is fine' without evidence
Which feature set aligns with a high-quality user interface in Access for admin staff?
Navigation Form, well-labelled command buttons, tab order set, consistent formatting and themes
Unlinked forms opened manually from the Objects list
Single form with all fields from all tables
Relying on datasheet view for data entry
Which control setup on a Student Entry form best minimises errors?
Combo boxes for FK selections, input masks for dates/postcodes, and required fields with validation text
Text boxes for all fields with no validation
Allow all fields to be optional
Use continuous forms only
Which Access tool best evidences automation?
Macros or VBA event procedures (e.g. Before Update) to validate and handle unexpected events
Manual checks performed by the user
Default values only
Navigation Pane grouping
Macros or VBA event procedures (e.g. Before Update) to validate and handle unexpected events
A button 'Generate Attendance Report' is required. What is the best implementation?
A macro attached to the button to open a grouped report based on a prebuilt totals query
A label explaining where to find reports manually
A hyperlink to an external spreadsheet
A command button with no action
A macro attached to the button to open a grouped report based on a prebuilt totals query
Which layout choice supports high usability in Form Design View?
Consistent alignment, logical tab order, grouped related fields, and descriptive control tips
Random placement of controls for speed
Rely on default auto-generated layout
Use colours heavily to decorate forms
Consistent alignment, logical tab order, grouped related fields, and descriptive control tips
How should unexpected events be handled on data entry?
Use error-handling macros/VBA with user-friendly messages and rollback of invalid operations
Let Access show default error numbers
Ignore errors and ask users to retry
Disable validation to prevent messages
Use error-handling macros/VBA with user-friendly messages and rollback of invalid operations
Which statement best evidences all functional requirements?
All functional requirements are met
Most features are implemented
Basic CRUD operations only
Interface opens but some features are missing
All functional requirements are met
Which evidences 'fully meets the given criteria'?
s implemented and verified via successful tests across all forms/reports
d but some untested
buttons do nothing
s implemented and verified via successful tests across all forms/reports
Which check supports 'easy maintenance by a third party'?
Consistent object naming (forms/reports/queries), comments in macros/VBA, and a simple navigation structure
Hide objects and use ad-hoc names
Store logic in many hidden macros
Use duplicate object names for speed
Consistent object naming (forms/reports/queries), comments in macros/VBA, and a simple navigation structure
Which test evidence proves interface testing for the Enrolments form?
A table of test cases with screenshots, expected vs actual results, and notes on fixes applied
One screenshot of a successful save
A test note saying 'form OK'
A list of field names only
A table of test cases with screenshots, expected vs actual results, and notes on fixes applied
In testing, a combo box allows selecting a non-existent ModuleID. What should be done?
Rebuild the Row Source to the Modules table and re-enforce referential integrity; re-test and record results
Allow any text to be typed and trust users
Remove the combo box and allow free entry
Ignore; it happens rarely
Rebuild the Row Source to the Modules table and re-enforce referential integrity; re-test and record results
Which set qualifies as test data for a Date of Birth field?
10/10/2005; 'abcd'; 31/12/1899 and Tomorrow's date
any text; any number; any symbol
01; 02; 03
Only today's date values
10/10/2005; 'abcd'; 31/12/1899 and Tomorrow's date
What distinguishes expected results in interface testing?
They are specific to the exact input and include the intended validation message or outcome
They are generic statements like 'should save'
They are omitted to save time
They are written after testing is complete
They are specific to the exact input and include the intended validation message or outcome
Which follow-up action is required after fixing a defect?
Re-test the exact case and record the new actual result referencing the fix
Delete the test case
Mark the case as 'N/A' without retest
Assume similar cases are fixed
Re-test the exact case and record the new actual result referencing the fix
Which evaluation statement best matches usability analysis?
The interface is intuitive: Navigation Form groups tasks logically; tab order supports efficient entry; labels/tooltips aid novice users
The interface opens quickly without errors
The colours are bright and engaging
There are many forms available
The interface is intuitive: Navigation Form groups tasks logically; tab order supports efficient entry; labels/tooltips aid novice users
How should performance be justified in the evaluation?
Explain how indexes on StudentID/ModuleID and filtered queries reduce load times for forms and reports
Say 'performance is fine'
Add more memory to the PC
Create larger tables to improve speed
Which technical vocabulary is used in database discussions?
Terms like 'referential integrity', 'junction table', 'Before Update event', 'totals query', 'cascading update'
General words like 'thing', 'stuff', 'nice'
Acronyms only with no context
No terminology to keep it simple
Which linkage to scenario requirements best supports evaluation?
A) Explaining how attendance reports by module support Heads of Department decision-making
B) Saying 'reports are useful'
C) Referencing a different school's needs
D) Stating 'we have reports' without purpose
Which improvement suggestion is the most effective?
Propose specific enhancements (e.g. add a macro to prevent duplicate enrolments) with
Suggest 'make it look better'
Add more colours to forms
No improvements needed
