Assessment Task 1: Creating a Database

(LO2) – 36 marks

To complete all the tasks below you need to create a new database file and give it an appropriate name.

Note: Once you have completed your tasks your database is to be saved as ‘Student Registrations.accdb’ and be submitted on Canvas.

1a) Create Tables

Participants (ParticipantID, FirstName, LastName, Email, Organisation)

Sessions (SessionID, SessionName, Date, Time, Facilitator)

Registrations (RegistrationID, ParticipantID, SessionID).

How this will be marked (13 marks total).

1 mark for each field.

1b) Check and Modify Fields

Define the primary key.

Set up relationships between tables using ParticipantID and SessionID.

How this will be marked (3 marks total).

1 mark for the primary key and 2 marks for the relationship.

1c) Enter Sample Data

Add all the data for the 20 participants.

 Add all the data for the sessions, with the session names being Computing, Business, AI, Finance, and Marketing.

Add data for Registration Table like R001, R002, R003, R004, R005, R006, and R007.

How this will be marked (7 marks total).

3 marks for Participants table data, 2 marks for the Sessions table data and 2 marks for the Registration table data.

1d) Create and Run Queries

Participants in Computing Sessions – Display a list of all participants who are registered for the

“Computing” session.

 Session with the Most Participants – Identify and display the session that has the highest number of participants.

 Most Active Organisation – Identify and display the organisation that has registered the most participants across all sessions.

How this will be marked (7 marks total).

3 marks for the Participants in Computing Session’s query, 2 marks for displaying the highest number of participants and 2 marks for displaying the Most Active Organisation

1e) Create a Form

Build a form to input new participants, allowing the user to enter the participantID, first name, last name, email, organisation and register button.

Ensure the form is user-friendly and connects to the participant table.

How this will be marked (6 marks total).

1 mark for correct textbox(s), 1 mark for correct labels. 1 mark for the register button, 3 marks for connection to the right table)

Assessment Task 2: Troubleshooting a Database

(LO3) – 14 marks

2a) Open the sample database file (provided by your tutor) containing:

Students table

Courses table

Enrolments table

Identify and describe three (3) issues from the following possibilities:

The Enrolments table has no StudentID or a duplicated field used as a primary key

Mismatches in field types between linked fields (e.g., StudentID is Number in one table, Text in another)

Missing relationships between tables

Foreign key values that do not match any primary key values in the linked tables.

Screenshots are to be taken of the identified errors.

Submit to Canvas:

screenshots of each highlighted error  the updated Access file to Canvas.

How this will be marked (6 marks total)

1 mark for each issue identified, 1 mark for each description

2b) For each issue you identified in Task 2a, describe the step(s) you would take to fix the problem and restore data accuracy or integrity.

(Use short bullet points or numbered responses.)

Implement the fix directly in the database.

Screenshots are to be taken of the changes.

Submit the updated Access file to Canvas.

How this will be marked (8 marks total)

1 mark for each described set of steps, 1 mark for each implemented fix and 2 marks for the updated Access file

Assessment Task 3: Ethical Data Management in a Database (LO4) – 4 marks

You are helping develop a client database for a local health and wellbeing service. The database includes client names, health notes, appointment records, and contact details.

3a) What are two (2) potential risks that could occur if the database data was stored incorrectly?

How this will be marked (2 marks total).

1 mark for each potential database risk.

3b) How can you limit sensitive fields or tables within the database? Give two (2) ethical approaches.

How this will be marked (2 marks total).

1 mark for each limiting approach.

Assessment Task 4: Developing Additional Skills

When Working With Data

(LO5) – 4 marks

4a) What new features or functions of database application did you learn while completing Task 1? Two

(2) new features or functions are to be provided)

How this will be marked (2 marks total)

• mark for each feature or function

4b) How could these new skills help you in future database projects or workplace tasks?

• marks

How this will be marked (2 marks total)