SECTION B - Short answer questions

Answer all questions in the spaces provided.

An accounting company wants to raise its profile by developing a website that will provide a secure login and allow existing clients to check their own tax returns.

a.   Name one software component that is required to set up the website. Describe its function. 2 marks

• webpage editor - creates and edits web pages
• TCP/IP protocols - to communicate across the internet
• web server software - to manage the web server that accepts requests for webpages and delivers the site's webpages to visitors
• browser - to test and manage the site
• FTP client - to send files to the web server

Many correct responses identified well-known software, such as Dreamweaver or Notepad, to receive a mark. Explanations that gained a second mark included 'allowing users to create links using http' or 'helping users to create web pages that can be viewed or tested'. Students who named less well-known software and explained how it could be used to create a website also received full marks.

Average mark was 0.9

b.   Name one hardware component that is required to host the website. Describe its function. 2 marks

• web server - to store the site's files
• cables (fibre optic or twisted pair) - to send data along between the visitor and the web server
• modem - to convert digital data to/from analogue data for transmission
• router - to route Ethernet packets between the visitor and the webserver

One mark was allocated to naming a hardware component and one mark for a description of its function. The most common student responses were 'web server' or 'hard disk' as the hardware component. Other accepted answers included any one of Network Interface Cards, modems or routers (network hardware). Acceptable descriptions of the functions for web servers, hard disks and network hardware included 'stores applications', 'stores data files' and 'provides communication to the network'.

Average mark was 1.3

c.    The company would also like to set up an online community where users can discuss sensitive tax issues. Explain what should be considered when deciding if the website should be closed or open. 2 marks

• Assuming that the users going to be giving personal or sensitive information that the whole world should not be free to see, a closed site is necessary.

There's not a lot to be considered. The question says the issues are sensitive and obviously must not be open to everyone to see!

Most responses to this question identified either 'privacy of client data as a legal obligation' or 'confidentiality of client data as part of ethical business practice'. Typically, these students argued that the website should be closed and accessed only by registered clients using a login identity and password. Students who argued for an open website and explained that they wanted to encourage more business or have more open discussion were also rewarded.

Average mark was 1.1

Discuss the possible effects on data integrity if an employee does not follow the company's security procedures for shutting down their computer at the end of the day. 3 marks

• Any passing person could access the computer to steal, read or damage sensitive or valuable data.
• If files are left open, backup software might not be able to read the files and back them up, leading to potential data loss in case of a data disaster.

Students had to identify the possible consequences of a user's action. Responses that described possible scenarios and likely consequences for the company's information system received marks. For example, one effect is that anyone else in the office can access at least the network and possibly private client data. This means that the business has not met its legal obligations. Another effect is that incorrect shutdown or power-off can cause data loss because the most recent activity is not saved or client files are corrupted. Another effect is that changes to data might not show up because the intruder is wrongly identified as a legitimate user. As a consequence, the company could lose customer data and not know it has gone or been stolen, or not be able to identify who last accessed a file.

Average mark was 1.7

A teacher records student percentage results on tests and calculates averages, as shown in the table below.

Indicate with a tick one software type that your answers will refer to.

• relational database management system (RDBMS)
• spreadsheet(SS)
  

a.    State how the teacher can list the students from highest average to lowest average. 1 mark

Sort them by their average value.

This is a year 12 question??

Students who chose RDBMS typically stated 'Sort Average field Z to A'. Students who chose spreadsheet software typically stated 'Sort the Average column Z to A'.

Average mark was 0.6

b.   Recommend an effective format that the teacher could use to display each student's average mark in a report. Justify your answer. 2 marks

Show them as percentages, maybe with a decimal place or rounded to the nearest integer. Right-justify the numbers, or align them on their decimal points. The percentage sign is a clear indicator to the reader what the value is. The rounding or removal of decimal places makes the key information (the integer part) easier to find and read.

Many students struggled with this question. Correct responses included 'displaying in bold or red font with the label 'Average' and displaying 'as a graph so that parents can compare individual marks to the average'. Marks were also awarded to answers that focused on the number of decimal places appropriate for a report. Examples of student responses included
 there should be no decimal places because the data has no decimal places
 round the average to whole numbers because it makes it easier for parents to read
 round the average to two decimal places because people are used to reading this format.

