Unit 2

Index

About This Unit
What You Need To Learn
Assessment Evidence

About This Unit

This unit helps you to:

• understand what information handling means and how it is used
• create a database to store and process records
• create a spreadsheet to store and process numerical information
• search, sort, explore and predict information
• discover trends and patterns from numerical information
• understand and develop good practice and standard ways of working with ICT.

You will produce a relational database and a spreadsheet to meet the needs of users together with notes describing the requirements.

This unit links closely with Unit 1: Presenting information (Intermediate). You can import information taken from the databases and spreadsheets described in this unit into documents created in Unit 1: Presenting information (Intermediate).

This unit provides broad knowledge, skills and understanding for Unit 203: Produce spreadsheet documents, and Unit 212: Maintain and use databases, in Level 2 NVQ IT.

This unit will be assessed through your portfolio work only. The grade awarded will be your grade for the unit.

(back to top of page)

What You Need To Learn

The topics are:

• information handling
• handling techniques
• design of information handling systems
• database methods
• spreadsheet methods
• standard ways of working.

Information Handling (back to topics)

Before you learn how to store and handle information you will need to understand the meaning of the word and why we sometimes use the term data instead.

Data is usually a small piece of information. Data has to fit into a structure such as a sentence to give it meaning. The sentence 'Jim has a red hat' is an example. The words 'Jim', 'red' and 'hat' are data. None of these tell us anything on their own, but when put together in a sentence they produce meaningful information.

You will also need to understand how we structure information to make it simple to handle and easy to read. There are many ways in which we can structure information, such as text in a letter, names and addresses in database records and numbers in a table structure or spreadsheet. In this unit you will learn about record-structured information (databases) and number-structured information (spreadsheets).

You can find information in various places. We call these places sources of information. You will need to be able to find the information you need using sources such as:

• people
• books and directories
• computer databases
• the Internet
• television and radio
• newspapers
• timetables
• CDs
• class notes
• instruction manuals
• magazines
• public databases.

You must learn to specify the information you need for a task. You must also learn to identify suitable sources of information.

You may need to use the following to find the information you need:

• indexes and contents lists in books or magazines
• time or date references in timetables
• sort and search routines in computer databases
• spreadsheet calculations.

The sort, search and logical conditions you will need to be able to use are:

• sort ascending
• sort descending
• is equal to (=)
• is less than (<)
• is greater than (>)
• is less than or equal to (<=)
• is greater than or equal to (>=)
• is not equal to (<>)
• AND
• OR
• NOT.

It is often necessary to use sort routines that make use of more than one field. You will need to be able to make use of a primary and secondary sort field. An example is the telephone directory. This has an alphabetical list of names where 'last name' is the primary sort field and 'first name' is the secondary sort field. In this way Alan Smith comes before Zacharia Smith.

Sometimes we need to search for information using two criteria. One example of this would be to search for all part-time employees over 60 years of age. This type of search uses the logic condition AND. The search would be specified as follows:

Status = part-time AND Age > 60 years

Searches of this kind are called complex searches because they use two or more criteria.

To store information in computers you often need to classify and format the information in records and tables in more detail. You will need to be able to identify the following different types of information:

• text
• number (integer)
• number (decimal)
• currency
• date
• time
• formula
• function
• logical (true or false value).

(back to topics)

Handling Techniques

Each type of database has different ways of finding the information you need. You must be able to use different types of computer database, including:

• hypertext databases
• record-structured databases
• spreadsheet (number-structured) databases.

Hypertext databases are pages of information with highlighted items of text or graphics. The highlighted items are linked to other pages or sections of information. Pointing at, or clicking on, a highlighted item reveals more information about it. Internet pages, CD ROM encyclopaedia pages and 'Help' facilities in software often use this method.

Record-structure databases are usually rows of information in the form of a table. Each row is a record. Records could be people's names and addresses, information about a hotel in a travel database or flights and destinations in an airport. Many record-structured databases make use of several tables that are related to form one information system.

Organisations use a great deal of record-structured information. You will need to be able to recognise when this form of structure may be useful. Typical uses are:

• directory - name, telephone number, address
• patient record - name, diagnosis, date
• orders - item, quantity, price, total
• products - name, size, colour, price
• sports results - team, date, score
• houses for sale - address, bedrooms, price.

Spreadsheets (number-structured databases) hold numerical data in cells that are laid out in rows and columns. One example is wages for staff that would include hours worked, pay rates and tax. Another example is the cost, sale price and profit of selling products. You can use spreadsheets to calculate results, such as totals, and to produce graphs of the results.

