GCSE ICT

1
GCSE ICT

• Data Handling Coursework

Version 3.0 - Last updated 2/12/05

• MS Access Help Slides
• Lesson Slides
• Project Report Slides
• (Click on the section you need)

2
MS Access Help Slides

• 1.Data Validation
• Value lists
• Input masks
• Range Checks
• 2. Setting Default values
• 3. Setting up Queries
• 4. Producing Reports
• 5. Producing Screen Forms
• 6. Creating a main menu

3
MS Access - Data Validation Value Lists

• Set data type to Lookup Wizard and then choose
  to type in the values you want in your drop down
  value list.

4
MS Access - Data Validation Input Masks

• 0 digit that has to be entered
• 9 digit that can be entered
• L letter that has to be entered
• ? Letter that can be entered
• gt - upper case lt - lower case
• [phone] (01424) 772155
• gtLL09 0LL TN33 0HT
• gtLlt???????????? Maria

5
MS Access - Data Validation Range Checks
In this example the values that can be entered
for a year have been restricted by a range check.
6
MS Access - Default Values
Setting a default value for a field can save you
lots of typing. In this example the default value
for the released has been set to 2004.
7
MS Access - Creating Queries
Simple query - finds any date due before
20/09/2003
N.B. This query will display what it finds in
surname order
More useful is this parameter query - produces
message Type in date and then allows user to
enter the value (parameter) used in the search.
8
MS Access - Example Queries

• France Matches the word France
• Like S Matches words beginning with S 
• Like ES Matches words ending with ES
• Like ES Matches words containing ES  
• lt N Matches words starting with letters from
  A to M
• lt 21/6/04 Matches dates before 21/6/04
• 2.5 Matches the number 2.5
• gt234 Numbers greater than 234
• lt1200.45 Numbers less than 1200.45
• ltgt 256 Numbers not equal to 256
• null Matches a blank/empty field
• 1 Matches a tick in a yes/no field
• Between 2/2/93 And 12/1/93 Dates from
  2-Feb-93 through to 1-Dec-93 (beware of US dates)

9
MS Access - Designing Reports

• Produce sketch designs of the key report(s) you
  need. Think about
• Which fields need to be displayed.
• The order in which the data is best presented.

10
MS Access - Creating Reports (1)

• Need to tidy up
• Title
• Column headings
• Text alignment

• Use the Report wizard to create your initial
  raw report.

11
MS Access - Creating Reports (2)

• Use Design view to tidy up your report.

12
MS Access - Creating Forms (1)

• Use the create forms wizard to produce the form.
• Then use Design view to change colours and add
  text.

You can have a form for each table or use one
form to enter and view data from a number of
tables.
13
MS Access - Creating Forms (2)

• You can create more complex screen forms which
  display data from more than one table.

Use the Combo box tool to allow you look up
data on other tables
Health warning! - this is seriously brain
stretching stuff. Dont worry if you cant get it
to work. Write about it in your Evaluate section
14
MS Access Designing a Main Menu

• Decide what buttons you need on your menu and
  what each one will do.
• E.g. Menu for video shop database.
• Add/Edit members members form
• Add/Edit videos video form
• Issue video loans form
• Return video loans form
• List Overdues overdues report

15
MS Access Creating a Main Menu

• Use Create form in Design view to get a blank
  form

16
MS Access Auto-displaying the menu

• Go to the Startup.. option on the Tools
  menu.
• Set Display Form/Page to the form you want
  displayed.

17
Lesson Slides

• A reminder of the most important points from your
  lessons.

18
(No Transcript)
19
Homework Problem Employee Database

• Design a database that could be used to store
  details about the hotels employees.