Average mark was 0.5

c.    List the steps in a procedure that the teacher could use to test the accuracy of the averages provided by the software. 3 marks

1. Select a random student.
2. Calculate the student's average manually (add the test marks and divide by 3)
3. Compare the manual calculation with the answer calculated by the software.
4. If they are the same, celebrate with champagne (or a fine Chardonnay). Perhaps have some duck liver paté spread on a nice water cracker. If not, fix the formula.

Really - this is year 12 level material? My year sevens would be bored by the simplicity of this stuff.

Responses that covered the three steps in a testing procedure received full marks. These steps are: 'create test data', 'use a calculator or desk check' and 'compare manually calculated answers to software answers' or 'get another person to check manual answers'. Students handled this question reasonably well.

Average mark was 1.5

Toetappers is an organisation that sells music online.

a.   Name a design tool that is used to represent the appearance of a website solution where music can be sold and downloaded. 1 mark

A screen mockup.

Responses that covered the three steps in a testing procedure received full marks. These steps are: 'create test data', 'use a calculator or desk check' and 'compare manually calculated answers to software answers' or 'get another person to check manual answers'. Students handled this question reasonably well.

Average mark was 0.6

b.   Describe a strategy for evaluating the extent to which the website solution meets the organisation's needs. 2 marks

• Assuming the needs are primarily financial, they should compare annual profits before and after launching the site.
• They could look at website logs to see how many people are visiting the site.
• If they wanted to raise their public profile or customer service, they could survey visitors with a web-based poll.

The question is not really fair, since it does not tell students what the organisation's needs are. Students have to guess.

High-scoring responses included two steps. The first was to identify what would be measured and the second was a comparison over time. The most frequently accepted responses were
 number or duration of visits to the site in one month compared to those in a previous month
 trends in sales of online music
 check sales against criteria set during the design of the site.

Average mark was 0.7

c.    Recommend a type of on-screen user documentation for new users of the website solution. Justify your choice. 2 marks

• A quick start guide - quickly give just enough basic information for the user to get started
• A tutorial - to teach the more stupid user how to make a purchase
• Content-sensitive help - to give relevant help based on the user's current activity
• The other option, a user manual, would be too detailed and unnecessary for a simple website.

There was a variety of accepted responses to this question. Those that were accompanied by a reasonable, plausible explanation of how the on-screen user documentation applied to new users purchasing music online achieved high marks. Typical examples were
 content-sensitive help automatically shows users how to solve a current problem when purchasing music
 FAQs provide answers to questions that are commonly asked by new users
 online tutorials provide step-by-step instructions for buying music online.

Average mark was 1.2

d.   Name legislation that could apply to this website. 1 mark

• Copright Act, 1968
• Privacy Act, 1988
• Spam Act, 2003

Well, the question said "name it". So, you just name it. Don't explain it or justify it. Just name it. It seems a pretty silly way to find out if students know why they're choosing that legislation.

Students received a mark for stating the Copyright Act 1968 or the Privacy Act 1988.

Average mark was 0.4

a.    Identify a technique that can be used to acquire data for a voting or polling website. 1 mark

• Use a web form to let users can enter their preferences.

I mean, what else can one say? What are they after, I wonder...

'Online survey' and 'form' were the most frequently accepted responses to this question. This question related directly to a key skill in Unit 3, Outcome 2, Task 2 (techniques for acquiring data via websites), and the key knowledge for this Area of Study specifically mentions 'voting' as a reason why individuals and organisations supply data via websites.

Average mark was 0.4

b.   Explain how the website designers could protect the rights of voters. 2 marks

• Keep the voters' identities secret.
• Give them a username and password to prevent unauthorised imposters voting as if they were the user.
• Encrypt store voting data to prevent unauthorised access.
• Not use the data in any secondary manner without the voters' consent.
• Not spam the voters.

What rights? Rights under the Privacy Act? Rights under the Human Rights and Responsibilities Act? Spam Act? All of the above? It's all a bit vague.