We use spreadsheets to calculate or forecast results from given information. You will need to investigate how industry and commerce use spreadsheets in different ways. Typical uses are:

• income and expenditure
• sales forecasting
• staff hours, rates of pay and tax
• mortgage payments and interest rates
• material and quantities for jobs
• number statistics.

(back to topics)

Design of Information Handling Systems

Database and spreadsheet handling systems are used to process information. When you plan the design of your system you will need to specify what processing is needed. To produce a suitable design you will need to define the requirements for items, such as:

• calculations
• sorting into order
• printed output
• line graphs and charts
• formulae
• functions
• complex searches
• 'what if' questions.

The design processes for databases and spreadsheets are similar. To design these systems you will need to be able to:

• define the purpose of the system
• define the needs of the users
• identify what processing is needed
• use ICT to create a storage structure
• identify the required information
• collect information for storage
• enter and edit the information
• process the information.

(back to topics)

Database Methods

We often use the term 'database' instead of 'record-structured database'. Database information is usually set out in rows. Each row is called a record. To create this type of database you will need to understand the meaning of the following technical terms:

• record
• field
• field name
• field length
• field data type
• primary key
• foreign key
• relationship
• database structure.

To create reliable databases we often need to use more than one table and these have to be related in some way. An example of this would be a database for the loan of videos to members of a video club. It would be wasteful to enter all the member details every time a video was borrowed.

To avoid this problem we create three tables containing details about:

• the customer (entered only once for each member)
• the videos available for loan (entered only once for each video in stock)
• the video loans (entered every time a video is booked out on loan).
  

In the 'loans table' the 'member no.' field is a foreign key because it is related to the 'member no.' field (primary key) in the 'member' table. The 'video no.' field is also a foreign key because it is related to the 'video no.' field (primary key) in the 'video table'.

Now each time a video is booked out, a new record (row) only needs to be created in the 'loans table' because all the other information can be acquired from the other two tables.

This type of database is called a relational database. You will need to learn how to construct and use a simple relational database.

(back to topics)

Spreadsheet Methods

Spreadsheets are used for storing and analysing numerical information. Spreadsheet cells are laid out in rows and columns. You will need to be able to identify cells, rows and columns and set row height and column width.

To create and use spreadsheets you will need to understand the following technical terms:

• cell format
• relative cell reference
• absolute cell reference
• formula
• function
• IF ... THEN … ELSE.

You will only make use of simple functions such as SUM and AVERAGE. These will be available in the software you use.

One purpose of a spreadsheet is to explore trends and predict results. It is most effective when the predictions depend on changes in one or two cells.

An example would be forecasting income for a retail outlet for different rates of VAT and profit margin. By placing VAT and profit margin in single cells and using absolute references to these cells in the calculations it is possible to see the effect of different values on the income.

You will need to understand and make use of absolute cell referencing. You must learn to build spreadsheets that can respond to 'what if' queries by changing the contents of single cells.

When you present numerical information in the form of line graphs or charts they should be made easy to understand. You will need to make full use of the facilities to title and label graphical information appropriately and clearly, including:

• main titles on charts
• axis titles
• axis scale labels
• legend titles
• data or series labels
• colours and patterns.

(back to topics)

Standard Ways Of Working

(see also this page)

Note: What you need to know and be able to do to implement standard ways of working is described in detail in Unit 1: Presenting information (Intermediate). The following is a shortened version of these requirements.

To develop good practice in your use of ICT you must:

• plan your work to produce what is required to given deadlines
• keep a log of ICT problems you met and how you solved them
• evaluate your work and suggest how it might be improved
• proof-read your database and spreadsheet information to ensure accuracy
• keep backup copies of files on another disk and/or in another location
• save work regularly using different filenames
• keep information free from viruses
• respect confidentiality
• respect copyright.

You need to produce a relational database and a spreadsheet to meet user needs. These must include a description of the system and annotated printed output demonstrating its operation and showing how it meets user needs. You must also produce an evaluation of your work.

(back to top of page)

Assessment Evidence

Pass
To achieve a pass your work must show:
Merit
To achieve a merit your work must show:
Distinction
To achieve a distinction your work must show:

• clear descriptions of the user's needs, the information to
  be processed and the processing required
• table structures created using suitable field names, field
  lengths, data types, primary keys and relations
• suitable spreadsheet created using row heights, column
  widths, cell formats, titles, cell references, IF … THEN
  statements, arithmetic functions and formulae are created
  and used
• your ability to use data-processing skills to enter data,
  sort, search, calculate, predict results, produce different
  types of charts or line graphs and create printed reports
  using related tables
