cpa by bpg - Issuu

COMPUTER APPLICATIONS UPPER SECONDARY

Computer Education Unit, Sciences Branch, Curriculum Planning and Development Division, Ministry of Education, Singapore.

Printed: Nov 2013

The modules studied from Secondary One to Four are:

1. 2. 3. 4. 5. 6.

Computer Fundamentals Document Processing Media Computing Media Elements Multimedia Communication Spreadsheets

This copy belongs to the school. It is not for sale. ÂŠ 2013 Curriculum Planning and Development Division, Ministry of Education, Republic of Singapore

CONTENTS

Page PROBLEM SET 1: Love your computer! (Sec 3) Learning Outcomes Overview Malicious programs Computer crimes Protecting personal data Worksheet 1.1 Virus virus! Worksheet 1.2 Crime watch Worksheet 1.3 Protect yourself

1 1 2 3 4 6 9 10

PROBLEM SET 2: Save water (Sec 3) Learning Outcomes Overview Game design User guide Elements of a computer game Mouse click Random number generator Variables Worksheet 2.1 Pollution free Worksheet 2.2 Every drop counts

11 11 12 12 13 13 14 15 16 20

PROBLEM SET 3: Quizzes (Sec 3) Learning Outcomes Overview User input Relational operators in games Arithmetic operations Sequence in game making Worksheet 3.1 Simple calculation Worksheet 3.2 National education

24 24 25 25 26 27 28 30

PROBLEM SET 4: Well being (Sec 3) Learning Outcomes Overview Key press Boundary conditions Colour detection Worksheet 4.1 Prevent dengue Worksheet 4.2 Cross safely

32 32 33 33 34 35 37

REVISION EXERCISE A: Ocean cleaning

REVISION EXERCISE B: Five worms

REVISION EXERCISE C: IQ quiz

REVISION EXERCISE D: Eat wisely

COURSEWORK

PROBLEM SET 5: Healthy sports for everyone (Sec 3) Learning Outcomes Overview Counting Conditional formatting Sort in ascending order Sort in descending order More about pie chart More about bar chart More about line chart Worksheet 5.1 Participation list Worksheet 5.2 Invitation form Worksheet 5.3 Sports safety

52 52 53 54 55 55 56 56 57 58 60 62

PROBLEM SET 6: Learning about numbers – The IT way! (Sec 3) Learning Outcomes Overview Random number Cursor movement What if Automatic recalculation Remainder Calculated value Rounding numbers Conditional statement Relational operators in spreadsheets Problem solution Worksheet 6.1 Dice it up! Worksheet 6.2 Let’s go shopping! Worksheet 6.3 Did I do well?

64 64 65 66 66 67 68 68 69 70 70 71 72 73 76

PROBLEM SET 7: It’s soccer time! (Sec 3) Learning Outcomes Overview Combination chart Combination chart (secondary axis)

78 78 79 79

Text boxes Text flow Word and spreadsheet tables Link objects between word processor and spreadsheet Worksheet 7.1 Soccer matches Worksheet 7.2 Soccer rules

80 80 81 82 83 86

REVISION EXERCISE E: Spreadsheets (Theory)

REVISION EXERCISE F: Spreadsheets (Practical)

PROBLEM SET 8: School competition (Sec 3) Learning Outcomes Overview 3D drawing Features in 3D drawing Area network Intranet versus internet Computers in society Services on the internet Negative impact of computers Worksheet 8.1 Trophy design Worksheet 8.2 Crossword puzzle

97 97 98 98 99 99 100 102 104 106 108

PROBLEM SET 9: School open house (Sec 3) Learning Outcomes Overview Mail merge Word-processed table as data source Data source fields Audio clips Worksheet 9.1 Invitation letters Worksheet 9.2 Media preparation Worksheet 9.3 Slide show for open house

116 116 117 117 118 119 120 122 123

REVISION EXERCISE G: Crossword puzzle I

125

REVISION EXERCISE H: Crossword puzzle II

127

SAMPLE PRACTICAL PAPER

129

PROBLEM SET 10: My CCA (Sec 4) Learning Outcomes Overview Data validation Types of data validation Error messages

132 132 133 134 136

Data entry String function (Date) String function (Text) Worksheet 10.1 Types of CCA Worksheet 10.2 Choosing CCA Worksheet 10.3 This is my CCA

138 139 139 143 148 153

PROBLEM SET 11: What’s next for me? (Sec 4) Learning Outcomes Overview Data analysis Nested IF statement Boolean operators Table lookup Worksheet 11.1 Boolean operators Worksheet 11.2 Lookup data

155 155 156 157 158 163 166 168

PROBLEM SET 12: It’s excursion time! (Sec 4) Learning Outcomes Overview Shared resources Form design Mail merge Spreadsheet table as data source Conditional mail merge Worksheet 12.1 Class website Worksheet 12.2 Excursion form Worksheet 12.3 Mail merge

171 171 172 172 174 174 175 177 179 181

REVISION EXERCISE I: Movie sales

182

REVISION EXERCISE J: Swimming club

185

REVISION EXERCISE K: Dinosaur week

188

REVISION EXERCISE L: Data usage

191

REVISION EXERCISE M: Library

194

REVISION EXERCISE N: Supplies

197

REVISION EXERCISE O: Class allocation

200

SAMPLE PAPERS PAPER 1

203

PAPER 2

215

PAPER 3

220

Problem Set 1

PROBLEM SET 1: LOVE YOUR COMPUTER! Learning Outcomes Students should be able to (a) Understand the effect of computer virus [1.5.1] (b) List measures to prevent computer virus [1.5.2] (c) List examples of computer crimes [1.5.3] (d) List measures to prevent computer crimes [1.5.4] (e) Show awareness of data privacy [1.5.5] Overview Some information is private to us. For example, your identification number identifies who you are and is unique to you. It represents you! Hence, you must use your identification number with care and keep your identity card safe. It is also important to remember not to give away personal information freely to anyone. This problem set is about computer viruses, computer crimes and data privacy. You will learn what computer viruses and malicious software may do to your computer and data. You will understand more about computer crimes and their harmful effects. There are three worksheets in this problem set to reinforce what you will learn. You may have to search the internet for the required information.

Problem Set 1

Malicious programs Viruses, worms and Trojan horses are types of malicious programs. They are sent by people to corrupt data, control computer operations or cause the computer to hang.

These programs enter the computer through infected storage media, email, and files downloaded from unauthorised websites.

An example of malicious email An infected computer may run slower than normal, fail to run (stops responding), crash or cause error messages to appear on the screen.

Problem Set 1

Spyware is a type of software installed on a computer to collect important information about the user without his knowledge. Important information can be bank personal identification numbers (PINs) or credit card numbers. Spyware may also cause a computer to slow down or crash. It may install programs or undesirable components to web browsers or change the configuration of a computer.

Precautions against malicious programs 1. Install anti-virus software and anti-spyware to protect your computer. 2. Update your anti-virus software and anti-spyware regularly. 3. Backup your data regularly to avoid unnecessary loss of data. 4. Download files from trusted source. 5. Use firewalls. Firewalls may be hardware or software. They are used to block unauthorised access to your computer. A router may be used as a hardware firewall to protect a network of computers. Software firewall can be installed on individual computers and several types of software are available to block undesirable content.

Computer crimes

CONF DATA IDENTIAL

IAL ENT NFID CO A DAT

Examples of computer crimes are:  Spam  Unauthorised access  Intellectual property theft  Software piracy  Identity theft (phishing and pharming)  Spreading computer viruses, worms or trojans  Denial of service (DOS) attack  Online child pornography

C DA ONF TA ID EN TIA L

Computer crimes are illegal acts performed using computers. These illegal acts are against the laws in the country.

Spam refers to the sending of unsolicited messages through illegal means of gathering the victims’ email addresses. Do not open email from unrecognised sources.

Problem Set 1

Computer hackers steal confidential information like bank account numbers and passwords from your computer. They do this by hacking into computer systems. Check before typing personal information into unfamiliar websites. Do not allow web browsers to remember your passwords. Phishing and pharming is the act of stealing important information from Internet users through fake websites. Visit secure websites. Most secure websites have the prefix “https” instead of “http”; and a padlock icon in the security status bar of the web browser. Denial-of-service (DOS) attack is an attempt to disable the availability of service website so that users cannot access the website. You forgot to lock your computer!

Protecting personal data Username and password are the primary means to protect accounts on the Internet. Do not share your passwords with others. To further protect the account from being ‘hacked’, one needs to:  Change password frequently;  Avoid using ‘simple’ passwords – use a password that contains numerals, uppercase letters and lowercase letters. What password must I use?

Problem Set 1

It is important not to reveal personal information on unsecured websites and social media websites. Others may use the information for illegal purposes. Users need to be careful when giving personal information like identification numbers, telephone numbers and home addresses, as well as bank account numbers, credit card information and passwords over the internet.

Can you see the face of the person you are communicating with over the internet?

Problem Set 1 â&#x20AC;&#x201C; Worksheet 1.1

WORKSHEET 1.1: Virus Virus! In this exercise, you will search for answers from the Internet to the following questions. No.

Question

What is a computer virus?

What is spyware?

How does firewall work?

How will a computer virus affect your computer?

What are the ways to prevent a computer virus?

Answer from Internet (provide the source/ use your own words)

Teacherâ&#x20AC;&#x2122;s answer

Problem Set 1 – Worksheet 1.1

Read the following and write your opinion in the space provided. Case study 1: One day, Andy received an email from his friend, Benny. The email had no content and had an attachment named “funny”. Andy decided to open the attachment. Do you agree? What will you do if you are Andy?

Problem Set 1 â&#x20AC;&#x201C; Worksheet 1.1

Case study 2: John finds that his computer is running slower recently. He suspects that something is wrong with his computer. Upon opening his web browser, he realises that there are additional components in his browser. What could have happened and what can he do about it?

Problem Set 1 – Worksheet 1.2

WORKSHEET 1.2: Crime watch In this exercise, you will reinforce your understanding about the different computer crimes and ways to prevent these crimes. Part A: Answer the following questions. 1. Computer crime may involve a computer and a ____________________. 2. Which of the following is not a computer crime? A Pharming B Spyware C Firewall D Computer fraud

Answer: ___________________

3. Malicious software is an example of computer crime. Give two examples of malicious software. i.

__________________________________________________________________

ii.

__________________________________________________________________

4. List three measures to prevent computer crimes. i.

__________________________________________________________________

ii.

__________________________________________________________________

iii.

__________________________________________________________________

Part B: Create a newsletter You are tasked to design a one-page newsletter to inform your school mates about computer crimes. In your newsletter, you are to provide the following: 

What computer crimes are.



Examples of computer crimes.



Measures to prevent computer crimes.



Relevant graphics (preferred self-drawn)

You may add borders, shapes and colours to your newsletter to make it more appealing.

Problem Set 1 â&#x20AC;&#x201C; Worksheet 1.3

WORKSHEET 1.3: Protect yourself Your teacher has explained why it is necessary to protect your personal data on the Internet. In this exercise, you will design a poster to share with your school mates the importance of personal data protection. You will: -

create a drawing to use in your poster; and

give a tagline for your poster. You can use one of the examples below: Never reveal your password to others STOP! Before you are stalked! Protect yourself online Donâ&#x20AC;&#x2122;t leak your identity away

Do seek approval from your teacher for you to do some research online.

Problem Set 2

PROBLEM SET 2: SAVE WATER Learning Outcomes Students should be able to (a) Understand and use a conditional loop [6.3.9] (b) Understand and use random number generator [6.4.3] (c) Understand use of a variable [6.4.6] (d) Change the value of a variable [6.4.7] (e) Output the value of a variable [6.4.8] Overview Singapore faces a water shortage problem. The water we are using comes from our reservoirs and overseas. You will discuss the importance of saving water and keeping our reservoirs pollution free; and then develop games about water conservation in this problem set. The first game is about keeping water pollution free (hit the worm_solution.sb) while the second game is about saving water (droplet.sb). You will learn about the game design and documentation process, and will be introduced to the concept of random number generation. You will apply what you have learnt when you work on the first worksheet. In the second worksheet, you will learn about variables and use the variables to create the game on your own.

Problem Set 2

Game design Score

Game design is a process to plan the structure and nature of the game, and the way the game would work. In designing a game, you may want to consider the following:  What are the characters in the game?  What do the characters need to do?  How do you move the characters?  What background should you use?  What kind of instructions do you need to give to the user?  How do you let the user know?

Boat

Crocodile

Background

User guide Some games can be very complicated. Therefore, user guides are usually provided to help people understand the game. Specifically, the guide will explain:   

What the game is about; What the game objectives are; and How to play the game

An example:

Game Documentation Name of Game: The River Game Brief Description: The River Game is played by moving a boat across the river. Each time the boat reaches the bottom of the screen, the score increases by 1.There is a crocodile swimming in the river. If the boat hits the crocodile, the score will decrease by 1. The game ends when the score reaches 10. Characters in the Game: Boat, Crocodile How to Play: Use the “Up” arrow key to move up; “Down” arrow key to move down; “Left” arrow key to move left; “Right” arrow key to move right

Problem Set 2

Elements of a computer game

Graphics  

Refer to images and graphics effects Draw players to the game

 

Gameplay   

Interface

Sound

How fun the game is The length of playability How engaging the game is

Refer to music and sound effects Make the game more interesting

 

Refer to anything that is used to play the game like mouse, keyboard or console Determine how the game is played

Story    

What the game is about Characters that players can identify with A realistic and achievable goal Different skill levels and variety

Mouse click Mouse click is normally used to start an action or select something in the game. Example 1 Clicking on the worm will indicate that the worm is hit and the score can be changed. This is the “hit the worm” game. The worm will pop up at a different hole each time. When the worm pops up, the player will need to click on the worm.

Problem Set 2

Example 2 Clicking on the ball will bring the ball to a random position with x co-ordinate between 230 and 230; and y co-ordinate between -170 and 170.

The ball will move randomly to any one of the coloured rectangles (red, blue, yellow or green). When the player clicks on the ball, it will “say” which coloured rectangle it is in.

Random number generator We use the built-in random number generator to get different numbers within a specified range. For example, “pick random 1 to 9” will generate a number between 1 and 9. Suppose we want to get a random (x, y) position within a specified area. We can “pick random -230 to 230” for the x co-ordinate and “pick random -170 to 170” for the y coordinate in this instruction:

The random number generator will then pick a position within this shaded space. 170

-230

230

-170

Problem Set 2

If we only want to randomise the y co-ordinate, we can use this instruction where y will be a value between -137 and 150.

Variables A variable is a memory location set aside to store data types such as numbers, text or dates. This location will contain something when it is first used. Hence, it is important to clear the location and fill it with the value that you want. The process of filling with a start value is called initialisation. A common variable that we will use frequently is the score of a game. The score is a variable because it keeps changing (varying) when the game is being played. We use the “set” instruction to initialise a variable. For example, we can set a variable “lives” to a start value of 5 by using this instruction:

Problem Set 2 – Worksheet 2.1

WORKSHEET 2.1: Pollution free STATE the problem Some games can be difficult to understand or play. Therefore, game documentation is provided to inform players how to play the game. Usually, the documentation includes the following:     

Background of the game Objective of the game Characters in the game How the game is played The game algorithm

Your teacher will run and explain how to play the game “hit the worm_solution.sb”. After which you will prepare the documentation for this game. The game is provided for you to play as well.

Problem Set 2 – Worksheet 2.1

THINK about what is needed Play the game “hit the worm_solution.sb” and answer the following questions: 1. Name the characters in the game. ________________________________________________________________________ ________________________________________________________________________ 2. What must you do to hit the worm? ________________________________________________________________________ ________________________________________________________________________ 3. What is the scoring system? ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 4. How do you know that the game is over? ________________________________________________________________________ ________________________________________________________________________ 5. What is the objective of this game? ________________________________________________________________________ ________________________________________________________________________

Problem Set 2 â&#x20AC;&#x201C; Worksheet 2.1

6. Your teacher will explain the flowchart and code for the game.

Start

Initialisation

Score = 10

ENGAGE in your work 7. Complete the game documentation using the given template. 8. Save your work.

Problem Set 2 – Worksheet 2.1

EVALUATE your work 9. Check () your game documentation to ensure that you have  Name of the game   Objective of the game   Characters in the game   How to play   Flowchart for the game  10. Suggest one way you can improve your game documentation. ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ REFINE your work 11. Improve your work as you have suggested. Save your work.

Problem Set 2 â&#x20AC;&#x201C; Worksheet 2.2

WORKSHEET 2.2: Every drop counts STATE the problem This game is on saving water. The aim is to click on each water droplet before it touches the ground. The ground is at the bottom of the screen. The water droplet will drop from anywhere at the top of the screen. A point is scored for every droplet saved. A point is deducted for every drop lost. The game starts when the space key is pressed and ends when 20 drops of water are saved.

Droplet

Ground

1. Summarise in two sentences what the game is about. ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

Problem Set 2 – Worksheet 2.2

THINK about what is needed 2. How many sprites do you need? _____________ 3. Name the sprite(s): ________________________________________________________________________ 4. What do you have as the background? ________________________________________________________________________ ________________________________________________________________________ 5. The flowchart shows the game algorithm. Complete the game documentation using the “droplet game document.doc” template. Start

Initialisation

When space key is pressed Initial state of water droplet Score = 0

Droplet moves down from top

Droplet touches ground?

Decrease score

Droplet clicked on?

Increase score

Score = 20

Display “Game Over”

End

Problem Set 2 – Worksheet 2.2

6. We use the flowchart to plan the code for the program. 7. We can break down the code for the background into 2 parts: (a) when the program starts (that is, green flag is clicked) and (b) when the game is played (that is, space key is pressed). There are two background screens: “title” and “ground”. 8. We also break down the code for the droplet into 2 parts. (a) The code for the droplet when it is clicked is provided for you. (b) When the space key is pressed, the code for the droplet should show how you  track the number of droplets saved  make the droplet move  check whether the droplet has touched the ground ENGAGE in coding 9. Open the file “droplet.sb”. 10. In the “title” screen, replace A.Student with your name. 11. Save the file as droplet_<your name>. 12. Create the code according to your plan. 13. Save your work.

EVALUATE your code 14. Run and test your program. 15. Check () that you have the program code to  Position the water droplet at the top of the screen  Move the water droplet downward toward the ground  Change score when the water droplet touches the ground  Change score when the water droplet is clicked

   

16. Write down one thing that did not work at first and what you did to make it work. What did not work at first: __________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

Problem Set 2 – Worksheet 2.2

What I did to make it work: _________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ REFINE your code 17. Suggest and write down one possible improvement to this game. One improvement: ________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 18. Modify your program such that when the water droplet touches the ground, it changes its look. Use the image “splash.bmp” as the new look for the water droplet when it touches the ground. Save your work.

CHALLENGING EXERCISE

Modify your program such that when the water droplet touches the ground, a “life” is lost. Initialise “life” to three. The game is over when there is zero “life”.

Problem Set 3

PROBLEM SET 3: QUIZZES Learning Outcomes Students should be able to (a) Understand and use mathematical operators [6.4.1] (b) Understand and use relational operators [6.4.2] (c) Read a response from the mouse [6.4.5] (d) Describe what the game is about, in short paragraphs [6.5.1] (e) Determine the characters or objects for the game [6.5.2] (f) Create the scripts for the game [6.5.3] (g) Explain how the game is played [6.5.4] Overview This problem set is about quizzes. You will see how learning can be fun if done through quizzes, which is a form of game playing. The problem set consists of 2 worksheets. Your teacher will explain the flowcharts for both worksheets so that you can create these flowcharts in your game document. The first worksheet is about a game to use arithmetic operators for calculations involving single-digit numbers. The second worksheet is a game that uses text for a national education quiz. Both games allow the players to type their answers via the keyboard. You will also learn the different steps that are necessary when designing and creating a game. It is important to remember this sequence for game making and to constantly apply the steps whenever you create new games.

Problem Set 3

User input Some games allow interaction with the user. This interaction may be in the form of mouse click, key press or user input. In the case of user input, the user is allowed to use the keyboard to provide information to the game. We can use the instruction to get input from the user. Two types of user input are shown.

Man crossing