Most students answered this question well. A range of responses was accepted for this question, including

 allow voting only by users with usernames and passwords to ensure users vote only once
 to protect voters' privacy, encrypt any data after it is acquired so that only authorised people can read it
 provide anonymity by removing names or aggregating data and only count totals.

Average mark was 1.1

Identify an online social networking website and explain how the needs of its users influence the nature of the website. 4 marks

One guesses the examiner is waiting for everyone to identify Facebook...

• Users want to exchange information with friends, so the site needs to keep track of users' relationships with other users.
• Users want privacy so the site needs to password-protect access to personal information and let users specify how much privacy they require.
• Users want quick updates and responsiveness so the site must be engineered to update quickly and deliver data without delay.
• Users want protection from abuse, so the site needs to set down rules and enforce their observance, and deal with rulebreakers.
• Users want to see a lot of really dumb comments being made by uneducated bigots, so the site needs to allow Americans in.

Students had to identify a social media website and respond to a statement about the relationship between users and the website. The question was very well answered. Most frequently, students identified Facebook and then went on to describe situations in which users' needs had resulted in a range of additional functions being added to the website. For example
 chat was originally for communication only between college students, but registration was opened to all when complaints indicated that users found this too restrictive
 users wanted to read other people's profiles and share photos, so Facebook has, over time, created a space for personal profiles where you can publicly or privately share photos or post comments on your profile.
Some students referred to a 'growing democratisation' of the social media website and gave a range of examples to support this claim. For example
 it has responded to user criticism of changes to the privacy policy by allowing users to vote on whether
proposed changes should be implemented
 users can choose the level of security they want and speak only to people they know
 users can invite new friends to join the conversation
 users can track the activity of existing friends.

The few students who argued that changes were brought about by commercial needs and who provided a coherent argument with examples to illustrate their claim also received marks.

Average mark was 2.5

A mock-up for the Southern Bird Spotters organisation's website is shown below. Members of this organisation observe birds in the temperate southern regions of Australia. The organisation wants to encourage members to use a range of online communication features to present and exchange information, and share a collective identity.

a.    Select two of the following design elements. On the mock-up, show with a label where these elements have been applied. 2 marks

• contrast - the "Sighting of the year"'s white text on grey background
  
• orientation - vertical alignments of the left-hand boxes and right-hand pictures
  
• proportion - the text in the heading is bigger than the other text

The examiners' answer annotate the picture in the exam. Arrows pointed to the relevant area...
"Southern Bird Spotters" logo in map of Australia: PROPORTION - larger font for the title.
Adelaide region - CONTRAST - black on grey or white.
Vaguely under the "This Week" item and left of the "Sighting of the year" item... not really clear what it was pointing at - ORIENTATION - caption has horizontal layout.

The majority of students scored full marks on this question, though some did not annotate the mock-up. Those who correctly labelled any two of the many examples available received full marks. The mock-up above shows examples of correct answers.

Average mark was 1.4

b.   Briefly explain how these two design elements have been used to influence the appearance of the website. 2 marks

• contrast - white text on grey stands out from the other black text on white background to make it visually distinctive and prominent
• orientation - the vertical columns makes the site - umm - neat and attractive?
  
• proportion - the bigger heading text makes it more prominent and easy to find.

Most students could describe features on the website to illustrate how two of the three design elements were used to enhance the appearance of the website. For example
 contrast – a white or grey background is used with black text or a black background with white text so that images and words stand out and appear more vibrant
 orientation – the shape containing the links is in portrait format because it is a list and the citing of the year is in landscape format because the caption text is on two longer lines
 proportion – font and image sizes have been used to create a hierarchy, where larger elements, such as the title, are more important.

Average mark was 1.4

c. Select two of the following design elements and explain how they have been used to enhance the functionality of the website. 4 marks.

• appropriateness - the complete lack of nude ladies means male visitors will not be distracted and can concentrate on using the site's functions. Perhaps the copyright statement may also be considered appropriate? It's appropriate that a birdwatcher site has a picture of binoculars? This isn't an easy one...
  
• navigation - the navbar makes it easy to find information on the site.
  
• relevance - learning how to upload photos is relevant to the site's purpose, and helps the site gather resources and be more functional.

