Textbook Solutions: The power of data: Using data modelling | IGCSE Cambridge Computing for Year 6 - Class 6 PDF Download

Data models, simulations and real-life scenarios

Q1. Create an infographic to show how databases and spreadsheets can be used to represent real-life situations and how they might be used to help with training, decision-making or even designing products.

- Copy the infographic outline on the next page.

- Expand your infographic to include three examples of applications where databases can be used to help with training, decision-making, designing products or in some other way.

- Add more detail to your infographic to include examples of where spreadsheets could be used to analyse data to help with training, decision-making or designing products.

Ans: 

Databases:

1. Training: In a corporate setting, databases can store employee information, training schedules, and progress reports. This helps in organizing and tracking the effectiveness of training programs.
2. Decision-Making: Retail businesses use databases to keep track of inventory, sales, and customer preferences. Analyzing this data helps in making informed decisions about stock replenishment and marketing strategies.
3. Designing Products: Car manufacturers might use databases to store design specifications, parts inventory, and supplier information. This centralization of data streamlines the product design process and helps in managing the supply chain.

Spreadsheets:

1. Training Analysis: Spreadsheets can be used to analyze employee performance data over time, identifying trends and areas for improvement, which aids in tailoring individual training needs.
2. Financial Decision-Making: Financial analysts use spreadsheets to project future revenues, expenses, and to calculate risks. This helps companies in budgeting and financial planning.
3. Product Design Optimization: Engineers may use spreadsheets to analyze stress tests and other simulation data when designing products. This helps in optimizing designs for safety and functionality before the manufacturing process begins.

Q2. Carry out some research into the use of simulations and how they are used in real-life situations.

- Discuss your findings with a partner and then expand your infographic. Add a new strand that has the title 'Simulations'. The new section of your infographic should be shaded a different colour from the other two. 

Expand your infographic to include three examples of applications you know of where simulations can be used to help with training, decision-making, designing products or in some other way.

Ans: 

Simulations are a crucial tool in various sectors for training, decision-making, and product design. Here are three real-life applications:

Healthcare Training: Medical simulations are used to train healthcare professionals, allowing them to practice procedures in a risk-free environment1.

Aerospace: Flight simulators are extensively used to train pilots, providing realistic scenarios that help improve their skills and decision-making abilities2.

Urban Planning: Simulations help in modeling traffic patterns and public transportation systems to improve flow and reduce congestion

Q3. In a small group, discuss some of the ways in which businesses and other organisations could use data modelling to help make decisions in the following scenarios:

modelling supermarket queues

modelling the design of a new car for car-safety tests

simulating the impact that reducing pollution has on climate change

modelling traffic flow on a busy road with new traffic-control systems.

- Create an infographic to show how data modelling and/or a simulation can be used in one scenario.

- Use your infographic to illustrate the advantages and disadvantages of data modelling or a simulation in that scenario.

Ans: 

• Supermarket Queues: By modeling customer arrival patterns and checkout times, supermarkets can optimize staff allocation to reduce wait times.
• Car Safety Tests: Data modeling can simulate crash tests, helping in the design of safer vehicles by predicting how different designs will perform in collisions.
• Climate Change: Simulating the impact of pollution reduction can help policymakers understand the long-term effects on climate change and guide environmental policies.
• Traffic Control Systems: Modeling traffic flow can assist in designing efficient traffic control systems to manage congestion during peak hours.

Q4. Discuss the following questions with a partner:

- How could the gaming competition organisers use a database to model the competition entry?

- Can you think of any other ways they could make use of a database?

- How might the gaming competition organisers use a spreadsheet application to model the sales of merchandise at the event?

Ans: 

Model Competition Entry: Store participant details, track progress, and manage results.

Other Uses: Analyze player behavior, preferences, and feedback to improve future events.

Spreadsheet Modeling Sales: Organizers could use spreadsheets to predict merchandise sales based on historical data, current trends, and participant numbers, aiding in inventory management and pricing strategies

Q5. If the gaming competition organisers decided to include a simulation game in the competition, do you think this would make the competition more appealing? Why?

Ans: 

It adds variety to the types of games offered, attracting a broader audience.

Simulation games can provide immersive experiences, enhancing engagement and entertainment value.

They can also serve as a tool for skill development, appealing to participants interested in learning and improvement

Spreadsheets: Using Microsoft Excel