Enter answer here Text input: In this game, the user will enter A, B, C or D. The answer may be a lowercase or uppercase letter or both, depending on the program.

Numeric input: In this game, the user will enter a numeric answer, which is the sum of yellow (7) and green (8).

Relational operators Relational operators are used to make comparisons. These comparisons are less than (<), less than or equal to (<=), equal to (=), greater than (>) and greater than or equal to (>=). Example 1

Timing <= 12?

Y Pass This checks whether a “pass” or “fail” is obtained.

Fail

Problem Set 3

Example 2 Marks >= 75?

This checks whether a distinction is achieved or not. Example 3 The movement continues till the score is more than 10.

Y Distinction

Score > 10?

No distinction

N Move

Arithmetic operations Mathematical calculations can be made using arithmetic operations. For example, if First_no is 16, Second_no is 12 and Third_no is 3.2, then the result of the various arithmetic operations are as shown in the table below: Arithmetic operations

Operator

Variable

Result

8.8

51.2

3.75

round

square root

Problem Set 3

Sequence in game making Game making normally follows a basic sequence with 6 steps: 1. Generating an idea:  Choose a goal and a topic

2. Designing the game:  Outline the structure of game  Identify milestones  Ensure design aligned to goal

4. Programming the game:  Create the code for the game  Test and debug the code  Prepare game documentation

5. Evaluating the game:  Allow people to play and critique the game

3. Preparing the game:  Create the media elements like graphics and sound

6. Refining the game:  Improve if necessary

Problem Set 3 – Worksheet 3.1

WORKSHEET 3.1: Simple calculation STATE the problem This game tests mental calculation on simple addition and subtraction. When the space key is pressed, the program randomly selects the addition or subtraction operation. Two numbers between 1 and 9 are randomised. The program will check the answer entered. If the answer is correct, the score is increased by 1. If the answer is wrong, the score is decreased by 1. There is no limit to the number of questions to be answered.

Score

2nd number

1 number

Enter answer here

THINK about what is needed 1. Open the file “calculation game document.doc” and create the flowchart with your teacher. Complete the rest of the document and save it. 2. Refer to your flowchart and plan the code for the game. 3. Plan the code for the two numbers such that (a) when the game is played, two numbers between 1 and 9 are generated; (b) the user is asked to enter the answer; and (c) when the answer is checked, the two numbers disappear. 4. Plan the code for the background such that (a) the background will switch among these screens: start, plusbackground, minusbackground, correct or wrong; depending on the situation; and (b) if the answer is correct, the score is increased by 1 and if the answer is incorrect, the score is decreased by 1.

ENGAGE in your work 5. Open the file “plusminus.sb”. 6. Modify the codes in “First_no” and “Second_no” according to your plan. 7. Save the file as plusminus_<your name>. 28

Problem Set 3 – Worksheet 3.1

8. Create the code for the background according to your plan. 9. Save your work. EVALUATE your work 10. Run and test your program. 11. Check () that you have the program codes to  Change to appropriate backgrounds  Check the answers correctly  Randomise the two numbers  Allow user to enter answer

   

12. Write down one thing that did not work at first and what you did to make it work.

What did not work at first: __________________________________________________ ________________________________________________________________________ What I did to make it work: _________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

REFINE your work 13. Modify your program such that a clapping sound is heard every time the answer is correct. Save your work.

CHALLENGING EXERCISE

Modify your program such that the game (a) ends when the score reaches 10; or (b) presents questions on multiplication. 29

Problem Set 3 – Worksheet 3.1

WORKSHEET 3.2: National education STATE the problem This game allows the user to answer questions on national education. The speaker starts by asking the user to click on the whiteboard to start the quiz. Questions are posed on the whiteboard for the user to answer. If the answer is correct, the score is increased by 1. If the answer is incorrect, the score is decreased by 1. At the end of the quiz, the speaker announces the final score.

Question Speaker

Enter answer here

THINK about what is needed 1. Open the file “NE game document.doc” and create the flowchart with your teacher. Complete the rest of the document and save your work. 2. Refer to your flowchart and plan the code for the game. 3. Plan the code for the speaker such that (a) when the game starts, the user clicks on the whiteboard to start; (b) when the user enters the answer, it announces the result and updates the score; and (c) when the game ends, it outputs the final score. 4. Plan the code for the background such that (a) it will first show a whiteboard; (b) then it will show each question for the user to answer; and (c) it will show the whiteboard again at the end of the quiz.

ENGAGE in coding 5. Open a new file. 6. Create the code for the speaker according to the plan above. 30

Problem Set 3 – Worksheet 3.1

7. Save the file as NEQuiz_<your name>. 8. Create the code for the background according to the plan above. 9. Save your work.

EVALUATE your code 10. Run and test your program. 11. Check () that you have the program code to  Change to appropriate backgrounds  Check the answers correctly  Respond to the answers correctly  Output the score correctly

   

12. Write down one thing that did not work at first and what you did to make it work.

REFINE your code Modify your program by adding another question to the quiz. You are required to create the question yourself. Save your work.

Problem Set 4

PROBLEM SET 4: WELL BEING Learning Outcomes Students should be able to (a) Read a key press [6.4.4] (b) Draw flowchart for the game algorithm [6.5.5]

Overview This problem set is about staying healthy and safe. To trigger off learning, you can share examples and stories of the significance of good health and keeping safe. You will realise the importance and benefits of staying safe and healthy. There are two worksheets for you to create games on well being. You will learn how keyboard keys can be used to move objects; start or stop a game; and perform other actions. At the same time, you will practise using instructions that you have learnt in previous lessons. The game flowchart and document are important pieces of work that have to be done for every game. In this problem set, you will practise doing this on your own.

Problem Set 4

Key press Key press is often used to start/stop a game or move an object. Commonly used keys are the arrow keys (up, down, left, right), space key, and enter key. The arrow keys are used to move an object in the directions specified. The space key can be used to start or stop a game. The enter key is used during user input.

Example 1 In the game about preventing dengue, the up and down arrow keys are used to move the bat up and down. The space key is used to swing the bat.

Bat

Example 2 In the game about eating wisely, the left and right arrow keys are used to move the bowl toward the left or right so as to catch the falling food items.

Move bowl left or right

The space key is used to stop the game.

Boundary conditions Sometimes, it is necessary to check whether the boundary of an object is reached so that a different action can be performed. Colour detection is one way to check whether these boundaries are reached. Colour detection is also used to detect contact with another object. 33

Problem Set 4

If the object is the screen or background for the game, then the boundaries could be the four sides of the screen. Depending on your game design, you may want different actions to be performed at or near the sides of the screen. (-240, 180)

(240, 180)

(-240, -180)

(240, -180)

A commonly used condition to check whether the side of the screen is reached is:

Colour detection Colour detection is used in the “prevent dengue” game to detect whether the mosquito is touching the net of the bat and not the handle of the bat.

Net of bat

Handle of bat

The mosquito will be “killed” if it touches the grey net of the bat. The condition for the colour detection is:

Problem Set 4 – Worksheet 4.1

WORKSHEET 4.1: Prevent dengue STATE the problem This game is about killing Aedes mosquitoes to prevent the spread of dengue fever. The aim is to swing the bat to kill the mosquitoes. The mosquito starts randomly from the left side of the screen and moves towards the right. If the bat hits the mosquito, the score increases by 1. If the mosquito is not hit, the number of lives which begins at 3 will decrease by 1. The game ends when 5 mosquitoes are killed or the number of lives equals 0.

Score Lives left

Bat

Background Mosquito

THINK about what is needed 1. Open the file “mosquito game document.doc” and create the flowchart with your teacher. Complete the rest of the document and save it. 2. Refer to the flowchart to program the game. 3. Plan the code for the background such that it switches to different scenes depending on the different situations. The three scenes are start of the game, end of the game when 5 mosquitoes are killed, and end of the game when number of lives is zero. 4. Plan the code for the bat such that it can only move up and down within the screen; and it will swing back and forth when the space key is pressed (to simulate hitting the mosquito). 5. Plan the code for the mosquito such that it (a) appears randomly at the left side of the screen; (b) moves from left to right; and (c) tracks the number of mosquitoes and number of lives.

Problem Set 4 – Worksheet 4.1

ENGAGE in coding 6. Open a new file and use all the images provided. 7. Create the code for the background and the bat as planned. 8. Save the file as mosquito_<your name>. 9. Create the code for the mosquito as planned. 10. Save your work.

EVALUATE your code 11. Run and test your program. 12. Check () that you have the program codes to  Change to appropriate backgrounds  Move the mosquito correctly  Move the bat correctly  Award the score correctly  Show the correct responses when “score” and “lives” are 5 or 0

REFINE your code 13. Modify your program by adding one more mosquito. Save your work.

    

Problem Set 4 – Worksheet 4.2

WORKSHEET 4.2: Cross safely STATE the problem This game is about moving a man across the road without being hit by a car. When the game starts, both the man and the car appear at fixed locations. The car starts moving while the man is moved by key press. The score decreases by 1 every time the car hits the man but increases by 1 every time the man crosses safely. The game ends when the score reaches 5.

Score

Man Moving car

THINK about what is needed 1. Your teacher will run the program for the game. Open the file “road game document.doc” and complete the first page on your own. 2. Complete the flowchart on the second page, using the helping words provided. Save your work. 3. Plan the code for the car such that it will appear at a fixed location on the left and move along the road. 4. Plan the code for the man such that (a) the man appears at a fixed location at the road near the top of the screen; (b) the man can be moved up, down, left and right through key press; (c) the score is increased by 1 when the man crosses the road safely; (d) the score is decreased by 1 when the man is hit by the car. ENGAGE in coding 5. Open a new file and use all the images provided. 6. Create the code for the game as planned. 7. Save the file as road_<your name>. 37

Problem Set 4 – Worksheet 4.2

EVALUATE your code 8. Run and test your program. 9. Check () that you have the program code to  Make the car move  Control the man  React correctly when the man is hit by the car  React correctly when the man crosses the road  Indicate when the game ends

    

REFINE your code 10. Modify your program such that both the man and car will appear randomly by the side of the road and along the road, respectively.

CHALLENGING EXERCISE

Modify your program such that the car increases its speed every time the man reaches the bottom of the screen.

Revision Exercise A

REVISION EXERCISE A: OCEAN CLEANING STATE the problem Create a game to gather as many metal cans as possible. The “can” will appear at any of these locations: coral (40, 43), seaweed (151, -137) and stone (-158, -103). The score will be the number of cans collected. The game starts when the space key is pressed and ends when 15 cans are collected.

THINK about what is needed 1. Open the file “clean ocean.sb”. 2. What do you have as the background? ________________________________________________________________________ ________________________________________________________________________ 3. How to start and end the game? 4. What instructions/actions are needed so that  the “can” will appear randomly at the coral, seaweed or stone; and  each time the “can” is clicked on, the number of cans collected is increased by one.

ENGAGE in coding 5. Create code to start and end the game. 6. Create code for the “can” according to your plan. 7. Include a “title” background similar to the screenshot shown, with A.Student replaced with your name. 8. Save your work.

Revision Exercise A

EVALUATE your code 9. Run and test your program. 10. Check () that you have the program code to  Make the can appear randomly   Change the score   Start the game   End the game 

REFINE your code 11. Suggest and write down one possible improvement to this game. One improvement: ________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

Revision Exercise B

REVISION EXERCISE B: FIVE WORMS STATE the problem This game is played by getting the player to hit as many worms as possible to get a winning score. There are four yellow worms and one red worm in this game. The five worms will appear at their own locations with the red worm in the centre location. The player will score 1 point if a yellow worm is hit and 2 points if the red worm is hit. The game starts when the space key is pressed and ends when the score is more than 10.

Red worm

THINK about what is needed 1. Open the file “five worms.sb”. 2. How many worms are there? _____________ 3. What do you have as the background? ________________________________________________________________________ ________________________________________________________________________ 4. We can break down the code for the background into 2 parts: (a) when the program starts (that is, green flag is clicked) and (b) when the game is played (that is, space key is pressed). The code for the background is such that  When the program starts, the background will show the “instruction” screen and set the score to zero; 41

Revision Exercise B



When the game is played, the background will do the following sub-parts: o show the “water” screen, o wait until the score is more than 10; o change to the “win” screen; o stop all scripts. 5. We can also break down the code for each worm. Each worm will appear at its own location for a while and then disappear. The player must be fast enough to click on the worm before it disappears. The score will increase when the worm is hit. ENGAGE in coding 6. Create code for the background and worms. 7. Save your work.

EVALUATE your code 8. Run and test your program. 9. Check () that you have the program code to  Make the worm appear and disappear   Change the score   Change the background 

REFINE your code 10. Suggest and write down one possible improvement to this game. One improvement: ________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

Revision Exercise C

REVISION EXERCISE C: IQ QUIZ STATE the problem This game requires the player to note 3 numbers and their coloured sections: yellow, green, blue. When the game starts, a mathematical question will appear that requires the player to either add two numbers or subtract one number from another. The question refers to the coloured sections instead of the numbers that appear on the sections. An example could be blue + green. The final score is displayed when the game ends.

Background

Number

Speaker

THINK about what is needed 1. Open the file “Game Documentation Template.doc” and complete the document with your teacher. 2. Plan the code for the background such that it will switch between the instructions and game background. 3. Plan the code for the speaker such that  it will ask the questions;  it checks the answer and announces the result “Correct” or “Incorrect”; and  it announces the final score at the end of the game.

Revision Exercise C

ENGAGE in coding 4. Open the file “IQQuiz.sb” 5. Create code for the background and speaker as planned. 6. Save your work.

EVALUATE your code 7. Run and test your program. 8. Check () that you have the program code to  Change to appropriate backgrounds  Allow user to enter answer  Change the background  Check the answers

   

REFINE your code 9. Modify your program to include a multiplication question.

Revision Exercise D

REVISION EXERCISE D: EAT WISELY STATE the problem The aim of the game is to catch the carrot with the bowl. Each carrot caught will score 5 points. If the other two food items (egg and prawn) are caught, 2 points will be deducted. The game starts with the bowl at a fixed location near the bottom of the screen; and the three food items at random locations along the top of the screen (that is, x-coordinate is randomised while y-coordinate is fixed). The three food items will start falling toward the bottom of the screen. The game ends when the space key is pressed.

Egg Prawn Carrot Bowl appears here

THINK about what is needed 2. How many sprites do you need? _____________ 45

Revision Exercise D

3. Name the sprites: ________________________________________________________________________ _______________________________________________________________________ 4. What do you have as the background? ________________________________________________________________________ 5. The flowchart shows the game algorithm. When green flag is clicked Start

Initialisation

initial positions of bowl and food items Score = 0

Food items move down from top

Move the bowl toward the left or right

Food touches ground?

Go to position at the top

Increase score

Decrease score

Carrot caught in the bowl?

Egg and prawn caught? N

Space key is pressed? N

End

Revision Exercise D

6. We can break down the code for the bowl into 2 parts: (a) when the program starts (that is, green flag is clicked) and (b) when the bowl is being moved left and right. Plan the code for the bowl so that the bowl positions itself at the bottom left-hand corner of the screen and will move left or right with the corresponding key press action. 7. Think of how you can move the three food items downwards. The food items will start near the top of the screen. If the food item is caught by the bowl, the score is adjusted according to the table shown below: Food item Score Carrot

+5 points

Egg

- 2 points

Prawn

- 2 points

ENGAGE in coding 8. Open the file “food.sb”. 9. Save the file as food_<your name>. 10. Create the codes for the bowl and food items according to your plan. 11. Save your work.

EVALUATE your code 12. Run and test your program. 13. Check () that you have the program code to  Position the food items near the top of the screen  Move them downward toward the ground  Position the bowl at the bottom left-hand corner of the screen  Change score when the carrot touches the ground  Change score when the carrot/egg/prawn is collected in the bowl

    

Revision Exercise D

14. Write down one thing that did not work at first and what you did to make it work. What did not work at first: __________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

REFINE your code 15. Suggest and write down one possible improvement to this game. One improvement: ________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

16. Modify your program such that (a) the three food items will appear randomly within the top half of the screen. That is, both the x and y positions are randomised. (b) when the carrot touches the ground, 2 points are deducted.

Coursework

COURSEWORK

Title of your game: __________________________________________________________________

STATE the problem 1. What is your game about? ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________ ___________________________________________________________________________

2. What are the characters (sprites)? ___________________________________________________________________________ ___________________________________________________________________________

3. What are the backgrounds? ___________________________________________________________________________ ___________________________________________________________________________ 49

Coursework

THINK about what is needed 4. Draw the flowchart for the game.

Coursework

ENGAGE in coding 5. Use your flowchart to plan the code for your game. 6. Create the code as planned. 7. Save your work.

EVALUATE your code 8. Run your program to test it. 9. Check () that you have the program code to  Start and end the game   Track the score for the game   Provide instructions   Make the characters move 

REFINE your code 10. Improve your game to make it better.

Game documentation 11. Complete the documentation for your game. 12. Save your work.

Problem Set 5

PROBLEM SET 5: HEALTHY SPORTS FOR EVERYONE Learning Outcomes Students should be able to (a) Work together on shared resources on the internet [1.4.4] (b) Create customised form for data collection over the internet [1.4.5] (c) Show awareness of data privacy [1.5.5] (d) Format (either through shading the cell or changing the font colour of contents) by setting criteria for the data value or formula in the cell [4.1.5] (e) Create bar and line charts for data not in adjacent columns [4.1.9] (f) Calculate the total number of data values [4.2.10] (g) Sorting a data table in ascending order of a particular data item [4.3.1] (h) Sorting a data table in descending order of a particular data item [4.3.2] (i) Understand how changes in one or more data items can affect other related data items [4.3.3]

Overview It is your schoolâ&#x20AC;&#x2122;s Sports Day. On Sports Day, students get a chance to showcase their talents and skills through different events. You will discuss the various sports events and the safety precautions to take during these events. In the first worksheet, you will learn about sharing documents online and using functions for counting. You will do conditional formatting and use skills such as sorting (numerically and alphabetically). You will also create a bar chart for data that are not in adjacent columns. In the second worksheet, you will use drawing software to draw a graphic on sports safety and use it to create an invitation form. In the third worksheet, you will create presentation slides on sports safety using hyperlinks and action buttons.

Problem Set 5

Counting A quick (instant) and efficient way to count the number of data in a selected field (either row or column) is to use built-in functions. Data type may be numeric, text or date. The table below shows the difference between COUNT, COUNTA and COUNTIF functions.

Definition

Situation

COUNT

COUNTA

COUNTIF

In a specified range, count the number of cells that contain numbers.

In a specified range, count the number of cells that are not empty.

In a specified range, count the number of cells that meet a given condition.

This function is used with cells that contain numeric data type. Cells with other data type (e.g. text) will not be counted.

This function is used when the user wants to count all the cells that contain numeric or text data. It will not count empty cells.

The function is used when there is a specific data that the user wants to count. It will not count cells that do not have the specified data (condition).

There are other COUNT functions (for other purposes) found in the spreadsheet application. You may want to explore them further on your own. The example below shows how the 3 COUNT functions (COUNT, COUNTA and COUNTIF) give different outputs. In cell A9, the value 5 means that there are only 5 cells in the range of cells A1:A7 that contain numeric data. In cell C9, the value 7 means that there are 7 cells in the range of cells C1:C7 that contain data (numeric or text). In cell E9, the value 2 means that there are 2 cells in the range of cells E1:E7 that contain the number 3. =COUNTA(C1:C7)

=COUNT(A1:A7)

=COUNTIF(E1:E7, 3) Range of cells Range of cells Specified data

Problem Set 5

Conditional formatting Conditional formatting is a process to format data in a column based on one or more criteria (condition) for the column. The cells in the column may be shaded or the data in the column may be in coloured font. Conditional formatting is useful when we want to pay attention to specific cells in a spreadsheet. Example 1 The data in column C is formatted based on gender (Male or Female). The format is Blue background for condition “Male” and Light red background and dark red text for condition “Female”.

Before conditional formatting

After conditional formatting

Example 2 The data in column I is formatted to bring special attention to students who have signed up for less than two events. The conditional formatting helped the teacher to find out, quickly, the names of these students. The format used is Light red background and dark red text for the condition “less than 2”.