Copy out and complete the following table to
explain the structure of the database you are
going to set up.
20
Employee Database Table Design
21
Relational Databases (1)
The key part of designing a database is
organising the data you want to store into a set
of tables. Very simple databases use just one
table, but most situations need more if the
database is going to work efficiently. Consider
adding short notes about each artist to a music
database
The problem with simply adding an extra field is
that you would have to type in the notes for
every recording by each artist.
22
Relational Databases (2)
A much better solution is to use a second table
for the artist details and link it to the table
of recordings
Now each artists notes only needs to be entered
once. There is a relationship between the two
tables - they share a common field -
ArtistID. Databases built around two or more
linked tables like this are called Relational
Databases.
23
Database Design - Getting it right (1)
The key part of designing a database is
organising the data you want to store into a set
of tables. Very simple databases use just one
table, but most situations need more if the
database is going to work efficiently. Consider a
database in a video shop used to record which
member has which video. A simple single table
solution would be
Why is this database not very efficient? What has
to be done each time a member takes out a video?
24
Database Design - Getting it right (2)
A much better solution is to use a table for the
video details and a table for the member details
Now when a video is hired only member number
needs to be entered. There is a relationship
between the two tables - they share a common
field i.e. member number. In this situation there
are two ENTITIES - members and videos. A separate
table is needed for each entity.
25
Database Design - Getting it right (3)

• The best solution is to use three tables

N.B. As for most other things, there is not just
one correct way of designing a database system.
But, some designs are clearly better than
others....
The main advantage of this method is that details
of previous loans are not deleted when videos are
returned.
26
Your Database Problem

• For coursework problem 3 you have to design and
  produce a database system for one of the
  following situations
• Video/DVD rental shop
• Gym/fitness centre
• Adult education centre
• Estate agents
• School work placements
• ICT equipment service records

Some ideas to help you on the next few
slides......
27
Database Design - Adult Ed/Gym (1)

• In this situation you could use three tables

28
Database Design - Adult Ed/Gym (2)

• Or... you could use four tables

29
Database Design - Estate Agents
The database records which properties have been
viewed by which buyers.
30
Database Design - Work Placements
31
Targets for Today!

• Choose the problem you are going to work on
• Video/DVD rental shop
• Gym
• Adult education centre
• Estate agents
• School work placements
• Add/delete fields to your people table so it
  fits in with the problem you have chosen (e.g.
  date joined, tutor group, max price...)
• Create new tables for the other entities
  needed in your database (e.g. videos, classes,
  employers...)
• Add around 10 records for each of these new
  tables.

32
YEAR 11 STARTS HERE!Database Coursework (contd)

• PREVIOUSLY in ICT you (should) have
• Set up your database tables
• Entered suitable data into the tables
• Set up data validation on some fields
• Linked the tables by creating relationships
• Produced well annotated printouts showing
• - data validation
• - the relationships screen
• - simple updating (insert/delete/amend)

33
Coursework Problem 3 - Databases

• To complete your database system you now need to
• Set up searches/queries to find important
  information.
• Set up reports to produce printouts of important
  information.
• Set up screen forms to produce a suitable user
  interface.
• BUT.... Before trying to do this......

34
Think!!

• You need to think carefully about the
  business/organisation that is going to use your
  database system. Use your imagination!
• Who are they?
• Freeman's Dental Surgery, Bexhill.
• Who will use the database system?
• Mostly the office manager, but also the four
  dental nurses and four dentists.
• What will they use the database system for?
• Storing patient details, recording appointments,
  printing daily patient lists for each dentist,
  sending reminders to patients that need a check
  up.

35
Designing the Searches/Queries

• You need to identify the valuable information
  that can be obtained by searching your database
  system.
• e.g.
• videos that are overdue
• buyers that have viewed a certain house
• students/members on a certain course......
• You should aim to carry out at least four such
  useful searches.

36
Creating the Queries

• Create each query you need - using parameters
  where ever appropriate. Give each query a useful
  name.
• Print out a screen snapshot showing your
  parameter messages.
• Print out the results of each query.
• Check that results are what you expected i.e. the
  query finds the number of records you expected.
• Fully annotate each query and show that you have
  tested it by recording the number of expected
  matches.

37
Designing the Reports (1)

• You need to identify and design the most useful
  printouts that can be obtained from your database
  system.
• These can be based on your queries e.g.
• videos that are overdue
• buyers that have viewed a certain house
• students/members on a certain course......
• or your tables e.g.
• complete list of members/buyers/students

