Get documents about "GCSE ICT Assignment Databases Assignment Databases Help Booklet GCSE
Document Sample
GCSE ICT Assignment B2 Databases
Assignment B2 – Databases
Help Booklet
GCSE ICT
Enter Data Paper based or
using…
electronic forms
Contains Contain Records Contain Fields
A File Tables
Can be Can produce
Sorted Searched Queries can Reports
be used for
and using
mailmerge
printed Queries
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 1
GCSE ICT Assignment B2 Databases
Table of contents
Task 1 - Data Capture Forms ........................................................................................................3
Help for creating a data capture form.........................................................................................3
Examples of Data capture forms................................................................................................4
Importing the Pupil Database ........................................................................................................5
Importing the ‘Contact Database’ ..................................................................................................7
Flat file and Relational Databases.................................................................................................8
Flat File ......................................................................................................................................8
Relational Databases.................................................................................................................8
Setting up the relationship .........................................................................................................8
Part A – Pupil Database....................................................................................................................9
Task 2 ...............................................................................................................................................9
Task 3 ...............................................................................................................................................9
Task 3 ............................................................................................................................................. 10
Task 4 ............................................................................................................................................. 11
Task 5 ............................................................................................................................................. 12
Task 6 ............................................................................................................................................. 13
Task 7 ............................................................................................................................................. 13
Part B – Contact Database ............................................................................................................. 14
Task 8 ............................................................................................................................................. 14
Task 9 ............................................................................................................................................. 14
Designing the Receipt.............................................................................................................. 14
Producing the receipt in Publisher ........................................................................................... 15
Completing the mail-merge...................................................................................................... 15
Task 10 ........................................................................................................................................... 17
Report writing frame – Assignment B2 – Databases....................................................................... 20
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 2
GCSE ICT Assignment B2 Databases
Task 1 - Data Capture Forms
You have been asked to produce 2 data capture forms. One for the pupil database, one for the
contact database. The forms will be used by the school before future trips and will be used as a
data capture form for data entry into the Pupil and Contact database. You need to include a school
logo (or school trip logo) which you can design yourself.
Help for creating a data capture form
When you create a data capture form you must make it very easy for people to fill in. Here are
some ideas that may help you do this.
1. Provide the user with a limited number of boxes to fill in the details. i.e.
Postcode:
What if the name is more than 20 letters or you don’t know how many characters will be needed?
Then use lines: First name: ___________________________________________
Or dots Surname:…………………………………………………………………
2. If you want the users date of birth, make it obvious!
DOB / / i.e. dd / mm / yyyy
3. If you want the user to make choices, get them to tick boxes.
Would you like to receive our monthly newsletter? Yes No (tick)
What is your favourite type of movie? (Please tick only one)
Thriller Comedy Romantic Comedy
Horror Other Drama
Note: If you want to give the user a box to write in ( ) you need to go to the ‘insert’ menu and
choose ‘symbol’. Change the font to windings and then choose the box type you would like. You
can then resize the box by highlighting it and changing the font size. Other useful symbols include:
4. Most importantly a good data capture form includes instructions for the user.e.g
• Please tick one or Please choose only one from the list below
• Fill in your telephone number including the area code
Your data capture form must be neat and tidy and easy to fill in!
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 3
GCSE ICT Assignment B2 Databases
Examples of Data capture forms
Pupil Database Data
Capture Form
Contact Database Data
Capture Form
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 4
GCSE ICT Assignment B2 Databases
Importing the Pupil Database
Before you begin the database tasks you will need to import the spreadsheet versions of the
databases into Microsoft Access.
1. Open Microsoft Access Start All Programs Core
Programs Microsoft Access
2. Click on ‘Blank Database’
3. Save the database as ‘Assignment A2’ to a folder you have
created called ‘Assignment B2’. Minimize Access.
4. Navigate to the ICT Explorer Website and choose
‘GCSE’ – ‘B2’ and save ‘Pupil_Database2.xls’ to your
own area (your ‘Assignment B2’ folder).
5. Next, import the spreadsheet data into the database
from where you have saved it choose:
File Get External Data Import
6. Change the ‘Files of type’ to Microsoft Excel. Find and
Click on ‘Pupil_Database2.xls’
7. On the first page of the ‘Import Spreadsheet
Wizard’ choose ‘Next’
8. You will be presented with the following error
9. Press OK and proceed with the import
10. On the next screen leave the box ‘First Row
contains Column Headings’ ticked and press
‘Next’. On the next screen leave ‘In a new table’
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 5
GCSE ICT Assignment B2 Databases
selected and press ‘Next’
11. As you are aware there is one field that has no name. You need to find this field and give it the
name ‘Permission’.
12. Click on the ‘Field 10’ column. Rename it ‘Permission’ by typing this in the ‘Field Name’ box.
13. Press ‘Next’
14. At the next screen you need to ensure
that ‘Choose my own primary key’ is
selected.
15. In the drop down box next to this choice
choose ‘Pupil No’
16. At the final screen make sure you name the
table ‘Pupils’ in the ‘Import to table’ box and
click ‘Finish’. Press OK on this window.
17. The data will now be imported. You should
check it is done correctly by double clicking
the new ‘Pupils’ table. Your table should look
like that shown below.
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 6
GCSE ICT Assignment B2 Databases
Importing the ‘Contact Database’
The process for importing the Contact database is very similar to that for the pupil database shown
above. The main difference is that the contact database does not have field names already
included.
1. Follow the previous example choosing ‘Contact.xls’ as the file
2. On the first page of the Import wizard make sure you have chosen ‘Sheet 1’ and then ‘Next’
3. For the contact database you must ensure that you
have not ticked ‘First Row Contains Column
Headings’
4. Ensure that ‘In a New Table’ is chosen at the next
step
5. The next stage involves you naming all the
fields in the database. You do this by clicking
on the field name headers and changing their
name in the ‘Field Name’ Box.
Below is a list of the field names you can
use in the correct order:
• Pupil No
• House Number
• Road
• Town
• Postcode
• Telephone no
• Parent Name
• Relationship
• Full amount paid
• Amount paid
6. Once complete press ‘Next’ and then choose ‘Choose my own primary key’.
Choose ‘Pupil No’ in the drop down box. Click ‘Next’ and name the table ‘Contacts’. Press OK.
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 7
GCSE ICT Assignment B2 Databases
Flat file and Relational Databases
For this task you have now imported two tables of information into your database. There are two
types of databases called ‘flat file’ and ‘relational’.
Flat File
The two spreadsheets you have used to import data into your database are considered to be a
‘Flat file database’. This is because all the data is organised into one table each which is stored in
one data file and the two tables are not linked.
Relational Databases
Relational databases store the data in separate tables however
the tables are linked together by key fields which they have
in common. E.g. The ‘Pupils’ table has a ‘Pupil number’ field
which you have set as a primary key. This is the unique
identifier for each pupil. The same field is also used in the
‘Contact’ table. Therefore for each record in the pupil table,
there is a corresponding record in the contact table.
Relational databases help avoid repetition in information.
Setting up the relationship
1. Once you have imported the two database files click on
Tools Relationships
2. Add both the Contact and pupils tables and press ‘Close’
3. Drag the ‘Pupil No’ field from the pupils table and drop it on
top of the ‘Pupil No’ field of the Contact database
4. Make sure you tick the
three entries in the bottom half of
the screen
5. Press OK. Your tables should now
be linked as shown
6. Close the relationship window,
clicking ‘Yes’ to save.
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 8
GCSE ICT Assignment B2 Databases
Part A – Pupil Database
Task 2
One pupil has decided against going on the trip as his parents have already booked their family
holiday for the same dates. Remove him from the pupil and contact databases.
His name is listed below along with his pupil number and year group:-
Name Pupil No Year Group
Alexander John 1 8
To delete a pupils record from the pupils
database you need to locate the record in the
‘Pupils’ table.
1. Double click the Pupils table
2. Locate the record for ‘Alexander John’
3. Right click on the record selector box
next to the record
4. Choose ‘Delete Record’
5. Confirm your deletion by clicking ‘Yes’
6. When you see the following error
message click ‘Yes’
7. Close the table by clicking the red X
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 9
GCSE ICT Assignment B2 Databases
Task 3
The teacher in charge has decided to allocate 1 place, to replace John Alexander. His details
are :-
1. Terry Gaskill who is in year 8. He was born on 12th October 1993 and will be pupil number 51,
has no medical problems and will be in the same group as J. Alexander who withdrew. He paid his
£100 deposit on 30/09/03 and can swim 50 meters. The school has permission to take and use his
photograph if necessary. His contact details are as follows:- Mr F. Gaskill, who is his father. The
address is 15, Belgrave Road, Belfast, BT8 6FT and the contact telephone number is 90593482.
His designated teacher will be Mr Black and he will be in group number 1. The balance of his
money must be paid by the end of April.
2. Jenny Vance who is a non-swimmer, in year 12. She will be pupil number 52, will be allocated
Miss White as teacher leader but will have no group number and no acceptance date as she has
not paid a deposit. Jenny was born on 23rd November 1989. She has asthma. The rest of her
details are not required unless she gets accepted for the trip in the event of someone else
withdrawing.
Please add the above 2 pupils to the relevant databases. Terry Gaskill needs to go into both the
Contact database and Pupil database. Jenny Vance only needs to go into the pupil database
because she has not yet been accepted onto the trip, therefore the other details are not needed.
For task 3 you need to add two new records to the pupil database. You could enter the data
directly into the pupils table in table view. However,
it easier and often quicker to create a ‘Form’ to do
this. To create a form for the pupils table follow the
steps below.
1. Click on the ‘Forms’ tab in the object selector
2. Double click on ‘Create form by using wizard’
3. Make sure you choose ‘Table:Pupils’ on the
form wizard.
4. Press the double arrows to move all the relevant
fields over to the right hand side column ‘Selected
fields’
5. Press finish. You will now have a basic form to enter
data into the pupils table
6. Complete these steps again but this time choose the
Contact table. Add the relevant data into each of the
forms for the pupils you have been asked to add. Use the icon to add the records.
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 10
GCSE ICT Assignment B2 Databases
Task 4
Mrs Green has asked for a list of all pupils in alphabetic order to be sent to the tour company. Sort
the PUPIL database file by both surname and forename and print the full list of pupils showing
all fields. Label the printout ‘Task 4’.
For this task you need to sort the pupils database into alphabetical order by both surname and
forename. You then need to print the full list of pupils showing ALL the fields.
1. Open up the pupil table by double clicking on it in
the object selector
2. Highlight both the ‘Sname’ and ‘Fname’ columns by
clicking on the column headers
3. Apply the ‘A-Z’ alphabetical sort
4. The top of your table should look similar to that
below
5. You now need to print out your table.
6. When printing out tables in Access you must make sure that you check the table will fit to a
pages ‘width’ before printing.
7. With the table open click on ‘Print Preview’
8. Click on the ‘Setup’ menu and change the margins to ‘15’ and the page orientation to
‘Landscape’.
9. If you find that the tables width will not fit on one page you need to adjust the column sizes in
‘Table’ view. To do this double click between the columns. This will automatically resize them.
10. Due to the number of records in the pupil table, it will not fit on one page. Therefore you should
have two pages printed out. Label your table ‘Task 4’
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 11
GCSE ICT Assignment B2 Databases
Task 5
Mr Black is in charge of medical details and needs a list of all pupils who have allergies to penicillin
or paracetamol. He wants to make sure all necessary precautions are taken before they leave the
country. Print the pupils full names, year group, allergies and teacher in charge of their
group only. Label the printout ‘Task 5’
Tasks 5, 6 and 7 require you to do a query (a search). Follow these simple steps for all queries.
1. Select the ‘Query’ tab and choose ‘Create query by
using wizard’. Make sure you choose the ‘Pupils’
table in the ‘Tables / Queries’ box.
2. Next, click on the single arrow to move the fields
required over to the selected fields column.
3. For task 5 you are asked to only include:
• The pupils full name (Fname & Sname)
• Medical Details
• Teacher name
4. Press ‘Next’ twice.
Name the query for task 5 as ‘Allergies’ and then click
on the ‘Modify query design’ button.
Press finish. You should see the screen below
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 12
GCSE ICT Assignment B2 Databases
You now need to set the criteria that will be used to search the Pupils table: Your task is to find the
pupils who have allergies to penicillin or paracetamol. To do this you need to enter the first criteria
into the Criteria line under the field heading “Medical Details”
• “Allergic to Penicillin”
You then need to enter the second criteria into the “OR” line under the field heading “Medical
Details”
• “Allergic to Paracetamol”
5. Go to the ‘Query’ Menu and choose ‘Run’.
6. You should have ten entries in your query
7. Print out the results of your query and label the printout “Task 5”.
Task 6
Mrs Browne, another teacher, is going to organise swimming lessons for any male pupils in her
group who cannot swim 50 metres. Search for those pupils and print out their full names and
year groups only. Label the printout ‘Task 6’.
Task 7
Mrs Green has organised a photograph for the local newspaper in order to publicise the activities
of the school. She has decided to use only girls in year 8 whose parents/guardians have given
permission for their photos to be taken for school purposes. Print a list of these pupils full
names and year group. Label the printout ‘Task 7’.
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 13
GCSE ICT Assignment B2 Databases
Part B – Contact Database
Task 8
Create the correct structure for the contact database file and import the relevant data.
You have already created the correct structure for the contact database at the start of this booklet.
You now need to print out the contact database in alphabetical order and label it as task 8.
Task 9
It has now been confirmed that the school trip will cost £500.
Mrs Green issues receipts to all pupils who have fully paid up for the trip. Search for those paid-up
and design a formal receipt which should include the school badge (this may be an original design
made by you). Using mail merge facilities you should now personalise the receipts. Each receipt
should not exceed 50mm by 105mm. Print out one copy of a receipt showing the fieldnames and
another set showing the data for those found by the search.
For this task you have been asked to issue a receipt to pupils who have fully paid for the trip.
1. Design a query that searches the contact table for all those pupils who have fully paid £500.
Call the query ‘Receipts’.
Note: The best way to search the database is by using the ‘Fully Paid?’ field and the search
criteria “Y”.
2. Print out the query and label it Task 9
Designing the Receipt
A basic receipt design
You need to design a receipt in Publisher. This
receipt must:
• Be a formal receipt
• Include the school badge
• Should not exceed 50mm by 105mm in size
• Should include the ‘Parents name’ and
‘Amount paid’ fields from the contact table
Right is an example of how the query should look
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 14
GCSE ICT Assignment B2 Databases
Producing the receipt in Publisher
1. To produce a receipt you have to create a ‘Mail Merge Document’ using ‘Publisher’. This
document will be based on the ‘Receipts’ query.
2. Open Publisher. Choose File New ‘Blank Publications’ and then ‘Postcard’.
3. Go to File Page Setup and change the width to
’10.5cm’, Height to ‘5cm’ and ensure the page is
‘Landscape’
4. Click on ‘Change Copies Per sheet’ and choose
the ‘Multiple copies’ option
5. Press OK
6. You should notice that Publisher has added an extra page to your document. The mail merge
will not work if there are two pages in your document.
7. Click on the second page and go to Edit Delete Page.
You should now only have one page to your receipt.
8. Remember to include some sort of border on your
receipt so that it stands out on the page when you print it.
Completing the mail-merge
9. Next choose Tools Mail Merge Mail merge wizard
10. Leave ‘Use an existing list’ selected and then click on
the ‘Browse’ button
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 15
GCSE ICT Assignment B2 Databases
11. Browse to your database and press ‘Open’
12. Choose your ‘Receipt’ query and press OK
13. Press OK on the ‘Mail Merge recipients’
dialogue box.
14. Once you have set up your labels you need to add the ‘Merge Fields’
so that the information contained in the contact database will appear
on each receipt. To do this you will need to click on the ‘Create the publication’ link at the
bottom of the mail merge wizard
15. On the next screen you will need to
place the cursor in the place you want
to insert the merge fields. Then click
on the ‘More Items’ link and add the
merge fields to your document by
pressing ‘Insert’.
You may need to close the “Insert Merge field’ box each time you add a field to your receipt.
16. Once you have completed your layout including the merge fields you need to print out 1 page
showing the merge fields. To do this you will have to Print screen the page, paste it into a
Word document and then printout.
17. Finally, complete the mail merge wizard and print out the receipts. Remember to check that
clicked on ‘Change copies per sheet’ in the page setup.
Remember you should have 20 tickets printed out!
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 16
GCSE ICT Assignment B2 Databases
Task 10
Miss White has responsibility for gathering outstanding money from pupils. Use mail merge
facilities to print a standard letter informing parents of the date for final payments only for those
who have paid less than £375.
For this task you need to use the mail merge facility again but this time in Word. Before you start
you need to ensure that you have:
• Created a query, called ‘Money Due’, to find all the pupils who have paid less than £375
• Create a standard letter in Word informing parents of the date for final payments
• Use mail merge to fill in the personal details of the recipient. I.e. Name, address and amount
paid
1. Create a query in access that uses the following fields:
a. Pupil table: Fname, Sname
b. Contact table: House No, Road, Town, Postcode, Amount Paid
2. Use a search criteria that finds all the pupils who have paid <£375
3. In Word create a standard letter using the letterhead you developed for task 1, Part A. The text
of the letter may look like that shown on the following page.
4. Next open the Mail Merge toolbar by
choosing View Toolbars Mail Merge
5. Click on the ‘Open Data Source’ icon and
browse to your database. Select the
‘Money Due’ query.
6. Next click on the ‘Insert Merge Fields’ icon.
7. The ‘Insert Merge Field’ Box will appear
8. Insert the necessary fields one at a time into your standard letter at
the appropriate point by pressing the ‘Insert’ button
Note: You must make sure that you put your own spaces in between
your merge fields. You may also have to close the ‘Insert Merge
Field’ box to move your cursor to the appropriate place.
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 17
GCSE ICT Assignment B2 Databases
9. You will need to print out one example of the letter with merge fields.
Go to File Print
10. You now need to perform the mail merge so that all the letters will be produced. To do this click
on the ‘Merge to new document’ icon.
Check all the documents produced before you print out.
11. Save the final merged document to your ‘Assignment B2’ folder with a suitable
name.
You should have printed out six letters for the following pupils:
1. Anne Jones
2. Julie Davison
3. Katie Cullen
4. Janet Johnston
5. Lynsey Davison
6. Terry Gaskill
If you have printed the letters for the correct pupils then in pencil write ‘Task 10’ on each letter.
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 18
GCSE ICT Assignment B2 Databases
151 Newry Road Kilkeel County Down BT34 4EU Tel: 02841 762747 office@stlouis.org.uk
Parent / Guardian of «Fname» «Sname»
«House_Number»
«Road»
«Town»
«Postcode»
Pupil: «Fname» «Sname»
Re: Water Sports Trip
Dear Parent / Guardian
I am writing to inform you that your son / daughter has not yet paid the full amount for the water
sports trip.
The trip costs £500 and you have currently paid: £ «Amount_Paid»
The total amount needs to be paid by 1/01/05. If the full amount has not been paid by this date
your son / daughter may not be able to go on the trip
Please do not hesitate to contact me if you have any queries regarding the amount outstanding.
Yours sincerely,
Miss White
Trip Organiser
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 19
GCSE ICT Assignment B2 Databases
Report writing frame – Assignment B2 – Databases
Once you have completed assignment B2 you need to write a report that describes how you
completed all of the tasks. Use this sheet as a guide to writing your report.
Task 1
For this task you had to design 2 data capture forms. One for the pupil file and one for the contact
file. When you are describing what you have done make sure that you mention that:
• First you designed your data capture form on paper to ensure that you included al the
necessary fields for the two tables
• You describe the features you used that will help users to fill in the form e.g. tick boxes,
dotted lines, tear off slips, symbols etc.
Make sure you have printed out an early and final version of each form. Mark clearly on each form
what it is for e.g. data capture form for the pupil database…
Task 2
For this task you had to delete the details for John Alexander. Before you did this you imported
both the Pupil and Contact files into a new database. In this section you should describe in detail
the process of importing the two tables and how you then created a relationship between them so
as to create a relational database. Briefly mention the benefits of a relational database and then
go on to describe in detail the process for deleting a record. How did you do it?
Task 3
For this task you had to add the details for two new pupils: Terry Gaskell and Jenny Vance.
Describe how you created a form within Access to do this so as to speed up data entry. Describe in
detail the process of creating the form and entering the data. Remember, Terry Gaskills’ details
had to be entered into both the Pupil and Contact tables AND that you did not have to enter any
details for Jenny Vance into the contact table.
Task 4
For this task you had to produce a list of pupils in alphabetical order so that Mrs Green could send
it to the tour company. Describe in detail how you sorted the Pupil database by both surname and
forename.
Task 5
For this task you had to produce a list of all the pupils who were allergic to both Paracetamol and
Penicillin using a query. Describe in detail the process of creating a query to find the information
needed. Include in this description the fact that you had to add the criteria to both the ‘Criteria’ line
and the ‘OR’ line. Do not forget to mention that you only had to print out a list that contained the
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 20
GCSE ICT Assignment B2 Databases
pupils’ full name, the year group, details of allergies (Medical Details) and the name of the teacher
in charge.
Task 6
For this task you had to produce a list of all those pupils in Mrs Browne’s’ group who were male
and who could not swim 50m so that Mrs Browne could organise swimming lessons. Describe in
detail the process of creating this query paying particular attention to the criteria you used and the
fields you had to query. Do not forget to mentions that you only had to print the pupils’ full names
and year groups.
Task 7
For this task you had to produce a list of all the girls in year 8 whose parents/guardians had given
permission for their photos to be taken for school purposes for Mrs Green. Describe in detail the
process of creating the query paying particular attention to the criteria you used and the fields you
had to query. Do not forget to mention that you only had to print the pupils’ full name and year
group.
Task 8
This task was completed in when you imported the two databases for task 1 so this does not need
to be repeated here.
Task 9
For this task you had to create a receipt in Publisher that would be issued only to the parents of
those pupils who had fully paid for the trip. Describe in detail how:
• You created a query called ‘receipts’. Do not forget to mention the criteria you used and the
fields you had to query.
• You created a template for a receipt in Publisher. Do not forget to mention that Publisher
was the most suitable package for the job as it allows you to mail merge to create as many
receipts as is needed and that it provided a template that could be easily adapted to create
a ticket of the required size (postcard).
• Mention the size you had to design the receipt to (no more than 50mm by 105mm)
• You printed out an example of your receipt that showed that you had used the correct fields
in your receipt design (the one showing the angle brackets <<Firstname>> )
• You incorporated the school badge into your design.
Task 10
For this task you had to use mail merge facilities to produce a standard letter informing parents of
the date for final payments for the trip for Mrs White.
Describe in detail the reasons why you chose to create the letter in word rather than Publisher and
how you inserted the correct fields using the mail merge wizard. Do not forget to mention that you
first created a query in Access that searched for all those pupils who had paid less than £375. Also
include in your description that you used the letterhead you developed for task 1 and that once you
had completed the design you inserted the necessary fields using the mail merge wizard.
Assignment B2 _ Help Booklet_V3_2006_2007.Doc 21
Related docs