Problem Set 5

Sort in ascending order For text data, sorting in ascending order means arranging the text from a â&#x20AC;&#x201C; z. For numeric data, sorting in ascending order means the smallest number is at the top and the largest number is at the bottom. All related data must be selected to avoid messing up the data table. Text data

Numeric data

Ascending order of unit price

Ascending order of names of food

In the examples shown, the data values in A4:D7 must be selected first before sorting.

Sort in descending order For text data, sorting in descending order means arranging the text from z â&#x20AC;&#x201C; a. For numeric data, sorting in descending order means the largest number is at the top and the smallest number is at the bottom. All related data must be selected to avoid messing up the data table. Text data

Numeric data

Descending order of names of food

Descending order of unit price

In the examples shown above, the data values in A4:D7 must be selected first before sorting.

Problem Set 5

More about pie chart The pie chart is plotted for two columns of data that are not next to each other. The numeric data are the data values of the pie chart. There is only one set of numeric data for a pie chart.

This pie chart is shown in an exploded 3-dimensional (3-D) format. Each slice of the pie represents a value in a cell.

More about bar chart A bar chart can be plotted for any number of columns of data values. The example shows a bar chart, in 3-D format, plotted for two columns of data that are not next to each other.

Problem Set 5

One set of bars is for one column of data values in the table. The bars are in different colours for different columns of data values. Each bar represents a value in a cell. More about line chart The line chart can be plotted for any number of data sets. In the example below, the lines in the chart are plotted for data values in two columns of data that are not next to each other. Each column of data values represents a data set. Each data set is plotted as a line in a different colour.

The chart shows two lines in different colours. Each point on the line corresponds to a value in a cell.

Problem Set 5 – Worksheet 5.1

WORKSHEET 5.1: Participation list 1. The annual Sports Day is coming. You and your classmates are required to sign up for at least two events in the spreadsheet put up by your teacher. Follow the instructions below. (a) In the spreadsheet, look for your name and enter “1” if you wish to participate in the event. (b) After all your classmates have made their selection, your teacher will tell you to download and save the file as Participation_List_<Your Name>. 2. Open the spreadsheet file that was downloaded: Participation_List_<Your Name>. (a) Insert your name and index number in the header. (b) Insert a new row before Row 1. (c) Merge and centre D1:H1. Type “Track & Field” in bold. (d) Use a function in cell I3 to find the number of events participated by the first participant. Complete the column for the rest of your classmates. (e) Format the cells in column I to yellow fill with blue text if the data is less than 2. (f) Use a function in the new row at the bottom of the events to find the number of participants for each event. (g) Format the cells in this new row to red fill with black text if the data is more than 15. (h) Save and print a copy of your spreadsheet. (i) Sort the data using the following criteria: I.

No. of events (largest to smallest), followed by

II.

Names in alphabetical order.

(j) Print a copy of your spreadsheet. (k) Create a bar chart showing the number of participants in the different events. Your chart must show title, axis labels, data values and legends. (l) Print a copy of your bar chart.

Problem Set 5 â&#x20AC;&#x201C; Worksheet 5.1

Answer the following questions. 3. What is the purpose of the following functions? (a) COUNT: ________________________________________________________________ _________________________________________________________________ (b) COUNTA: _______________________________________________________________ _________________________________________________________________ (c) COUNTIF: _______________________________________________________________ _________________________________________________________________

4. Why is there a need to sort data? Give two reasons. (a) _________________________________________________________________ _________________________________________________________________ (b) _________________________________________________________________ _________________________________________________________________

Problem Set 5 â&#x20AC;&#x201C; Worksheet 5.2

WORKSHEET 5.2: Invitation form You are to design an invitation form on sports safety for your class. In the form, you are required to use one sports safety message and a self-designed graphic. 1. Refer to the Singapore Sports Council website given by your teacher. Website:_________________________________________________________________ 2. Write down your choice of sports safety message. This will be the message in the invitation form. ________________________________________________________________________ ________________________________________________________________________ 3. Use the space below to plan your drawing.

Problem Set 5 â&#x20AC;&#x201C; Worksheet 5.2

4. Show your plan to your teacher before using drawing software to design a graphic. 5. Save your drawing as a bitmap graphic. 6. List three tools that you used for your drawing. (a) _______________________________ (b) _______________________________ (c) _______________________________ 7. Design an invitation form for your school mates. A sample is shown below.

Put your graphic and sports safety message here

Dear friends of XXX Secondary School, My class, Secondary 3XX is running a workshop on sports safety on 31 May 20XX and we are inviting you to attend this meaningful event. We will be sharing good practices on sports safety and you will definitely gain much from this workshop. Below are the details: Workshop: Date: Time: Venue:

Sports Safety 31 May 20XX 7:00 p.m.to 8:30 p.m. Computer Lab 1

---------------------------------------------------------------------------------------------------------------------------Reply I will/ will not* be attending the workshop organised by your class. Name: __________________________________________________________________ Class:

_______________________

Date:

_______________________

Contact number: _______________________

*Please delete accordingly.

8. Save your invitation form as safety_<Your Name>.

Problem Set 5 â&#x20AC;&#x201C; Worksheet 5.3

WORKSHEET 5.3: Sports safety 1. You are to prepare a set of 6 presentation slides for the workshop about sports safety. Slide 1: Graphic drawn in worksheet 5.2

Title Bulleted sub-titles

Each sub-title to link to relevant slide

Your name, class

Slides 2 â&#x20AC;&#x201C; 5: Graphic drawn in worksheet 5.2

Title Bulleted list

Link to home page

Slide 6: Graphic drawn in worksheet 5.2

Title Provide link to website

Link to home page

Problem Set 5 â&#x20AC;&#x201C; Worksheet 5.3

The text for the six slides are shown below: Slide 1

Slide 2

Slide 3

Sports Safety

Personal Precautions

Environment

1. 2. 3. 4.

Personal Precautions Environment Getting ready Protection gears

Drink enough fluids to prevent your body from overheating Avoid coffee or alcohol as they will cause dehydration Isotonic or sports drink are recommended for any activity that lasts more than an hour

Make sure the environment is safe and suitable for the sports Check weather forecast before the sports activity

Slide 4

Slide 5

Slide 6

Getting ready

Protection gears

Reference

Do warm-up and stretching exercises before engaging in sports activity Allow enough time for your body to cool down after the exercises

