VALID THRU: 04-Apr-2025

Assignment Briefs 03-06-2025

Unit 5: Data Modelling

Unit 5 Data Modelling

Authorised Assignment Brief for Learning Aims B and C

Introduction To Computer Infrastructure (Aberystwyth University)

BTEC Assignment Brief

Qualification	Pearson BTEC Level 3 National Extended Certificate in Information Technology Pearson BTEC Level 3 National Foundation Diploma in Information Technology
Unit number and title	Unit 5: Data Modelling
Learning aim(s) (For NQF only)	B: Design a data model to meet client requirements C: Develop a data model to meet client requirements
Assignment title	Design, create and evaluate a data model
Assessor
Issue date	w/c 27^th February 2023
Hand in deadline	w/c 17^th April 2023

Vocational Scenario or Context	You have recently joined a new company as a trainee data analyst. The company import a range of different speciality Cheeses from France and the Netherlands. They supply the cheese to a number of different restaurants and specialist delicatessens throughout the UK. The company face a number of challenges due to currency exchange rate fluctuations (between the Euro, that they buy the cheese in, and the pound that they sell it in) and the variations in economic growth. Their product is relatively expensive and sells better when economic growth is good and less well when it is poor. You have been asked to design and create a spreadsheet model which can predict the potential impact of exchange rate fluctuations on the company profit margins. The model should use the data collected by the company on average monthly sales of the top five best-selling cheeses in their range:
		Cheese	Cost per Kilo in Euros	Average monthly sales based on a Euro to Pound exchange rate of 0.7
		Camembert	28	900
		Emmental	32	850
		Gruyere	35	875
		Edam	29	825
		Gouda	31	700
	The typical margin applied to calculate the UK sales price is 40%. Profit is calculated by subtracting the cost per kilo in pounds from the sales price. The company have found that on average for each 10p rise in the sales price (based on the 0.7 exchange rate) the average monthly sales drops by 20 units. If the exchange rate drops below 0.7 then the sales increase by a similar amount. When the exchange rate increases the company can do one of two things:

decrease the margin so prices stay the same
increase the prices keeping the margin the same, but this will reduce sales volume

Your model should show the impact of doing each of these on the overall profit the company makes.

The model you develop should be designed to be used by people who are not experts in spreadsheets and should include a user interface which allows the user to enter the exchange rate and then choose to either change the margin or the prices.

Task 1

Following on from the presentation you created for the manager of the company you now need to:

Produce a design for the data model that meets the requirements described above including worksheet structure diagrams and a test plan. Your design should include a number of alternatives, for example to the way the user interface is designed.
Review your design for the model with two other people and record their feedback and use it to improve your design.
Write a justification of the design decisions you made and how you arrived at the final design.
Develop the model based on your final design.
Test the model using the test plan you created, checking that it functions as it should and that it produces the correct results.
Demonstrate your completed model to a user and collect their feedback, using their feedback and results of your testing to refine and optimise your model.
Write an evaluation of the design and the final optimised version of your data model considering how well it meets the client’s requirements.

Present evidence that you have shown individual responsibility, effective time-management in your design and development of a data model making high-quality justified recommendations and decisions. For example, you need to show how you have:

Planned and managed your time and met targets.
Reviewed and responded to outcomes including the use of feedback from others
Behaved appropriately while completing the assignment – including professionalism, etiquette, supportive of others, timely and appropriate leadership, accountability and individual responsibility
Evaluated outcomes to help inform high-quality justified recommendations and decisions
Used appropriate methods of communication effectively

Checklist of evidence required

Design documentation
Feedback from the design review from at least two people and evidence of how you used this to improve the design, e.g. before and after designs
Written justification of your design decisions
Evidence of developing the model, e.g. annotated screen shots showing the development process

		Feedback for the review of the model by a user Evidence of how you used the feedback to refine and improve the model, e.g. before and after screen shots An evaluation of the design and the completed model Evidence that you have shown individual responsibility, effective time-management and made high-quality justified recommendations and decisions
Criteria covered by this task:
Unit/Criteria reference	To achieve the criteria you must show that you are able to:
5/BC.D2	Evaluate the design and optimised data model against client requirements.
5/BC.D3	Demonstrate individual responsibility, creativity, and effective self-management in the design, development and review of a data model.
5/B.M2	Justify decisions made, showing how the design will fulfil its purpose and client requirements.
5/C.M3	Optimise the data model to meet client requirements.
5/B.P3	Produce designs for a data model which meet client requirements.
5/B.P4	Review the designs with others, to identify and inform improvements.
5/C.P5	Develop a data model to meet client requirements.
5/C.P6	Test the data model for correctness, functionality and acceptance.
5/C.P7	Review the extent to which the data model meets client requirements.

Sources of information to support you with this Assignment
Other assessment materials attached to this Assignment Brief	e.g., work sheets, risk assessments, case study

All You Need to Know About Unit 5 Data Modelling

Unit 5: Data Modelling focuses on understanding how data is structured, stored, and used within a system. It teaches the principles of designing data models that ensure efficiency, accuracy, and ease of access.

What is Data Modelling?

Data modelling is the process of creating a structured representation of data. It defines how data is organised, how different data elements relate to each other, and how they can be stored and retrieved efficiently.

Key Components of Data Modelling

Entities – These are objects or concepts that store data, such as customers, products, or employees.
Attributes – These describe the properties of entities, like a customer’s name, email, or phone number.
Relationships – Define how entities are connected, such as a customer placing an order.
Primary Keys – Unique identifiers for each record in an entity, ensuring no duplicates.
Foreign Keys – Links between entities, establishing relationships between tables in a database.

Types of Data Models in Unit 5

Conceptual Data Model – A high-level overview showing key entities and relationships.
Logical Data Model – Defines attributes, relationships, and constraints in more detail without considering the database structure.
Physical Data Model – Specifies how data will be physically stored in a database, including tables, columns, and indexes.

Importance of Data Modelling in Unit 5

Ensures data integrity by reducing redundancy and inconsistency.
Improves database performance by designing efficient structures.
Enhances data retrieval for analysis and reporting.
Supports better decision-making by providing a structured data foundation.

Final Thoughts

Unit 5: Data Modelling is essential for anyone working with databases. It provides the foundation for designing well-structured, efficient, and scalable data systems that support business operations and decision-making.

Sample Answer

Produce a design for the data model that meets the requirements described above including worksheet structure diagrams and a test plan. Your design should include a number of alternatives, for example to the way the user interface is designed.

Data Model Design Overview

This design follows a relational database approach, ensuring data is stored efficiently, reducing redundancy, and improving accessibility.

Entities and Attributes

Entity	Attributes (Fields)	Primary Key	Foreign Key
Customers	Customer_ID, Name, Email, Phone, Address	Customer_ID	N/A
Orders	Order_ID, Customer_ID, Order_Date, Total_Amount	Order_ID	Customer_ID
Products	Product_ID, Name, Price, Stock_Level	Product_ID	N/A
Order_Items	Item_ID, Order_ID, Product_ID, Quantity, Subtotal	Item_ID	Order_ID, Product_ID

2. Worksheet Structure Diagrams

Conceptual Diagram

Illustrates how data entities relate to each other.

Logical Diagram

Tables and Relationships (Primary & Foreign Keys)

This structure ensures normalisation, reducing data duplication.

3. User Interface (UI) Design Alternatives

Option 1: Form-Based UI

Customers: A form to add/update customer details.

Continued...

Unit 5: Data Modelling