• you have produced printed copy showing that you have
  met the above requirements and explaining your work
  (this may include screen prints or annotated data output)
• your ability to check the accuracy of your data and keep
  backup copies of all files.
• effective use of software to sort on multiple fields, make
  use of cell relationships and produce good quality
  printed copy, showing both data content, formats and
  formulae (clear and detailed annotation, screen prints or
  notes must explain why and how all printed items are
  produced)
• good use of titles, graphic lines, spacing, text size, text
  enhancement, column and row headers, page headers or
  footers and graph labels to enhance the presentations,
  making them attractive, easy to read and free of layout
  errors
• you have checked your work for accuracy and corrected
  obvious errors
• your ability to work independently to produce your work
  to agreed deadlines by carrying out your work plans
  effectively
• clear progression from the design stage to completion
  and evaluation.
• an in-depth understanding of database and spreadsheet
  systems and an evaluation of your work making
  suggestions for improvement and describing any
  problems experienced
• your ability to use technical language fluently and
  produce clear, coherent and comprehensive explanations
  and annotations
• effective and efficient use of complex search criteria on
  related tables, formulae and absolute cell references to
  produce the desired outcomes.

The above criteria only need to be met once for this unit. Except where stated otherwise, they may be met in either your database or your spreadsheet.

This unit requires students to create a database and a spreadsheet to meet user needs. The main difference between this unit and Unit 2: Handling information (Foundation) is the requirement for a relational database. This unit also requires more detailed descriptions, design plans, annotation and valuation notes than would be expected at foundation level.

This unit helps students to acquire experience in processing information that is in the form of records, such as:

• name, address, telephone number
• patient, diagnosis, treatment.
  It also covers information in the form of tables of numbers and calculations, such as:
• name, hourly pay, hours worked, gross pay, tax, net pay
• goods description, price, quantity, total price, VAT, price including VAT.

Students need to learn how to use database and spreadsheet software to create storage structures for this kind of information. They will also need to be able to enter the information into the storage structure that they have created.

When students have stored the information, they need to learn how to process (handle) it.

First they will learn how to check the accuracy of the information and how to amend or
delete it. Later they will learn how to search and sort records and to insert formulae and
functions to calculate results. They will also learn how to print copies of their storage structures and their search, sort and calculation results.

The students will require access to a wide variety of sources of information, particularly that stored for computer-based retrieval. They will need to make extensive use of:

• record-structured databases
• spreadsheet-type databases
• CD-based hypertext databases
• possibly the Internet.
  They will need good experience in:
• specifying the information they require
• identifying suitable sources for that information
• using appropriate search methods
• reporting their findings clearly and concisely.

Students will need to exercise various skills before they are ready to produce a database or spreadsheet application. They could reasonably decide on the contents of these applications between them, but may need guidance to ensure that the results are meaningful.

Students can work together to:

• identify appropriate database and spreadsheet applications
• prepare and collect data
• suggest how it may be used to develop new information.

Having worked as a team to identify and collect the information, students must work individually to design, construct and create the database systems required. It is essential that these items are the individual work of each student and not simply copies of other students' files.

This unit is assessed through evidence in students' portfolios.

When grading student evidence you should consider the following general qualities that distinguish between the three grades:

• increasing depth and breadth of understanding
• increasing coherence, evaluation and analysis
• increasing independence and originality
• increasing skill in the effective application of ICT.

Pass
To achieve a pass, students' evidence must cover all the requirements stated in the pass criteria of the assessment grid. It may be, however, that a student has demonstrated considerable effort and skill in some areas at the expense of precise detail in another. You should use your professional judgement to decide what is a reasonable expectation of the student and whether the stated quality and sufficiency requirements have, on balance, been met.

It is important that the student has a good grasp of the purpose of the database and how a user may wish to make use of the facility. The student must produce outline hand-written, word-processed or even actual rough drafts showing how they planned the database and the spreadsheet. These plans must show that the student understands how to select suitable field and cell formats and layouts.

Sketch diagrams will usually help with this process.
The student must be able to use the software to create the spreadsheet and database storage structures. Advice and help may be provided in terms of instruction and demonstration. The actual storage structure must be created independently by the student.

The database storage structure must comprise at least two related tables.
It is important for assessment purposes that students work independently on the design and construction of their database and spreadsheet to produce the necessary evidence. You need to ensure the product is the student's own individual work.