Most students chose to explain how navigation and relevance had been used to enhance the functionality of the website.
Typical responses include
 navigation – major links are underlined or are in a box on the left; the login link is in a prominent position. Together, these let members access the login screen and then choose what they want from the link list.
 relevance – images are of birds found in the region shaded in the map or indicated in the club's name.
This is what the club is about – birds sighted in the southern parts of Australia.

Very few students chose to explain how appropriateness influenced the functionality of the website. Those who did suggested that the club takes birds and knowledge about birds seriously. Typical responses include
 the images are all of birds and are of good quality
 there is no bad language, no jokes and no images of dead birds.

Average mark was 2.7

d. Discuss the extent to which this website meets the organisation's aim to encourage members to present and exchange information, and share a collective identity. 4 marks

• The "upload picture" tutorial enables users to present and exchange relevant images.
• The "chats" link lets them develop their collective identity by socialising and meeting each other.
• The login prevents non-members access features that help define the solidarity and exclusivity of being members.
• The calendar can help them plan and participate in collective events.
• The blogs let members present information.
• The wikis let them collaborate on information gathering.
• All of these features taken together would significantly contribute to knowledge sharing and a feeling of community.

Students were required to address the two aims stated in this question to receive full marks.
With regard to the first aim, many students identified features that would allow members to share and exchange information. For example
 users can share knowledge by clicking on links to blogs, wikis, chats, a photo gallery and an events calendar
 members can discuss birds and find out where some species are located
 new users can find out how to become members.
Fewer students were able to address the second aim – share a collective identity. Typically, appropriate student responses referred to members 'identifying with the logo', 'using the image of binoculars' or 'using a map' to encourage a collective identity.

Average mark was 2.2

Ally knows that her best friend Jack, who is a brilliant programmer, has access to other students' passwords. Jack uses these passwords to access the other students' print accounts to print his own assignments on the school network. He has offered to print Ally's assignments. This would save Ally at least $50.

State an ethical dilemma that Ally might have and discuss what she should consider when resolving this issue. 3 marks

• If she agrees to Jack's offer, she would benefit from saving money but she would be stealing money from other students, which is unfair to them.
• To resolve the issue, she needs to consider the effect on other students if she accepted Jack's offer, compared to the financial benefit to herself.
• She should also consider the possibility of Jack and her being punished if they were discovered breaking the rules.
• She should consider whether she should try to convince Jack that he should also not access other students' accounts.
• She should consider what she would do if Jack continued breaking the rules: she could inform the authorities, but that would raise another dilemma because she would have to balance the wellbeing of her best friend against that of the school and its students.

This question was well answered. Many students framed questions that Ally might ask herself. For example
 is it fair to use other students' money?
 is her loyalty to Jack more important than to other students or to the school?
 how would I feel if it was my money that is being used by someone else?
High-scoring responses typically started with an introductory statement about Ally's problem, identified a likely consequence and recommended an option for action. For example
 Ally needs to realise Jack is stealing … ask herself if the school's reputation will suffer … she should talk to Jack and ask him to stop
 Ally needs to decide if her friendship with Jack is more important than … doing the right thing … she should help Jack to think about others.

Average mark was 1.9

The following data has been collected and stored by an elderly citizens' club. Drivers who transport members of the club are paid an hourly rate according to the number of years they have worked and the type of car they drive - $10 for a small car and $20 for a large car with wheelchair access. You have been asked to set up a database of members and drivers so that the information can be accessed online.

Normalise this data and explain how each normal form (1-3) ensures the integrity of data. Use the space below and over the page. 6 marks

[1.5 empty pages provided!]

To achieve 1NF, I needed to split up multiple data items that were in a single cell: member title, firstname, surname; driver firstname, surname. This lets us search, sort and report on the data items individually rather than as a unit. The member and driver tables needed unique IDs to avoid potential confusion between different people with the same names. These IDs are used as key fields for their tables. 1NF also requires each field to refer to the key, but in this table there is no unique key field: you'd have to use membername+drivername as a combined key. Adding key fields to the members and drivers tables avoids that: now, each field in those tables relates directly to the key in that table. e.g. a member's address and title are both connected to that member's ID.