Q1. Open the file Introducing Spreadsheets.xlsx provided by your teacher.

- Change the format of cells AI:FI and A2:A5 so that they are formatted in bold.

Copy to cell F2 the formula = AVERAGE(C2:E2), which calculates the average for the first student.

- Use the same method to add a formula that calculates the average score for the remaining students.

- Can you think of any other ways of calculating the average score for a student in this spreadsheet?

Think about how you might add together the students' three scores and then divide the result by 3. Discuss with a partner how you might add this formula to the spreadsheet instead of using the AVERAGE function.

Ans: 

To calculate the average score for a student without using the AVERAGE function, you can add the scores together and then divide by the number of scores. Here’s how you would write the formula in cell F2:

= (C2 + D2 + E2) / 3

This formula adds the scores from cells C2, D2, and E2, then divides the sum by 3 to find the average. You can then drag the fill handle from cell F2 down to fill in the formula for the remaining students.

Q2. The teacher wishes to record the students' total scores in column F instead of the average score. Discuss with a partner how you could use the SUM function to calculate the total scores in this column, instead of AVERAGE. Change the label text in cell Fl to read Total and add an appropriate formula for each student to enable you to calculate their total score. 

Ans: 

To record the total scores in column F, you would use the SUM function. Here’s the formula you would place in cell F2:

= SUM(C2:E2)

This formula sums the scores from cells C2 to E2. After changing the label in cell F1 to “Total,” you can copy this formula down the column to calculate the total scores for all students.

Q3. Spreadsheets are often used for modelling and decision-making, especially in scenarios where numeric data and calculations are involved. Now open the file School Trip.xlsx provided by your teacher. This spreadsheet is being used by a teacher to keep track of payments made by students each month for an upcoming school trip.

With a partner, discuss what calculations are needed in columns Land M.

- Highlight cells B2 to M11 and format those cells to represent currency data. To select a different format for the data in a spreadsheet cell, highlight the cells, then right-click and select Format Cells ... Then select Currency.

Select the correct currency, for example $.

- With a partner, discuss the spreadsheet structure. Are there any additional cells that should be formatted in currency format?

- Amend the contents of cell L2 to include a formula that will calculate the total of all of the payments made by Student 1 in this example.

- When you are happy with the formula entered into cell LI, you need to copy this formula into cells L2 to L11 so that you can calculate the Total Paid for the remaining students.

- Click on cell L1 and hover the mouse over the bottom-right hand corner of the cell. A small plus sign will appear: this is called a fill handle. Keep your finger on the mouse button and drag down to highlight cells L2 to L11. The formula will automatically copy into the remaining cells. You are able to copy the formula in this way because we have used relative cell referencing in this formula.

Ans: 

For the School Trip spreadsheet, the calculations needed in columns L and M would likely involve the total payments made and the remaining balance for each student. Here’s what you should discuss and do:

• Format cells B2 to M11 as currency to reflect the monetary nature of the data.
• Other cells that might need currency formatting could include the total trip cost and any cells that show calculated totals or balances.
• To calculate the total payments made by Student 1, you would enter the following formula in cell L2:

= SUM(B2:K2)

This formula adds up all the payments from January to October for Student 1. After verifying that the formula is correct, you can use the fill handle to copy it to cells L3 to L11 to calculate the total paid for each student.

Q4. Now calculate the balance remaining for each student. Think about how you could use the value in cell P1 to help calculate the balance remaining for each student:
Balance= Trip Cost - Total Paid

Can you add a formula to cell M1 to perform this calculation?

Check that the Balance calculated for Student 1 is correct.

- Now fill t his formula down to calculate the balance for the remaining students.

The results are no longer correct. Examine the formula to work out what has gone wrong.

- Using $ in a cell reference in this way tells the spreadsheet not to update the cell reference when the formula is copied. This is known as an absolute cell reference. 

- Save your spreadsheet

Ans: 

To calculate the balance remaining for each student, you can use the Trip Cost from cell P1 and subtract the Total Paid for each student. Here’s the formula you would enter in cell M2 to calculate the balance for Student 1:

= $P$1 - L2

This formula subtracts the Total Paid amount in cell L2 from the Trip Cost in cell P1. The dollar signs ($) make P1 an absolute reference, meaning it won’t change as you copy the formula to other cells.