Wear appropriate protective sports gears Use correct sports equipment (e.g. helmets for cyclists and shin guards for soccer players

Singapore Sports Council Website

2. Use a suitable background colour for your slides so that the text can be seen. 3. Save your work as Sports_Safety_<Your Name>. 4. Print 6 slides per page to hand in to your teacher.

Problem Set 6

PROBLEM SET 6: LEARNING ABOUT NUMBERS - THE IT WAY! Learning Outcomes Students should be able to (a) Restrict cursor movement to input cells [4.2.1] (b) Understand how changes in one or more data items can affect other related data items [4.3.3] (c) Generate random numbers below a specific value [4.4.1] (d) Round (up or down) numeric data values [4.4.2] (e) Express numeric data values as whole number [4.4.3] (f) Find the remainder when one number is divided by another [4.4.4] (g) Understand and use conditional statements [4.4.7] (h) Understand and use relational operators to compare data values [4.4.8] (i) Express problem solutions using flowcharts [4.4.12]

Overview We see and use numbers every day. For example, bus service numbers, block numbers, prices of products and test marks. Numbers can be random and occur by chance. For example, when we throw a die, we are not able to predict the number that we will get. In this problem set, you will learn how the computer can be used to generate random numbers for different purposes. You will learn spreadsheet functions to use with numbers that are encountered in different daily situations. In the first worksheet, you will use a random number function to simulate the throwing of a die. As you work on your spreadsheet to perform several calculations, record the calculated answers in your worksheet. In the second worksheet, you will learn about the modulus function (MOD) and how it can be used to find the remainder when one value is divided by another. In the third worksheet, you will use conditional and relational operators, together with rounding functions (e.g. ROUND) to calculate subject marks to the nearest whole number. You will also draw a flowchart for a process to determine whether a “Pass” or “Fail” grade is to be awarded.

Problem Set 6

Random number A random number is a number that is unpredictable and occurs by chance. For example, if we were to put numbers 1 – 40 into a box and draw one number from the box, it will be difficult to predict what the number may be. A computer can be used to generate random numbers through application software. Banks use computers to generate sets of random numbers as personal identification numbers (PIN) to their clients’ credit cards or bank cards. As each number is randomly generated, it will not be easy for others to guess what the PIN is. Banks will also issue hardware locks or tokens to their customers to provide additional security for electronic banking over the internet. For secure transactions and work over the internet, many companies also issue their staff with security tokens to log into the company’s secured website. An example of a security token is shown below. The security token will generate and display a set of random numbers each time it is used. The user will enter the numbers into the computer to connect to a secured website. In this way, internet transactions can be conducted securely.

In the spreadsheet application, we can use built-in functions RAND and RANDBETWEEN to generate random numbers.

Do you know the difference between RAND and RANDBETWEEN?

Problem Set 6

Cursor movement Cursor movement can be restricted to specified cells in a spreadsheet. This is useful because we can control which cells can be changed or formatted by users of the spreadsheet. Another reason to restrict cursor movement is to direct users to the cells that require their attention and not to other cells. The tab key can be used to navigate the worksheet. We can only enter data in these four cells.

The rest of the cells are protected and we will not be able to type anything in them.

I have forgotten to protect my worksheet and all my data are edited by someone!

To further protect your worksheet in spreadsheet, you can also set a password. This will ensure only the creator of the worksheet can edit the data. The danger is you may forget what the password is and will therefore be unable to access your own spreadsheet!

What if A â&#x20AC;&#x153;what ifâ&#x20AC;? analysis is used to explore the results of changing data values due to choice or circumstances.

Problem Set 6

Example Suppose there are 30 people in your class; and your class is preparing for a tea party at the end of the year. Each person pays $2 for the tea party. The type of food planned is shown in the data table. All the $60 will be used up, so the balance should be $0.00.

What if there are changes to the plan?

Nothing is left

(i) What if 12 people want to have curry puffs instead of buns? (ii) What if each curry puff now costs $0.33?

Automatic recalculation Whenever numeric values are changed, the spreadsheet will automatically recalculate and update the values in related cells. This automatic recalculation is useful for â&#x20AC;&#x153;what ifâ&#x20AC;? analysis to find out what will happen when there are changes. All changes need to be saved. Example What happens if 12 people say that they do not like buns and want curry puffs instead? (2) Values in D6:D9 are automatically recalculated

(1) Values in C6 and C7 are changed

(3) Result: $1.80 left.

Problem Set 6

Remainder In the spreadsheet application, the MOD function returns the remainder after a number is divided by another number (called a divisor). For example, if a user wants to find out the remainder when 17 is divided by 4 (divisor), the function to use is MOD(17,4). Alternatively, the user can use cell referencing when using this function; that is, MOD(A1, A2) where A1 contains 17 and A2 contains 4. Cell referencing

The function in this cell is MOD(A1, A2).

Calculated value Spreadsheets are used to make calculation easier for us. This is especially true when it becomes very tedious to manually compute data, even with the help of a calculator. As a general rule, we enter values that we do not know or cannot calculate. For example, the date a book was returned. Values that can be calculated by using functions and formulas should not be entered through the keyboard. We should let the spreadsheet do the job of calculating the values we want. We can subtract one date from the other date to get the number of days the book is overdue.

We will need to enter the book returned date. Do you know why?

Problem Set 6

Rounding numbers There are functions for us to round, round up or round down numbers. We can round, round up or round down numbers so that these numbers are expressed as  

whole numbers, or decimal numbers with a required number of decimal places.

The functions for round, round up and round down are similar, where num_digits stand for the number of decimal places in the final answer and number is number to be changed:   

ROUND(number, num_digits) e.g. ROUND(34.567, 2)  Get 34.57 ROUNDUP(number, num_digits) e.g. ROUNDUP(34.567,1)  Get 34.6 ROUNDDOWN(number, num_digits) e.g. ROUNDDOWN(34.567, 0)  Get 34

Example This example shows the results of rounding up and rounding down the percentage to one decimal place and zero decimal place (whole number).

The function is: =ROUNDDOWN (cell address, 0)

The function is: =ROUNDUP (cell address, 1)

Try this out with 2 decimal places.

Problem Set 6

Conditional statement A conditional statement or function makes logical comparisons of values to check whether the specified condition is true or false. The purpose is to find or highlight data that meets the specified condition. The table below shows an example of how the IF conditional statement is used in spreadsheet to determine a ‘Pass’ or ‘Fail’ grade for each name. Another example is the COUNTIF function. This means “if the value in cell B2 is greater than 49.9, output ‘Pass’, else output ‘Fail’.

Conditional formatting is done for the output. Do you still remember how to format cells conditionally?

Relational operators Relational operators like =, < and > are used to compare values. For example, if we want to compare the daily sales of Shop A and Shop B for a week, we use the relational operator > (greater than) in a conditional statement like this: IF Shop A > Shop B, then display Shop A else display Shop B In a spreadsheet, the conditional statement is entered in the data column where the results are to be displayed.

This is the result column. Type the IF conditional statement here.

Problem Set 6

Problem solution A flowchart can be used to represent a problem solution. Standard symbols are used in a flowchart to ensure information is conveyed in a way that is understood by everyone. The table below shows four symbols that are used frequently. Symbols

Meaning Terminator (Start and End)

Process

Decision

Input / Output

What other symbols are there?

Example of a flowchart This flowchart checks that the input data type is numeric; and prompts the user again to enter a number if it is not. Start

Enter a number

Is it a number?

Yes End

Problem Set 6 – Worksheet 6.1

WORKSHEET 6.1: Dice it up! 1. What do RAND and RANDBETWEEN functions do? (a) RAND: __________________________________________________________________ __________________________________________________________________ (b) RANDBETWEEN: __________________________________________________________________ __________________________________________________________________ 2. Follow the instructions below to create the dice game. (a) Open the file ‘prob_student.xls’. (b) Enter a function in each of the cells C8, H8 and M8 so that it will generate a random number between 1 and 6. (c) Enter a formula in cell D11 to calculate the total score for the 3 dice. (d) Press “F9” on your keyboard to generate a new set of random numbers. (e) Record the total score in cell C15. (f) Repeat step (d) and record the total score in the rest of the cells in column C. (g) In cell G15 find the average score for C15:24. (h) In cell G16 find the maximum score and in cell G17 find the minimum score. (i) Save your work as prob_<Your Name>. (j) What happens to the value in cell G15 if you change the value in cell C15 to another number? __________________________________________________________________ (k) Looking at the pattern of your total score, can you predict the next total score? Why? __________________________________________________________________ __________________________________________________________________ __________________________________________________________________

Problem Set 6 – Worksheet 6.2

WORKSHEET 6.2: Let’s go shopping! 1. List two reasons why it is necessary to protect worksheets in a spreadsheet. (a) __________________________________________________________________ __________________________________________________________________ (b) __________________________________________________________________ __________________________________________________________________

2. You are to design a receipt for a store set up by your school. In this activity, you will learn how to use the MOD function. You will also restrict cursor movement to cells that require input. Follow the instructions below to create the receipt. (a) Start a new spreadsheet and enter the following into your file. Save it as receipt_<your name>.

(b) Change the following for your receipt: I.

<Your Name> with your own name

II.

ABC Secondary School with your school’s name

III.

Your school address in cells B3 and B4

IV.

Your school telephone number in cell B5

Problem Set 6 â&#x20AC;&#x201C; Worksheet 6.2

The value in this cell will indicate how much is saved. For example, if the grand total is $12.28, then this cell will show $0.03.

(e) Enter a formula in cell E8 to find the total cost of the purchased item. (f) Do the same for cells E9:E15. (g) Find the grand total of all the items bought in cell E17. Grand total: ______________________________________________________________

Problem Set 6 â&#x20AC;&#x201C; Worksheet 6.2

Usually, the store will not collect one cent coins from students. For example, if the grand total is $12.28, then the store will collect $12.25. The amount saved is $0.03. If the grand total is $12.24, then the store will collect $12.20. The amount saved is $0.04. (h) Using only the MOD function in cell E18, find the amount saved. (Refer to the explanations above.) Amount saved: ___________________________________________________________

(i) Then, in cell E19, find the actual amount to pay. To pay: __________________________________________________________________ (j) Enter the value 100 in cell E20. (k) Find the change received in cell E21. Change: _________________________________________________________________ (l) Format all relevant cells to currency format, 2 decimal places. (m) In cell C23 find the number of items purchased. Number of items: _________________________________________________________ (n) Given that Goods and Services Tax (GST) is 7% of the grand total, find the GST in cell C24. GST: __________________________________________________________________ (o) Create a border for the cells A1:E24; and set print area to A1:E24. (p) Save your file as receipt_<Your name>. (q) Print the worksheet for submission. (r) Protect your file so that only the data in C8:C15 can be changed. (s) Save your work and submit the softcopy of your work to your teacher.

Problem Set 6 â&#x20AC;&#x201C; Worksheet 6.2

WORKSHEET 6.3: Did I do well? 1. What do ROUND, ROUNDUP and ROUNDDOWN functions do? Use examples to explain your answers. (a) ROUND: __________________________________________________________________ __________________________________________________________________ (b) ROUNDUP: __________________________________________________________________ __________________________________________________________________ (c) ROUNDDOWN: __________________________________________________________________ __________________________________________________________________ 2. For this activity, you will use the IF conditional statement and the ROUND function. At the end of the activity, you will draw a flowchart. Follow the instructions. (a) Open the file grade_student.xls. Save the file as grade_<Your Name>. The screenshot shows the marks obtained by students in three tests.

(b) Select cells A1:H21 and change the font to Batang, size 12. Make sure all the information in each cell can be read. (c) In cell G2 find the average of the three tests. Round up this value to an integer. 76

Problem Set 6 – Worksheet 6.2

(d) Find the average for the rest of the students, rounding up to an integer. (e) The passing mark is 60. Set a condition in cells H2:H21 so that “PASS” will be displayed if the student passes the test and “FAIL” will be displayed if the student fails the test. (f) Format the font in cells H2:H21 to I.

blue, bold for passes;

II.

red, bold for failures.

(g) Enter a formula in cell C23 to calculate the number of passes and in cell C24 to calculate the number of failures. (h) Draw a flowchart for the processes in items (e) and (f) in the space below. Part of the flowchart is done for you. Then, create the flowchart and print a copy for submission. Start

Read Average Marks

Problem Set 7

PROBLEM SET 7: ITâ&#x20AC;&#x2122;S SOCCER TIME! Learning Outcomes Students should be able to (a) Create combination charts [4.1.10] (b) Understand and state the purpose of a combination chart [4.1.11] (c) Embed a chart (with or without data table) from spreadsheet [3.1.8] (d) Import text into text box (frame) [3.3.6] (e) Understand that text can flow from one text box (frame) to another [3.3.7] (f) Control the flow of text from one text box (frame) to another [3.3.8] (g) Add, remove or modify borders of a text box [3.3.9] (h) Import graphics into text box (frame) [3.3.10] (i) Wrap text around a text box(frame) [3.3.11]

Overview Soccer is one of the popular games in the world. It promotes teamwork and fosters good communication among the members. Data is often collected to track the performance of the teams and their members. These data can be represented visually using charts and tables. You will discuss the types of data that can be collected in a soccer competition. In the first worksheet, you will learn how to create a combination chart to compare two sets of soccer data. In the second worksheet, you will use text boxes to lay out the text and graphics for a newsletter about soccer. You will also embed a chart in the newsletter.

Problem Set 7

Combination chart A combination chart is obtained when two types of chart are combined to make one chart. Usually the line and bar charts are combined. A combination chart is useful if comparison between two sets of data is necessary. This example shows a combination chart for two sets of data â&#x20AC;&#x201C; (1) the number of cars sold and (2) the number of car Model A sold, from January to June. It gives us an idea how popular car Model A is.

Combination chart (secondary axis) A combination chart with a secondary axis is shown in the example below. A secondary axis (namely, the y-axis) is useful when we are comparing two different types of data. The data items on the x-axis must be common to the two types of data to be compared. In this example, the number of cars sold is compared with the total amount of commission ($), on the y-axis.

Secondary Axis: commission

Primary Axis: number of cars What is on the x-axis?

Problem Set 7

Text boxes We have learnt how to use tables to lay out text and graphics in a document. Text and graphics can be imported easily into the cells of a table. A multi-column table will means a multi-column layout for the document. Text boxes may also be used to lay out text and graphics in a document. A text box is a container to hold text or pictures.

Use of tables

Use of text boxes

We can have a more flexible layout for our document if we use text boxes as text boxes can be put anywhere. For example, if we want a story that begins on page 1 to continue on page 3, we place the text in a textbox on page 1 first. Then, we create a text box link so that the text can flow to the textbox on page 3.

............ ............ ............ ............ ............ ............

........ ........ ........ ........ ................... . ................... . Page 3

Page 1 Text flow

Text is typed into a text box or imported from another document into the text box. When text is imported, the text box may be too small to hold all the text. We can either resize the text box or move the excess text into the next text box. We refer to this movement of text 80

Problem Set 7

as text flow. The purpose of text flow is to move excess text into the next text box so that the text is readable and all the text can fit into the document. When we add lines of text to a linked text box, the text flows forward into the next text box. When we delete lines of text from a text box, the text in the next text box moves backward. We can have many text boxes in a document. What we can do with text boxes  We can format and edit the text in a text box. 

We can change the size of a text box.



We can import a picture into a text box.



We can add/remove outlines of text boxes.



We can fill a text box with colour.

Word and spreadsheet tables Selected rows and columns of a table in a word-processed document may be copied and pasted into a worksheet. Likewise, a table may be copied from a worksheet and pasted into a word-processed document.

Spreadsheet table

Word table

However, any changes made in one table will not affect the contents of the other table. This is because although both tables have the same content initially, they are not linked.

Problem Set 7

Link objects between word processor and spreadsheet The word processing program allows a spreadsheet table to be embedded so that a link is created between the tables in word processor and spreadsheet. When the table in the spreadsheet is changed, the table in the word processor will change accordingly because of the link between the two applications. The same can be done to a chart. The chart is created in spreadsheet and then embedded in the word-processed document. Whenever a website address or URL (uniform resource locator) is typed in a document, the word processor will also automatically format the website address or URL to become a link. The link will be broken if the URL is typed incorrectly.

Problem Set 7 – Worksheet 7.1

WORKSHEET 7.1: Soccer matches 1. What is the purpose of a combination chart? ________________________________________________________________________ ________________________________________________________________________ 2. Give an example of what can be compared using a combination chart. ________________________________________________________________________ ________________________________________________________________________

3. Follow the instructions below to create data tables and charts on soccer goals and matches. (a) Open the file ‘combchart_student’ and save it as ‘combchart_<Your Name>’. (b) Create a combination chart as shown below. Replace <Your name> with your name. (c) Save your file and print the chart for submission.

Problem Set 7 – Worksheet 7.1

(d) Now, open the file ‘soccer_student’. A screenshot is shown below.

(e) In cell B2, type the title SALES REPORT FOR HOME MATCH, in capital letters, Calibri 16. (f) Merge cells B2:F2. (g) Centre the data in these columns: Number of Spectators, Category A, Category B. (h) Spectators may buy tickets for Category A or Category B. In the ‘Category B’ column, enter formulas to find the number of spectators in this category. (i) Use a formula to calculate the ticket sales for each day. What is the formula for Wednesday? __________________________________________________________________ (j) Use a function to find the total amount of ticket sales. What is the total amount? __________________________________________________________________ (k) Replace A.Student with your name. (l) Use a function to find today’s date in cell C25. (m) Change the page orientation to landscape mode.

Problem Set 7 â&#x20AC;&#x201C; Worksheet 7.1

(n) Create a combination chart using data values in Category A, Category B and Total Sales as shown below.

(o) Arrange your chart below the report. Save your work. (p) If the cost of a Category B ticket has increased to $21.50, what is the new total amount of ticket sales? __________________________________________________________________ (q) Which cells are affected by the change in the cost of Category B ticket? __________________________________________________________________ (r) How many cells are affected altogether? __________________________________________________________________

Problem Set 7 – Worksheet 7.2

WORKSHEET 7.2: Soccer rules 1. What is the advantage of embedding a chart in a word document? ________________________________________________________________________ ________________________________________________________________________ 2. What is the purpose of text flow? ________________________________________________________________________ ________________________________________________________________________ 3. You are going to create an A4-page document on soccer news. You will embed a chart (Goals Galore!) that you have created in Worksheet 7.1 into this document. You are encouraged to be creative in your design of the document. Follow the instructions below. You may refer to the sample provided on the next page. (a) Open a new word document and save it as newsletter_<Your Name>. (b) Type the heading “The Soccer Newsletter” in word art. (c) Insert a textbox and import the file on_the_field.doc into the textbox. Change the outline of the textbox to pattern. Make sure all the text can be read. (d) Repeat step (c) for the next two files: off_the_field.doc and offer.doc. (e) Using textbox (with no outline), import the graphic soccer.png. (f) Type the following text after placing the above textboxes. “The chart below shows the total number of goals scored by the top 5. The number of goals for the top-scorer is also shown in the chart.” (g) Embed your chart from Worksheet 7.1 as a linked object in the document. (h) Save your work and submit to your teacher. (i) Open your file ‘combchart_<Your Name>.xls’ (done in Worksheet 7.1). (j) Change the name Snow City to Capital City. (k) Return to your word document and print a copy of the newsletter.

Problem Set 7 â&#x20AC;&#x201C; Worksheet 7.2

Problem Set 7 – Worksheet 7.2

CHALLENGING EXERCISE

1. You are the manager of a football club. You are required to update the payment to the soccer players of your football club. (a) Open the file ‘payment_student.xls’. You should see a report as shown.

Problem Set 7 – Worksheet 7.2

(b) Type ‘December’ for payment month and ‘2013’ for payment year. Use the spreadsheet function to enter today’s date. (c) Replace A.Student with your name. The players are paid according to the number of games they have played; as shown in the table.

Number of games

Payment

0–8

$20, 000

9 – 15

$30, 000

16 – 25

$40, 000

> 25

$55, 000

(d) Use a conditional IF statement to find the payment for each player. (e) Use a function to find the total payment for the year. (f) Sort the data in ascending order of player’s name. (g) Create a combination chart using ‘Number of games played’ and ‘Payment’. (h) Save your work as ‘payment_<Your Name>.xls’.

Optional: (i) Create a logo for the club stamp. (j) Save your logo as bitmap graphic. (k) Insert your logo into the report. (l) Save your work.

Revision Exercise E

REVISION EXERCISE E: SPREADSHEET (THEORY) Part A: Multiple choice questions Put a tick in the box beside the correct answer. 1. The diagram below shows a ___________________ chart.

A B C D

bar line column combination

2. What has been done to the data in Diagram A to get the data in Diagram B?

Diagram A A B C D

Diagram B

arrange align sort wrap 90

Revision Exercise E

Refer to the screenshot below to answer questions 3, 4 and 5. The screenshot shows the top 6 types of symptom reported by a clinic.

3. What is the formula used in cell B8? A B C D

=AVERAGE(B2:B7) =SUM(B2:B7) =ADD(B2:B7) =TOTAL(B2:B7)

4. What is the function used to find todayâ&#x20AC;&#x2122;s date in the cell B10? A B C D

DATE DAY NOW TODAY

5. Which type of chart will best represent the data? A B C D

line pie column combination

6. The function MOD gives the ______________ when a number is divided by a divisor. A B C D

remainder quotient integer decimal number 91

Revision Exercise E

Refer to the screenshot below to answer questions 7, 8 and 9.

7. What should be entered in cell A8 if you want to find the total number of people in the list? A B C D

=COUNT(A2:A7) =COUNTA(A2:A7) =MAX(A2:A7) =SUM(A2:A7)

8. What should be entered in cell B8 if you want to find the number of males (M) in the list? A B C D

=COUNT(B2:B7,M) =COUNTA(B2:B7,M) =COUNTIF(B2:B7, M) =SUM(B2:B7,M)

9. The range of cells B2:B7 has been formatted. What is the name of the formatting? A B C D

colour formatting font formatting shade formatting conditional formatting

10. What task is a spreadsheet used for? A B C D

recording sound clips searching the internet creating a storyboard keeping track of expenses

Revision Exercise E

Part B: True or False Circle TRUE if the statement is true or FALSE if the statement is false. 11. We use a combination chart if we want to compare two sets of data. TRUE / FALSE 12. We can only sort data in ascending order.

TRUE / FALSE

13. To round the value 23.4 to 24, we can use the ROUND function.

TRUE / FALSE

14. The formula ‘=RANDBETWEEN(1,10)’ will only return integers from 1 to 10. TRUE / FALSE 15. There is no limit to the number of functions used in a formula.

TRUE / FALSE

16. A combination chart can be made from a pie chart and a line chart.

TRUE / FALSE

17. The function MAX returns the largest value in a set of values.

TRUE / FALSE

18. Restricting cursor movement is useful if you want your user to change any data in the spreadsheet. TRUE / FALSE 19. A bar chart is used to show trend over time.

TRUE / FALSE

20. The RAND function can only return a value to 2 decimal places.

TRUE / FALSE

Revision Exercise F

REVISION EXERCISE F: SPREADSHEET (PRACTICAL) The diagram below shows the time (in minutes) taken for 10 teams of 2 contestants each to complete four tasks in The Wonderful Race. Under the gender column, MM means that the team is made up of 2 male contestants; MF means that the team is made up of 1 male and 1 female contestant; and FF means that the team is made up of 2 female contestants.

1. Follow the instructions below to complete the task. (a) Open the file â&#x20AC;&#x153;wonderful_raceâ&#x20AC;?. (b) Replace A. Student with your name.

[1]

blue if it contains MM;

II.

red if it contains FF; and

III.

orange if it contains MF.

[2]

(d) Format the range of cells A5:G6 with a grey fill.

[1]

Revision Exercise F

(e) Enter a formula using function to calculate the average timing, round up to an integer, for each team under the Average column. [3] (f) Sort the data A7:G16 according to the data under the Average column, in ascending order. [2] (g) Insert an additional column before column A, and type the following into the cells A6:A16. [3] A 6

Ranking

1st

2nd

3rd

4th

5th

13 14 15

Consolation

(h) Enter a function to count the number of MM team, MF team and FF team in each of the cells F19:F21. [2] (i) One of the teams will be selected for an interview. In cell F22, use a function to randomly select a team. [1] (j) Save your work as wonderful_race_<your name>. (k) Format your work to show formula, save it as wonderful_race_formula_<your name>. [1] (l) Close your work.

Revision Exercise F

2. You are now asked to compare the timing between Task A and Task B for the top 5 teams using a chart. (a) Retrieve your file wonderful_race_<your name>. (b) Create the chart shown below. Replace A. Student with your name.

[6]

Problem Set 8

PROBLEM SET 8: SCHOOL COMPETITION Learning Outcomes Students should be able to (a) Understand concepts of planes [2.4.1] (b) Understand concepts of views [2.4.2] (c) Create a 3D drawing [2.4.3] (d) Orientate a 3D drawing [2.4.4] (e) Edit a 3D drawing [2.4.5] (f) Understand the difference between a local area network (LAN) and a wide area network (WAN) [1.1.16] (g) Understand and state that computers in a network can share resources such as documents, databases and software [1.1.17] (h) Understand the difference between intranet and internet [1.1.18] (i) Identify the situations in which computers are used in society [1.4.1] (j) State how the use of computers has affected the way people live and work [1.4.2] (k) State the various services available on the internet such as on-line news, cyber shopping, on-line registrations, seat reservations and examinations [1.4.3]

Overview This problem set is about 3-dimensional objects that we see around us. You will learn how objects can be viewed from different directions or angles. You will use 3D application software to design 3D models which are useful as samples of original products that can be manufactured. 3D models or objects are also used in animations and games. In the first worksheet, you will create a 3D drawing of a trophy for a competition. In the second worksheet, you will work on a crossword puzzle.

Problem Set 8

3D drawing A 3-dimensional (3D) drawing application is used to design 3D solids or drawings for products, animations or games. The drawings may show how objects look like when viewed from different directions or angles. Each view shows how an object looks like on a flat surface known as a plane. Two common views are isometric and orthographic.

Plan View

Orthographic Drawing

Isometric Drawing Front View

Side View

An orthographic drawing is the most commonly used drawing for 3D solids. It shows the front, plan (top) or side (right) views of the product. These views are drawn on three different flat surfaces or planes referred to as the front plane, base plane and lateral (or right) plane.

Features in 3D drawing Common features in 3D drawings include: 

Extrude – add volume to 2D shape to produce a solid



Chamfer – cuts off an edge



Sweep – create 3D solid from 2D shape along a defined path



Extrude

Chamfer

Revolve – rotate 2D shape 3600 about an axis of revolution to create 3D solid Revolve Sweep

Problem Set 8

Area network A network is formed when two or more computers are linked or connected together so that information and data can be shared. The computers within the network can access resources such as documents, databases and application software. Local Area Network (LAN) is a network that covers a small geographical area such as a home, office or building.

Metropolitan Area Network (MAN) connects two or more local area networks. The geographical area usually covers within a campus, town or city.

Wide Area Network (WAN) covers a broad geographical area like a country or across countries.

Password Authorisation

Intranet versus internet

An intranet is a private space accessible by authorised people who are usually employees or occupants of the same organisation or place. This space is usually protected by passwords and firewalls.

The internet is a public space accessible by everybody. Some intranets are connected to the internet while intranets with high security are not connected to the internet.

Problem Set 8

Computers in society Computers have impacted society in the following areas:    

Entertainment Education Work Daily Life

Computers in entertainment The Internet has become a very powerful platform for online entertainment. Music and video can now be created, uploaded and shared easily in the Internet. Movies can be screened with special effects like 3D, and combining actors and actresses with animated creatures.

Online computer games such as Massive Multiplayer Online Role-Playing Games (MMORPG) and social networking games are gaining popularity.

Computers in education Computer simulations can be used in education to show processes that may be dangerous or take a long time to complete. A computer-networked platform like the Learning management systems (LMS) helps teachers to assign work to students and collect work from students via the platform. Several computer-based learning packages are also available for learning English, Mathematics, Science, History and Geography, for example. The computer is also used together with application software to create publications, presentations, and spreadsheets; as well as for self-help tutorials and learning materials.

100

Problem Set 8

Computers at work Computers can help to increase efficiency at work. Emails can be sent to many people instantly while mail merge helps in creating multiple customised documents for many people. Video conferencing allows people at different places to meet and discuss online, using application software like Skype. Sender’s Email Server

Receiver’s Email Server

Sender

Mail merge Receiver Sender

Computer automation increases productivity and allows dangerous tasks to be performed by robots and machines instead of humans. New jobs have been created to keep up with the changes and advancement in technology. For example, the driver will control a “driverless” train through remote control at the train control station. Plastic cards, each with an embedded computer chip, serve to identify employees and allow them to enter their work place.

Computers in daily life Computers change the way people live their lives. With the computers becoming smaller but more powerful, people can use handheld devices to do their work and connect to the Internet while on the move. With the help of computers and their processing power, more advanced medical equipment is created to perform surgery, help patients fight diseases, and improve health care. Medical staff uses computers to track patients’ medical records and medication.

101

Problem Set 8

Computers in supermarkets make shopping more convenient through self-checkout stations. Computers help to control traffic and ensure a “green flow” to ease traffic congestion in certain areas. Computers are also used for weather forecasts and we are able to know what the weather will be like for the next few days.

Services on the internet As the internet is a public space accessible to everyone, it has become a very useful platform for sharing information, communicating with people and providing services. Internet for information Digitised or digital books/articles are books/articles in electronic form. These digitised materials make sharing information easier. The materials can be stored in digital storage media or uploaded in the internet. Online news and maps are also readily found in the internet. Information is usually found by typing keywords in search engines. The search engines are able to display many results within seconds. Singapore

Internet for service The internet can also be used to do online shopping, online registration for schools, workshops and courses, and seat reservations for movies and air tickets. Payment for these services is made through electronic fund transfer (EFT) where money is transferred electronically from one bank account to another. This is also referred to as a “cashless” transaction because there is no actual handling of money.

102

Problem Set 8

Online tests and examinations are also becoming popular for educational institutions to reach out to students who may be in different countries or for computer-based tests where videos and other media elements are used as part of the tests.

Internet for communication Different groups of people use the internet to communicate for different purposes. For example, 

Political parties use social networking platforms to rally votes.



Government agencies maintain sites to provide important information for the public and to gather feedback from them.



Companies use the internet to advertise and sell their products.



Individuals use tweets, blogs, wikis, forums and other social networking platforms to make friends and update their status. Tweets are posts or messages with less than 140 characters and available to group members through a micro blogging service. Wikis are personal websites containing descriptions of the writers’ own experiences, observations and opinions. Forums are available on many websites to enable open discussions among the users.

103

Problem Set 8



Businesses and organisations use the internet for email and video conferencing across countries and time zones.



We can send messages via mobile phone numbers through applications like Whatsapp.

Negative impact of computers Although computers have many advantages, they have negative impact on our lives as well. Some negative impact includes the following: 

Lack of face-to-face communication: This may cause misunderstanding or misinterpretation of material put up on the cyberspace.



Living in a virtual world: We may spend much time in a virtual or cyber world such that we forget about the real world around us. For example, we may spend too much time chatting with online friends that we forget to spend physical time with our families. We may also spend too much time playing online games that we lose touch with reality.



Poor social skills: Computers may affect our social skills and cause us to be impatient. For example, we may be too used to getting results instantly through computers that we may get irritated with other people who do or say things slowly.



Health problems: Long usage of computers may cause health problems such as eye strain, tiredness and backache.



Inappropriate content: Content that is pornographic, hateful, violent, and encouraging illegal, dangerous or harmful activities may have negative influence on us and this is not what we wish would happen to us.

C DA ONF TA ID EN TIA L

Why is everybody using the phone to text one another when everybody is here?

CONF DATA IDENTIAL

IAL ENT NFID CO A DAT

104

Problem Set 8



More crimes: There may be an increase in computer crimes like theft, hacking, phishing, and pharming when people are not careful with their passwords or forget to verify the authenticity of websites.



Computer malware (e.g. virus and worms) can damage data and cause computers to malfunction or hang.



Intellectual property rights: There may be an increase in copyright infringement as people may just copy and download materials from the internet without proper acknowledgement.



Cyber wellness: Friends made during cyber contact may be dangerous as these people may not be what they claim to be. We may be tricked into giving them information, buying things from them or sending them expensive things.

105

Problem Set 8 – Worksheet 8.1

WORKSHEET 8.1: Trophy design The school is organising a trophy design competition. Every class is required to submit at least one design for the trophy design competition. You are required to submit a trophy design. Think of a design and use 3D application software to draw a model of your trophy. You may also use the trophy design suggested below. The trophy base The trophy base is a square block with a square base of 8cm by 8cm. The height of the block is 13 cm. At the top of the block, 1 cm is chamfered off from the top and sides. The instructions to create the base are: 1. Create a square 8cm by 8cm. 2. Extrude to height 13cm. 3. Cut the top sides of the base 1cm from the top and 1cm from the edge. 4. Save your work as “Trophy Base”.

The trophy centre The centre part of the trophy will look like this when done.

106

Problem Set 8 – Worksheet 8.1

Follow these instructions to create the centre part of the trophy. 1. Create a circular base of radius 4cm. 2.

Create the shape as shown on the right perpendicular to the circular base.

3. Hence, create the 3D solid. 4. Save your work as “Trophy Centre”.

The trophy top 5. Design a top for the trophy on your own. Save your work as “Trophy top”. 6. Put the three components together to form the trophy. Save your assembled trophy as “My trophy_your name”.

This is the assembled trophy of our design. It shows the centre and base.

Centre of trophy

Base of trophy

107

Problem Set 8 – Worksheet 8.2

WORKSHEET 8.2: Crossword puzzle Review questions 1. Form a group of 4 members. 2. Your teacher will assign each member a section of the textbook to study. 3. Spend the time given to read the section of textbook assigned to you. 4. Answer the questions related to the section that you have read in the textbook. 5. Spend the next 10-15 minutes to take turns to share what you have learnt after reading the section of textbook assigned to you with your group members. 6. Answer the rest of the questions in the worksheet based on what was shared by your group members.

Crossword puzzle 7. Open the document “Crossword Puzzle.doc”. 8. A copy of the puzzle is reproduced in the following pages. 9. Complete the crossword puzzle based on the clues given. 10. Save and submit the completed copy to your teacher.

108

Problem Set 8 â&#x20AC;&#x201C; Worksheet 8.2

Review Questions Area network 1. What is a network? ________________________________________________________________________ 2. Name some resources that computers in networks can access. ________________________________________________________________________ _______________________________________________________________________ 3. Types of network: ________________________________________________________________________

Intranet and internet 4. What is an intranet? ________________________________________________________________________ 5. Name two ways to prevent people from gaining access to information on the intranet. ________________________________________________________________________ ________________________________________________________________________ 6. What is the internet? ________________________________________________________________________

Computers in society 7. Name two areas where computers have impacted society. ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

109

Problem Set 8 â&#x20AC;&#x201C; Worksheet 8.2

Computers in entertainment 8. Creating, uploading and sharing of ______________________ ____________________ are now very easy in the internet.

and

9. Some 3D drawings and animated drawings are combined with actors and actresses to provide ____________________ effects in movies. 10. Computers are used in __________________ like Warcraft, Farmville and Slingo.

Computers in education 11. If the processes are too dangerous or take too long to complete, teachers can use computer _____________________ to show these processes to the students. 12. Students can log onto their schoolâ&#x20AC;&#x2122;s _______________________________________ to do the online homework assigned by their teachers. 13. Students can find information in the _______________ while doing their research on certain topics for their projects.

Computers at work 14. Write down and explain one way a computer can increase efficiency at work. _______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________

15. Factories and assembly lines use _______________ instead of man to perform jobs, like fixing automobile parts, repeatedly and tirelessly.

110

Problem Set 8 â&#x20AC;&#x201C; Worksheet 8.2

Internet for information 16. Name two ways that computers can help in sharing knowledge and information with people. ________________________________________________________________________ ________________________________________________________________________

17. While looking for information, typing keywords in __________________ can produce many results.

Internet for service 18. Name four services that can be used in the internet: a.

__________________________________________

Internet for communication 19. The internet is a very powerful platform for communication among people. Different groups of people use the internet to communicate for different purposes. Name some groups and describe the purpose for each group in the table below. Group

Purpose

111

Problem Set 8 â&#x20AC;&#x201C; Worksheet 8.2

Negative impact of computers 20. Name three negative impact of computers: (a)

____________________________________________________________

(b)

____________________________________________________________

(c)

____________________________________________________________

112

Problem Set 8 â&#x20AC;&#x201C; Worksheet 8.2

Crossword puzzle a 1

f 5

6 7 j

k 8

10 n

p 13

Clues Across 1

Clues Down

Computers may make us live in a _______ world and forget about the real world. A _________ is formed when two or more computers are connected together. Special ______ in movies include combining real-life actors and actresses with animated creatures. _______news and games are readily available in the internet. 113

An ______ is sent so that many people can receive it instantly.

This is an open public network accessed by everybody.

_______ is an example of video conferencing software.

This computer network covers a small area like within a home or

Problem Set 8 â&#x20AC;&#x201C; Worksheet 8.2

Clues Across

Clues Down

In an orthographic drawing, the front view of a 3D object is viewed from the front ________. Computer crimes, like _________ and phishing, are on the rise.

An _______ is a private website that can only be accessed by authorized people. _________ creates multiple customised documents.

With more people talking online, there is a lack of face-to-face _______. _______ are websites that allow people to add, delete or update the content that they put up as well as add on to content that others have put up. People make use of ________ devices to work and connect to the internet on the go.

______ are websites that contain the writersâ&#x20AC;&#x2122; own experiences, observations and opinions. As many things can be copied from the internet, there is a rise in infringement of _________. One feature in 3D drawing software allows a 2D shape to be rotated 360o about an axis of _________ to form a solid.

114

office. Digitising books means making them into ______ forms. Music and videos can now be created, ______ and shared easily in the internet. A ______ connects many computers together within a country or across countries. A ______ is used when the process is dangerous or takes very long to complete. _______ are posts or messages with less than 140 characters on a microblogging service. A _______ is a feature of a website for open discussion.

______ such as documents, databases and software can be accessed by computers within a network. Computer ______ uses robots to perform dangerous tasks instead of humans. _____ engines are used to find information in the internet.

Computer _______ is an example of malware that can attack your computer and damage your data.

Friends made during _____ contact may be dangerous as they may not be what they claim to be.

Problem Set 8 â&#x20AC;&#x201C; Worksheet 8.2

Clues Across

Clues Down p

115

When payment is made by transferring money electronically from one bank to another, it is known as ______.

Problem Set 9

PROBLEM SET 9: SCHOOL OPEN HOUSE Learning Outcomes Students should be able to (a) Extract parts of a sound clip/track [2.3.3] (b) Edit sound clip/track for a purpose [2.3.4] (c) Record and store digital voice [2.3.5] (d) Understand the concept of mail merge [3.4.7] (e) Use the mail merge feature for documents with data from table in word processor [3.4.8]

Overview Your school is preparing for an open house for primary school pupils. The open house is to showcase your schoolâ&#x20AC;&#x2122;s achievements, facilities and programmes. You will explore the tasks required in organising the event with your teacher. In the first worksheet, you are to use an application software to prepare a stack of personalised documents for the event using mail merge. In the second worksheet, you will edit sound clips for a multimedia slide show. In the third worksheet, you will create a multimedia slide show so that it can be run during the open house.

116

Problem Set 9

Mail merge Mail merge is the automatic process of creating several pages of the same word-processed document according to a mailing list. Mail merge is useful when we need to send the same document to different people (addressees). It can produce individualised or customised documents like letters, notifications and mailing labels. The example below shows a factsheet on tropical fruits for a talk to be held in three different places. Word-processed table

Mailing list

Word-processed document

Spreadsheet table

The mailing list serves as a data source for the word-processed document. It indicates who the word-processed document is for. The mailing list may be a wordâ&#x20AC;&#x201C;processed table or spreadsheet table.

Word-processed table as data source A word-processed table may be used as a data source. It is important to check that all the necessary data for the document is typed correctly in the word-processed table. The data is tabulated under proper column headings. It is a good practice to arrange the columns according to the order that the data will be used in mail merge.

117

Problem Set 9

Word table

Field names from Word table Mail merge

Document

Multiple pages according to mailing list

Data source fields The column headings in the data source table are the data field names in the wordprocessed document. The word processor will insert the data field values in the areas specified by << >>. There is no limit to the number of data fields. The data types may be text, numeric or alphanumeric (e.g. time).

Data field values

Source document

Pages in merged document

118

Problem Set 9

Audio clips Audio clips can be edited, trimmed, deleted or inserted using audio editing software. Trimming is the process of removing unwanted segments from either or both ends of an audio clip.

Editing is the process of changing an audio clip by varying its loudness or pitch. Special effects like fade in or fade out can also be added.

Trimmed

Inserting is the process of inserting an audio clip into a segment of another audio clip.

Deleted

Deleting is the process of removing selected segments from an audio clip.

119

Problem Set 9 – Worksheet 9.1

WORKSHEET 9.1: Invitation letters Your school is organising an open house to attract primary school students to enrol in the school. You are tasked to write invitation letters to caterers to send quotations of their refreshment. You are also tasked to write letters to invite nearby schools to attend the open house.

Writing to caterers 1. Open the document “Caterer Invite.doc”. 2. Insert the graphic “logo.png” at the top right-hand corner of the document. 3. Change “A. Student” to your name, and change the date to today’s date. 4. Using the table in the document “Caterer List.doc”, create a mail-merged document addressed to the available caterers. 5. Insert the merge fields “Name of Company”, “Location” and “Contact Person” at the locations shown below:

6. Merge to a document and print the second page for submission.

120

Problem Set 9 – Worksheet 9.1

Writing to schools 7. Open the document “Primary Schools Invite.doc”. 8. Insert the graphic “logo.png” at the top right hand corner of the document. 9. Replace “dd/mm/yy” with the date of the Open House. 10. Replace “A. Student” with your name, and replace the date with today’s date. 11. Replace all “event” with “Open House”. 12. Using the table in the document “School List.doc”, create a mail-merged document addressed to the available primary schools. 13. Insert the merge fields “School”, “Address” and “Postal Code” at the locations shown below:

14. Merge to a document and print the fifth page for submission.

121

Problem Set 9 – Worksheet 9.2

WORKSHEET 9.2: Media preparation The Open House is approaching. You are tasked to prepare a slide show for the students attending the open house. Before creating the slide show, you have to edit the audio clip to be used in the slide show.

Music for slide show 1. Open the audio clip “playing games.mp3”. 2. Remove all the dog barks. 3. Create a fade out at the end of the audio clip. 4. Save the final clip as “games.mp3”. 5. Open the audio clip of the school song. 6. Edit the audio clip such that it plays only the chorus. 7. Save the final audio clip as “School song chorus.mp3”. 8. Record your own voice saying the following sentence: “Welcome to My_School’s Open House.” where My_School is the name of your school. 9. Save the audio clip as “announce.wav”.

122

Problem Set 9 – Worksheet 9.2

WORKSHEET 9.3: Slide show for open house Your preparation of the media elements for the slide show is now complete. You can now continue with the creation of the presentation slides about your school.

Creating the title page 1. Create the title page similar to the slide below:

Insert School Badge here Insert School Name here Insert School Motto here

Insert “announce.mp3” here

2. Insert an appropriate background for the slide. 3. Insert an appropriate slide transition for the slide.

Creating the slide show 4. Create the remaining slides with one topic on each slide: 

School Leaders



School Achievements



CCAs: Uniformed Groups



CCAs: Clubs and Societies



CCAs: Sports



School Contact 123

Problem Set 9 – Worksheet 9.2

5. Insert the audio clip “School song chorus.mp3” in the slide with the topic School Achievements. 6. Insert appropriate text, pictures, animation and videos for each slide. 7. Save the file as “MySchool_MyName” in a presentation show format.

124

Revision Exercise G

REVISION EXERCISE G: CROSSWORD PUZZLE I

Complete the crossword puzzle below.

14m

Clues across 1. This diagram uses shapes to represent an algorithm. 2. Abbreviation for uniform resource locator. 3. A game must have an achievable ____ so that players will be interested. 4. To make changes to a document. 5. The World Wide Web is sometimes referred to as the ___.

125

Revision Exercise G

6. This refers to the arrangement of text and graphics in a document. 7. We used this key to create fixed number of spaces between words in a document. 8. Several computers connected together so that resources can be shared. 9. An intersection of a row and column in a spreadsheet. 10. We must use ______ websites to safeguard our data and to ensure that the website is authentic. 11. Abbreviation to represent the three basic operations of a computer. 12. A spreadsheet function to generate a random value. 13. The smallest dot in a screen image. 14. A logical function in a spreadsheet to check whether a condition is true or false. 15. A network of computers that is accessible only by employees of the same company.

Clues down a. Software applications used to prevent unauthorised access to our computers. b. A sprite is also known as this. c. A spreadsheet function to find the current date. d. An outcome of a process. e. A personal website that contains posts in chronological order. f. An iterative process to execute a series of actions repeatedly. g. A way to represent data in rows and columns. h. A spreadsheet function to count the number of data items. i.

An electronic letter sent via the internet.

Capital letters are in ______case.

k. This is used as a terminator in a flowchart to indicate that a process is completed. l.

A spreadsheet function to get the leftmost letter from a string of letters.

m. Abbreviation for a commonly used web browser. 126

Revision Exercise H

REVISION EXERCISE H: CROSSWORD PUZZLE II

Complete the crossword puzzle below.

Clues across 1. Action buttons and URLs are examples of _____. 2. A mathematical operation to find the sum of two numbers. 3. Indicates a set of values between two values. 4. Communications between computers may be ____d through cables or ____less via satellite. 127

Revision Exercise H

5. One way to protect an account on a website is to use this. 6. A boundary condition in animation. 7. A visual representation of data. 8. Abbreviation for operating system. 9. Abbreviation for denial of service. 10. The largest single digit number in the decimal system. 11. May be music or voice. 12. We use this device to interact with the computer. 13. Rotate a shape 3600 about an axis of revolution so as to get a 3D model.

Clues down a. These media elements are used as characters or backgrounds in an animation. b. This software collects your data without your knowledge. c. A commonly used key to remove typing errors. d. A set of program code to do something. e. Some games used these to show different scenes. f. A type of malicious program. g. One way to check whether two objects are touching each other is to detect this. h. We do this to check whether our program works properly. i.

An unpredictable number is a _________ number.

128

Sample Practical Paper

SAMPLE PRACTICAL PAPER Game making You are provided with the following files for your game: 

Butterfly



Red flower



Yellow flower



Garden

[2]

You are to create a game where the butterfly moves to collect nectar from the two flowers. The rules of the game are: 1. The butterfly will follow the mouse pointer.

[2]

2. When the red flower is clicked, the red flower will disappear and the score is increased by 50. [3] 3. When the yellow flower is clicked, the yellow flower will disappear and the score is increased by 75. [3]

Garden

X (0, 0) Blue boundary

4. The game is started by pressing the space bar.

129

[2]

Sample Practical Paper

5. When the game starts, the butterfly will appear at (0, 0) and the score is reset to zero. The red and yellow flowers will appear randomly within the boundary shown in blue. [6] 6. The score is displayed on the screen.

[1]

7. The game finishes when the butterfly touches the blue boundary lines or when all the flowers are picked. 

When the butterfly touches the blue boundary lines, the words “Game Over” are displayed. [2]



When all the flowers are picked, the words “You Win” are displayed.

8. Save your work as ‘butterfly_your name’.

[2] [1]

Spreadsheet 1. Open the file ‘voucher’ and save it as ‘voucher_your name’. 2. You will see a worksheet with two parts of data, similar to the screenshot below. Part (a) is Daily Transactions and Part (b) is Update for Jan – Sep.

130

[1]

Sample Practical Paper

Set up the spreadsheet 3. In the Full cost column, enter a formula to find the full cost (Full cost = number sold x value of voucher). [2] 4. In the Discount column, enter a formula that uses a function to look up 1 the percentage discount in the Type of purchase table, and multiply it by the full cost. [3] 5. In the Price paid column, find the price paid (Price paid = Full cost â&#x20AC;&#x201C; Discount).

[2]

6. Format appropriate cells as currency in Singapore dollars to zero decimal places. [3] 7. Automatically format all cells in the Buyer code column with a red background for members (M). [2] 8. In cell C22, place a function to return todayâ&#x20AC;&#x2122;s date only.

[1]

9. In cell C30, use a function to find the total number of vouchers sold for all the items. [1] 10. In cell D30, use a function to find the total amount of money collected for all the items. [1]

Set up Status Update chart 11. Create a combination chart with Total number sold as a column chart and Money collected as a line chart. [8] 12. Save your file.

You will need help from your teacher to do this.

131

Problem Set 10

PROBLEM SET 10: MY CCA Learning Outcomes Students should be able to (a) (b) (c) (d) (e) (f)

Check and allow valid input data [4.2.2] State why data needs to be validated prior to processing [4.2.3] Understand that values that can be calculated should not be input [4.2.11] Display appropriate text message in place of error displays [4.2.12] Extract the day, month and year components from a date [4.4.5] Extract character(s) from a string of text [4.4.6]

Overview Co-curricular activities (CCAs) are an integral part of a balanced and holistic education for all students in schools. It helps to nurture qualities such as resilience, confidence and perseverance, which prepare you to adapt and thrive in a rapidly changing world. In all schools, CCA records (data) are kept for all students over the years, and a testimonial or certificate of your CCA achievement will be given to you at the end of your secondary school education. Thus, it is important that the information on your certificate and testimonial is recorded accurately. In this problem set, you will learn what data validation is and how to check for valid data in the spreadsheet application software. As you run through the problem set, you will understand the importance of data validation and learn to apply your knowledge and skills in the creation of a simple form in the spreadsheet application. In the first worksheet, you will understand and learn how string functions like LEN, LEFT, and MID works and use these functions to validate data. In the process, you can also understand how the use of these functions helps to simplify work processes. In the second worksheet, you will apply what you have learnt and validate data in a CCA form in the spreadsheet application. Lastly, in the third worksheet, you will create presentation slides to promote your CCA to the students in the school. This activity serves as a revision of the multimedia presentation skills that you have learnt at lower secondary.

132

Problem Set 10

Data validation Data validation is a process used to control the type of data that is entered in the spreadsheet. Data needs to be validated before being processed to make sure that the data entered is according to requirements. By configuring data validation field, you can prevent users from entering data that is not valid. In the spreadsheet software, data validation can be configured by (a) providing a list of choices (drop-down list); or (b) defining the types of input data (setting criteria). (a) Providing a list of choices In this example, there is a list of valid classes to choose from as seen in the screenshot below; 3-1 to 3-8. The creator limits the selection to the users, by providing a list of choices. In this way, no invalid classes will be entered, as the user can only choose from the given list of classes in the cell. Hence, the input data is controlled and defined by the list provided.

Screenshot above shows data validation by providing a list of choices (b) Defining the types of input data In this example, cell B1 is formatted to accept only numeric input. When the user enters data which are not numeric (meaning invalid input), the system will show a message box to inform the user that the data entered is invalid. The user is expected to enter the data again if he wishes to continue with the computer application. This cell can also be formatted to accept other type of inputs such as text, date or time.

An example of a message box Screenshot above shows the results of data validation 133

Problem Set 10

Types of data validation In this problem set, we will learn three types of data validation: (a) length check; (b) range check; and (c) type check. (a) Length check Length check determines the length of the data and make sure that it is not too short or too long. For example, we would like the length of a password to be 5 characters only, so we have to set a condition to accept a text length of 5 characters only. With the length check validation, we can control the number of characters that can be entered into the cell.

(b) Range check However, if the required length of the password can be between 5 and 8 characters, we will then do a range check instead of a length check. A range check determines whether the data entered falls within a specified range. For example, in this case, the user can then enter a text between 5 and 8 characters.

134

Problem Set 10

(c) Type check Lastly, type check determines the type of data that can be entered, like date, whole number or decimal number in the cell. When type check is used, the user can only enter the allowed type of data.

This will allow user to enter date that is only between 1/6/2013 and 30/6/2013

This will allow user to enter whole or integer number that is greater than 18 only

This will allow user to only enter decimal number that is less than 4.5

135

Problem Set 10

Error messages Messages may be displayed to give instructions or feedback to the users. An error message can be displayed if an invalid data is entered. This will prompt the user to re-enter the data that is defined as acceptable in the cell. Error messages may be set by the computer system or user. System error message Usually, the system will generate a system error message if an invalid data is entered in the cell. A system error message is shown by default when no user-defined error message is entered. However, it is recommended to use an user-defined error message with detailed instructions that show how the user can proceed if an invalid data is entered.

If no user-defined error message is entered here, a system error message will be shown

The above shows an example of system error message

What should be included in an error message so that it can be easy to understand?

136

Problem Set 10

User-defined error message An example of an user-defined error message is shown below. Here, cell B1 has been formatted to accept numerical value only; any other types of data input will prompt the error message to be displayed. In this case, the error message is clear to the user, and he can continue by providing valid data in that cell.

An example of an user-defined error message

137

Problem Set 10

Data entry To save time and effort, values that can be processed by the computer should not be calculated and entered manually. Instead, we should use the different functions in the spreadsheet application software to do the job. This will increase efficiency especially when we are dealing with a large amount of data. Example In the example below, it is useful to use a function to help us determine whether the score is a ‘Pass’ or ‘Fail’ in the test. By using a function, the program will automatically display ‘Pass’ or ‘Fail’ instantly. Hence, unlike the test score that has to be entered manually, we let the program determines the ‘Pass’ or ‘Fail’ results automatically.

An example of using function to output data of ‘Pass’ or ‘Fail’

Next, we will learn the functions to extract data on date and text strings.

138

Problem Set 10

String function (Date) The functions DAY, MONTH and YEAR are used to extract the day, month and year components of a date respectively. The TODAY function will give todayâ&#x20AC;&#x2122;s date and it is a volatile function, meaning the value given by the function will change when the function is used on another day.

Actual view

Formula view

String function (Text) The functions LEN, LEFT and MID are examples of text string functions. They are useful as they can be used to determine the length (LEN) or to extract only the required information (LEFT or MID) from a text string. (a) LEN function The function LEN counts and displays the number of characters in a text string. Spaces are counted as characters too. Essentially, it tells us how long the text string is. In this example, the function LEN helps to determine the number of characters in the text string found in cell A1. Note that the spaces between the words are also counted as characters. Thus, nine spaces will be counted as nine characters.

The formula in this cell is =LEN(A1)

139

Problem Set 10

In the next example, a statement using the functions IF and LEN is used to control the number of characters during data entry. Here, the user is required to enter a username with 8 or fewer characters. The formula in cell B3 is =IF(LEN(B1)>8,”Your username is too long.”,”Please proceed.”). First, it checks the length of the text in cell B1. Then, if there are more than 8 characters in cell B1, then the text ‘Your username is too long.’ will be displayed. If not, the text ‘Please proceed.’ will be displayed.

Examples showing the use of IF and LEN functions A flowchart representing the program solution for the above example is shown below.

Start

Enter username

Is username more than 8 characters?

Y Output “Your username is too long.”

End

140

Output “Please proceed.”

Problem Set 10

(b) LEFT function In the example below, members enter their 9-digit membership number of a club in cell B1. The first four characters in the membership number represents the year they joined the club. To find out which year they joined the club, we can use the function LEFT (see screenshots below) in cell B3. This will extract the first four characters in the string of characters in cell B1 to get the required information. By doing so, the process of extracting the year of joining is automated, without the hassle of getting the members to enter on their own.

Example in actual view

Same example in formula view (c) MID function The function MID can be used to extract a certain number of characters from a text string starting from any position in the text string. If the start position is 1, the function MID will behave like the LEFT function. Position 1

Position 6

20134778 Position 3 Position 8

141

Problem Set 10

If the string of characters is 20134778, then the first position is the number 2; the second position is the number 0; the third position is the number 1; and so on. In the example below, a temporary password is to be generated from the last four numbers of the membership number. Here, the function MID can be used to extract the last four characters of the membership number. By doing so, the system will automatically generate the temporary password.

5th position Cell D2:

20134778 Next 4 characters

Here, the formula =MID(D2,5,4) refers to the data in cell D2, starting at the fifth position (i.e. the number 4), extracting the next four characters.

What will the formula =MID(D3,3,2) gives?

142

Problem Set 10 – Worksheet 10.1

WORKSHEET 10.1: Types of CCA 1. Why is it necessary to do data validation? Name two reasons. (a) __________________________________________________________________ __________________________________________________________________ (b) __________________________________________________________________ __________________________________________________________________ 2. Write down the functions that can be used to: (a) extract ‘4728’ from ‘T0134728F’: __________________________________________ (b) extract ‘M’ from ‘M2013428’: ____________________________________________ 3. Circle TRUE if the statement is true or FALSE if the statement is false. (a) The LEN function will not count the spacing between the characters. TRUE / FALSE (b) The TODAY function is a volatile function.

TRUE / FALSE

(d) The function MID(“hello”, 2, 3) will return ‘ell’.

TRUE / FALSE

4. Follow the instructions below. (a) Open the file ‘form_student’ and save it as ‘form_<your name>’. The form contains two parts: Part (A) Data validation and Part (B) Data extraction.

143

Problem Set 10 – Worksheet 10.1

Part A: Data validation This part of the form contains first aid certificate numbers and ages of 18 students, with blank spaces for student number 19 and 20.

(b) In cell C3, enter a function to display today’s date. (c) The first aid certificate number has to start with ‘T’ followed by 7 digits. In the First Aid Certificate No. column (cells C6:C25), add validation to ensure that only 8 characters are entered, including the error message “Please enter ‘T’ followed by 7 digits” in your validation. (d) The first aid course is organised for students between ages 13 and 17. In the Age (as of 1 Jan) column (cells D6:D25), include validation to ensure that only students of age between 13 and 17 are eligible to collect their first aid certificate. Also, include the input message “You must be between 13 and 17 years old.” and the error message “I am sorry. You cannot collect your certificate.” in your validation if the above criteria is not met.

144

Problem Set 10 – Worksheet 10.1

(e) Enter ‘T00292’ in cell C24. Write down what happened. __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ (f) Enter ‘12345678’ in cell C25. Write down what happened. __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ (g) Enter ‘12’ in cell D24. Write down what happened. __________________________________________________________________ __________________________________________________________________ __________________________________________________________________ (h) Enter ‘fifteen’ in cell D25. Write down what happened. __________________________________________________________________ __________________________________________________________________ __________________________________________________________________

145

Problem Set 10 â&#x20AC;&#x201C; Worksheet 10.1

Part B: Data extraction This part of the file contains the CCA index number of twenty students. The first character of the index number represents the type of CCA that the student is allocated. For example, U0371 represents Uniformed Group, A0054 represents Performing Arts, and C0199 represents Club and Society.

(i) In the Type of CCA column, enter a function to display U, A or C for the type of CCA for all students. (j) The school decides to categorise the students into four groups based on their index number. In the Code column, enter a function to extract four characters from the CCA Index No, starting with the second character. For example, 0371 will be extracted from U0371. (k) To determine the group number, you have to find the remainder of each code when it is divided by 4. In the Group column, use a function to determine the remainder when the code is divided by 4. If the remainder is 0, replace it with the number 4. *Hint: This part involves a nested statement. Consult your teacher if you need help.

146

Problem Set 10 – Worksheet 10.1

Complete this column (l) Complete the table ‘Student Groups’ by using appropriate functions to find the number of students in each group.

147

Problem Set 10 â&#x20AC;&#x201C; Worksheet 10.2

WORKSHEET 10.2: Designing a CCA Selection Form Part A In this exercise, you are required to design a co-curricular activity (CCA) selection form for your CCA teacher-in-charge. A screenshot of an example of a CCA Selection Form is shown below.

Your teacher will demonstrate the skills needed to complete this activity. After that, you will design your own CCA selection form. You may use the same layout as this example and add in more details so that it contains more information.

148

Problem Set 10 – Worksheet 10.2

Open the file named ‘CCA selection form’. Complete the following as your teacher runs through the worksheet with you. 1. In cell E3, enter a function to display the current date. 2. Look at the section labelled ‘Personal Details’ and complete the following tasks. (a) (a) Give instruction to enter the name in full.

(b) Give instruction to enter data with the required length. (c) Give instruction to select the gender as either M or F.

(d) Give instruction to enter the date in the dd/mm/yyyy format.

(a) Write (b an IF statement to check that (1)Male students are not allowed to join Girl Guides; and (2)Female students are not allowed to join Scouts. (b) Write a function to determine the age.

149

Problem Set 10 – Worksheet 10.2

3. Look at the section labelled ‘CCA Choices’. Merge cells C13:E13 and type an instruction for users to enter their three CCA choices.

4. Look at the section labelled ‘Hobbies and Skills’ and complete tasks (a) and (b).

(a) Give instruction to the user to enter remarks that are not more than 50 characters.

(b) Use a function to display the number of characters entered by the user.

5. Protect the content in cells E9, E10 and C23 so that the user cannot edit it. 6. Save your work as ‘CCA application form_<Your name>’. Part B Design your own CCA selection form and save it as CCA_<your name>. Use the skills you have learnt to validate the data to be entered.

150

Problem Set 10 – Worksheet 10.2

Challenging Exercise

1. Open the file ‘gym_membership’ and save it as ‘gym_membership_<your name>’. This file contains two parts: Part (A) Renewal of Membership and Part (B) Membership Data. Part (A) Renewal of Membership This part of the file contains the gym membership number of 15 members.

2. In cell C3, enter a function to display today’s date. 3. In cell F3, enter a function to extract the year from today’s date in cell C3. 151

Problem Set 10 – Worksheet 10.2

4. The first character of the data in the Membership no. column represents the gender of the member. That is, F represents a female member and M represents a male member. In the Gender column, enter a function to display F or M for the gender of the 15 members. 5. The second to fourth characters in the Membership no. column represents the year the member was born. The age of the member is calculated by subtracting the year that he was born from the current year. In the Age column, enter a formula that contains functions to determine each member’s age. 6. Complete the two tables ‘Member’ and ‘Age Group’ by using appropriate functions.

Part (B) Membership Data This part of the file contains the membership data of all the gym members.

7. Use the data to create the chart shown below. Replace A.Student with your own name.

Membership Data by A.Student 3500 3000 2500 2000 1500 1000 500 0

Below 21

21 - 30 No. of Male

152

31 - 40 No. of Female

Above 40

Problem Set 10 â&#x20AC;&#x201C; Worksheet 10.3

WORKSHEET 10.3: This is my CCA You are required to create six presentation slides to promote your co-curricular activity (CCA). It is important for you to showcase the best of your CCA to promote to your juniors. Below is a sample layout of the slides. You may use your own layout for the slides if you wish. Slide 1: Insert your school logo or your CCA logo

Your CCA name Bulleted sub-titles

Each sub-title to link to relevant slide

Your name, class

Slides 2 â&#x20AC;&#x201C; 5: Insert your school logo or your CCA logo

Title See suggested list on next slide

Link to first slide

Slide 6: Insert your school logo or your CCA logo

Title Provide link to CCA website

Link to first slide

153

Problem Set 10 – Worksheet 10.3

Suggested list for sub-titles:       

Information about the CCA Day and time of activity Main events/ competitions/ performances Past learning journeys/ excursions Community service Camps Types of training provided

154

Problem Set 11

PROBLEM SET 11: WHATâ&#x20AC;&#x2122;S NEXT FOR ME? Learning Outcomes Students should be able to (a) (b) (c) (d) (e)

Use the spreadsheet to perform data analysis due to changes in data values [4.3.4] Understand and use nested conditional statements [4.4.9] Understand and use Boolean operators: AND, OR [4.4.10] Look up data in tables or lists [4.4.11] Describe problem solutions in short sentences [4.4.13]

Overview After you have graduated from school this year, you will be pursuing studies in different educational institutions. The courses in these institutions will equip you with the necessary skills and knowledge to meet the demands of the various industries. Hence, it is time to source for information on the courses in the different institutions. In this problem set, your teacher will give you information on courses in the different institutions. At the same time, you will be introduced to spreadsheet functions such as AND, OR, VLOOKUP and HLOOKUP and understand the usefulness of these functions in retrieving the information that you need. You will also learn about the nested IF statement and apply the nested IF statement when it is needed. In the first worksheet, you will work on a file that involves Boolean operators (AND and OR) and understand the advantages of using these operators. Then, in the second worksheet, you will be introduced to lookup functions and use them to simplify processes. On top of that, you are also expected to explain your solutions in short sentences so that others can understand them. In both worksheets, you will also use nested IF statement in your solution and will see how it simplifies your solution.

155

Problem Set 11

Data analysis Data analysis is used to analyse and summarise important data so that information can be presented in a clear manner. We can use functions to automate the analysis process and this will save time for users as data can be categorised under meaningful headings. For example, the function COUNTIF can be used to determine the number of male and female students in a class.

By using the function COUNTIF, we can instantly see the number of male or female students in the class. Now, if the data in cell B4 is changed to â&#x20AC;&#x2DC;Maleâ&#x20AC;&#x2122;, the numbers in cells E3 and E4 will automatically change too.

Can you name one advantage of using functions?

156

Problem Set 11

Nested IF statement The function IF checks whether a certain condition is met, and determines its outcome (True or False) based on the result. It is useful when there are only two outcomes, for example, pass or fail, male or female, and go or stop. However, if more than two outcomes are required, then more than one IF function has to be used. This is called the nested IF statement. Let us illustrate with an example below.

Here, the nested IF statement is applied to the allocation of class based on students’ examination marks. There are 3 outcomes, namely, Class A, B or C. Hence, we use two IF functions to check the conditions. For instance, the formula =IF(B2>70,”A”,IF(B2<50,”C”,”B”)) will first check if the value in cell B2 is greater than 70, if it is true, then it will output ‘A’, if not, it will continue to check using the second condition. The second condition checks if the value in cell B2 is less than 50, if it is true, it will output ‘C’, if not, it will output ‘B’. A nested IF statement is used to check several conditions in one statement. Do you know that =IF(B2>=50, IF(B2>70, “A”,”B”),”C”) can get the same results? Do you know why?

157

Problem Set 11

Boolean operators Boolean operators such as AND and OR are considered logic functions in the spreadsheet application software. They check the logic of two or more conditions and output the word ‘TRUE’ or ‘FALSE’ based on the result. The function AND outputs the word ‘TRUE’ only when all the conditions are fulfilled; if not, it outputs the word ‘FALSE’. On the other hand, the function OR outputs the word ‘TRUE’ as long as one of the conditions is fulfilled and outputs the word ‘FALSE’ otherwise. Now, let us look at some examples to understand the Boolean operators better. (a) AND function Suppose you want to participate in a computer game design competition. The rules set by the organiser stated that the participants need to be (1) 15 years old and above; and (2) Studying in a local secondary school. Hence, for you to participate in this competition, you must satisfy both conditions. If any of the conditions is not met, then you will not be allowed to participate in the competition.

Although I am studying in a local secondary school, I cannot participate because I am only 14 years old!

I am 16 years old AND I am studying in a local secondary school. Can I participate?

158

Problem Set 11

Now, let us look at how the above examples and the function AND work in the spreadsheet software. In the screenshots below, the details such as their age and whether they are studying in a local secondary school of eight students are provided. The function AND is used to determine whether both the conditions are met so that the word ‘TRUE’ or ‘FALSE’ is displayed in column E.

‘TRUE’ is displayed when both conditions were met; ‘FALSE’ is displayed when any one of the conditions was not met

We can then use the function IF to change the word ‘TRUE’ to ‘ALLOWED’ and the word ‘FALSE’ to ‘DISALLOWED’ in column F. This is done so that the output makes sense to the user.

Use the function IF to display ‘ALLOWED’ for ‘TRUE’ and ‘DISALLOWED’ for ‘FALSE’

159

Problem Set 11

In the above example, we see that two columns are shown to determine whether the student qualifies for the competition. However, we can combine the two functions AND and IF so that the word ‘ALLOWED’ or ‘DISALLOWED’ is shown immediately without showing the word ‘TRUE’ or ‘FALSE’ in column E. This is called nested functions. Nested functions allow user to write a formula with more than one function. It is usually used when one function is not sufficient to fulfill the condition; hence multiple functions are used to complete the task. An example below shows how nested functions are used to complete the task.

We have combined the two columns E and F in the previous example into one single column

Here, it checks whether the function AND is ‘TRUE’, and if it is true, the word ‘ALLOWED’ is displayed, if not, the word ‘DISALLOWED’ is displayed.

160

Problem Set 11

(b) OR function Suppose you want to join the robotics club in your school. In order to join the club, you need to (1) achieve ‘A’ in Computer Applications; or (2) be a member of robotics club in your primary school Hence, as long as you satisfy one of the conditions, you are allowed to join the robotics club as a CCA.

I achieved a grade ‘B’ in Computer Applications and was not a member of robotic club in my primary school. Can I join?

I got a grade ‘A’ in Computer Applications! Can I join?

Now, let us go back to the spreadsheet application and look at how the function OR works.

161

Problem Set 11

‘TRUE’ is displayed when at least one of the conditions is met; ‘FALSE’ is displayed when none of the conditions are met

Use the function IF to display ‘ALLOWED’ for ‘TRUE’ and ‘DISALLOWED’ for ‘FALSE’

We can also use nested functions to simplify our formula for this example.

Try using the functions OR and IF to write nested functions.

162

Problem Set 11

Table lookup Data is often tabulated so that it is easier for its user to make reference to it. One example is to use a data table. Once the data is arranged in a table form, data can then be looked up easily. Data table In the table below, it shows the pricing of movie tickets for different days of the week, at different timings of the day. It is arranged in columns and rows so that the user can use them to make reference to the cells they are interested in. For example, if someone wants to know how much a ticket costs for a 3 pm movie show on Tuesday, he or she will look for the ‘Tuesday’ row and the ‘Before 4pm’ column, and will retrieve an indicated price of $8.50 for the movie ticket. Days Monday Tuesday Wednesday Thursday Friday Saturday Sunday Public Holiday

Before 4pm $8.50 $8.50 $8.50 $8.50 $9.50 $11.50 $11.50 $11.50

After 4pm $9.50 $9.50 $9.50 $9.50 $11.50 $11.50 $11.50 $11.50

Sneaks Preview $9.50 $9.50 $9.50 $9.50 $11.50 $11.50 $11.50 $11.50

Lookup functions In the spreadsheet software, a table lookup function returns a value either from a one-row or one-column range or from a range of cells selected. There are two useful table lookup functions in spreadsheet; namely VLOOKUP and HLOOKUP. The difference between the two functions is that VLOOKUP searches in the first column while HLOOKUP searches in the first row of a range of cells selected (data table). (a) VLOOKUP function This example uses the function VLOOKUP to determine the dining discount for different types of membership. In cell D8, the formula =VLOOKUP(B8,$A$2:$B$5,2,FALSE)*C8 means that the user wants to look up the data in cell B8 (which is Bronze) in the range $A$2:$B$5 (data table), and retrieve the value in the second column of the data table (which is 10%). After which, that value is multiplied by the value in cell C8.

163

Problem Set 11

An example of VLOOKUP The diagram below shows how this function works.

VLOOKUP(B8,$A$2:$B$5,2,FALSE) This refers to the value that you want to look for in the first column of the data table

This refers to the column number of the data table where the matching value is returned

This refers to the data table where the lookup value is retrieved

164

FALSE refers to an exact matching; whilst TRUE refers to an approximate matching

Problem Set 11

(b) HLOOKUP function The same example above is now demonstrated by using the function HLOOKUP. HLOOKUP looks for a value in the first row of a range of cells selected in a table. Essentially, when the data in the lookup table is arranged in rows, we will use HLOOKUP. In the example below, the table that gives information such as membership type and dining discount is arranged in rows. Hence we will need to use HLOOKUP to look for the dining discount in the table.

An example of HLOOKUP

In cell D5, the formula =HLOOKUP(B5,$A$1:$E$2,2,FALSE)*C5 means that the user wants to look up the data in cell B5 (which is Bronze) in the range $A$1:$E$2 (data table), and retrieve the value in the second row of the data table (which is 10%). After which, that value is multiplied by the value in cell C5.

165

Problem Set 11 – Worksheet 11.1

WORKSHEET 11.1: Boolean operators 1. Circle TRUE if the statement is true or FALSE if the statement is false. a) We can only use up to 2 IF functions in a nested statement.

TRUE / FALSE

b) Boolean operators will return either true or false only.

TRUE / FALSE

2. Open the file ‘boolean_student’ and save it as ‘boolean_<your name>’. After that, follow the instructions below: Proposal A: 2 classes a) In cell E3, use a function to display today’s date. b) In the Temporary Password column, enter a function to generate a 6-digit random number between 100,000 and 999,999. c) There are only two classes for Proposal A. The allocation is based on: I.

