Unit 18 – Database Design | Assignment

Added on - 19 Sep 2019

  • 6

    pages

  • 2137

    words

  • 51

    views

  • 0

    downloads

Showing pages 3 of 6
PORTFOLIO REFERENCE:STUDENT’S NAME:PROGRAMMEEdexcel Level 3 BTEC Diploma in Information TechnologyUNIT 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 myown and all source material has been referenced. (If you choose to use any informationor diagrams taken from the Internet or redrawn from text books, you must reference thiswork 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 aspreadsheet system to store and manage the information about learners and their drivinglessons.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 decidedto hire more driving instructors and expand his business. He has also started looking atreplacing 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 ofthe 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 IDLesson DateLesson TimeLearner IDLearner ForenameLearner SurnameLearner AddressLearner PostcodeLearner Contact NumberLearner EmailLearner GenderLearner DOBInstructor IDInstructor NameInstructor GenderLesson PriceLesson Paid(Y/N)Booking Staff IDBooking Staff NameBooking 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 validationon 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 databasecontaining at least 5 tables. You will need to provide documentary evidence that demonstratesthat you have:Carried out the normalisation process to 3rdNormal form and have identified at least 5entities.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, theprimary and foreign identified during the normalisation and any input validationrequired 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 havereferential integrity enforced through primary/foreign key relationships to allow cascadeupdating and deletion of linked records. Evidence this task by taking screen shots of thedesign interfaces of the completed database tables and the Entity Relationship Diagramsupplied by MS Access.Task 1c (M2)Using the driving school's spreadsheet, import all the data from the spreadsheet into therelevant 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 toensure consistency. Show how you have implemented any validation rules, input masks anddropdown 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 beupdated from "N" (No) to "Y" (Yes).2