After entering the formula in cell M2 and verifying that the balance for Student 1 is correct, you can copy this formula down to cells M3 to M11 to calculate the balance for the remaining students. Because you’ve used an absolute reference for the Trip Cost, only the reference to the Total Paid will change for each row, ensuring the correct balance is calculated for each student.

Modelling tools: Conditional formatting

Q1. In the school trip example from the last theme, the teacher might wish to see quickly who has paid all $550.00 towards their school trip. Open your saved copy of the file SchoolTrip.xlsx.

Highlight cells M2 to M11.

Select conditional Formatting from the Home Tab, then select Highlight cell Rules and set the cell rule to be equal to O, and select the Green  Fill formatting option.

Nothing should happen just yet. Add the following payments for Student 1 in the

months July to August.

I 50 I 50 I 100 I 100

Add additional payments for the remaining students for the months of July to October, so that some have not yet paid the full balance of the trip. 

Test your spreadsheet and conditional formatting now that some students have paid the entire balance of their trip.

Now change the overall cost of the trip to $600. How does this affect the data highlighted in the spreadsheet?

Ans: 

To see who has paid the full amount for the school trip, you can use conditional formatting in Excel. Here’s how you can set it up and test it:

1. Open your saved SchoolTrip.xlsx file.
2. Highlight cells M2 to M11.
3. Go to the Home Tab, select Conditional Formatting, then Highlight Cell Rules.
4. Set the rule to be equal to 0, and choose the Green Fill formatting option.

Now, let’s add the payments for Student 1 for July to August:

| July | August | 
 |  50  |   50   | 
| 100  |  100   |

For the remaining students, add varying payments for July to October so that some have not yet paid the full balance.

Once you’ve added these payments, the conditional formatting will highlight in green the cells where students have paid the entire balance of $550.00.

If you change the overall cost of the trip to $600, you’ll need to adjust the conditional formatting rule to match the new full payment amount. The cells that were previously highlighted may no longer be highlighted if the payments do not cover the new cost of $600. This visual cue quickly shows which students still owe money towards the trip.

Databases: Using Microsoft Access

Q1. Practise using MS Access with the small school trip database from the Learn box above.

Open the file SchoolTrip.accdb provided by your teacher.

Double-click on the table in the database, called Student Table.

This will open a blank database table. Click on the design view icon. You will see that data types for each field have been set as short text. This means that each field can be used to record up to 255 letters, numbers or other keyboard characters.

In this database, we will use StudentlD as a primary key. MS Access provides a special data type that will automatically assign a number to each record in a database. Each time a new record is added this number is automatically increased by 1. Click on the field heading 'StudentlD' and select the autonumber data type for this field to ensure that this happens with StudentlD. Click on the 'StudentlD' field again. This time you are going to set this field to be the primary key field for the database. Do this by clicking on the Primary key icon. surname, Firstname, Year and Class can all remain as 'short text' data types. However, DOB should be formatted using the 'date/time' data type.

Ans: 

 Setting up the Student Table:

1. Open the SchoolTrip.accdb file.
2. Double-click on the Student Table to open it.
3. Switch to the design view by clicking on the design view icon.
4. Notice that the data types are set to ‘short text’.
5. For the StudentID field, change the data type to ‘autonumber’ to ensure each student gets a unique ID automatically.
6. Set the StudentID field as the primary key by clicking on the primary key icon.
7. Leave the surname, firstname, year, and class fields as ‘short text’.
8. Change the data type for the DOB (Date of Birth) field to ‘date/time’ for proper date formatting.

Q2. You have now successfully edited your first MS Access database table! To add data to the database table you must now exit design view and instead enter data sheet view.

- Add the details for each of the students attending the school trip using the table of information provided in the Learn box. Do this by clicking on each cell in turn and adding the students' details under each field heading.

- Save your database by clicking on File and then save.

Ans: 

Adding Data to the Table:

1. Exit the design view to return to the datasheet view.
2. Add the details for each student attending the school trip as provided in the Learn box. Click on each cell and type in the information accordingly.
3. Once all the details are entered, save your database by clicking on File and then Save.

Database models: Setting up your data model

Q1. Examine the car database example shown below and discuss with a partner the questions that follow. Write down your answers.

- Which field would be best used as a primary key and why?

— What data type would you select for each field?

— What would be an appropriate field length for each field?