Module A is selected – PURPLE class;

II.

Module A is not selected – ORANGE class.

In the Class Allocated column, enter a formula that uses functions to allocate the classes to the students based on their module selections. d) Complete the Allocation Details table for Proposal A. e) Save your work ‘boolean_<your name>_A’. Proposal B: 3 classes f) Copy the list of passwords in the Temporary Password column from Proposal A and paste them as values in the Temporary Password column in Proposal B. g) The pupils are allocated based on the modules selected. The allocation is based on: I.

Modules A and B selected – BLUE class;

II.

Modules A and C selected – YELLOW class;

III.

Modules B and C selected – GREEN class.

In the Class Allocated column, enter a formula that uses functions to allocate the classes to the pupils based on their selection. (Hint: A nested IF statement is needed.)

166

Problem Set 11 – Worksheet 11.1

h) Automatically highlight the cells in the Class Allocated column to: I.

Blue for BLUE class;

II.

Yellow for YELLOW class;

III.

Green for GREEN class.

i) Complete the Allocation Details table for Proposal B. j) Save your work ‘boolean_<your name>_B’. 3. Look at the data for Proposal A again. Change the data in cell D9 to ‘A’. Write down two observations. I.

__________________________________________________________________ __________________________________________________________________

II.

__________________________________________________________________ __________________________________________________________________

