spec unit 7

Unit 7: Using Database Software
A2 compulsory unit
Externally assessed
Introduction
In Unit 2, you used database software to organise and interrogate
structured information and produce reports. You saw how large
organisations rely on databases to manage their information and provide
them with fast and flexible access to it. You looked at examples of
transactional websites which use databases to store product, customer and
sales information and learned at first hand how database queries can be
used to analyse information and identify trends.
In this unit you will develop your knowledge of, and skills in using,
databases further. You will learn the principles of data modelling and
sound database design, and will use relational database software to build
working database systems capable of storing large quantities of data and of
handling both routine and one-off requests for information.
In all likelihood, people other than yourself will want to use some — if not
all — of the databases you create. With this in mind, you will design and
implement user interfaces that make it easier for people to enter data and
extract information, whilst ensuring the overall security and integrity of
the database.
You will make full use of the facilities of the software to produce reports
that are well presented, easy-to-read and fit for purpose. Your work for
this unit will culminate in the design, development and testing of a
database for a specific purpose.
This is an externally assessed unit. Details of the assessment are given on
page 119.
This is a user-focused unit. The knowledge and skills developed in this unit
are particularly relevant to those who use advanced ICT skills on a daily
basis at work or at school/college for personal, social and work-related
purposes.
Recommended prior learning
This unit builds on the database knowledge and skills you acquired in
Unit 2: The Digital Economy.
UA015271 – Specification – Edexcel AS/A GCE in Applied ICT (Single and Double Awards) – Issue 1 – February 2005
113
What you need to learn
7.1
Database
applications
In Unit 2, you looked at the role that databases play in the day-to-day
operation of transactional websites. You will find it useful to begin your
work for this unit by exploring other database applications in areas such as
commerce, education and manufacturing.
You should pay particular attention to design issues such as:
• the database structure
• the user interface
• measures used to protect the quality of the data
• types and forms of output
• the methods used to extract information.
You will be able to put much of what you learn to good use when you start
to design and implement relational databases of your own.
7.2
Functional
specification
As you will have realised by now, databases are too complex and usually
too important to the business for their creation to be left to chance. They
need to be systematically designed, built and tested.
As with all software development, the first step in the process is to
produce a functional specification identifying the requirements of the
database — what exactly it needs to do — including:
• the task(s) the database must perform
• the information it must supply, in what format, to whom
• the data to be input into the database, how and from where
• the processing that is required
• the level of security needed.
Having a clearly defined functional specification will help to keep you on
track once you start building the database. By checking what you have
achieved against the functional specification from time-to-time you can
measure progress and make sure you do not lose sight of your goal.
7.3
Database
development
Once you are clear about the functional requirements, you can begin work
on the design of the database.
The first thing to do is to analyse the data requirements of the system and
produce a data model.
The next step is to translate the data model into a physical database
structure using your chosen database software.
You must be able to convert data models into physical database structures
and test that they work correctly, before beginning work on other aspects
of the system, including:
• • queries
• reports
•
114
data entry and validation functions
menus.
UA015271 – Specification – Edexcel AS/A GCE in Applied ICT (Single and Double Awards) – Issue 1 – February 2005
7.4
Data modelling
You are already familiar with the concept of a model. In Unit 3, you used
spreadsheet models to represent real-life processes and situations. In this
unit you will learn a technique known as entity-relationship modelling to
represent the data in a given scenario.
Entity-relationship models (ER models) have three components:
• entities
• attributes
• relationships.
You will learn what each of these terms means and will practise
constructing ER models showing the entities, their attributes and the
relationships between them for a given scenario.
You will learn how to determine the degree of a relationship and to take
steps to resolve many-to-many relationships.
Normalisation is a process for removing unnecessary duplicated data and
maximising the efficiency of a database. You will learn how to normalise
an initial data model to third normal form.
7.5
Creating a
relational
database
structure
You will learn how to use database software to build physical
representations of data models, in which entities are represented as tables
and attributes as fields.
You must be able to choose suitable data types and formats for fields,
including:
• text, eg limited length, unlimited length, memo
• number, eg integer, auto record number, floating point
• Boolean, eg true and false, Y and N
• date, eg dd/mm/yy, dd month
• time, eg 24-hr clock, hh/mm/ss
• currency, eg pound (£), dollar ($).
You should also know how to store sounds, pictures and other forms of
non-textual data.
You must understand how these data types are actually stored and how the
database software converts them to the format required.
It is important that you are aware of the limitations of particular number
formats and choose the most appropriate.
You will learn how to index a field or group of fields in order to speed up
data retrieval and when it is appropriate to do so.
You will use the primary/foreign key mechanism to create relationships
between tables and learn how to make relationships compulsory by
enforcing referential integrity.
UA015271 – Specification – Edexcel AS/A GCE in Applied ICT (Single and Double Awards) – Issue 1 – February 2005
115
7.6
Validation
techniques
The ‘garbage in, garbage out’ rule is particularly relevant to databases.
You must make every effort to stop incorrect data from getting into a
database.
You will learn a number of techniques for validating data, including
building in checks, such as:
• • file lookup check
• list check
• format (picture) check
•
The user
interface
range check
•
7.7
presence check
length of data check.
You will learn how to design and implement user-friendly interfaces to
help people enter data into your databases and extract information from
them.
You must be able to produce screen input forms with facilities, such as:
• input masks
• drop-down lists
• option buttons
• command buttons.
You must incorporate help for users by means of:
• instructions
• help screens
• easy to understand error messages.
You will learn how to minimise the potential for invalid data input by
incorporating validation checks into forms.
You also need to think about how information from your databases will be
selected and displayed. You should know how to create menus and
searches to make it easier for people to use a database and to control how
much or how little users are allowed to see and do.
You will use the presentation and formatting features of the software to
produce reports on screen and on paper that turn database output into
meaningful information, using titles, layout, etc. Where appropriate, you
will incorporate calculations such as totals and running sums into reports.
7.8
Testing
You will learn to test any databases you create to make sure that they
work correctly. You should carry out checks to ensure that:
• • all menus work properly
• validation checks prevent unacceptable data from being entered
• the database can cope with normal, extreme and abnormal data
• output from the database is complete, accurate and in the required
   format
