Model Assignment: Candidate Information
OCR Level 3 Nationals in ICT
Unit 6: Advanced Databases
CANDIDATE NAME:
General Information for Candidates
Notes to Candidates
This assignment has been designed to meet the full assessment requirements of the unit. Candidates will need to take part in a planned learning programme that covers the underpinning knowledge and skills of the unit.
You are expected to carry out preparations prior to undertaking the tasks; there is no time limit for this.
Each candidate must produce individual and authentic evidence for each task within the assignment.
Candidates may use information from any relevant source to help them with producing evidence for the tasks. All sources used must be quoted within evidence submitted where applicable.
You must Pass this Unit to achieve the full qualification. If candidates do not meet the minimum PASS requirements for the assessment objectives, further work will be required.
It is your responsibility to ensure that you have read the assignment in full. If you do not understand any part of the assignment, check with your Tutor.
General Information for Candidates
Q Do I have to pass this assignment?
A Yes. You must pass this assignment to achieve the full qualification.
Q What help will I get?
A Your tutor will help you when completing the OCR model assignment and will make sure that you know what resources/facilities you need and are allowed to use.
Q What if I don’t understand something?
A It is your responsibility to read the assignment carefully and make sure you understand what you need to do and what you should hand in. If you are not sure, check with your tutor.
Q Can I copy other people’s work?
A No. The work that you produce must be your own work and you may be asked to sign a declaration to say that the work is your own. You should never copy the work of other candidates or allow others to copy your work. Any information that you use from other sources, e.g. books, newspapers, professional journals, the Internet, must be clearly identified and not presented as your own work.
Q Can I work in a group?
A Yes. However, if you work in a group at any stage you must still produce work that shows your individual contribution.
Q How should I present my work?
A You can present your work in a variety of ways, e.g. hand-written, word-processed, on video.
However, what you choose should be appropriate to the task(s). For some work, e.g. presentations, coaching sessions, role-play, work experience, you will need to provide proof that you completed the task(s). A witness statement or observation sheet could be used for this. If you are unsure, check with your tutor.
Q When I have finished, what do I need to hand in?
A You need to hand in the work that you have completed for each task. Do not include any draft work or handouts unless these are asked for. When you hand in your work make sure that it is labelled, titled and in the correct order for assessing.
Q How will my work be assessed?
A Your work will be marked by an assessor in your centre. The assessor will mark the work using the assessment objectives and the grade descriptors in the qualification specification.
Scenario
Peak PC Supplies
Peak PC Supplies is a local UK computer warehouse manufactures who supply and delivery pc parts and accessories to local warehouse suppliers and national organisations.
Recently they have switched from a local single database system to a multi management system that has been nominalised to the 3rd form.
The new system has three database models. Each model has been chosen to hold all customer details, employee details and parts and accessories details. Your role means that you will need to design and create a working management system that needs to be implemented within 2 months. All staff will need access to the database thereafter so you will need to ensure that they are fully trained in how to use the system.
The senior team have also asked you to ensure that there is a user guide and technical guide made available should any aspect of the database cannot be managed.
Each model will need to hold at least 60 records that can be accessible at all times by the senior team.
Within the database system you will need to ensure that the ERD is able to manage the financial data required for Peak Supplies to run the reports required.
The database must be able to access all areas of the database through customised areas, show the reports created as well as allow the user to add or amend the data required.
Tasks
Task 1: Design a relational database to meet the needs of an organisation
Assessment Objective 1
To complete AO1, you must not only design the relational database that Peak PC Supplies will be transferring to, but also explain the purpose and audience of the database and it’s design.
You will need to produce a design for a working relational database that will contain at least 3 tables and 60 records. The design should be a complete design and should include all features of the database such as forms, queries and reports. All data should be accessible and editable to meet the needs of the organisation.
A Identify the purpose and audience of the management database system you have
been asked to design.
• How can a customised database help improve the efficiency of the business
• Describe the needs of the different users within the organisation.
• Why is it best for the database to be designed to 3NF form? Explain.
B Prepare the database system for the organisation. Ensure the designs for the
different tables identified , are in 3NF.
· Explain the different types of data that will be included in tables.
· Within each of the three tables designs, describe and include the different
elements :
§ Primary keys
§ Foreign keys
§ Data validation rules
§ Input errors / messages
§ Entity relational Diagram.
C Draw and explain the different entity relationships that will be created within the database.
D Design the forms that will be accessible and the reports for the users. Any form that will be customised, explain where and how you will do this.
E Based on the different user requirements you identified in tasks A and D, design the user interface that will assist the users of accessing, amending and adding additional data as well as accessing all other areas of the database
Task 2: Create the database according to the design using advanced
features.
Assessment Objective 2
The senior team have asked you to use a suitable management software application to create a working relational database in line with designs it has agreed. The database will need to include:
· primary keys
· foreign keys
· linked tables
· at least 4 data types e.g. Boolean, text, number, date
· data validation including input masks
· customised error messages
· data for at least 60 records across the tables
Printouts showing the implementation would provide the most suitable form of evidence. Screenshots with annotation showing major parts of the implementation would also be appropriate
Task 3: Interrogate the database.
Assessment Objective 3
Design and create appropriate queries that are suitable for the purpose of the organisation. Explain in detail how you could use the queries created.
Evidence provided should be printouts of the design of the queries along with the results of the queries when run.
A Create a query to show the subtotal for the unit(s) ordered using a calculated query. Use
appropriate tables to run the query.
B Using at least two parameters, find out the income Peak Supplies made during the first quarter period. The financial year starts in April., put the results into ascending order showing the employee with the highest sales made. (two parameters Between and And range operators).
C Using at least two linked tables produce a query to show the sales orders for organisations
who only ordered monitors and keyboards. (Like monitors and keyboards)
D The main sales manager, has asked for data on all sales above £500.00 in the north or
south of England during the month of June and August . (Or) (> £500)
E Run a query to show all orders purchased by Organisations XYZ Ltd during the month of
May but not under £800.00. Sort the data for one of the fields appropriately.
The Sales Manager has also asked for the data to be complied with organisation VixPC Ltd
to make comparisons. (Not ) , (multiple criteria)
.
G Each employee was given a target to sell at least 50 units during the summer month of
June. Work out which employee (s) has / have not met their targets.(<50 )
H Towards the end of the summer period, Peak Supplies noticed there has been a dramatic
decline in the orders for CD packs, Find out which area this has affected and why.
Work out which employee has reached their target of over £100??/ sales during the first
quarter (more than).
Higher level : parameter queries and crosstab queries, using multiple criteria on linked tables.
at least three different logical operators (eg AND, OR, NOT, LIKE)• at least three different range operators (eg ,>,>,=)
Task 4 Produce reports
Assessment Objective 5
The management team would like the results of the queries to be produced in suitable formats
so that they can be presented at the next General Executive meeting.
Evidence provided should be printouts of the design of the reports along with the reports
that are produced when the query has been run.
The report layout and formatting should be appropriate and data should be fully displayed.
Provide evidence of where you have customised the report, including sorting or grouping
on fields as appropriate.
A Produce reports to include:
o at least one report which is customised.
o at least one report sorted on more than one field.
o at least one report grouped on one or more fields.
B To achieve a Higher Grade: Reports should match the designs and at least one report
displaying grouped and overall summaries.
.
Task 5: Create a customised User Interface
Assessment Objective 4
The senior team of Peak Supplies wish to ensure that there is consistency in the performance of the new database system. They have asked you to make sure that the database interface opens with the main Orders Form so that the Orders team are able to input data directly into the database as they feel they are the team who will be accessing and amending the database on a regular basis.
They also would like access to all other areas of the Database.
A Create and amend the User Interface for the organisation.
B Ensure that all forms, tables are accessible for the organisation.
Provide evidence of where you have customised your forms and interface and why.
For higher grade: All forms and interface must be customised including any sub-form.
Task 6: Produce a user and technical guide
Assessment Objective 6
To ensure that all users are able to use the database and its advanced features effectively,
A Produce a user documentation that will allow the user to use various aspects of the
database.
The documentation could cover most of the following;
· how to start the database
· how to append, delete and edit records,
· instructions about using queries and producing reports,
And could cover most of the following;
· examples of screens and data entry forms,
· advice about how to respond to error messages
· and examples of data output screens and printed reports.
B Produce a technical guide for the database.
A technical manual should also be produced containing information about;
· details of the hardware, software and other resources required,
· a detailed entity-relationship diagram,
· a detailed data dictionary and details of validation
· and verification procedures.
Task 7: Test the Database
Assessment Objective 7
Create a test plan and carry out tests on different parts of the database.
· database meets original design brief
· validation
· forms
· queries
· reports
You do not need to include any screenshots of testing the areas, but it will be appropriate to
include the areas of where there changes required.
Ensure that any issues that have been flagged up in the test, appropriate changes have been
made.
Task 8: Evaluate the Database
Assessment Objective 8
Provide an evaluation of the effectiveness of the database in relation to its users needs.
· purpose and audience of the database
· specification of the database.
You do not need to include any screenshots of testing the areas, but it will be appropriate to
include the areas of where the changes required have been made.
The evaluation must be detailed and accurate to achieve a higher grade with detailed
suggestions and improvements to refine any changes.