4. Why did the data in the other cells change when the data in cell D9 is changed to ‘A’? ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

167

Problem Set 11 â&#x20AC;&#x201C; Worksheet 11.2

WORKSHEET 11.2: Lookup data 1. We need to find the cost for the electives taken by the students, as indicated in the screenshot below.

A lookup function can be used to help us find the cost for each student. In the boxes provided, fill up the instructions to perform the table lookup.

168

Problem Set 11 – Worksheet 11.2

In Questions 2 and 3, you will find the subsidy and allowance granted to some students. The subsidy and allowance granted are based on the total income of the family of these students. 2. Open the file ‘lookup_student’ and save it as ‘lookup_<your name>’. Go to the first worksheet labelled as ‘VLOOKUP’. a) In cell D3, enter a function to show today’s date. b) In cell B4, use a function to extract the year of today’s date. c) In the Tier column, enter a formula to determine the level of subsidy (1, 2 or 3) given to each student. The criteria are: Tier 1 2 3

Family Income $2,000 or less $2,001 to $4,000 $4,001 or more

d) In the Subsidy and Allowance columns, enter formula using functions to look up data in the Family Income Level table to determine the amount of subsidy and allowance provided for each student. e) In the Number per Tier table, enter a function to count the number in each tier. f) Save your work. 3. Now, go to the second sheet labelled as ‘HLOOKUP’ and complete it using the same instructions as Question 2. Save your work. 4. While you are still on the sheet labelled as ‘HLOOKUP’, complete the table below using the information from rows 22 – 25. Student ID Family Income 20130151 $ 900 20130154 $ 1,400 20130158 $ 3,600 20130175 $ 4,000