Using a combination of fields as a key is A Bad Idea, because it allows 2NF failure. 2NF is never going to be a problem if you create a unique key field in each table you create. 2NF says that each field in a table must refer to the whole key, not just part of it. The original table fails because if we use membername+drivername as the key, the member address only relates to the membername, not the drivername. And the driver's Car rate only relates to the drivername, not the membername. This violates 2NF. Creating the separate key fields in each table avoids this problem.

Having only the member and driver table was a partial solution, but it had its limitations: one driver could help many members, but a member would be tied to a single driver. It would be impossible for a member to use different drivers on different days without using a many:many relationship, and RDBMS do not allow many:many relationships!

Another problem is that a history of trips would not be possible to produce since there is no record of individual trips: the driver's payrate is just increased after each trip. This is unacceptable.

To solve these problems, a "line table" is used to avoid having a many:many relationship. It acts as an intermediary between the two main tables. One record in the trips table records the member, the driver and the trip cost. The calcTotalFee field is a calculated field equal to Drivers_Table::numYearsWorked * Drivers_Table::numCarRate.

With referential integrity turned on (in Access - RI does not exist in Filemaker Pro), database integrity can be helped by preventing the deletion of drivers who have trip data that refers to their record.

3NF requires that each non-key field in table should refer to nothing but the key field. In the normalised database, each non-key field in each table relates only to the key field in that table. e.g. the totalfee relates to the trip that produced it, not to the driver to whom it applies.

I confidently predict that the average mark for this question will be about 1.5 out of 6! Six marks for a normalisation question is grossly excessive and unfair. Detailed normalisation theory is not something for year 12 students to have to worry about.

APRIL 2013 - ha... I knew it! The average mark was 1.7 out of 6. 38% of students got zero.

Few students gave complete responses to this question. Many were able to correctly normalise the data but did not attempt to explain how each normal form contributes to data being stored efficiently and redundancy or repetition being avoided.
Some responses comprised drawings of tables that were accompanied by explanations. Other responses consisted of explanations with references to examples of changes to the fields shown in the original table. Both types of responses were acceptable.

With regard to the first normal form, students either stated that a field should contain only a single item of data and provided an example or they prepared a new table in which the 'Address' field was separated into three new fields of 'StreetNumber', 'StreetName' and 'Suburb'. An explanation of the reason for this adjustment was also required. The most frequent explanation for separating the 'Suburb' from the 'StreetName' or the 'FirstName' from the 'LastName' is it allows for greater accuracy and more detailed searches. For example, 'drivers can be grouped by suburb and then
assigned to jobs so that driving time is reduced' and 'names can be sorted alphabetically by LastName so that browsing a list is faster'.
With regard to the second normal form, students either stated that the fields should be grouped into two separate tables of 'Member' and 'Driver' so that each field could relate to a unique identifier or primary key for that field, and provided an example, or they prepared the two tables and explained the need for the primary key fields of 'MemberID' and 'DriverID'. Other accepted explanations included 'creating relationships' and problems associated with 'deleting and updating records'.
With regard to the third normal form, students either stated that one field should not rely on another field or indicated with a diagram that the 'TotalFee' should be calculated in a query or report by multiplying the data from the 'YearsWorked' and 'CarRate' fields. The most frequent explanations were that this was more efficient because 'data was not repeated' and calculations were 'processed only when needed'.

Indicate with a tick one software type that your answers will refer to.

• relational database management system (RDBMS)
• spreadsheet (SS)

a.    State how you would list all the coaches who are working on Monday. 1 mark

• RDBMS: do a query (Access) or find (Filemaker) to select records with "Mon" in the TrainingDay field.
• SS: use a filter to show rows with "Mon" in the TrainingDay column.

Average mark was 0.5

b.   State how you would list all Juniors trained by James. 2 marks

• RDBMS: Create a query for Agegroup=Junior AND Coach=James.
• SS: Good question! I don't think Excel filters allow multiple conditions. I would create new column that concatenates the AgeGroup and Coach values (=AgeGroup&Coach) and filter that column for values equalling "JuniorJames". Messy and inflexible, but this is why databases exist!

Average mark was 0.9

