Unit 18 – Database Design | Assignment


Added on  2019-09-19

6 Pages2137 Words1164 Views
PORTFOLIO REFERENCE: STUDENT’S NAME:PROGRAMME Edexcel Level 3 BTEC Diploma in Information Technology UNIT NUMBER AND TITLE:Unit 18 – Database DesignASSIGNMENT No. AND TITLEAssignment 2 - Database Design & ImplementationISSUE DATE:01/03/2017SUBMISSION DATE:29/03/2017DEADLINE MET:YESNORESUBMISSION DATE:04/04/2017ACCEPTED:YESNOI 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)PERFORMANCEEVIDENCE:(tick if achieved)P1P2P3P4P5P6P7M1M2M3M4M5D1D2Functional Skills L2SkillEvidenceEnglishMathematics

Unit 18 - Database DesignAssignment 2 - ScenarioAn 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 beena 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 thatsome 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 theywish.The director has approached your College's IT department with an offer: the student who isable to develop the best database software solution for the driving lesson booking system willbe offered free driving lessons until they pass. If they have already passed, then they cantransfer 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 SurnameLearner Address Learner Postcode Learner Contact Number Learner EmailLearner GenderLearner DOBInstructor IDInstructor Name Instructor Gender Lesson PriceLesson Paid (Y/N)Booking Staff ID Booking Staff Name Booking DateDuring his visit to your College, the director of the driving school specified the followingrequirements:Input requirementsThe following forms will be required:Learner formInstructor formTime slot formBooking staff formLesson booking formSwitchboard (This should be used to enable the user to open all forms, reports andqueries that have been created)1

Unit 18 - Database DesignIt is important that the details are captured accurately and that good use is made of validation on all forms.Output requirementsThe following reports will be required:Outstanding payments – this report displays all lessons that still require paymentThis will display all lesson booking details including: learner name, address, contactnumber, email address, lesson ID, lesson date, lesson time, instructor name, lessonprice, lesson paidInvoice – this report generates an invoice for a particular learner for a particular monthThis will display all order details including: learner name, address, contact number,email address, lesson ID, lesson date, lesson time, instructor name, lesson price, totalpriceBoth of these reports must be designed in accordance with the requirements.Design requirementsA consistency of styling must be employed in order to create a professional image and to helpusers interact with the system. Consistency must extend to at least the following:Layout inc. forms, reports and use of logos etc.ColoursNaming of tables and fieldsTask 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 demonstratesthat 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 eachtable. 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 etcTask 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 processTask 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

End of preview

Want to access all the pages? Upload your documents or become a member.