— What do you think should be the maximum number of characters required for each field?

Ans: 

• Primary Key: The ‘Registration’ field would be best used as a primary key because it is unique to each car and does not change over time. This uniqueness ensures that each record can be identified distinctly.

• Data Types:

  • ‘Registration’: Text, as it includes both letters and numbers.
  • ‘Make’: Text, as it is a string representing the brand of the car.
  • ‘Model’: Text, for the specific model name.
  • ‘Colour’: Text, for the color description.
  • ‘Mileage’: Number, as it represents a numerical value.
  • ‘Price’: Currency, to represent monetary values with proper formatting.

• Field Lengths:

  • ‘Registration’: Typically, a registration number doesn’t exceed 10 characters.
  • ‘Make’: Car makes usually don’t exceed 20 characters.
  • ‘Model’: Model names can vary, but 30 characters should be sufficient.
  • ‘Colour’: Color names are generally short, so 15 characters would be ample.
  • ‘Mileage’: A number field’s length is determined by its data type and doesn’t require a set length.
  • ‘Price’: Similar to mileage, the currency data type will handle the length.

• Maximum Number of Characters:

  • ‘Registration’: 10 characters should cover most registration formats.
  • ‘Make’: 20 characters to accommodate even the longest car make names.
  • ‘Model’: 30 characters to ensure even complex model names fit.
  • ‘Colour’: 15 characters, which is more than enough for any standard color name.
  • ‘Mileage’: This will depend on the number data type’s settings, but typically, a 7-digit number would cover most mileages.
  • ‘Price’: As with mileage, the currency data type will dictate the maximum number of characters, but typically, prices don’t exceed 10 digits including decimal places.

Q2. Consider the most appropriate data types for the gaming competition database table called 'Registration Data'.

Copy the table structure shown below.

- List the field names from the gaming competition database in the table you have created. The first row has been completed for you.

— Place an asterisk (i) beside the field you think could be used as a primary key field. Write a sentence to explain why you selected this field.

Ans: 

Q3. Edit your table to show the data types and field lengths you would assign to each field. The first row in the table has been completed for you.

Ans:  Here’s how you can edit your table to show the data types and field lengths for the ‘Registration Data’ table:

Q4. Use your new data dictionary to help you as you develop the gaming competition database further. In this part of the task, you are going to assign the correct data types and field lengths to the gaming competition database. Follow these instructions:

- Open the file GamingComp.accdb provided by your teacher.

Open the table called Registration Data.

Click to view the table called Registration Data in design view.

Select the most appropriate data type for each field in the database by selecting the data type from t he dropdown list beside each field name.

Edit the field size where appropriate. Set Username to be the primary key field. To do this, highlight the row containing the Username field and click on the Primary Key icon in the menu bar. Save the file.

Ans: 

To assign the correct data types and field lengths in the ‘GamingComp.accdb’ database, follow these steps:

1. Open the file GamingComp.accdb.
2. Open the table called ‘Registration Data’.
3. Switch to design view to see the structure of the table.
4. For each field, select the most appropriate data type from the dropdown list:
   • Username: Short Text (Field Length: 30)
   • Password: Short Text (Field Length: 30)
   • Email: Short Text (Field Length: 50)
   • Age: Number (Integer)
   • Date Joined: Date/Time
   • Gamer Level: Short Text (Field Length: 20)
   • Parental Permission: Yes/No
5. Set the ‘Username’ field as the primary key by highlighting the row and clicking on the Primary Key icon.
6. Save the changes to the table.

Collecting user data 

Q1. Use what you have learned to design a user-friendly form that could be used to collect data for new competitors in the gaming competition. Things to consider when designing your form include: 

Should you design a gaming competition logo or use the school logo?

What title should you include on the form?

What field headings are you collecting data for?

Do all of these field headings need to link to data that must be provided by the competitors (for example, some forms include a section with the heading 'For office use only')?

Should all of the data be included on the form?

Should any of the fields contain a limit to the number of characters the person completing the form can include in their answer?

Do any of the fields contain options the user could select from? How might you present these on the form?

Should any additional guidance be provided to help users complete the form? What should this be and where should it be added?

Ans: 

Designing a user-friendly form for new competitors in a gaming competition involves several considerations to ensure it’s both functional and appealing. Here’s a guide on how to approach it:

Logo:

• If the competition is closely associated with the school, using the school logo can reinforce the connection.
• For a distinct identity, designing a unique gaming competition logo could be more engaging and appropriate.

Title:

• The title should be clear and descriptive, such as “Gaming Competition Registration Form.”

Field Headings:

• Collect data for:
  • Username
  • Password
  • Email
  • Age
  • Date Joined
  • Gamer Level
  • Parental Permission (if applicable)

Sections for Office Use:

• Include a section titled “For Office Use Only” for internal tracking and comments.

Data Inclusion:

• Include only the data necessary for the competition’s organization and communication with participants.

Character Limits:

• Fields like ‘Username’ and ‘Password’ should have character limits to ensure data uniformity and security.

Selectable Options:

• For fields like ‘Gamer Level,’ use dropdown menus with options like ‘Beginner,’ ‘Intermediate,’ ‘Expert.’
• For ‘Parental Permission,’ use checkboxes or radio buttons for ‘Yes’ or ‘No.’

Additional Guidance:

• Provide guidance for fields that require specific formats, like ‘Email’ or ‘Date Joined.’
• Include tooltips or a help icon next to complex fields.
• Add a brief instruction section at the top or bottom of the form.

Form Design Tips:

• Ensure the form is logically organized, with personal information at the top and consent-related fields at the bottom.
• Use clear, legible fonts and adequate spacing.
• Highlight required fields with an asterisk (*).
• Provide a clear submission button, like “Register Now.”

Highlighting the important data

Q1. The gaming competition organiser wants to highlight the beginner level gamers when they are registering for the competition, as they would like to check that they are familiar with the rules of the competition.

Use conditional formatting in the Registration Form (this form has already been created for you) to highlight any students who are beginner level. This will help give the organisers a quick view of how many beginners they have in the competition. Open GamingComp3.accdb provided by your teacher. Open the item called Registration Form by double-clicking on it. Display the form in design view. 

- Highlight the control (the text box) in the form where the user will enter the gamer level of the student.

Click on the Format tab.

- Click on Conditional Formatting to display the conditional formatting rules manager.

In the conditional formatting rules manager, click on New Rule and set the rule for the gamer level field, as shown.

Click OK in the Edit Formatting Rule screen and then click OK again to return to your form in design view.

Ans: 

To highlight beginner level gamers in the Registration Form using conditional formatting in MS Access, follow these steps:

1. Open GamingComp3.accdb and double-click on ‘Registration Form’ to open it.
2. In the form, switch to design view.
3. Locate and highlight the text box control where the gamer level is entered.
4. Go to the Format tab.
5. Click on Conditional Formatting to open the rules manager.
6. In the rules manager, click on ‘New Rule’.
7. Set the rule to highlight the field when the text is “Beginner”. You might set the condition to “Field Value Is” and then “equal to” and type “Beginner” in the value box.
8. Choose a formatting style, such as a bold font or a different background color, to make the “Beginner” entries stand out.
9. Click OK to apply the rule, and then click OK again to exit the Conditional Formatting Rules Manager.

Q2. Save your form and scroll through the records in your form to check that all beginner level registrations are highlighted.
Ans: 

To check that the conditional formatting is working:

1. Save your form.
2. Switch to form view and scroll through the records.
3. Verify that all entries with the gamer level set to “Beginner” are highlighted according to the rule you set.

Modelling methods: Software choices

Q1. Examine each of the scenarios listed below. With a partner, discuss the following questions:

- What factors do you need to record for each scenario? For example, what data do you need to store?

- List the field headings you would use to organise the data associated with each scenario.

- List any queries or calculations you would need to help you to model each scenario.

Scenario 1

You are creating a data model to record seat bookings in a cinema.

Scenario 2

You are creating a model to keep a record Of student performance in a school swimming gala where an award is given to the team with most points.

Ans: 

Scenario 1: Cinema Seat Bookings

Factors to Record:

• Movie Title
• Showtime
• Seat Number
• Customer Name
• Booking Status (Reserved, Paid, Available)
• Price

Field Headings:

• Movie_Title
• Show_Time
• Seat_No
• Customer_Name
• Booking_Status
• Ticket_Price

Queries/Calculations:

• Calculate total revenue per showtime.
• Count available seats.
• List all reserved/paid seats.

Scenario 2: School Swimming Gala Performance

Factors to Record:

