Consistent Design and Implementation for Driving School Database

The assignment involves designing and implementing a database, forms, reports, and a promotional letter for a driving school. Tasks include ensuring consistency in design requirements, adding buttons to each form for navigation purposes, creating a test plan to verify the database's functionality, writing a report evaluating the completed database, and submitting all work via Moodle.

PORTFOLIO REFERENCE:
STUDENT’S NAME:
PROGRAMME Edexcel Level 3 BTEC Diploma in Information Technology
UNIT NUMBER AND TITLE: Unit 18 – Database Design
ASSIGNMENT No. AND TITLE Assignment 2 - Database Design & Implementation
ISSUE DATE: 01/03/2017
SUBMISSION DATE: 29/03/2017 DEADLINE MET: YES NO
RESUBMISSION DATE: 04/04/2017 ACCEPTED: YES NO
I confirm that in compliance with the College Plagiarism Policy, all work produced is my
own and all source material has been referenced. (If you choose to use any information
or diagrams taken from the Internet or redrawn from text books, you must reference this
work and list your sources, giving a full URL for websites.)
Student signature: ……………………………………… Date: ……………………………………
PRE-CHECKED BY:
(sign & date)
ASSESSED BY: Ashfaq Ahmed
(sign & date)
INTERNALLY VERIFIED BY:
(sign & date)
PERFORMANCE
EVIDENCE:
(tick if achieved)
P1 P2 P3 P4 P5 P6 P7 M1 M2 M3 M4 M5 D1 D2
Functional Skills L2 Skill Evidence
English
Mathematics

Unit 18 - Database Design
Assignment 2 - Scenario
An established driving school with five instructors and about 40 learners currently uses a
spreadsheet system to store and manage the information about learners and their driving
lessons.
However, due to the high success rates of learners attending the driving school, there has been
a huge increase in demand for driving lessons. As a result, the company director has decided
to hire more driving instructors and expand his business. He has also started looking at
replacing the spreadsheet based booking system with a new and improved one.
One of factors contributing to the success of the business is due to the school being aware that
some people are put off learning to drive because they feel uncomfortable with an instructor of
the opposite sex, so all learners have the option to choose an instructor of the same sex if they
wish.
The director has approached your College's IT department with an offer: the student who is
able to develop the best database software solution for the driving lesson booking system will
be offered free driving lessons until they pass. If they have already passed, then they can
transfer the prize to a family member or friend.
The driving school spreadsheet records the following information:
 Lesson ID
 Lesson Date
 Lesson Time
 Learner ID
 Learner Forename
 Learner Surname
 Learner Address
 Learner Postcode
 Learner Contact Number
 Learner Email
 Learner Gender
 Learner DOB
 Instructor ID
 Instructor Name
 Instructor Gender
 Lesson Price
 Lesson Paid (Y/N)
 Booking Staff ID
 Booking Staff Name
 Booking Date
During his visit to your College, the director of the driving school specified the following
requirements:
Input requirements
The following forms will be required:
 Learner form
 Instructor form
 Time slot form
 Booking staff form
 Lesson booking form
 Switchboard (This should be used to enable the user to open all forms, reports and
queries that have been created)
1

Unit 18 - Database Design
It is important that the details are captured accurately and that good use is made of validation
on all forms.
Output requirements
The following reports will be required:
 Outstanding payments – this report displays all lessons that still require payment
This will display all lesson booking details including: learner name, address, contact
number, email address, lesson ID, lesson date, lesson time, instructor name, lesson
price, lesson paid
 Invoice – this report generates an invoice for a particular learner for a particular month
This will display all order details including: learner name, address, contact number,
email address, lesson ID, lesson date, lesson time, instructor name, lesson price, total
price
Both of these reports must be designed in accordance with the requirements.
Design requirements
A consistency of styling must be employed in order to create a professional image and to help
users interact with the system. Consistency must extend to at least the following:
 Layout inc. forms, reports and use of logos etc.
 Colours
 Naming of tables and fields
Task 1a (P2)
Using the information supplied in the scenario and functional requirements design a database
containing at least 5 tables. You will need to provide documentary evidence that demonstrates
that you have:
 Carried out the normalisation process to 3rd Normal form and have identified at least 5
entities.
 Produced an Entity Relationship Diagram (ERD)
 Created a data dictionary that contains information about all the fields required for each
