Edexcel GCE
Applied Information and Communication Technology
Unit 7: Using Database Software
Specimen Assessment MaterialAssessment window 3 weeks
Time: 10 hoursMaterials required for examination
Pre-release
Scenario: SCENARIO
Issued at the start of the examination
Details of transactions: TRANSACTIONS
Details of drivers: DRIVERS
Instructions to candidates
Use relational database software to carry out the activities in this examination.
At the end of the examination transfer your work to the location specified by your teacher.
Information for candidates
There are 5 activities in this examination totalling 90 marks.
Advice to candidates
Study all the information provided carefully.
turn over
Appendix A – pre-release materials
Filename: SCENARIO
Scenario
S-Cars is a company which hires out stretch limousines, complete with uniformed chauffeurs, for special occasions. S-Cars currently has a fleet of 12 limos the smallest of which can take seven passengers the largest 20. The limos are hired out in sessions.
In general there are three sessions in a day. The morning session starts at 7 am and finishes at 12 midday, the afternoon session lasts from 1 pm to 6 pm and the evening session from 7 pm to midnight. Earlier starts can be negotiated depending on the availability of limos and drivers. If customers book consecutive sessions they can negotiate with the driver as to when the hour break is taken.
The firm employs 15 freelance drivers who are paid on a ‘number of sessions’ basis. Their rates vary from £40 to £80 a session and their wages are paid by the firm at the end of each month.
Customers may hire more than one car or driver for special occasions (eg weddings or West End trips) and many customers book limos regularly.
The first time customers make a booking they are given a client number which consists of the first three letters of their surname plus a four figure number. Customers quote their number when they make further bookings. Regular customers can negotiate discounts. Limos can only be booked up to 90 days in advance but drivers are assigned on the morning of the hire.
A customer rings up or comes to reception. If they are an existing customer you look up their details in the card index file. If they are new, you ask them for their details and create a card.
You are the new owner of S-Cars and are shocked to find the company uses this archaic paper-based system.
This is what happens at the moment
A customer rings up or comes to reception. If they are an existing customer you look up their details in the card index file. If they are new, you ask them for their details and fill out a card for them.
You will then need to know the following:
- number of passengers
- date and session required
- pickup point and destination.
The next step in the process is to tell the customer which limos are available.
Once customers have made their choice, details of the booking are written in the ledger. Customer details are held in a card index file, as are the drivers’ details. Pictures and descriptions of limos can be found in a ring binder.
This is what you are going to do about it
You have decided to use database software to provide a more efficient solution. The new database system will hold information about customers, limos and drivers, handle customer bookings and generate invoices and reports once a month.
In preparation for this, a work experience student has typed the contents of the ledger and the two card index files into three text files: bookings.txt, drivers.txt and customers.txt.
Activity 1 – Understanding the problem
Produce a functional specification identifying:
the tasks the database has to perform
the information it must supply, and to whom
the data to be input into the database, and how this is done
the processing that is required
the level of security needed.
Evidence to be submitted
A one A4 page functional specification identifying the requirements of the database.
(Total 8 marks)
Activity 2 – Structure
Use data modelling techniques to produce an efficient database structure that minimises duplication of data.
Apply validation rules on as many fields as is sensible.
Import the data from the text files into your tables.
It seems that the work experience student has made some mistakes when entering the data which the validation rules have rejected.
Produce a table listing the rejected records and give an explanation for why each is invalid.
Evidence to be submitted
With one screen dump per A4 page, provide:
- A screen dump showing the entities and relationships in your database system.
- For each table, a screen dump showing the table structure you have created, including field names and data types.
- Printouts or screen dumps of all tables after import, showing either at least twenty records or the whole file (whichever is the smaller). This must be done before any other data is entered.
On one A4 page, produce a table listing the rejected records and explaining why each is invalid.
(Total 20 marks)
Activity 3 – Making a booking
Create the user interface to make a booking on your system.
The system must enable a user to:
- add and edit customer details
- identify suitable limos which are available for the required session
- assign drivers to limos.
Evidence to be submitted
A one A4 page listing the test data.
With one screen dump per A4 page, provide:
- Screen dumps of forms in design view, with a full description of any relevant system features
- Screen dumps of forms in data entry view, with a full description of any relevant system features
- Screen dumps of any searches/queries built in design view, with a full description of any relevant system features
No more than two A4 pages, provide:
- Evidence of details of a new customer entered via form
- Evidence of new multi session booking
Note: Each feature of your system must be described in full and evidence provided to prove that it works.
(Total 25 marks)
Activity 4 – Invoicing and payments
Create the user interface to generate invoices.
Once a month the system must produce:
- invoices for all outstanding payments (outstanding means those bookings which have taken place but haven’t yet been paid for)
- a list of all the invoices produced, and the amount of each, with a total of how much money has been invoiced that month.
An invoice must include at least the following:
- S-Cars name, address and logo, telephone number, fax number
- the word ‘INVOICE’
- the booking numbers (it is possible that a customer may have more than one booking payment outstanding)
- the customer’s ID number, name and address to which the invoice will be sent
- an itemised list of all bookings by that customer showing for each booking the registration number, date and cost (Note that cost is the total cost of hiring that vehicle and not necessarily cost per session.)
- the total cost of all the itemised hires exclusive of VAT
- the amount of VAT (charged at 17.5%)
- the total including VAT.
For invoices with payments outstanding for more than three months the following statement should be printed in red on the bottom:
“Please note that this invoice contains payments which have been outstanding for over three months. Please pay within the next 14 days to avoid legal action”.
The system will need to record that customer payments have been received so that no further invoices are sent.
Evidence to be submitted
A one A4 page listing the test data.
With one screen dump per A4 page, provide:
- Screen dumps of all reports in design view, with a full description of any relevant system features
- Screen dumps of any searches/queries built in design view, with a full description of any relevant system features
With one invoice per A4 page, provide:
- At least two sample invoice prints, one where the money has been outstanding for more than three months and one where it hasn’t
- Example of invoice for more than one session in a booking
- Example of invoice with more than one booking
- Example of invoice list
On no more than one A4 page describe how reports can be printed off using one action.
Note: Each feature of your reports (calculations, new page etc) must be described in full and evidence offered to prove that it works.
(Total 30 marks)
Activity 5 – Main menu
Create a menu screen which opens automatically when you open the application. The screen must provide an interface to make a booking and print off the invoices.
Evidence to be submitted
With one screen dump per A4 page, provide screen dumps of your opening screen in both design and form view.
On one A4 page:
- Describe what each button does
- Explanation of how the menu opens
On one A4 page, submit a witness statement provided by your teacher, as evidence that your buttons work as described.
With one screen dump per A4 page, provide screen dumps of any macros etc.
(Total 7 marks)
TOTAL FOR PAPER: 90 MARKS
Appendix A – pre-release materials
Filename: SCENARIO
Scenario
S-Cars is a company which hires out stretch limousines, complete with uniformed chauffeurs, for special occasions. S-Cars currently has a fleet of 12 limos the smallest of which can take seven passengers the largest 20. The limos are hired out in sessions.
In general there are three sessions in a day. The morning session starts at 7 am and finishes at 12 midday, the afternoon session lasts from 1 pm to 6 pm and the evening session from 7 pm to midnight. Earlier starts can be negotiated depending on the availability of limos and drivers. If customers book consecutive sessions they can negotiate with the driver as to when the hour break is taken.
The firm employs 15 freelance drivers who are paid on a ‘number of sessions’ basis. Their rates vary from £40 to £80 a session and their wages are paid by the firm at the end of each month.
Customers may hire more than one car or driver for special occasions (eg weddings or West End trips) and many customers book limos regularly.
The first time customers make a booking they are given a client number which consists of the first three letters of their surname plus a six figure number. Customers quote their number when they make further bookings. Regular customers can negotiate discounts. Limos can only be booked up to 90 days in advance but drivers are assigned on the morning of the hire.
A customer rings up or comes to reception. If they are an existing customer you look up their details in the card index file. If they are new, you ask them for their details and create a card.
You are the new owner of S-Cars and are shocked to find the company uses this archaic paper-based system.
This is what happens at the moment
A customer rings up or comes to reception. If they are an existing customer you look up their details in the card index file. If they are new, you ask them for their details and fill out a card for them.
You will then need to know the following:
- number of passengers
- date and session required
- pickup point and destination.
The next step in the process is to tell the customer which limos are available.
Once customers have made their choice, details of the booking are written in the ledger. Customer details are held in a card index file, as are the drivers’ details. Pictures and descriptions of limos can be found in a ring binder.
This is what you are going to do about it
You have decided to use database software to provide a more efficient solution. The new database system will hold information about customers, limos and drivers, handle customer bookings and generate invoices and reports once a month.
In preparation for this, a work experience student has typed the contents of the ledger and the two card index files into three text files: bookings.txt, drivers.txt and customers.txt.
Applied GCE Unit 7 – Mark Scheme – Sample AssessmentActivity / ANSWER / POSS. MARK / MAX
Activity 1 / Understanding the problem
The tasks the database has to perform:
Store data about cars, drivers, customers and bookings
Input and edit customer details
Book cars to customers
Book drivers to cars
Print invoices
Mark bookings as being paid for.
Any three of above / 1
1
The information the database must supply:
Amount owed to company and for what
Outstanding invoices
Available cars of large enough size
Available drivers
Customer number
Cost of booking
Any three of above / Sensible recipient / 1,1
2
The data to be input into the database:
Old data / import
Customer details / keyboard
Booking information (itemised) / keyboard
All three of above / 2
Any two of above / 1
2
Applied GCE Unit 7 – Mark Scheme – Sample Assessment
Activity / ANSWER / POSS. MARK / MAX
Activity 1 / The processing that is required:
Apply validation rules
Find customer
Find available cars with enough room (both aspects needed)
List available drivers
Calculate total cost of booking
Calculate VAT
Check if invoice is older than three months
Format invoice
Any six or more of above / 2
Any four or five of above / 1
2
The level of security needed:
Mention of Data Protection Act and either passwords or backup / 1
1
Total marks for Activity 1 / 8
Activity 2 / Structure
Customer Table / 1
Car Table / 1
Booking Table / 1
Driver Table / 1
Session Table / 1
Relationship Customer - Booking / 1
Relationship Booking - Session / 1
Relationship Session – Car / 1
Relationship session - Driver / 1
9
Applied GCE Unit 7 – Mark Scheme – Sample Assessment
Activity / ANSWER / POSS. MARK / MAX
Activity 2 / Import
Data successfully loaded (Customer) / 1
Data successfully loaded (Booking) / 1
Data successfully loaded (Session) / 1
Data successfully loaded (Driver) / 1
Data successfully loaded (Car) / 1
5
Validation
Identification and explanation required
Customer FREO156, Customer number contains four letters not three / 1
Car PU04NCV, Max Passenger > 20 / 1
Booking FDRD9827, Customer SMI0013 doesn’t exist / 1
Booking NITFO134, Number not valid / 1
Driver KL87M328H, NHI number invalid / 1
Driver WE757654K, Earns £89 a session / 1
6
Total marks for Activity 2 / 20
Applied GCE Unit 7 – Mark Scheme – Sample Assessment
Activity / ANSWER / POSS. MARK / MAX
Activity 3 / Making a booking
Customer details
Customer ID field / 1
1
Customer detail fields (name, initial, title, five address fields, two telephone fields)
Five marks for all 10 / 5
Four marks for 8 or 9 / 4
Three marks for 6 or 7 / 3
Two marks for 4 or 5 / 2
One mark for 2 or 3 / 1
5
Find by customer name available / 1
Any other interactive aid (eg Combo Box) / 1
Link to booking form (may be sub form) / 1
3
Booking
Pickup details / 1
View restricted to current customer / 1
Link to session (may be sub form) / 1
3
Session
Session date and session / 1
Number of passengers / 1
Selection method for session (Drop down list, radio buttons) / 1
Registration Number Field / 1
Drop-down list or other selection for registration (must show price) / 1
List does not show limos where max passengers < number of passengers / 1
List of unavailable limos available / 1
View restricted to current booking / 1
8
Applied GCE Unit 7 – Mark Scheme – Sample Assessment
Activity / ANSWER / POSS. MARK / MAX
Activity 3 / Testing
New customer added / 1
New booking added / 1
Session added / 1
More than one session for same booking / 1
4
Layout
User Friendly Layout / 1
1
Total marks for Activity 3 / 25
Activity 4 / Invoicing and payments
Invoice
Date invoice printed / 1
S-Cars name, address, telephone no and fax no on each invoice / 1
S-Cars logo on each invoice / 1
‘INVOICE’ on each invoice / 1
Customer name and address appears once on each invoice / 1
Customer ID appears on each invoice / 1
Suitable layout for address (Number and street on same line) / 1
Overdue notice appears on some invoices / 1
Overdue notice is red / 1
Overdue notice only appears on relevant invoices / 1
Detail separated from remainder of report / 1
Detail shows registration number, make, date of hire, session and cost / 1
Detail grouped by booking number / 1
All booking numbers shown but only once / 1
Total exclusive of VAT shown and labelled / 1
VAT calculated and shown / 1
Applied GCE Unit 7 – Mark Scheme – Sample Assessment
Activity / ANSWER / POSS. MARK / MAX
Activity 4 / Total inclusive of VAT shown / 1
All fields labelled / 1
New page for every invoice / 1
19
Invoice List
Suitable heading / 1
Print date / 1
Value of individual invoice / 1
Invoice totals for customers / 1
Sensible order (eg sorted by Customer ID) / 1
Total owed at end / 1
6
Single Action
Method of linking to reports (eg macro) / 1
1
Testing
Any four of:
Test data includes invoice over 3 months old / 1
Test data includes invoice not over 3 months old / 1
Test data includes invoice with more than one booking / 1
Test data includes invoice for more than one session / 1
Test data includes more than one invoice in invoice list / 1
4
Total marks for Activity 4 / 30
Applied GCE Unit 7 – Mark Scheme – Sample Assessment
Activity / ANSWER / POSS. MARK / MAX
Activity 5 / Main menu
Opens automatically (macro autoexec) / 1
No navigation buttons/close buttons / 1
Exit application method / 1
Call booking form method / 1
Print invoice method / 1
S-Cars name displayed / 1
Tidy layout (buttons aligned and same size layout symmetrical) / 1
7
Total Marks for Activity 5 / 7
Total marks for paper / 90
1
GCE in Applied ICT – Unit 7 - Sample Assessment Material – Issue 1 – March 2005