• Student Name
• Team Name
• Event
• Event Time
• Points Awarded

Field Headings:

• Student_Name
• Team_Name
• Event
• Event_Time
• Points

Queries/Calculations:

• Sum points per team.
• Rank teams by total points.
• Identify top performers in each event.

Q2. Copy and complete the table below to help you decide which application you would use to model each scenario:

Ans: 

Challenge yourself

Q. Earlier in this unit, you used a spreadsheet to keep track of pupil payments into a school- trip fund. You then changed the cost of the trip and some of the payment figures to try to make sure that every student paid the trip balance in full. Changing data in a model in this way is known as What-If Analysis. Spreadsheet applications such as MS Excel offer some built-in tools, such as What-if Analysis, to help organisations make decisions. Another valuable tool offered by MS Excel is Goal-Seek Analysis. Use the internet to find out more about each of the tools listed below and how they can be used to analyse data in a spreadsheet application. 

• Describe what the tools are designed to do. Say how you think the gaming competition organisers could use these tools in a spreadsheet to help them keep track of the funds they are raising for the school. Copy and complete this table.

Ans: 

Goal-Seek Analysis is a function in Excel that is part of the What-If Analysis toolkit. It allows you to find the necessary input value that will produce a desired outcome in a formula. For example, if the gaming competition organizers want to reach a specific amount of funds, they can use Goal-Seek to determine how many tickets they need to sell at a certain price to meet their financial goal.

What-If Analysis in Excel refers to a set of tools that allow you to test different scenarios by changing input values in your spreadsheet to see how they affect the outcomes. This can include tools like Data Tables, Scenario Manager, and Goal Seek. The gaming competition organizers could use What-If Analysis to explore various financial scenarios, such as what would happen if the ticket prices were raised or if additional sponsors were found, and how these changes would impact their total funds.

Here’s how the table would look with the descriptions filled in:

Final Project

Some of the competitors in the gaming competition have asked the organisers whether they will have an opportunity to use simulations in the competition. The organisers have asked you to help them understand the difference between a model and a simulation.

Q1. Write a short paragraph to explain to the organisers what is meant by a data model. Explain the key features of a data model and the advantages and disadvantages of using one.

- Use an example to show how data models are used in a real-life application you have studied in this unit.

Explain how a data model is used in this application to help make decisions.

Ans: 

A data model is a conceptual representation of the data structures that are required by a database. It outlines how data is connected to one another and how they are processed and stored inside the system. The key features of a data model include entities (which represent data types), relationships (which show how entities are connected), and constraints (which ensure the integrity of the data).

Advantages:

• Provides a clear structure for the database.
• Helps in designing the database by specifying the data and how they are related.
• Facilitates data management and querying.

Disadvantages:

• Can be complex to design for large systems.
• Changes in requirements can necessitate changes in the model, which can be time-consuming.

Real-Life Application Example: In the scenario of tracking pupil payments for a school trip, a data model is used to manage student information, payment amounts, dates, and the status of each payment. This model helps the school to make decisions regarding who has completed their payments, who still owes money, and how close they are to reaching the total amount needed for the trip.

Q2. Describe one application where simulations can be used to help model a real-life situation. What are the advantages of using a simulation in the real-life application you have selected?

Ans: 

One application where simulations are used is in urban planning, specifically in traffic flow management. Simulations can model the behavior of traffic through a network of roads, considering various factors like traffic signals, time of day, and accident scenarios.

Advantages:

• Allows planners to test the impact of changes without disrupting real-world traffic.
• Helps in optimizing traffic light timings and road layouts to improve flow.
• Can save time and resources by preventing ineffective real-world trials.

Database task

The gaming competition organiser has created a data model in the form of a database to help keep track Of the sales of both merchandise and meet-and-greet tickets. They have asked you to help them set up the database so that they can produce some useful data about how the sales have gone so far. Before you can do this, however, you must amend the database design to ensure that:

the correct data items have been assigned to each field

a key field has been correctly assigned to the database table

the field lengths have been amended so that the database does not take up too much space.

Q1. Open the file Sales.accdb provided by your teacher. Use this file to complete the following tasks:

Produce queries to list the student numbers of each person that has purchased baseball caps (as they would like to distribute these to the students at the end of the week).

- Identify merchandise that has sold fewer than ten items. To do this, open the report called 'Sales'. Edit the report to highlight in red merchandise items where fewer than ten items have been sold.