table. The data dictionary should identify the data types and sizes for each field, the
primary and foreign identified during the normalisation and any input validation
required i.e. input masks, validation rules etc
Task 1b (P3)
Implement your designs using MS Access by creating a set of tables (min 5 tables) that have
referential integrity enforced through primary/foreign key relationships to allow cascade
updating and deletion of linked records. Evidence this task by taking screen shots of the
design interfaces of the completed database tables and the Entity Relationship Diagram
supplied by MS Access.
Task 1c (M2)
Using the driving school's spreadsheet, import all the data from the spreadsheet into the
relevant tables. You will need to document each step of the importation process
Task 2a (P4)
Design and implement all the forms that have been outlined in the requirements taking care to
ensure consistency. Show how you have implemented any validation rules, input masks and
dropdown boxes you identified in the design stage.
You will also need to create a sub-form for learners so that lessons that have been paid can be
updated from "N" (No) to "Y" (Yes).
2

Unit 18 - Database Design
Task 2b (M4)
To avoid users leaving records incomplete, use VBA code on either the Lesson booking form or
Learner form to perform a presence check on all the fields to ensure that they are not left
blank. If a field is left blank you will need to draw attention to this field by highlighting it and
displaying an error message. Evidence this task by taking screen shots of the VBA code and
interfaces, with annotations.
Task 3 (P5)
Create all queries that are required from the output specification. In addition, design and
implement the queries below to enable the user to retrieve the following information:
 Learners Records
 Learners Bookings
 Total number of lessons booked by females in the last year
 Total number of lessons booked by males in the last year
 Total number of lessons booked by a female learner with female instructor in the last
year
 Total number of lessons booked by a male learner with male instructor in the last year.
Task 4a (P2), (P6)
Design and implement all the reports that have been outlined in the requirements taking care
to ensure consistency in line with your form designs.
Task 4b (P6)
Ensure consistency in design requirements as per the scenario
 Layout of forms and reports
 Use of logos (forms and reports)
 Naming of tables and fields
 Presentation, i.e. logos
In addition, you need demonstrate the use of an advanced feature. You are required to add
buttons to each of the forms for navigation purposes. An observation record provided to you by
your tutor will be used as evidence for this task.
Task 5 (M3)
Design and implement a promotional letter in MS Word that will be sent to all of the driving
school's learners advertising a 10% discount on all lessons (for new and existing learners) next
month. This letter will need to make use of Word’s Mail Merge feature to obtain all the learners’
details from MS Access. You will need to make sure that the letter’s template is professionally
presented.
Task 6 (P7)
Once you have completed the database, you will need to make sure that the database is
working correctly. You are required to create a test plan (see Figure one) that tests the main
database functionality.
Any test failures should be corrected and annotated using screen shots in a test log.
Figure One
Test Plan
Tester:
Test Location: Test Date:
Test No: Purpose: Test Data: Expected
Result:
Actual
Result:
Pass/Fail:
3

Unit 18 - Database Design
Task 7 (D2)
Write a report that examines the completed database and produce a detailed evaluation of
how the finished product meets all the user needs and requirements as stated in the original
assignment brief.
4

Unit 18 - Database Design
Guidance
Work through each task, making copies of your work as you complete them. These will be
required in your submission. Any diagrams should be produced using appropriate tools.
Take regular backups of your work. This will enable you to recover quickly should the
system fail and also allow you to backtrack if you decide to add to, delete from or edit your
work.
Discuss your recommendations, giving the reasons for the choices you are making, with
your tutor, who may give advice. Document your choices as you progress. Update all your
documentation as changes are made.
Submission Requirements
A Word-processed report of professional standard covering task 1 & 2, submitted
electronically via Moodle.
Warning: All media must be virus free!
Media containing viruses, or media which cannot be run directly, will result in a FAIL grade
being awarded for this module.
Your responses to every task, including clear and full workings, which have been
proofread, checked and corrected. Do not incorporate your answers within the task sheet.
Instead, produce a separate document for these - you may copy and paste diagrams and
tables from the task sheet into your answers document if you wish.
All work must be submitted via Moodle on the link provided by your tutor
ASSIGNMENT SUBMISSIONS
If your assignment is formally submitted by the deadline, but your evidence does not
meet the standard required, you will be given one more opportunity to resubmit on a new
date agreed with the lecturer but you will be given only Pass grade upon resubmission of
appropriate work (if resubmitted work meets the required criteria).
If your assignment is submitted after the deadline, you may not be entitled to Merit or
Distinction grades. Therefore you must be sure that the evidence submitted is of at least
Pass standard (within the agreed date).
If you are resubmitting work, the original submission including accompanying assignment
documentation must be resubmitted along with a completed Resubmission Sheet
(available on Moodle) followed by the revised work.
ASSESSMENT EVIDENCE
All documents related to the assignment must be submitted via link on Moodle.
5