c.    Explain how you would calculate and display the total amount of money paid by Seniors. 3 marks

• RDBMS: In Filemaker, you'd create a summary field defined as "TOTAL OF FeePaid". Filemaker would than add up all the FeePaid fields currently found. I have no idea how this is done in Access.
• SS: Somewhere (e.g. E12) put the formula =SUM(E2:E11)

Average mark was 1.1

Students who chose spreadsheet software generally used a filter function and 'IF' statement in their response to this question. High-scoring responses used names and headings from the question. Examples of accepted responses include
 use a filter function on the 'TrainingDay' column and select 'Mon'
 use a filter function on the 'Coach' column and select James and use the filter function on the 'AgeGroup' column and select 'Junior'
 use the 'IF' statement in a new labelled column to list seniors from the 'AgeGroup' column and applied the 'Count' function multiplied by 450.
Responses that applied the 'CountIF' function to the correct column and explained how the result should be displayed and labelled also received marks.
Students who chose RDBMS software generally used queries, filters and 'Sum' or 'Total' in their responses. Examples of accepted responses include
 use a filter TrainingDay = 'Mon'
 use a filter 'Coach= James' and AgeGroup= 'Junior'
 use a filter AgeGroup = 'Senior' and a query called 'SeniorFeeTotal to sum the 'FeePaid' field.
Responses that used 'Count*450' on the filtered 'AgeGroup' field were also awarded marks.

AppsNow is a company that provides a website where young programmers can advertise and sell their software applications (apps). AppsNow uses cloud computing so that programmers can store their apps and discuss their latest projects online, and receive payments for apps that are sold.

a.   Describe how AppsNow can make sure that the apps stored online are safe from hackers. 2 marks

• Require authorised users to use a username and strong password to access the apps.
• Encrypt the app files so they can't be opened even if a hacker manages to get to them.
• Use SSL or TLS connections to prevent user or app details being observed in transit.
• Ensure the site is protected by a router and a software firewall to prevent port sniffing and unauthorised communication port access.

This question was very well answered, with most students providing appropriate examples to support their recommendations and strategies.The most frequently accepted response to this question was 'encrypt the Apps-R-Us files so that they are unreadable to anyone who does not have the encryption key'. Responses that suggested a firewall and clearly described how it protected the apps stored online from external threats were also awarded marks.

Average mark was 1.3

b.   Explain how AppsNow can test that the apps uploaded to the cloud are completely uploaded. 2 marks

• Compare the file size of the uploaded file on the server with the size it was before it was sent.
• Download the app that has just been uploaded and try to run it.
• Compare the uploaded file's MD5 hash value with that of the file before upload.

What an odd question. What point was it trying to make, I wonder? It does not seem relevant to anything in the study design.

Successful testing strategies involved comparing two versions of a file. Some students suggested uploading a file twice and comparing the two. Others suggested checking that all of the coding is present in both copies of the file.

Average mark was 1.0

c. Explain one disadvantage of using cloud computing for storing data with AppsNow. 2 marks

• All user data becomes inaccessible if
  • the cloud service provider closes down
  • the service provider is closed down (like Megaupload)
  • the user's account is wrongly shut down for perceived "misbehaviour" (as Google famously did)
• It requires internet access to load/save data.
• Internet speed is much slower than storing to local hard disk.
• It usually costs money.
• You have to trust that the service provider is backing up your data properly
• The service provider might be misusing the data you store with them.

There was a variety of accepted responses to this question. The most common response was that backup and retrieval depends on the internet functioning. Other acceptable responses included
 security depends on procedures being followed correctly at a remote location
 processes are managed by people you do not know
 the cloud company could steal your data or go bankrupt.

Average mark was 1.3

Fast Wheels sells eight models of sports bicycles. The manager wants to increase bicycle sales in the new year and reduce costs. He has decided to

1. review the sales trends of all models after the first three months
2. drop the two models that have sold the least in the first three months
3. reward sales staff who sell more than 20 bicycles in the first three months.

Describe how you would use a spreadsheet or a relational database management system to provide the information the manager needs. For each information requirement, make reference to

1. data required
2. functions to be used
3. data to test the solution.