•
116
the solution meets all the requirements of the functional specification
other people can use it without help.
UA015271 – Specification – Edexcel AS/A GCE in Applied ICT (Single and Double Awards) – Issue 1 – February 2005
It is important to involve others in the testing/evaluation process and to
act upon the feedback they provide. You will learn how to use database
prototypes at various stages during development to gather feedback on
particular aspects of the design, such as ease of use and appropriateness of
output.
7.9
ICT skills
In order to produce relational databases you must be able to use database
software to carry out tasks, including:
• constructing tables to represent entities
• defining the fields in each table to represent attributes
• defining appropriate data types and formats
• defining primary keys
• creating relationships between tables
• defining searches and sorts (single and multiple fields and tables)
• using relational logic in searches
• importing data from and exporting data to other databases and
   applications
• using macros to automate common tasks
• using wizards effectively
• creating screen data-entry forms that
− − have appropriate entry-form field lengths
− provide clear labelling of entry-form fields
− provide instruction fields where necessary
− include validation checks on field entries as appropriate
− enable the selection and entry of data from built-in lists
   (constructed from other tables)
− include calculation (formula) fields
− make use of automated number fields (counter fields)
−
•
enable the entry of data into single and multiple tables
use date and time fields
creating database reports that
− have suitable headers and footers
− have an appropriate format and layout
− have sorted data grouping
− include calculations and total fields
− include specified queries.
UA015271 – Specification – Edexcel AS/A GCE in Applied ICT (Single and Double Awards) – Issue 1 – February 2005
117
Standard ways
of working
• Whilst working on this unit, you will be expected to use ICT efficiently,
   legally and safely. You must adhere to standard ways of working,
  including:
•
7.10
file management
− − using sensible filenames
− setting up directory/folder structures to organise files
− making backups
− choosing appropriate file formats
− limiting access to confidential or sensitive files
− using effective virus protection
−
•
saving work regularly
using ‘readme’ files where appropriate to provide technical
   information, eg system requirements
personal effectiveness
− − customising settings
− creating and using shortcuts
− using available sources of help
−
•
selecting appropriate ICT tools and techniques
using a plan to help you organise your work and meet deadlines
quality assurance
− − proofreading
− seeking views of others
−
•
using spell check, grammar check and print preview
authenticating work
legislation and codes of practice
− − respecting copyright
− avoiding plagiarism
−
•
acknowledging sources
protecting confidentiality
safe working
− − ensuring that lighting is appropriate
− taking regular breaks
−
118
ensuring that hardware, cables, seating etc are positioned
correctly
handling and storing media correctly
UA015271 – Specification – Edexcel AS/A GCE in Applied ICT (Single and Double Awards) – Issue 1 – February 2005
•
eportfolio
− creating an appropriate structure for an eportfolio
− collecting together all the required information, converting files to
   an appropriate format if necessary
− authenticating your work
− providing a table of contents, using hyperlinks to locate
   information easily
− testing for size, compatibility and ease of use, making sure that
   the eportfolio conforms to the technical specification.