There is no minimum quantity of data needed in the database and spreadsheet, but there should be enough to make the activity sensible. The database needs two tables. Not all the data needs to be keyed in if students are able to import it from other sources. The spreadsheet data may also be imported provided that the student has produced the essential storage structure and independently inserted the formulae and functions necessary to produce the required results.

Students must demonstrate their ability to edit, delete, sort, search, produce graphs, make predictions and calculate results. They must also show that they have checked the accuracy of their work and kept backup copies of all files. You should check that students are keeping backup copies of their work.

It is important that students annotate printed output to explain its purpose and how it was produced. A search, for example, should have annotation that describes the purpose of the search and shows the criteria used.

UNIT 2: HANDLING INFORMATION (INTERMEDIATE)
G006097 - Intermediate GNVQ Information and Communication Technology - Issue 1 - May 2000 48
Merit
To achieve a merit, students must demonstrate good skills in their use of the software and be
able to use it efficiently. Their data will have been very carefully checked for accuracy. It
will contain few obvious errors.
At this level, students will carefully plan their work and work independently once they have
acquired the necessary knowledge and understanding. Their design plans should demonstrate
a good understanding of the data types, formats and layout facilities provided. They make
allowances for problems and monitor progress to ensure that they meet the agreed deadlines.
The requirement for students to work independently does not mean that they should work
without your intervention or assistance, rather they should not display undue dependence.
A note should be kept of where students have worked independently and produced work to
agreed deadlines. The agreed deadline may be renegotiated to meet unforeseen
circumstances.
Students at this level will improve on default printout facilities. They will have given a great
deal of attention to ensuring that their printed output is well laid out, clear and easy to read.
For example, column widths will be minimised and output will not be unnecessarily split
across several pages. Titles, headings or labels will be in suitable positions, clear and
appropriate in size. Required information will be easy to find and read. Database reports will
probably make use of graphic lines, bold headings, titles or footers.
Students will collate all their work for the unit and insert page numbers so that it clearly
shows progression from initial ideas through the design stage to completed output and final
evaluation.
Distinction
To achieve a distinction, students must demonstrate an in-depth understanding of database
and spreadsheet software in their evaluation notes. They will have described exactly how
sorting, searching and calculations are undertaken and how graphical output is produced.
They will recognise how they could improve their work and will make suitable suggestions
in a clear and concise, but brief, evaluation report.
Their descriptions, explanations and evaluation notes will be fairly comprehensive and will
make fluent and appropriate use of technical language.
The accuracy and quality of their database and spreadsheets reports will be of a high
standard and show that they have taken great care to check, proof-read and correct errors.
They will show that they are able to produce desired outcomes by altering their search
criteria, formulae or cell contents.
UNIT 2: HANDLING INFORMATION (INTERMEDIATE)
G006097 - Intermediate GNVQ Information and Communication Technology - Issue 1 - May 2000 49
hÉó=pâáääë
This guidance highlights the most relevant Key Skills opportunities in this unit. It contains
suggestions only. You will need to check that students have produced all the evidence
required to meet part A and part B of the Key Skills specifications. Students may need to
develop additional evidence elsewhere to meet fully the requirements of the Key Skills
specifications.
Guidance is referenced in two ways:
K - keys to attainment
These are Key Skills or aspects of Key Skills which students should achieve as they meet the
vocational requirements of the units. Only part B of the Key Skill is highlighted - you will
need to check that students achieve part A.
S - signposting
These are opportunities that can be incorporated naturally into the learning programme.
APPLICATION OF NUMBER, LEVEL 2 Key Skills
Reference
When students are: They should be able to develop the following Key
Skills evidence:

• describing the user
  needs and the
  information
  required
  Carry through at least one substantial activity that
  includes straightforward tasks for N2.1, N2.2 and N2.3.
  N2.1 Interpret information from two different sources,
  including material containing a graph.
  K one
  source

• producing a design
  plan and identifying
  the types of
  information to be
  processed
  N2.2 Carry out calculations to do with:
  a amounts and sizes
  b scales and proportion
  c handling statistics
  d using formulae.
  S
  S
  S
  S

• using their dataprocessing
  skills to
  predict results and
  print graphs and
  reports
  N2.3 Interpret the results of your calculations and
  present your findings. You must use at least one
  graph, one chart and one diagram.
  S
  UNIT 2: HANDLING INFORMATION (INTERMEDIATE)
  G006097 - Intermediate GNVQ Information and Communication Technology - Issue 1 - May 2000 50
  COMMUNICATION, LEVEL 2 Key Skills
  Reference
  When students are: They should be able to develop the following Key
  Skills evidence:

• communicating
  clearly the user
  needs and the
  reasons for selecting
  database or
  spreadsheets
  C2.1a Contribute to a discussion about a
  straightforward subject.
  C2.1b Give a short talk about a straightforward subject,
  using an image.
  S
  S

• describing the ways
  in which data is to
  be processed using
  the database or
  spreadsheet
  methods
  C2.2 Read and summarise information from two
  extended documents about a straightforward
  subject. One of the documents should include at
  least one image.
  S

• producing their
  design plans or
  evaluating their
  completed work
  C2.3 Write two different types of documents about
  straightforward subjects. One piece of writing
  should be an extended document and include at
  least one image.
  S
  IMPROVING OWN LEARNING AND PERFORMANCE, LEVEL 2 Key Skills
  Reference
  When students are: They should be able to develop the following Key
  Skills evidence:

• producing a
  relational database
  and a spreadsheet,
  and evaluating their
  work. Opportunities
  include:
  - using dataprocessing
  skills
  - showing they
  have checked
  accuracy
  - explaining their
  work
  There are additional
  opportunities when
  students are working at
  merit and distinction
  level.
  Provide at least two examples of meeting the standard for
  LP2.1, LP2.2 and LP2.3.
  LP2.1 Help set short-term targets with an appropriate
  person and plan how these will be met.
  LP2.2 Take responsibility for some decisions about
  your learning, using your plan and support from
  others to help meet targets.
  Improve your performance by:

• studying a straightforward subject

• learning through a straightforward practical
  activity.
  LP2.3 Review progress with an appropriate person and
  provide evidence of achievements, including how
  you have used learning from one task to meet the
  demands of a new task.
  S
  S
  S
  UNIT 2: HANDLING INFORMATION (INTERMEDIATE)
  G006097 - Intermediate GNVQ Information and Communication Technology - Issue 1 - May 2000 51
  PROBLEM SOLVING, LEVEL 2 Key Skills
  Reference
  When students are: They should be able to develop the following Key
  Skills evidence:

• producing a
  relational database
  and a spreadsheet,
  and evaluating their
  work. Opportunities
  include:
  - creating table
  structures
  - creating
  spreadsheets
  - checking
  accuracy
  - producing
  printed copy to
  show
  requirements are
  met
  There are additional
  opportunities when
  students are working at
  merit and distinction
  level.
  Provide at least two examples of meeting the standard for
  PS2.1, PS2.2 and PS2.3.
  PS2.1 Identify a problem and come up with two options
  for solving it.
  PS2.2 Plan and try out at least one option for solving
  the problem, obtaining support and making
  changes to your plan when needed.
  PS2.3 Check if the problem has been solved by
  applying given methods, describe results and
  explain your approach to problem solving.
  S
  K
  S
  UNIT 2: HANDLING INFORMATION (INTERMEDIATE)
  G006097 - Intermediate GNVQ Information and Communication Technology - Issue 1 - May 2000 52
  UNIT 3: HARDWARE & SOFTWARE (INTERMEDIATE)
  G006097 - Intermediate GNVQ Information and Communication Technology - Issue 1 - May 2000 53
  
  This unit helps you to:

• understand ICT specifications for hardware and software

• select an ICT system and configure it to meet the needs of users

• write a program to improve efficient use of application software (macros)

• write a program to display hypertext information (html)

• understand and develop good practice in your use of ICT.
  You will configure operating system software and applications software to meet user needs
  and write some short programs.
  Much of what you have learnt using ICT systems and software in Unit 1: Presenting
  information (Intermediate) and Unit 2: Handling information (Intermediate) will help you in
  this unit. The techniques for presenting information, learned in Unit 1: Presenting
  information (Intermediate), will also help you to create good-quality hypertext presentations.
  This unit provides broad knowledge, skills and understanding for unit 213: Contribute to the
  installation of hardware, and unit 214: Contribute to the installation of software, in Level 2
  NVQ IT, Operating IT systems and Installing and supporting IT systems and in unit 217:
  Contribute to software system testing, in Level 2 NVQ IT, Installing and Supporting IT
  Systems.
  This unit will be assessed through your portfolio work only. The grade awarded will be
  your grade for the unit.
  te^q=vlr=kbba=ql=ib^ok
  The topics are:

• hardware

• software

• computer programming

• html programs

• macro programs

• standard ways of working.
  
  ICT systems are made up of hardware and software. You must learn to identify and know the
  purpose of the components (pieces) of hardware that make up an ICT system, including:

• input devices

• output devices

• main processing unit

• cables and connectors.
  Some hardware components are inside the main processing unit.