- Save your database.

Ans: 

To complete the tasks using the Sales.accdb file, you would:

1. Create a query to list student numbers who purchased baseball caps:

   • Open the database and navigate to the Query Design view.
   • Add the table(s) that contain student numbers and purchase details.
   • Set the criteria to filter for ‘Baseball Caps’ in the merchandise field.
   • Select the student number field to be displayed in the query results.
   • Run the query to view the list.

2. Identify merchandise with fewer than ten sales and edit the ‘Sales’ report:

   • Open the ‘Sales’ report in design view.
   • Access the Conditional Formatting options for the field that displays the number of items sold.
   • Set a new rule to highlight the field in red if the value is less than ten.
   • Save the changes and switch to report view to see the updated formatting.

Q2. Consider additional queries that might be useful to the organisers of the gaming competition. Design the queries using a table similar to the one used on page 135. If you have time, you should also create the queries.

Ans: 

Q3. Design a data capture form that would allow users to enter data into the table called Orders in the database called Sales.accdb.

Ans: 

For designing a data capture form for the ‘Orders’ table:

1. Open the database and navigate to the Form Design view.
2. Add fields corresponding to the ‘Orders’ table, such as:
   • Order ID (AutoNumber, set as primary key)
   • Student Number (Text, with validation for format)
   • Item Purchased (Combo box with predefined merchandise list)
   • Quantity (Number, with validation for positive integers)
   • Date of Purchase (Date/Time, with a date picker)
3. Set properties for each field to ensure data integrity, such as limiting character length or setting required fields.
4. Include buttons for ‘Submit’, ‘Clear Form’, and ‘Cancel’.
5. Test the form by entering sample data and ensure it saves correctly to the ‘Orders’ table.

Spreadsheet task

The organiser needs your help to set up a system that can enable them to make decisions such as:

how much the meet-and-greet tickets should cost

how much each T-shirt, sweatshirt and baseball cap should cost.

You have advised the organiser that a spreadsheet would be the best tool for this task.

Review the key features of a spreadsheet, covered earlier in this unit.
Write a sentence to explain why you feel a spreadsheet would be best for this task.

The organisers have created a spreadsheet to help them keep track of the data they have collected so far. They need your help developing  some of the calculations in the spreadsheet.

Q3. Open the file Final Project.xlsx provided by your teacher.

- Continue the development of the spreadsheet by adding the data and additional labels shown in the spreadsheet below. (Do not worry about shading your spreadsheet cells in the same way.)

Use what you have learned about spreadsheet applications and formulae to add the correct formulae to the cells outlined in red . The spreadsheet cell colours provide a hint as to which cells can have a formula copied and pasted. 

- Use conditional formatting to colour the funds green when Funds Raised > Target and red when Funds Raised < Target.

- A second celebrity gamer will appear; their appearance fee is $300 and 25 tickets have been sold so far. Update the spreadsheet to include this additional information.

— Save your spreadsheet.

Ans: 

A spreadsheet is an ideal tool for this task because it allows for the organization of data in a tabular format, supports the use of formulas to perform calculations, and offers features like What-If Analysis and Goal-Seek to model different pricing scenarios. Additionally, conditional formatting can visually highlight data based on specific criteria, such as whether funds raised are above or below the target.

For the spreadsheet development:

1. Add the required data and labels as indicated in the provided spreadsheet outline.

2. Insert formulas in the cells outlined in red. For example, if you’re calculating total revenue from ticket sales, the formula might look like this:

   = Number_of_Tickets_Sold * Price_Per_Ticket
   

3. Apply conditional formatting:

   • Select the ‘Funds Raised’ cells.
   • Go to the ‘Home’ tab, click on ‘Conditional Formatting’, and choose ‘New Rule’.
   • Use a formula to determine which cells to format. For example:
     • For green: =Cell_Reference > Target
     • For red: =Cell_Reference < Target
   • Choose the formatting options (green fill for above target, red fill for below target).
   • Click ‘OK’ to apply the formatting.

4. Update the spreadsheet for the second celebrity gamer:

   • Add a new row or section for the second celebrity gamer.
   • Enter their appearance fee as $300.
   • Record the 25 tickets sold.
   • Calculate the revenue generated from this celebrity’s tickets.

5. Save the spreadsheet after making all the updates.