Tier

Subsidy

5. Change the value of cell B43 to 270.

169

Allowance

Problem Set 11 â&#x20AC;&#x201C; Worksheet 11.2

6. Complete the table below using the information from rows 22 â&#x20AC;&#x201C; 25. Student ID Family Income 20130151

900

20130154

1,400

20130158

3,600

20130175

4,000

Tier

Subsidy

Allowance

7. What do you notice about the values in the table after cell B43 is changed? ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ 8. Which value must you change in order for the values in the Number per Tier table to change? ________________________________________________________________________ ________________________________________________________________________

170

Problem Set 12

PROBLEM SET 12: ITâ&#x20AC;&#x2122;S EXCURSION TIME! Learning Outcomes Students should be able to (a) (b) (c) (d) (e)

Work together on shared resources on the internet [1.4.4] Create customised form for data collection over the internet [1.4.5] Use internet tools to publish web pages [1.4.6] Use the mail merge feature for documents with data from table in spreadsheet [3.4.9] Use the mail merge feature for documents with data from spreadsheet using conditional formatting [3.4.10]

Learning Outcomes It is coming to the end of your secondary school journey. Therefore, it is time for you now to pen down all the good memories you had during your four years of secondary education. One of the ways to capture these memories is putting up information via a website on the internet. In recent years, online portals have become an important means of communication. Information is now stored online and updated easily. Hence, in this problem set, you will learn how to create and update information online as it has become an essential skill for all of us. In the first worksheet, you will design a class website so that you and your classmates can put up information to remember and reminisce the time spent during your secondary school days. After which, in the second worksheet, you will learn how to design an online form and create an online form for easy data collection. You will also understand the usefulness of sharing resources and editing data online. In the last worksheet, you will apply conditional mail merge to select the people from a list to receive a letter. You will also understand how time can be saved by using the conditional mail merge feature.

171

Problem Set 12

Shared resources With the development of cloud computing, people can share resources online. It is now very common for companies and organisations to share their programs and files on their intranet. With this new technology, people can access their files anywhere and the same file can be accessed by many people. Examples of shared resources are documents, photographs, music files and even software programs. However, while these resources are available online, it is also important to secure these resources as they are vulnerable to hackers and viruses. Generally, confidential data should not be shared online. However, if there is a need to share confidential data online, the data should be encrypted or password-protected to prevent unauthorised access.

Form design A good form includes components such as labels, data fields, instructions, messages and validation. A label that includes details such as name, age and gender informs what the corresponding data field means, while data field refers to the part for user to enter the data. Sometimes, it is also necessary to provide instructions so that users know what and how to complete the form. Messages include system error message and/or user-defined error message as defined in Problem Set 10. The messages tell us whether the data input is valid. This brings us to a process called â&#x20AC;&#x2DC;data validationâ&#x20AC;&#x2122;, so as to check that the data is valid and can be accepted in the data field. An example of instructions provided for the user

Labels

Data field

A list is provided for the user to choose

172

Problem Set 12

The previous page shows examples of various components of an online form using spreadsheet application. Data can be captured in the following ways: (1) (2) (3) (4)

Text field - for data to be typed List – select data from a list of choices Check box – select one or more choices Radio button – select one choice from all the choices

Online form An online form is useful as it can be created quickly and can be shared online with anyone with internet access. Usually, such online platform allows the creation of forms and it comes with templates that people can use. Alternatively, you can also customise and create your own form. Once the form is created, you can share with its intended audience and tabulate the data by downloading their responses. All of these are done online, without the need to install any programs into your computer. An example of an online form may include the following options:

Text fields are provided for the user to input data

A list is provided for the user to select

Check boxes are provided for the user to select their option(s)

Radio buttons are provided for the user to select their choice

173

Problem Set 12

Mail merge Mail merge is the automatic process of creating several pages of the same document according to a mailing list. We have discussed mail merge using word-processed table as data source in secondary 3 previously. Now, we will learn how to use the spreadsheet table as the data source.

Spreadsheet table as data source A spreadsheet table may be used as a data source/ recipient list. As with word-processed table, it is important to tabulate the data under meaningful column headings. A meaningful column heading is needed so that it is clear to the user as to which merge field to insert into the merged document. The screenshot below shows a data source/ recipient list containing information such as names, gender, usernames and passwords of seven people.

Column headings

The above shows an example of a data source/ recipient list from spreadsheet application software.

174

Problem Set 12

In another screenshot below, three merge fields are inserted in the document. By doing so, all the data under the column headings Name, Username and Password will be used. Once the fields are inserted and merged, a total of seven letters will be generated.

Conditional mail merge Conditional mail merge is mail merge with one or more conditions. This process is used to choose addressees or recipients who satisfy the condition(s) indicated by the owner. The data source can be either a word-processed or a spreadsheet application table. Suppose you have two files: (1) A name list that consists of both the female (F) and male (M) recipients; and (2) A document that is intended only for the female recipients.

The screenshot above shows a data source/recipient list with all the information. However, in order to create letters for only the female recipients, you have to uncheck the male recipientsâ&#x20AC;&#x2122; checkbox. This will result in only the female recipients being selected. Once it is done, the merged document will be only for the female recipients. 175

Problem Set 12

The screenshot below shows a data source/recipient list with only female recipients.

176

Problem Set 12 â&#x20AC;&#x201C; Worksheet 12.1

WORKSHEET 12.1: Class website You are tasked to design a website for your class using an online tool. An example of a sitemap for the class website is shown below. You may include other information if you want.

Main page

Who are we?

Class committee

Our form teacher

Our subjects and subject teachers

Photo Gallery

An example of a class website Instructions 1. Plan the layout and content of your website using the planning sheet on the next page. 2. Submit your plan to your teacher for approval. 3. Create your class website upon approval. 4. Copy and save your web link. Submit the web link to your teacher so that he/she can view your website. *Remember to make your website public so that it can be viewed.

177

Problem Set 12 â&#x20AC;&#x201C; Worksheet 12.1

178

Problem Set 12 – Worksheet 12.2

WORKSHEET 12.2: Excursion form Your teacher is planning an excursion for your class and wants to know where the class would like to go. You are tasked to design a form to collect data on your classmates’ preferences over the internet. Part A In your customised class excursion form, you will need to include the information as below. (a) (b) (c) (d)

Class Excursion by <Your name> Your name (User will be able to enter their name) Gender (User to choose between Male or Female) Preferred Venue (A list of 3 venues – Gardens by the Bay, Singapore Zoological Garden or Universal Studios Singapore) (e) Other proposed venue (User will be able to enter a different choice) You may wish to include additional information. A screenshot of an example is shown below.

179

Problem Set 12 â&#x20AC;&#x201C; Worksheet 12.2

Part B (a) Add a new web page to your class website designed in Worksheet 3.1. This new web page must be linked to your homepage. (b) Include the customised class excursion form in the new web page and share it with your classmates.

180

Problem Set 12 – Worksheet 12.3

WORKSHEET 12.3: Mail merge In this worksheet, you will perform mail merge and conditional mail merge for a given set of data. Two files ‘details’ and ‘mail_merge_student’ are provided. Follow the instructions below. 1. Open the file ‘mail_merge_student’. 2. In the header, enter your name, class and index number. Part A: Mail merge 3. Make your document into a merge document. 4. Select the file ‘details’ as source data. 5. Insert the fields as shown in the screenshot below.

Name of recipient (underlined)

Reporting time

Session number (underlined)

6. Save your merged document as ‘mail_merge_<your name>’. 7. Merge to a new document and save your merged document as ‘mail_merge_<your name>_all’. Part B: Conditional mail merge 8. Open the file ’mail_merge_<your name>’. 9. Merge to a new document for gender ‘M’ only. 10. Save your merged document as ‘mail_merge_<your name>_conditional’.

181

Revision Exercise I

REVISION EXERCISE I: MOVIE SALES Going to the movies is one of the many activities that Singaporeans do over the weekend. This exercise requires you to compute the amount of money earned by a movie distributor from April to June. You will also create a combination chart for the data from January to March.

182

Revision Exercise I

1. Open the file ‘movie_sale’. The file consists of two parts. Part (a) Movie ticket sale from Apr – Jun and Part (b) Update for Jan – Mar. Save the file as ‘movie_sale_<your name>’. 2. In cell C24, use a function to return today’s date only.

[1] [1]

3. The genre code is represented by the first three characters of the genre. For example, the genre code for Adventure is Adv and Drama is Dra. In the Genre Code column, use a function to fill up cells D4:D22.

[2]

4. In the Ticket Sale column, enter a formula that uses a function to find the ticket price of the different format of movie using the Ticket Price table and multiply it by the number of tickets sold to show the Ticket Sale. [3] 5. Format appropriate cells for ticket prices as currency in Singapore dollars to 2 decimal places. [2] 6. Automatically format all cells in the Ticket Sale column with a blue background if the ticket sale is above $5,000,000. [2] 7. Sort the data in Part (a) Movie Ticket Sale from Apr – Jun in descending order of ticket sales, that is, from the highest ticket sales to the lowest ticket sales. [2] 8. In cell F23, use a function to calculate the total number of tickets sold.

[1]

9. In cell G23, use a function to calculate the total ticket sales, format to zero decimal place. [2] 10. Insert a border and bold the content in cells F23:G23.

[2]

11. In the Movies Released table, use a function to calculate the number of movies for each movie genre. [2] 12. Create a combination chart as shown for the data in Part (b) Update for Jan – Mar. Replace A.Student with your own name. [8]

183

Revision Exercise I

1,000,000 900,000 800,000 700,000 600,000 500,000 400,000 300,000 200,000 100,000 0

$12,000,000

$10,000,000 $8,000,000 $6,000,000 $4,000,000

Ticket Sale

No. of Tickets Sold

Update for Jan - Mar by A.Student

$2,000,000 $0

Movie Genre No. of Tickets Sold

Ticket Sale

13. Save your work.

Algorithm When the process starts, it prompts the user to input a value. If the value is above $5,000,000 

Highlight the cell with a blue background

The value is added to the total. The total is displayed. The process ends.

1. Use your word processor to create a flowchart to represent the algorithm given in the text box. [5] 2. The flowchart must include all the steps in the algorithm and contain appropriate flowchart symbols. [9] 3. Save your file as ‘VALUE_<your name>_<class>_<index number>’.

184

Revision Exercise J

REVISION EXERCISE J: SWIMMING CLUB The monthly membership signup of a swimming club is shown in the screenshot below. You are to complete Parts (A) and (B) using the file provided for you.

1. Open the file ‘swim_club’. The file consists of two parts: Part (A) Membership of Swimming Club and Part (B) Monthly Update. Save the file as ‘swim_club_<your name>’. 2. In cell C4, use a function to return today’s date only.

[1] [1]

3. The membership number consists of 7 characters. Add validation to cells C10:C19, with an appropriate error message, so as to ensure only 7 characters are entered. [3] 185

Revision Exercise J

4. The type of membership is represented by the first letter in the membership number. For example, T136755 represents ‘Temporary’ membership, J134277 represents ‘Junior’ membership and S132884 represents ‘Senior’ membership. In the Type of Membership column, use function(s) to determine the type of membership of each member. [5] 5. In the Date of Expiry column, enter a formula that uses a function to find the expiry date of each membership using the Membership Information table. [Hint: Expiry date = Days + Date Joined] [3] 6. In the Membership Fee column, use a function to determine the membership fee for each member using the Membership Information table. [2] 7. In the Club Enrolment table, use a function to find the number of members for each type of membership. [2] 8. Automatically format all cells in the Membership Fee column with: i.

Green background for $100.00;

ii.

Blue background for $200.00.

[3]

9. Create a combination chart as shown below for the data in Part (B) Monthly Update. Replace A.Student with your own name. [8]

$8,000

$7,000

$6,000 $5,000

$4,000

$3,000

$2,000

$1,000

$0 January February March

April

May

June

July

Month Number of new members

10. Save your work.

186

Amount of membership fee

Membership fee

No. of new members

Monthly Update by A.Student

Revision Exercise J

Algorithm When the process starts, it prompts the user to enter membership number. If the membership number has 7 characters,  Accept the membership number Else  An error message “Invalid number” will appear  Prompt the user to enter membership number Process ends only when a valid membership number is entered. 1. Use your word processor to create a flowchart to represent the algorithm given in the text box. [5] 2. The flowchart must include all the steps in the algorithm and contain appropriate flowchart symbols. [9] 3. Save your file as ‘MEMBER_<your name>_<class>_<index number>’.

187

Revision Exercise K

REVISION EXERCISE K: DINOSAUR WEEK “Dinosaur Week!” is here again and your teacher will bring your class to the exhibition at the Singapore Expo. However, you only have time to visit 1 zone out of the 4 zones A, B, C and D in this exhibition. In this exercise, you are required to complete Part (A) Choose Your Zone and create a combination chart using the data in Part (B) Earnings per Zone.

1. Open the file ‘dino_week’. The file consists of two parts: Part (A) Choose Your Zone and Part (B) Earnings per Zone. Save the file as ‘dino_week_<your name>’. 188

[1]

Revision Exercise K

2. Insert the following data between rows 8 and 9: Name – Benny Foo Yong Huat School ID – 2012M0187 Zone – A

[2]

3. The fifth character in the school ID represents the gender of the student (M represents male and F represents female). For example, 2013F0089 means a female student. In the Gender column, enter a function to determine the gender of all the students. [2] 4. Centralise the data in the Gender column.

[1]

5. Automatically format all cells in the Gender column with: I.

Blue background for M;

II.

Red background for F.

[2]

6. In the Ticket Price column, enter a function to find the price of the ticket for each student using the Zone Prices table. [2] 7. In cell F25, enter a function to find the total amount paid for all the tickets.

[2]

8. Format all relevant cells for the cost of tickets to Singapore currency format and to 2 decimal places. [2] 9. In order to enter the zone, every student is required to have a 3-digit Personal Identification Number (PIN). In the PIN column, enter a function to generate a 3-digit PIN between 100 and 999 for each student. [2] 10. In the Student Number table, enter a function to calculate the number of male and female students going for the exhibition. [2] 11. The total number of characters for the school ID is 9. In the School ID column, add validation to ensure that only 9 characters are entered with an appropriate error message. [2] 12. Create a combination chart as shown for the data in Part (B) Earnings per Zone. Replace A.Student with your name. [8]

189

Revision Exercise K

4000

$70,000

3500

$60,000

3000

$50,000

2500

$40,000

2000 $30,000

1500

Total Sales

Number of Visitors

Earnings per Zone by A.Student

$20,000

1000

$10,000

500 0

$0 A

Number of Visitors

D Total Sales

13. Save your work.

Algorithm When the program starts, it prompts the user to enter ‘M’ for male or ‘F’ for female. If the data entered is ‘M’  Add one to number of male. Else If the data entered is ‘F’  Add one to number of female. Else  Display “Enter M or F”, and  Prompt the user to enter ‘M’ for male or ‘F’ for female again. The program ends.

190

Revision Exercise L

REVISION EXERCISE L: DATA USAGE Many people use their smart phones to check email, surf the internet, or communicate with their friends online. They will have to subscribe to different mobile data plan provided by their telecommunication service provider. Hence, it is important to monitor their monthly data usage so as to do financial budgeting for every month. In this exercise, you are required to complete the columns in Part (A) Data Usage in September and create a combination chart using the data in Part (B) Update for August.

1. Open the file ‘data_usage’. The file consists of two parts. Part (A) Data Usage in September and Part (B) Update for August. Save the file as ‘data_usage_<your name>’. 2. In cell C4, use a function to display today’s date only.

191

[1] [1]

Revision Exercise L

3. Complete the Data Limit (GB) column by using a function to find the data limit based on the type of plan using the Data Plan table. [2] 4. In the Excess Data Usage (GB) column, enter a formula to calculate the excess data usage. [Excess Data Usage = Data Usage – Data Limit]. [2] 5. Enter a formula that uses a function to display the excess data usage as zero (0) if it is a negative value. [2] 6. In the Charges in Excess column, enter a formula that uses a function to find data in the Data Plan table and multiply it with the excess data usage to determine the charges in excess. [3] 7. Format the data in the Charges in Excess column to Singapore currency, 2 decimal places. [1] 8. The customer code consists of at least 9 characters. In the Customer Code column, add validation to ensure at least 9 characters are entered and display an appropriate error message. [2] 9. The second to fifth characters in the customer code represent the year that the customer was born. For example, M20010524 means that the customer was born in the year 2001. In the Age column, enter a formula that uses functions and today’s date to determine the age of the customer. [Hint: Age = Current year – Year born] [4] 10. Complete the cells in the Customer Age table.

[2]

11. Create a combination chart as shown below for the data in Part (B) Update for August. Replace A.Student with your own name. [8]

192

Revision Exercise L

10000 9000 8000 7000 6000 5000 4000 3000 2000 1000 0

$200,000 $175,000

$150,000 $125,000 $100,000 $75,000

Charges

Data Usage

Update for August by A.Student

$50,000 $25,000 $0 Normal

Data Usage (GB)

4G Charges in Excess ($)

12. Save your work. Algorithm

When the program starts, it prompts the user to enter the customer code. The program finds the length of the customer code. If the length is less than 9 characters  Output a suitable error message; and  Prompt the user to enter the customer code again. Else  Accept the customer code;  End the program.

193

Revision Exercise M

REVISION EXERCISE M: LIBRARY A library has a database system to store data of books and members. Once books are borrowed or returned, the system will capture the data. In this exercise, you will work on a library database. You are required to use functions and mathematical operators to find the required data values.

1. Open the file ‘overdue’. You will see the above screenshot which consists of two parts: Part (A) – List of Borrowers and Part (B) – Status Update. Save the file as ‘LIBRARY_<your name>’. 2. In cell C4, use a function to display today’s date only. 194

[1] [1]

Revision Exercise M

