Pearson BTEC International Level 3 — Information Technology

Unit 2: Creating Systems to Manage Information

The RAR archive contained 3 files. Here is the full content of each:

File 1: Assignment Brief — Learning Aim A

Unit 2: Creating Systems to Manage Information

Vocational Scenario or Context

You are an IT consultant and have been asked to work with Go Wild Wildlife Park to advise on how a relational database management system (RDBMS) could help them manage their information. The wildlife park has provided you with a CSV dataset containing records about animals, their keepers, and enclosures.

Although you will not yet design or develop the database, you must explain, analyse and evaluate how the features and functions of a relational database could be used to manage this dataset effectively.

The owner of the park is not familiar with relational databases and wants to understand how such a system would support:

• Storing and organizing data from the CSV file (animals, staff, enclosures)
• Managing relationships between data (e.g., one keeper to many animals, many animals per enclosure)
• Supporting data integrity, validity, and accuracy
• Enabling useful queries and reporting for decision-making

Task 1

Produce a report for the park owner that evaluates the purpose and structure of a relational database management system. You should include:

• An explanation of how relational database features (tables, records, fields, primary keys, foreign keys, relationships, queries, forms, reports, validation, etc.) are used for database management. (A.P1)
• An analysis of how these features would contribute to a suitable database solution for managing the provided dataset. (A.M1)
• An evaluation of how the features and functions of a relational database contribute to an effective final solution for managing the dataset. (A.D1)

Checklist of evidence required: Report

Criteria Covered by Task 1

📄 File 2: Assignment Brief — Learning Aims B and C

Unit 2: Creating Systems to Manage Information

Vocational Scenario or Context

You have been asked to create a relational database for Go Wild Wildlife Park. The database will store records on animals and their keepers. The park looks after animals from across the world. The park is based in Wollombi, New South Wales, Australia.

The data provided in the data file is presented in one large table. This table is inefficient because it has repeated data. You will split up this large table into five linked tables that record the:

1. Animals — animal ID, name, gender, year of arrival, and primary keys from other tables
2. Species — species ID, species type, species group, lifestyle, conservation status
3. Diet — diet ID, diet type, number of feeds per day
4. Keepers — keeper ID, name, date of birth, rank
5. Enclosures — enclosure ID, type, location

The park employs keepers of varying ranks: Senior, Standard, and Junior. Each animal is assigned to one keeper who cares for several animals. The park uses a variety of enclosures (e.g., moat), each housing several animals. Each animal is fed a specific diet a set number of times a day.

Task 1

Create a data dictionary and entity relationship diagram (ERD) for the database. The database structure must:

• Accept the data provided
• Avoid unnecessary duplication
• Use recognised naming conventions
• Ensure data integrity

The data dictionary must demonstrate appropriate validation which may include:

• Presence checks
• Length checks
• Value lookups
• Table lookups
• Range checks
• Format checks

Comment on your designs to evaluate how your decisions meet the purpose of the database.

Checklist of evidence required: A database dictionary + design comments

Task 2

Use your data dictionary and ERD to create the relational database structure.

Task B — The owner wants you to create data entry forms for:

• Animals in the park
• Animals' species
• Animals' diet
• Keepers
• Enclosures

Forms must include features to ensure validity and integrity of data, and include at least one automated feature.

Checklist of evidence required: A written report

Task 3

Import the data from the CSV file into your database. Create the following queries (show design view and datasheet view for each):

1. Alphabetical sorted list of keepers — showing ID, name, and rank
2. Count of animals in each type of enclosure
3. Parameter query — user enters keeper rank; displays keeper name and date of birth
4. Total animals per species with more than 3 feeds per day
5. Omnivores who are critically endangered — showing animal ID, arrival date, species ID, keeper ID
6. Number of animals in each enclosure location (North, South, East, West)
7. Animals that arrived from 2016 onwards — displaying ID, name, year, species type, keeper ID

Checklist of evidence required: A written report

Task 4

Create database reports that:

1. Display animals supervised by Dave and Temi, grouped by keeper with animal details, count per keeper, and overall total
2. Show how many animals belong to each Conservation Status category (Endangered, Vulnerable, Least Concern, etc.)
3. Group animals by Enclosure Type, displaying species type and total number of animals per group

Checklist of evidence required: A written report

Task 5

Create a test plan to test:

• Input forms including calculations, validation, and macros/code
• Use appropriate test data: normal, erroneous, and extreme
• Complete the plan showing expected outcomes, actual outcomes, and how errors were fixed

Task 6

Evaluate your database against the given scenario, justifying:

• How well your solution meets the requirements of the scenario
• The quality, performance and usability of the database
• The changes made during the development and testing process

Internal Verification — Assignment Brief

Internal Verifier Checklist:

📄 File 3: Data Dictionary (CSV) — Go Wild Wildlife Park

Note: Animal A46 has a missing Species ID in the original data — this is worth noting for your data integrity checks in your data dictionary (presence check).

Note: This report is provided as a sample for reference purposes only. For further guidance, detailed solutions, or personalized assignment support, please contact us directly.

Unit 2: Creating Systems to Manage Information

Sample Solution – Go Wild Wildlife Park

Learning Aim A – Task 1 Report

Introduction

Go Wild Wildlife Park currently stores its data in a single CSV file, which leads to duplication, inconsistency, and difficulty in managing relationships between data. A relational database management system (RDBMS) provides a structured way to store, manage, and retrieve data efficiently. This report explains, analyses, and evaluates how an RDBMS would support the park’s operations.

A.P1 – Explanation of Relational Database Features

A relational database uses several key features:

Tables
Data is organised into tables such as Animals, Species, Diet, Keepers, and Enclosures. Each table stores related data.

Records and Fields
A record represents a single entry (e.g., one animal), while fields represent attributes (e.g., name, gender).

Primary Keys
Each table contains a unique identifier, such as AnimalID or KeeperID, ensuring that each record is unique.

Foreign Keys
Foreign keys link tables together. For example, SpeciesID in the Animals table links to the Species table.

Relationships
Relationships define how tables are connected. For example:

• One keeper can manage many animals
• One enclosure can contain multiple animals

Queries
Queries are used to retrieve specific data, such as animals that arrived after a certain year or animals in a particular enclosure.

Forms
Forms provide a user-friendly interface for entering and editing data.

Reports
Reports summarise data for decision-making, such as the number of animals per enclosure.

Validation
Validation rules ensure data accuracy, such as:

• Presence checks (no empty required fields)
• Range checks (valid year values)
• Format checks (correct date format)

A.M1 – Analysis

The use of an RDBMS improves data management in several ways:

• Reduction of duplication: Data is split into separate tables, avoiding repeated entries (e.g., keeper details stored once).
• Improved accuracy: Validation rules prevent incorrect or missing data.
• Better organisation: Logical structure makes data easier to manage and understand.
• Efficient querying: Users can quickly retrieve specific information.

For example, instead of storing keeper details repeatedly for each animal, the system stores them once and links them through a foreign key. This reduces redundancy and ensures consistency.

A.D1 – Evaluation

An RDBMS is highly suitable for managing the wildlife park’s data:

• Data integrity is maintained through primary and foreign keys
• Consistency is ensured by eliminating duplication
• Flexibility allows new data to be added easily
• Performance is improved through efficient queries

However, implementing an RDBMS requires:

• Initial design effort
• Staff training

Overall, the advantages outweigh the limitations, making it an effective solution for managing the park’s data.

Learning Aim B – Task 1 Design

Data Dictionary (Sample)

Animals Table

Validation Example (Important)

Animal A46 has a missing Species ID.
To prevent this:

• Apply a presence check on SpeciesID
• Use a lookup constraint to ensure valid entries

Entity Relationship Design (ERD Explanation)

• Animals → Species (Many-to-One)
• Animals → Keepers (Many-to-One)
• Animals → Diet (Many-to-One)
• Animals → Enclosures (Many-to-One)

This design removes redundancy and ensures efficient data management.

Design Justification (M2 & D2)

The design:

• Eliminates duplicate data
• Uses keys to maintain relationships
• Ensures data integrity through validation
• Supports all required queries and reports

It fully meets the client’s requirements by providing a structured and scalable solution.

Learning Aim C – Development

Task 2 – Database Creation

• Created five tables: Animals, Species, Diet, Keepers, Enclosures
• Defined primary keys and foreign keys
• Established relationships between tables

Forms

Forms created:

• Animal Form
• Species Form
• Diet Form
• Keeper Form
• Enclosure Form

Features included:

• Drop-down lists (lookup fields)
• Required field validation
• Automatic ID generation

Task 3 – Queries

Queries created include:

1. List of keepers sorted alphabetically
2. Count of animals in each enclosure
3. Parameter query based on keeper rank
4. Animals with more than 3 feeds per day
5. Critically endangered omnivores
6. Number of animals per location
7. Animals that arrived from 2016 onwards

These queries support decision-making and data analysis.

Task 4 – Reports

Reports include:

• Animals grouped by keeper
• Animals by conservation status
• Animals by enclosure type

These reports provide clear summaries for management.

Task 5 – Testing

Errors were identified and corrected through validation rules.

Task 6 – Evaluation

The database meets the requirements by:

• Providing accurate and structured data storage
• Supporting efficient queries and reporting
• Ensuring data integrity through validation

During testing, improvements were made such as:

• Adding stricter validation rules
• Refining relationships

Overall, the database is effective, reliable, and suitable for the wildlife park.