38
Designing the Reports (2)

• Produce a design sketch for each report showing
  the columns (fields) that need to be printed and
  the order in which the details should be
  displayed.
• e.g.
• You should aim to produce at least three reports
  from your database.

Include ideas for fonts and text sizes too
39
Creating the Reports

• Create and print out each of the reports that you
  have produced design sketches for.
• Fully annotate each printed report to explain
  what it shows and why it is useful.

40
Designing the User Interface (1)

• Produce a diagram showing the buttons that you
  will have on your main menu and the action of
  each button.
• e.g.

Include ideas for colours and fonts too
41
Designing the User Interface (2)

• Produce a more detailed design sketch for your
  main menu and other screen forms showing
• position of each field
• position of buttons
• position of text labels
• position of logo
• position of key explaining codes
• choice of colours/fonts......

42
Producing the User Interface

• Produce each of your screen forms and print out
  a screen snapshot of each.
• Check that all of your buttons work correctly.
• Annotate your printouts to show that you have
  tested the action of each button.

43
FINISHED!!

• Now you just have to complete your coursework
  report!
• !EASY!

44
Coursework Problem 3 Databases

• Project
• Report

IDENTIFY ANALYSE DESIGN IMPLEMENT EVALUATE
45
Identify Section - Outline

• a) Background Information
• What is the business? Where is it? Who owns/runs
  it? How many people work there?
• What does the business do?
• How does it store information at present?
• What problems does it have with storing
  information at present?
• What is the problem that you are going to
  solve? Be imaginative!
• b) The 'User'
• Who will be the user or users of the system
  you are going to produce? This person is
  important you have got to produce a system they
  are happy with. You will need to seek their views
  on what you have produced on a number of
  occasions. Be imaginative!
• c) Possible Solutions - manual
• d) Possible Solutions - computer
• What are the advantages/disadvantages of using a
  computer system to solve this problem? Why is the
  computer solution you have chosen is the best
  solution?
• e) Objectives
• 4-5 objectives or targets for the system you are
  going to produce. Twhen you finish this project.
  Good objectives are things that you will be able
  to check or test once your database has been
  set up.

46
Identify Section (5 marks)- Full Details

• a) Background Information
• This is your introduction. Give as much
  background information as possible.
• What is the business? Where is it? Who owns/runs
  it? How many people work there?
• What does the business do?
• How does it store information at present?
• What problems does it have with storing
  information at present?
• What is the problem that you are going to
  solve? Be imaginative!
• b) The 'User'
• Who will be the user or users of the system
  you are going to produce? This person is
  important you have got to produce a system they
  are happy with. You will need to seek their views
  on what you have produced on a number of
  occasions. Be imaginative!
• c) Possible Solutions - manual
• If you werent going to use a computer to solve
  this problem, how could you store the information
  manually? What are the disadvantages/advantages
  of using a manual system?
• d) Possible Solutions - computer
• What are the advantages/disadvantages of using a
  computer system to solve this problem? Why is the
  computer solution you have chosen is the best
  solution?
• e) Objectives
• Make a numbered list of 4-5 objectives or targets
  for the system you are going to produce. These
  should be things that your system needs to be
  able to do or things that will show you and your
  'user' how successful you have been. Try to make
  these objectives measurable and not too vague
  i.e. things that you can clearly say you have or
  have not achieved when you finish this project.
  Good objectives are things that you will be able
  to check or test once your database has been
  set up.

47
Analyse Section - Outline (1)

• a) Software
• 1. What type of software are you going to use to
  solve this problem?
• 2. What features of this software make it most
  suitable?
• b) Hardware
• 3. What type of printer will be needed? Any other
  hardware needed?
• c) Output
• 4. What printouts/reports will need to be
  produced from your database?
• 5. Who is going to read these printouts?
• 6. How many screen forms will you need?
• 7. Will you use menu screens? Why?
• 8. Will you ever need to export data from your
  database to other software?

48
Analyse Section - Outline (2)

• d) Input
• 9. How many tables will you need in your
  database?
• 10. Make a simple list of all the fields that
  will be in each table.