Assessment — Unit 7: Using Database Software
This unit is externally assessed.
Working under supervision, you will be required to design, implement and
test a relational database system to meet a given set of functional
requirements. Some of the data to be stored in the database will be
supplied to you as a comma separated values (csv) file.
You will have 10 hours in which to complete this work.
During the production of your database system, you should actively seek
and respond to feedback from others. However, the final database solution
must be entirely your own work.
Only fully functional relational database software capable of supporting
one-to-many relationships should be used for this unit. An up-to-date list
of acceptable software will be maintained on the Edexcel website.
At the end of the designated examination period, you will transfer your
work to a location specified by your teacher. Your work must be submitted
in one of the formats specified on the front of the examination paper.
Delivering this unit
General information
Assessment requirements
This is an externally assessed unit. Instructions for the conduct of the
examination will be published on the Edexcel website in advance of each
examination series.
Vocational context
This unit has a user focus. It is important that learners acquire some
experience of live, large-scale database systems: the sheer volume of data
they handle, the different views of the data they provide and the way in
which data is imported from and exported to other software applications.
Standard ways of working
Learners must work safely and adhere to relevant legislation and codes of
practice when carrying out the externally-set practical computer-based
activities required for this unit. Marks will be awarded for evidence of
adherence to relevant standard ways of working.
UA015271 – Specification – Edexcel AS/A GCE in Applied ICT (Single and Double Awards) – Issue 1 – February 2005
119
Teaching and learning strategies
Learners should be reminded of the work they did at AS level in Unit 2:
The Digital Economy and may find it useful to revisit the database
concepts introduced there before beginning to create database structures
of their own.
Learners should be encouraged to follow a structured systems development
process, involving investigation and analysis of the problem, design and
implementation of the solution. They should be instructed in data
modelling using entity relationship diagrams and normalisation and they
should be given opportunities to practise the techniques they have learnt.
Learners need to develop skills in the chosen database management
software and be familiar with the development tools supplied with this
software. They should be able to create related tables, interactive forms,
complex searches of their database (including linked tables, parameter
searches, calculated fields, multi-field searches, range searches and
totals), sophisticated reports and macros. There is no expectation that
learners will need to supply programmed solutions.
Learners need to understand the importance of testing a system and
choosing appropriate test data to ensure their system works.
Learners need to have practised developing parts of a system and simple
systems prior to the exam.
Links
This unit builds on the database work undertaken in Unit 2: The Digital
Economy. The concept of a model introduced in Unit 3: The Knowledge
Worker is a useful foundation for the work on data modelling that learners
undertake in this unit.
Other units
Learners taking the Double Award may wish to study Unit 12: Customising
Applications in conjunction with this unit.
Learners taking Unit 14: Web Management will benefit from having already
completed this unit, since they need to understand how and why database
integration has become a key feature of most websites.
Assessors may want to use this unit as a vehicle for introducing some of the
project management techniques covered in Unit 8: Managing ICT Projects.
National Occupational
Standards (NOS) for IT
Users, e-skills UK,
November 2003
The IT knowledge and skills covered in this unit map to NOS for Using IT,
Areas of Competence:
Make selective use of IT
• Operate a computer
• IT trouble-shooting for users
• IT security for users
•
120
• Database software.
UA015271 – Specification – Edexcel AS/A GCE in Applied ICT (Single and Double Awards) – Issue 1 – February 2005
The standards specify what users of IT working at Levels 1–3 are expected
to know and understand and be able to do. They include examples of tasks
that users operating at a specific level might be expected to perform. The
ICT skills listed in What you need to learn section 7.8, and some of the
standard ways of working listed in What you need to learn section 7.9,
cover aspects of several NOS Areas of Competence (AOCs), but focus in
particular on use of database software. Teachers should refer to the
standards for further information about what is expected of learners using
databases at Level 3.
Resources
Equipment
Learners will need access to:
•
desktop/laptop computers ideally with the following minimum
specification (based on the Becta workstation specification 2/10/03):
− 256MB memory
− 1.7Ghz Intel processor or equivalent
− 40GB hard drive
− video card with 32MB memory
− CD/DVD
− some form of rewritable media
− UK keyboard and pointing device
− colour, high resolution monitor, capable of supporting 1024x768
• printing facilities
• sufficient individual storage space
• internet access (broadband)
• Windows XP operating system or equivalent
• software:
−
Books
relational database software, eg Microsoft Access, Lotus Approach
Heathcote P M — Successful ICT Projects in Access (Payne-Gallway
Publishers Limited, 2002) ISBN 0953249069
Warrender R L — Databases (Crucial, 2003) ISBN 1903337089