3. In the Due Date column (cells E9:E22), enter a formula that uses a function to look up the duration (day) in the Item Table and add it to the data in the Borrow Date column to find the due date. [4] 4. In the Days Overdue column (cells G9:G22), enter a formula that uses a function to find the number of days overdue. For items that are not overdue or not returned yet, show a zero value (0). [3] 5. In the Fines Due column (cells H9:H22), enter a formula that uses a function to find the fines per day in the Item Table and multiply it with the data in the Days Overdue column. [3] 6. The first character of the Borrower ID represents the gender of the borrower. In the Gender column (cells I9:I22), enter a function to complete the column. [2] 7. The length of the Borrower ID should be exactly 9. Add validation to cells B9:B22 to ensure that only 9 characters are entered with the error message â&#x20AC;&#x153;This is not a valid Borrower IDâ&#x20AC;?. [2] 8. Automatically format all cells in the Days Overdue column with blue background if the item is overdue. [2] 9. In the Gender Table, enter a function to find the number of borrowers for each gender. [2] 10. Create a combination chart as shown below for the data in Part (B) Status Update. Replace A.Student with your name. [8]

9000 8000 7000 6000 5000 4000 3000 2000 1000 0

$800 $700 $600

$500 $400 $300 $200 $100 $0

General

Children

Young readers

Quantity Loaned

11. Save your work.

195

Media resource

Fines Received

Quantity Loaned

Loans vs Fines by A.Student

Revision Exercise M

Algorithm When the program starts, it prompts the user to enter the borrower’s ID. If the first character of the ID is ‘M’  Add 1 to ‘Number of Male’  Show the total number of male Else  Add 1 to ‘Number of Female’  Show the total number of female Add 1 to the total number of students. The program ends.

196

Revision Exercise N

REVISION EXERCISE N: SUPPLIES In this exercise, you will prepare an invoice by completing the table in the file “invoice”. The screenshot shows the list of transactions that schools had for the purchase of office supplies. You are required to use functions and mathematical operators to find the required data values.

1. Open the file ‘invoice’. You will see the following display which consists of two parts: Part (A) – List of Transactions and Part (B) – Status Update. Save the file as ‘INVOICE_<your name>’.

[1]

2. In cell C4, use a function to display today’s date only. 197

[1]

Revision Exercise N

3. In the Due Date column (cells E10:E23), enter a formula that uses a function to find the credit term (days) for the different credit type using the Credit Table and add it to the data in the Invoice Date column to find the due date. [3] 4. In the Days Overdue column (cells G10:G23), enter a formula that uses a function to find the number of days overdue. Payments that are not overdue or have not been paid should be shown as zero value (0). [4] 5. In the Late Charges column (cells H10:H23), enter a formula for each transaction that uses a function to find the charge per day in the Credit Table, and multiply it with the data in the Days Overdue column. [3] 6. The first character of the School Code represents the School Type. For example, B0827 represents Boys’ school, G0877 represents Girls’ school and C3210 represents Co-ed school. In the School Type column (cells I10:I23), enter a function for each transaction to complete the column. [2] 7. The length of the School Code is 5. Add validation to cells B10:B23 to ensure that only 5 characters are entered with the error message “This is not a valid School Code”. [2] 8. Automatically format all cells in the Days Overdue column with blue background if the payment is overdue. [2] 9. In the School Type Table, enter a function to find the number of schools (cells H28:H30) for each school type (making reference to the School Type column). [2] 10. Create a combination chart as shown below for the data in Part (B) Status Update. Replace A. Student with your own name. [8]

10000

$5,000

8000

$4,000

6000

$3,000

4000

$2,000

2000

$1,000

$0 Ad hoc

New

No. of Transactions

11. Save your work. 198

Regular

Old

Late Charges Collected

Late charges

No. of transactions

Transactions vs Charges by A.Student

Revision Exercise N

Algorithm When the program starts, the number of days overdue is calculated using the formula: overdue days = current date – due date. If the item is overdue  Highlight with a blue background  Calculate fines using the formula: Fines = 0.30 * overdue days  Display the fines The program ends.

199

Revision Exercise O

REVISION EXERCISE O: CLASS ALLOCATION You will be taking your ‘N’ level examinations soon. Your teacher is arranging for extra lessons and wants to categorise the students into different groups based on their English language and Mathematics results. You can help your teacher by completing the columns in (A) Class List in the file provided. After which, you will use the data in (B) Past Data to create a combination chart for your teacher.

1. Open the file ‘class_allocation’. The file consists of two parts: Part (A) Class List and Part (B) Past Data. Save the file as ‘CLASS_ALLOCATION_<your name>’.

200

[1]

Revision Exercise O

2. The students are assigned to group 1, 2 or 3 based on their performance in English Language and Mathematics results. The passing mark for each subject is 50%. The criteria used are: I.

Group 1 – Passed both English Language and Mathematics

II.

Group 2 – Passed either English Language or Mathematics

III.

Group 3 – Failed both subjects

In the Group Allocated column, use a function to determine the group each student is assigned to. [This part requires nested IF with AND and OR functions.] [6] 3. Complete the Day(s) of Lesson and Time of Lesson columns, with a formula that uses a function to find the above information using the Lesson Information table. [5] 4. In the Temporary Password column, use a function to generate a 4-digit random number between 1000 and 9999 for each student. [2] 5. Automatically format all cells in the English Language column with red text for students who have failed the subject. [2] 6. Sort the list of students in ascending order of group numbers, from 1 to 3.

[2]

7. In the Allocation Data table, use a function to determine the number of students in each group. [2] 8. The school decides to allocate 1 teacher to 4 students. In the Allocation Data table, use a function to determine how many teachers will be allocated to each group. [Hint: Number of teacher(s) = Number of students / 4; any decimal answer should be rounded up.] [2]

201

Revision Exercise O

9. Create a combination chart as shown below for the data in Part (B) Past Data. Replace A.Student with your own name. [6]

Percentage (%)

PastData by A.Student 100 90 80 70 60 50 40 30 20 10 0 English

Mother Tongue

Mathematics

Computer Applications

Subject Percentage Passes (%)

Percentage Distinction (%)

10. Save your work. Algorithm

When the program starts, it checks the student’s results for English and Mathematics. If the student passes both English and Mathematics  Allocate the student to Group 1  Add one to number of students in Group 1  Display number of group 1 students Else  Check student’s results for other subjects The program ends.

Sample Paper – Paper 1

SAMPLE PAPER – PAPER 1 Answer all questions. The number of marks is given in brackets [ ] at the end of each question or part question. You are advised to spend about 30 minutes on Section A (Questions 1 – 20). Section A Answer all questions.

Which of the following is an output device? A

Monitor

Mouse

Web camera

Keyboard

[1]

Which of the following is a malicious program? A

RAM

Firewall

Spyware

Hard disk

[1]

A video created on a computer can be measured in ________________________. A

dots per inch

dots per second

frames per inch

frames per second

[1]

What should be done to prevent sudden loss of data in your computer? A

install firewall

install anti-virus software

backup regularly

use a password

[1]

203

Sample Paper â&#x20AC;&#x201C; Paper 1

To prevent others to log into your personal account, you should use a __________________. A

firewall

password

backup

screen protector

[1]

What is the easiest method to meet several people from around the world at one time? A

Fax

E-mail

Video-conference

Tele-conference

[1]

What is not the advantage of using internet for banking? A

Pay bills conveniently

Check bank account instantly

Withdraw money easily

Transfer funds conveniently

[1]

The process of removing the unwanted segment of an audio clip is called _______________. A

trimming

inserting

exporting

importing

[1]

Which of the following cannot be imported into a word document? A

Text

Chart

Picture

Video

[1] 204

Sample Paper â&#x20AC;&#x201C; Paper 1

10 Which of the following statements is true for document header? A

The content will appear at the top of each page.

The content cannot be edited.

Graphic cannot be imported to header.

What appears in the header will also be in the footer.

[1]

11 When is spreadsheet commonly used? A

To edit webpages.

To design the layout of a magazine.

To create database of customers in a bank.

To calculate the average examination marks of students.

[1]

Use the spreadsheet table below to answer questions 12 and 13. It contains information about fruits.

12 What formula would you use in cell D9 to display the total cost for all the fruits? A

=SUM(D4,D8)

=SUM(D4:D8)

=COUNT(D4,D8)

=COUNT(D4:D8)

[1]

205

Sample Paper – Paper 1

13 What will be displayed in cell E4 if the following statement is placed in it? =IF(C4<100,"Station A",IF(C4<150,"Station B",IF(C4<200,"Station C","Station D"))) A

Station A

Station B

Station C

Station D

[1]

14 Mr Chua accidentally typed the word ‘hear’ instead of ‘here’ in a document a number of times. Which word-processing feature can he used to correct the mistake? A

Find and replace

Spell check

Copy and paste

Align text

[1]

15 What can be used to show the sequence of content in a multimedia presentation? A

Textbox

Flowchart

Storyboard

Video

[1]

16 White space is used in a multimedia presentation. What is the purpose of using white space? A

To add more graphics

To enhance the visual appeal

To hide the content from the audience

To add speaker’s information

206

[1]

Sample Paper – Paper 1

17 Links can be used to navigate a multimedia presentation. Which of the following is an example of internal link? A

Another slide in the presentation

Website of a government agency

A video on the internet

Email address

[1]

18 Which of the following is an element of a computer game? A

Textbox

Video

Flowchart

Graphics

[1]

19 Relational operators are often used in game design. What is the purpose of using relational operators? A

To determine the error in coding.

To make comparisons.

To evaluate the game.

To allow interaction between user and the game.

[1]

20 HIT is a variable used in a game. The following script is used within the game. SET “HIT” TO 0 REPEAT UNTIL “HIT”= 5 IF <SPACE KEY PRESSED?>, CHANGE “HIT” BY 1 LOOP again When the game is played, the space key is pressed 4 times, followed by the enter key. What is the final value for “HIT”? A

[1]

207

Sample Paper â&#x20AC;&#x201C; Paper 1

Section B Answer all questions. 21 Memories are classified into volatile memory and non-volatile memory. In the table below, circle Volatile if the memory is volatile or Non-volatile if the memory is nonvolatile. Type of memory Random access memory

Circle the correct answer Volatile / Non-volatile

Read only memory

Volatile / Non-volatile

Memory stick

Volatile / Non-volatile

Hard disk

Volatile / Non-volatile [4]

22 Computer operations include input, process and output. For each of the examples in the table below, indicate if the operation is an input, a process or an output by ticking in the appropriate column. Operation Saving a file on a thumb drive

Input

Process

Output

Enter memberâ&#x20AC;&#x2122;s data into a database Running an anti-virus program on the computer Printing a document on a printer [4]

208

Sample Paper â&#x20AC;&#x201C; Paper 1

23 A computer network connects two or more computers together. a) Write down two advantages of a computer network. I.

__________________________________________________________________ __________________________________________________________________

II.

__________________________________________________________________ _______________________________________________________________ [2]

Two examples of computer network are Local Area Network (LAN) and Wide Area Network (WAN). b) What is the difference between a LAN and a WAN? _____________________________________________________________________ _____________________________________________________________________ __________________________________________________________________ [2] 24 Sound is an example of media elements that are used in a web page. List three other media elements that can be used in a web page. I.

__________________________________________________________________

II.

__________________________________________________________________

III.

_______________________________________________________________ [3]

209

Sample Paper â&#x20AC;&#x201C; Paper 1

25 The number of orders and profit of Number 1 Catererâ&#x20AC;&#x2122;s Services are shown in the spreadsheet below.

Circle True if the statement is true or False if the statement is false. a) The table is sorted in ascending order of the profit.

True / False

b) The chart shown is a line chart.

True / False

c) A TRUE return will appear if cell B9 contains =OR(B3>250, B4>250).

True / False

d) It is impossible to hide the legend in the chart.

True / False

e) To find the average profit, we can input =AVERAGE(C3:C8) in cell C9.

True / False [5]

210

Sample Paper â&#x20AC;&#x201C; Paper 1

26 Drawing B was produced from Drawing A.

Drawing A

Drawing B

From the list below, circle three features of the graphics software that may have been used. duplicate

rotate

shade

skew

resize

delete [3]

211

Sample Paper â&#x20AC;&#x201C; Paper 1

27 a) A part of the word-processed document is shown below.

This is an example of a type of text alignment. To understand what the purpose of text alignment is, you will need to refer to your textbook.

i) Name the type of text alignment. ______________________________________________________________

[1]

ii) Give two examples where this type of text alignment is used in a word-processed document. I.

_______________________________________________________________

II.

___________________________________________________________

[2]

b) Mail merge is used in word-processed documents. What is mail merge? _____________________________________________________________________ __________________________________________________________________ [2] 28 E-learning is commonly used in education. a) Name two advantages of using internet for e-learning. I.

__________________________________________________________________

II.

_______________________________________________________________ [2]

b) Name two disadvantages of using internet for e-learning. I.

__________________________________________________________________

II.

_______________________________________________________________ [2]

212

Sample Paper â&#x20AC;&#x201C; Paper 1

29 Phishing is an example of computer crimes. a) Name two ways to prevent phishing. I.

__________________________________________________________________

II.

_______________________________________________________________ [2]

b) Name two other examples of computer crimes. I.

__________________________________________________________________

II.

_______________________________________________________________ [2]

213

Sample Paper – Paper 1

30 The flowchart below shows part of a computer program to find Body Mass Index (BMI). Complete the flowchart with the letters representing the statements. [4] A B C D

Calculate BMI BMI more than 27 Enter your weight in kg and height in cm Display “You are overweight, please attend the exercise programme.”

Start

Yes

End

214

Sample Paper â&#x20AC;&#x201C; Paper 2

SAMPLE PAPER â&#x20AC;&#x201C; PAPER 2 Scenario: Water is precious. In this exercise, you will help to promote water conservation through a newsletter and a set of presentation slides. In Task 1, you will have to create a logo for water conservation. Then for Task 2, you will need to create a one-page information sheet on water shortage. The file WATER contains the information. Finally, in Task 3, you will create five slides for a talk on water conservation. The text for the slides is provided in the file PRESENTATION. The slides will link to the information sheet created in Task 2. Task 1 Media Elements [21 marks] Create the logo: 1. Use computer software to draw the logo with a transparent background. Water tap [6]

Two water droplet with gradient fill effect [4]

Curved text [4]

2. Fill the logo with appropriate colours.

[2]

3. Make sure that the objects are drawn proportionally to one another.

[2]

4. Order the objects as shown above.

[2]

5. Save the logo as a bitmap graphic TAP_<your name>.

[1]

215

Sample Paper â&#x20AC;&#x201C; Paper 2

Task 2 Document Processing [28 marks] Create the newsletter: 6. Use computer software to edit the file WATER. The finished document should look like this:

Page Layout and Contents 7. Set the page size to A4 and landscape orientation.

[2]

8. Insert your name and index number in the header, left aligned.

[1]

9. Change the title Turn it off to WordArt at the top of the newsletter, centred across the page.

[3]

10. Format the newsletter to 3 columns.

[1]

11. Format the text as fully justified and set the line spacing to 1.5 lines spacing.

[2]

12. Bold the three sub-headings: Water sources; Water conservation; Do your part.

[2]

13. Indent the first line of all the paragraphs by 2 centimetres.

[2]

14. Find and replace the word consumption to usage throughout the document.

[2]

216

Sample Paper – Paper 2

15. Insert the image created in Task 1 at the end of the document.

[2]

16. Format the document such that it falls within one page.

[1]

17. Include a border to your newsletter.

[1]

18. Save your document as MYWATER_<your name>.

[1]

Mail Merge 19. Make your document into a merge document.

[2]

20. Select the file ORGANISATION as source data.

[1]

21. Insert the following merge fields at the footer, left justified. «Name» «Designation»

[2]

22. Save your merged document as MERGEDOC_<your name>. 23. Merge to a new document for designation “Director” only. 24. Save your merged document as DIRECTOR_MERGEDOC_<your name>.

217

[3]

Sample Paper – Paper 2

Task 3 Multimedia Communication [21 marks] Create the slide presentation: 25. Use the text from PRESENTATION to create 5 slides. The slides should be like this: Slide 1: Logo from Task 1

[14]

Title of presentation

Name of presenter: <Your name> Link to appropriate slides

   

Water Sources Water Conservation Do Your Part Contact Us

Slide 2 to 4: Logo from Task 1

Title of slide

Bulleted list Create hyperlink to first slide Link to Slide 1

Slide 5: Logo from Task 1

Title of slide

Link to newsletter created in Task 2

Bulleted List

Link to MYWATER_<your name>

Create hyperlink to first slide

Link to Slide 1 26. Ensure that all slides are easy to read and all objects can be seen easily. 218

[3]

Sample Paper â&#x20AC;&#x201C; Paper 2

27. Ensure that the repeated logo and the titles of the slides are in the same positions on all slides.

[4]

28. Save your slides and name your file MYPRESENTATION_<your name>.

219

Sample Paper â&#x20AC;&#x201C; Paper 3

SAMPLE PAPER â&#x20AC;&#x201C; PAPER 3 Task 1 Spreadsheets [28 marks] Scenario: The National Environment Agency monitors and collects data on complaints about the noise/fumes/cleanliness of public places. In the file provided, you are required to use functions and mathematical operators to find the required data values in Part (a). Then, you will need to plot a chart using the data table provided in Part (b). Setting up the spreadsheet: 1. Open the file NOISE. You will see the following screenshot, showing two parts: (a) Source of Complaints, and (b) Past three years. Save it as COMPLAINTS_<your name>. [1]

2. In row 35, replace A.Student with your name.

[2]

3. Sort the Type table in ascending order of item type.

[2]

220

Sample Paper â&#x20AC;&#x201C; Paper 3

4. Enter the following into row 22: Source: Places of worship Item Type: Others Number: 12

[2]

5. In the Type Code column, enter a function to extract the first letter of the corresponding data value in the Item Type column. [2] 6. In the Fine Payable column, enter a formula that uses a function to look up the Fine in the Type table and multiply it by the Number. [3] 7. Format the appropriate columns in the Source and Type tables to currency format with no decimal places. [2] 8. Automatically format the cells in the Type Code column with a green background if the code is F. [2] 9. Change the data of cell C20 to Food shops.

[1]

10. In the Total column, enter a function that will return the total for each item type. [2] 11. In the Total Fines column, enter a formula to calculate the fines for each item type. Save your file. [2] 12. Alter the view of the spreadsheet to display formulas. Save your file as FORMULA_<your name>. [1] 13. Alter the view of the spreadsheet not to display formulas.

221

Sample Paper â&#x20AC;&#x201C; Paper 3

Plotting the chart: 14. Use the data in the Complaints table for the past three years to create the combination chart shown below with a chart title. The Fines is plotted as a line chart. [6] 15. Save your file as CHART_<your name>.

222

Sample Paper – Paper 3

Task 2 Game [28 marks] Create Game: 1. You are provided with the following files in your folder:  Baby duck  Clapping  Family  Pond 2. Choose the file Pond and add the baby duck and family.

Family

Baby duck 3. Save your work as DRIFT_<your name>.

[4]

4. You are to create a game where baby duck has to find his way back to his family safely without touching the driftwood. (a) When the game starts, (i) The duck family will appear at the top left-hand corner of the screen; (ii) Baby duck will appear at position (137, -160) near the bottom of the screen; (iii) Baby duck will face toward the right; and (iv) The score is set to 100 points but not shown on the screen. [7] 223

Sample Paper – Paper 3

(b) Baby duck is moved up, down, left and right with the arrow keys on the keyboard. [4] (c) When baby duck touches the edges of the screen, he will bounce back.

[2]

(d) When baby duck touches a piece of driftwood, 50 points are deducted from the score. [2] (e) When baby duck reaches his family, the text “I am happy to find you!” is displayed for two seconds, the “clapping” sound is played and the game ends. [5] (f) The game also ends when the score is zero, and the text “Game Over” is displayed for one second. [3] 5. Save your work.

[1]

Task 3 Algorithm [14 marks] When the game starts, score is initialised to 100 and baby duck is checked to see if he has touched any driftwood. If baby duck touches a piece of driftwood, score = score - 50. The game stops when the score is zero, otherwise baby duck continues to move.

1. Create a flowchart to represent the algorithm given in the textbox.

[5]

2. The flowchart must include all the steps in the algorithm and contain appropriate flowchart symbols. [9] 3. Save your flowchart as a word-processed document DUCK_<your name>.

224

NOT FOR SALE