Pearson BTEC Level 4 HNC/D Diploma in Computing and Systems Development Assessment Extra Notes Database Design Concepts Assessment Date for Submission: 9th February 2017 (The submission portal will close at 13:59 GMT) Set by: John PiperiasPage 2 of 11 Assignment Brief As part of the formal assessment for the HNC/D programme you are required to submit an assignment for each module. Please refer to your Student Handbook for full details of the programme assessment scheme and general information on preparing and submitting assignments. After completing the module you should be able to:  Understand databases and data management systems  Understand database design techniques  Design, create and document databases Assignment Task CASE STUDY: Tennis League of Dundee (TLoD) You are required to construct a relational database system to record details for a non-profit organisation that manages the tennis competition around Perthshire with the main headquarters centred in Dundee (Scotland). The main part of the system is to receive and acknowledge any new member registrations, using a friendly user interface – this allows any prospective member to enter their personal details (i.e. name, address, contact, etc.). Although the organisation is non-profit and a registered charity, it does permit new members to make a (compulsory) donation once they have completed the membership registration form. This donation itself can either be done via (BPay, ApplePay or AndroidPay) with the later accepted by staff members at the various courts around Dundee. A newsletter is one of the main forms of communication between the administrators of the league and its players, this newsletter sets of the rules of competition, the scoring and a list of actual games that players are scheduled to play at the main centre in Dundee. This newsletter is sent to all the members on a monthly basis and allows the members to send any feedback to the organisation to the company secretary who manages all the administrative functions of the organisation. Concerning the actual games: Each singles game records two players and one result from that particular division. Players can only compete within their own particular set division. When a match has being completed a score is recorded in the following format 3 set format (e.g. 6-4, 4-6, 6-2). The system needs to be able to record each players win and loss, the points on the ladder, matches played. A separate part of the system will contain the competition ladder which is divided into a number of separate divisions, the five (5) sections which are based on age, with the open draw also separated by sex (i.e. an open women's competition, and a men's competition) with each division having a number of positions or rankings according to the players' proficiency and their accumulated points throughout the season.Page 3 of 11 The system will need to also identify future fixtures that is it should show any scheduled games to be played in the near future (i.e. a period of 3 months). It should also have an archival facility in that it will show any past fixtures for that season along with the scores. The system will need to record the various staff members at the tennis court centre, so a record is kept of who took payments from which member. This member will also act as the official and umpire for the various fixtures throughout the season. Since this is a charity organisation, the staff members are also players from the various divisions of the ladder, which naturally means that their personal details are also recorded on the system. Naturally NO CASE STUDY can capture all the details of the operations of the franchise; you are permitted to make any assumptions (provided you write these down).Page 4 of 11 Task 1 (LO 1.1, LO 1.2, LO 2.1, LO 2.2) Business Case Extra notes: The mock-ups explained in Task 1C, Task 1D – these are more for presentation and layout, so are non-functional, that is why I recommend some generic packages like MS Word. But they should show the command buttons, navigation buttons and layout for the system being proposed. (You can however use the actual form/report generator that the database itself has, provided you know how to use it. Of course you would naturally do this once the database has being built) Task 1A. Explain why a relational database would be a suitable information system for the organisation such as the one provided in the case study and provide (3) three reasons to support your recommendation (600 words). Task 1B. Explain (3) three distinct advantages concerning what a database management system provides to any organisation. Note: Make these relevant to the organisation in the case study (600 words). Requirements Definition Read the case study carefully, and decide the important (i.e. key) features of the system. Construct a basic prototype that will display the following: Task 1C. Design a set of Initial screens that can be shown to the client – this allows for the input of information to satisfy the user requirements, you should produce at a minimum (5) five forms. Submit a softcopy that has a set of screen shots that illustrate the screen designs with appropriate narrative for each screen. (200 Words) Task 1D. Design a set of typical reports that would be appropriate for the proposed company database – this requires you to assume the role of the user/manager and list three (3) likely reports he/she might need to do their job. It must show the report, its main features, and then populate it with some likely test data. Submit a softcopy that has a set of screen shots that illustrate these proposed screen designs. (200 Words) Note: A suitable package to do the above task (A to D) would be Microsoft Word or any similar product.Page 5 of 11 Task 2 (LO 2.1, LO 2.2) Extra notes: Task 2B: A key part of the system is the Entity Relationship Diagram – you must you the correct formalism as shown in the ilearn notes and the textbook. Care must be taken to identify the candidate entities from the case study. A second draft is typically done, and careful re-examination of the case study to ensure you have all the potential entities. Task 2C: A complete and thorough analysis using Normalisation is required and evidence that every table satisfies 1, 2, and 3 Normal forms. Development Use a database development strategy to decide the main elements of the database – this will include nominating the: Fields (also the primary keys and foreign keys) Tables Links and Cardinalities Task 2A. Produce a Data Dictionary – which contains the following information: table, field type, field size and field description. (400 words). Note: A suitable package to do Task 2B would be Microsoft Word or any similar product. Database Design Develop an initial prototype of this case study system, therefore in your design phase you and your team should create the following appropriate diagrams. Use the MySQL Workbench (or equivalent tool set) to create the following: Task 2B. Propose an Entity - Relationship diagram that matches the information provided in the case study. Use the appropriate formalism and structure as explained in the theory. (Diagram along with 200 words). Task 2C. Develop the Normalised Schema for the system you have proposed – normalise to 3rd normal form. Provide a relevant justification which shows how your ER-schema passes each of the normal forms. (Diagram along with 600 words). Note: A suitable package to do Task 2B would be Gliffy, EDraw, Microsoft Visio or any similar product.Page 6 of 11 Task 3 (LO 3.1, 3.2, 3.3, 3.4) Extra notes: Task 3A, Task 3B: Screenshots to show the tables and records inside of these tables is sufficient to show me the tables have being built and records entered. Task 3C: User guide is done from the perspective of a novice user – that needs to learn how the system operates, you can employ re-use of the forms already devised in Task 1C and Task 1D, along with the tables you have built in Task 3A, 3B. Implementation of a Relational Database Task 3A. Generate a physical database, using the final normalised ER – Diagram to create this database. Use MySQL server, Oracle Express or an equivalent relational database to create the system. Provide a relevant screenshots of the final system that displays the entire set of tables as evidence of its creation. (Labelling - 100 words). Populate the Database Task 3B. Demonstrate the entry of six (6) records into the database system that you previously created. These records might be used to display to the client that the system is operational. Provide screenshots of the final system that displays (3) three of the tables with the mock data inserted. (Labelling - 100 words). User Help Guide Task 3C. Design a comprehensive user manual that will describe how to use the database system you have developed. Screenshots of the main menu and navigation between the sub-menus along with accurate descriptions is an important feature of this section of the assignment. (800 Words) Note: You may use previous screenshots (forms, reports and data screens) to create this manual. Improvement Task 3D. Reflect upon your new developed understanding of database design and provide a lessons learned log – that indicates what you found important and any improvements you wish to make for the next version of the database, and other personal reflections. (200 Words) Note: A suitable package to do Task 3A, Task 3B would be MySQL community server or any similar product. A suitable package to do Task 3C, Task 3D would be Microsoft Word or any similar product.Page 7 of 11 Formative Feedback Opportunity In order to assist with your learning and to give you some early, formative feedback you are encouraged to submit "one review of a full draft of your report", the document must be emailed to the tutor by 9th January, 2017. The tutor's email address can be found on the module front page on iLearn and you will receive feedback within seven days. Student Guidelines 1. You should write the answers to the tasks in the form of a report, including a title page, contents page, and sections headings based on the tasks and sub-tasks. 2. You must ensure that the submitted assignment is all your own work and that all sources used are correctly attributed. Penalties apply to assignments which show evidence of academic unfair practice. (See the Student Handbook which is in the Induction Area). Please also refer to the 'Guide to Unfair Practice in Assessment' on the module page on ilearn. 3 You MUST underpin your analysis and evaluation of the key issues with appropriate and wide ranging academic research and ensure this is referenced using the Arden University (AU) Harvard system. The My Study Skills Area contains the following useful resources: Guide to Harvard Referencing http://moodle.bl.rdi.co.uk/guides/HarvardRef/AU_Harvard_Quick_Ref_Guide.pdf Guide to Harvard Citation http://moodle.bl.rdi.co.uk/guides/HarvardRef/AU_Guide_to_Harvard_Citation.pdf You must use the AU Harvard Referencing method in your assignment. 4. You should use diagrams and tables of figures where appropriate ensuring to reference their source using the AU Harvard Referencing method. 5. You are required to write your assignment within 4000 words in order for your research and summarising skills to be developed, and for effective time management. You are required to ensure that the assignment addresses all of the assessment tasks. In the interests of good academic practice, an assignment submitted with excessive word counts (i.e. more than 10% in excess of the limit) will be returned to you. You will be given a maximum of 48 hours to edit the work to reduce the word count to the permitted maximum. In the event that the submission is still regarded as excessively long then, in line with advice received from Pearson, we will be unable to accept it for marking and you will receive a Refer grade for Non-Submission. The word count excludes the title page, reference list and appendices. Where assessment questions have been reprinted from the assessment brief these will also be excluded from the word count. ALL other printed words ARE included in the word count. Printed words include those contained within charts and tables. 6. Your assignment should be submitted as a single document. For more information please see the "Guide to Submitting an Assignment" document available on the module page on iLearn.Page 8 of 11 Recommended Additional Resources Textbooks Online E-Book recommended for this course – Beginning database design solutions by Gavin Powell: http://it-ebooks.info/book/1161/ then click on the relevant e-book download link Online 'library' resource available via ilearn EBSCO Business Source Complete – Rob, P., Morris, S. and Coronel, C., Database Systems 10th Edition, Cengage Learning, 2013 A First Course in Database Systems (3rd Ed.) by Jeffrey Ullman and Jennifer Widom Database System Concepts (6th Ed.) by Avi Silberschatz, Henry Korth and S. Sudarshan MyiLibrary Magazines, journals and newspapers Websites www.oracle.com www.mysql.com www.edraw.com www.gliffy.comPage 9 of 11 Assessment Criteria for Pass To achieve a pass you must meet all of the assessment criteria as stated below. Failure to cover all of the assessment criteria will result in a referral grade and you will be required to re-submit your assignment. Further guidance on completion of your assignment can be found in the guidance notes which are posted on the group learning space by your module tutor. For additional support please post questions onto the group learning space, or email [email protected] Learning Outcomes/ Assessment Criteria Criteria Met For tutor use (you may wish to use this in your preparation for your assignment submission) LO1 Understand databases and data management systems 1.1 Analyse the key issues and application of databases Task 1 1.2 critically evaluate the features and advantages of database management systems Task 1 LO2 Understand database design techniques 2.1 Analyse a database developmental methodology Task 2 2.2 Discuss entity-relationship modelling and normalisation Task 2 LO3 Be able to design, create and document databases 3.1 Apply the database developmental cycle to a given data set Task 3 3.2 Design a fully functional database (containing at least four interrelational tables) including user interface Task 3 3.3 Evaluate the effectiveness of the database solution and suggest methods of improvement Task 3 3.4 Provide supporting user and technical documentation. Task 3Page 10 of 11 Assessment Criteria for Merit To achieve a Merit all of the Pass criteria need to be met, then the tutor will assess whether you have met the Merit Criteria. Each of the Merit criteria must have been met at least once within the assignment. The following statements are examples of how a merit may be achieved, if you do meet the Merit Criteria by showing you have reached this level in other ways then credit will be awarded for this. You will need to meet M1, M2, and M3 at least once. 1.1 A range of credible sources of information have been used to identify appropriate products and solutions 1.2 Effective judgements have been made when creating the design of the database M1 2.1. Material presented is clearly and directly related to the scenario 2.2 Evaluate the effectiveness of the database solution and suggest methods of improvement M2 3.1 Material is presented in a logical and accurate manner matching the stated requirements for each task. 3.2. Appropriate levels of material are presented, taking into account the target audience. 3.2 Harvard referencing attempted throughout 3.3 Assignment has been completed fully and on time to the original deadline M3Page 11 of 11 Assessment Criteria for Distinction To achieve a Distinction you have met all of the Pass and the Merit criteria. Each of the Distinction criteria must be met at least once within the assignment. The following statements are examples of how a Distinction may be achieved, if you do meet the Distinction Criteria by showing you have reached this level in other ways then credit will be awarded for this. You will need to meet D1, D2, and D3 at least once. 1.1 Proposals offer clearly justified matches to the requirements, with alternatives being discussed. 1.2 The work demonstrates a clear understanding of the overall requirements of the system as well as the individual system requirements D1 2.1 Independent research, outside that provided in class, has been utilised to prepare the work 2.2 Assignment has been completed fully and on time to the original deadline D2 3.1 Solutions presented show an innovative approach to systems design based on the latest available equipment. 3.2 A superior understanding of database design concepts is demonstrated by the designer and opportunity to make positive recommendations on how the system would scale. D3