• 11. Will you need a data collection sheet(s)?
• 12. Will you use data validation? Which fields
  do you plan to validate? How?
• e) Processing
• 13. What searches/queries will need to carried
  out in the usual day to day running of the
  business? Why? When? How often? (Are complex
  searches/queries needed?)
• 14. How/when will your database be updated? (New
  records inserted, old records deleted,
  corrections/alterations made?)

49
Analyse Section - Outline (3)

• f) Backups
• 15. How often will a backup copy of your
  database need to be taken?
• 16. What media (floppy disk, Zip disk, CD R/W,
  memory card..) will you use for this backup?
• g) Security
• 17. Why will you need to consider the security of
  your system?
• 18. Why do you need to consider the Data
  Protection Act?
• 19. What steps can you take to ensure that the
  system is as secure as possible?

50
Analyse Whats wrong?

• A special piece of hardware that could be used
  is a magnetic swipe card reader. When a member
  wants to rent a video the employee just swipes
  their membership card through the reader and
  their details appear on the screen. If you were
  using a database you would have to perform a
  search

51
Analyse Whats Wrong?

• Title
• Surname
• Forename
• Address
• Town
• Postcode
• Phone no
• customer ID

You cant validate addresses so I cant use data
validation on the customers of the estate agent
52
Design Section

• Initial Designs
• User comments
• Final Designs
• Test Plan

53
Improving Your Design Section

• LABELLING!! What is this drawing???
  initial? final? screen? printout?
• Realistic user comments. Is your user too clever?
  too nice?
• Re-do table designs that have corrections rather
  than comments.
• Colour font button details??

54
Database Project The Story So Far!

• Database (implementation)
• 2/3/4 tables with some data?
• Linked tables?
• Screen forms?
• Write Up
• Identify
• Analyse
• Initial designs for tables/screen forms

55
Database Project The Happy Ending!

• Database (implementation)
• Simple printouts initial data updates
• Main Menu Screen
• Working printouts Queries/Reports
• Screen snapshots
• Testing
• Write Up
• User comments final designs
• Test plan
• Evaluation

56
Implementation Testing

• Create the database you have designed and enter
  around 15 records in each table.
• Create screen forms/menus
• Create queries
• Create reports
• Carry out test plan

57
Key Printouts

• Create QUERY to find the information. e.g.

• Create REPORT from Query to produce printout
  needed.

Use Wizards to create Queries and Reports. Use
Design tool to adjust appearance of final
report.
58
Screen Snapshots

• Screen forms main menu
• Data validation - drop down lists
• - screen messages
• Relationships screen
• Details of key Queries e.g.

ESSENTIAL Title, label and fully explain ALL
screen snapshots. What does each one show?
59
Test Plan

• Data validation tests
• Searches do they work?
• Form/menu tests - do buttons work?
• List of real life tasks e.g. loan video, return
  video, new student, new course, enrol student,
  employee goes on course.....

Test plan template
60
Evaluation

• Your comments on your objectives
• User comments on finished system
• (Questionnaire/interview QA)
• Your comments on what the user said.
• Possible future improvements?

Guidance notes
61
Day of Destiny!

• Hand in date for completed database project
• ??????????
• 1 complete week to go!

62
Things to do today 6/11/03

• Non- Computer
• 1) Initial designs
• Screen forms
• Printouts
• Tables
• 2) Add user comments
• 3) Final Designs

• Computer
• 1) Finish data entry
• 2) Screen forms
• 3) Main menu
• 4) Initial printouts
• All records
• Simple updates

Hwk Finish Design Section (not test plan)
63
Things to do today 3/9/04

• 1) Decide on business/organisation that is going
  to use your database system.
• 2) Decide on key searches (Queries) printouts
  (Reports)
• 3) Produce design sheet showing Queries that
  you will need and paper designs for Reports.
• 4) Set up Queries produce printouts showing
  them in action.
• 5) Set up Reports and print out.

64
Homework!

• Initial designs for
• Screen forms
• Reports
• Main menu

Hand in before Wed and I will do user comments