Indicate with a tick one software type that your answer will refer to.

• relational database management system (RDBMS)
• spreadsheet (SS)

9 marks!

The 8 mark question was poorly answered last year, so what do the examiners do? Make it into a 9 mark question!

Average mark was 3.4 out of 9. 22% of students scored zero.

The responses to this question were frequently well framed and coherent. Most students set out their response under the three main headings: review the sales trends, drop the two models and reward sales staff. Under each heading they then described the data required for processing, a function used and the test data required.

RDBMS solution (Filemaker Pro)

To compare models

Test data could be as simple as a single field containing a bike model and the number sold in the transaction. If the database has to isolate records from the past 3 months, a date field would also be required. Use a find on the date field with a value of (say) ">=1/1/2012 AND <=31/3/2012" to find records.

Gather the sales records from the 3 months, including the model of the bike sold. Sort the records by the model field. Create a layout (Filemaker) that has a subsummary part with MODEL as its break field. In the subsummary part, put a summary field defined as NumSold = "SUM OF NumSold". This will display the total bikes sold for each model. From this they can easily see the worst-selling models. (I don't know how you'd do this in Access.)

To give sales bonuses (boni?)

Each test data record needs to include salespersonID (and date if necessary. Use the method described above to locate the records from the required 3 months) and the number of bikes sold in the transaction. Ensure at least one salespersonID has 20 or more sales.

In the sales table, create a summary field defined as CountOfSales = "SUM of NumSold" to count the occurences of models. Sort the data by SalespersonID and create a layout with a trailing subsummary part containing the salesperson ID and the summary field. The layout will show/print a sorted list of IDs with a count of their sales.

To sweeten the deal, create a calculated BONUS field defined as IF(CountOfSales>20, "Bonus", "No Bonus") and show the value of this field along with the total sales figure.

Students who selected RDBMS software included an introductory description or drawing of the tables and fields. For example, 'Create database tables 'Staff', 'Bicycles' and 'Sales' with the fields 'StaffID' and 'BicycleModelID', and 'SaleID' and 'QuantitySold' in the 'Sales' table. Then 'At the end of three months the Manager would …'
Review the sales trends
 Create a report with a graph using the 'QuantitySold' field to show trends in monthly sales or give the manager a picture of sales trends for each model with a summary table.
 An accepted response for testing the functionality of the solution was 'change quantities sold for the two models each month and check the actual graph'.

Spreadsheet solution (Excel)

To compare models

Test data may need a sales date (see above). Each sales record should include the model and number sold in the transaction. Sort the data by model. Use the SUBTOTAL button in Excel and specify that you want to "At each change in MODEL use function SUM and add subtotal to SALES."

This will show the subtotals for each model.

To give sales bonuses

Test data needs a SalesPersonID value and a number of bikes sold.

Use the SUMIF function to generate totals for each salesperson. e.g. =SUMIF(SalesPersonIDs,"Fred",NumberSoldRange) to count the sales for each sales person.

Students who selected spreadsheet software included an introductory description or drawing of the column structure in their response. For example, 'Set up a spreadsheet with 'Staff', 'Bicycles' and 'Sales' worksheets. Use columns for 'StaffID', 'BicycleModel' and 'QuantitySold' in the 'Sales' worksheet to record each sale. Then 'At the end of three months the Manager would ….'
Review the sales trends
 Select the 'BicycleModel' and 'QuantitySold' columns to create a column graph to show monthly sales during the three-month period. This will give the manager a visual indication of sales trends for each model.
 An accepted response for testing the functionality of the solution was 'use various quantities sold for some models each month and check against the expected graph'.
Drop the two models
 Use a 'SUM' formula to record the total of 'QuantitySold' for each model and 'SORT function A to Z' to identify the two lowest-selling models.
 An accepted response for testing the functionality of the solution was 'create equal quantities sold for some models and compare to the expected output'.
Reward sales staff
 Use an 'IF QuantitySold is greater than 20' statement to create an alert in a column called 'Bonus'.
 An accepted response for testing the functionality of the IF function was 'QuantitySold is 19, 20 and 21 and compare to the expected alert'.

END OF QUESTION AND ANSWER BOOK