The SUMMER Olympics in Brazil

The SUMMER Olympics in Brazil You and your team have been lucky enough to be selected by Rio - Brazil 2016 Olympic Games Organisers to produce a sophisticated system to record the attendance and activities of the thousands of competitors. The following business rules have been distilled from interviews with organisers and represent the basis from which a database system must be designed and implemented to cover this function. • Teams may come from any country which is part of the Olympic Movement. Each national team will consist of a varying number of competitors, trainers, medical personnel and administrators. • Most team members stay in the Olympic Village to which they can only gain access by using their biometric identity pass. This is also used to gain access to the training facilities. Not all competitors will use these facilities. • The Village is designed in clusters, each cluster containing a number of accommodation units linked to communal facilities, such as a kitchen and a lounge. • A competitor may compete in more than one event. • There are many different types of event, which may be individual events (e.g. high jump) or team events (e.g. hockey). • Events are held at different venues. • Some events (e.g. decathlon) may have a series of events associated with them. • Many events have various stages, i.e. heats e.g. “competitor X is competing in the semi-final heat A of the 100m freestyle for women” and a Final. • Each event is officiated over by differing combinations of starters, referees, timekeepers, judges, etc. depending on the roles required by each event. • Each individual’s or team result in every heat and final must be recorded. • Competitors can be drug tested immediately after any stage of any event. • There are likely to be World and Olympic records for each event. • There is a medal ceremony for each event at which the national anthem of the winning competitor / team is played. The awards are presented by a variety of senior officials of the Olympic committee, senior members of each nation’s teams and other dignitaries. The organisation of the ceremony is in the hands of employees of the host organisation. • There are a variety of officials employed by the host organisation running the Games. Assessment Details You will be undertaking a database development project for a client (in this case your tutor) based on given requirements. Using feedback from this stage you will go on to design, implement and test your system using appropriate database techniques and technologies. You will also be expected to evaluate your development processes relating to the System Development Life Cycle, as well as considering emerging technologies that could be used to improve the organisation’s data management and decision making processes. 1.1 Part 1: Database Design (40%) 1. Produce an Extended Entity Relationship Model (EERM) using top-down approach for the given case study organisation. List Validation Questions (6-10) used to validate your EER Diagram. Use the notation taught in the module. [15 Marks] 2. Normalise the Forms in the Appendix (apply bottom- up approach). [10 marks] 3. Produce a Composite EER Diagram (based on outputs from bottom up and top down approaches). Derive this EERD to produce a final list of relations/tables and for each relation/table produce a full list of attributes, attribute definition and occurrences (data values), clearly defining attribute(s) that are Primary Key(s) and any derived Foreign Key(s) attributes. [ 10 Marks] 4. Physical Design – produce a final list of table that will include any necessary physical design considerations and document this clearly. [5 Marks] You are expected to use QSEE to produce the designs of your system. 1.2 Part 2: Implementation and Report (60%) 1.2.1 Implementation and Testing (Assessed via Demo) Implement a sub-system of the produced composite model in assignment part 1. You will be given general feedback for the design part with a suggested solution that you can use for this task. Note that all students must complete tasks 1 and 2; and either task 3a or 3b. 1. Tables and Data Implementation in Apex using SQL Script environment. Also, create a view, based on your sub-system table(s). Implementation a sub-system (4-6 related tables). As a guideline you should populate each table with at least 10 rows. The data should be designed to provide appropriate results from the queries, and should reflect the requirements of the organisation. [15 Marks] 2. SQL queries (min of 5 to be produced) To test your system, you are asked to produce a set of queries which will be specified in plain English (as business statement) and implemented using SQL statements. Your SQL statements will need to be complex to achieve high marks. Please note that each query is marked on its own merits. [15 Marks] 3.a Build appropriate APEX Forms/Pages to manage the data (insert/update/delete) of your sub-system. Include a master/detail. Consider including a home page, calendar, reports, charts, etc. Higher marks will be awarded for applications which effectively utilise a good range of APEX functionality. [10 Marks] OR 3.b Import your APEX tables into the SAS Enterprise guide and a. Produce 3 programs to manipulate data using SAS program [5 Marks] b. Produce 3 reports using SAS program [5 Marks] 1.2.2 Report Part 1: Produce a report that critically evaluates database SDLC, approaches and the role of database management for your case study organisation. Critically research and reflect on your database development process, considering issues such as data quality, data integrity, performance, data maintenance, and including security considerations that system should/could have. Part2: Include recommendations concerning appropriate existing and/or emerging technologies/processes that the case study organisation could consider for a future database management system implementation. Also, list ten issues that may cause this DBMS’s performance to be affected. Word count: 1500 words [20 Marks] We strongly advise that you read the marking scheme carefully. You will be expected to research appropriate and recent literature, analyse this and show evidence of a synthesis of views within your report. Clear references to the database case study you have investigated are expected. Marks will be awarded for critical evaluation, discussion, analysis and synthesis of views. 1.2.3 Deliverables part 2 Submit only one document file (converted to pdf). You must use the Assessment Template part 2(found on VLE) for this submission, found on the VLE, by the deadline. Submitted file should include screen shots with clear evidence of all task's code working successfully and where appropriate show evidence of code being tested. Where you feel it is an appropriate, include a brief discussion. SQL code for tables and SQL statements should be included. All Oracle Apex screenshots have to include your student id part. 1.2.4 Preparation for the DEMO • You will each be asked to demonstrate your work within 10 minutes. • Failure to attend the demo session will be treated as a non-submission and late demos will apply penalties from that demo date. • These are some sample questions your tutor may ask you during your demo session: o Explain/Show the table structure. What constraints have you implemented? o What data have you got on the tables? o Explain your SQL statement. What does the join part do? Why do we write a self-join query? • You are being assessed on your understanding of your system (APPLICATION) and the underlying principles NOT just your ability to navigate round it. Marks will be lost for poor understanding or explanation. • Ensure that you demonstrate your application in a way that provides evidence to your tutor that you have met the specification of the application, read the marking criteria and have a good understanding of the subject. • You are strongly advised to PRACTISE your demo, so that you make full use of the limited time. 2 Marking Schemas Part 1: Database Design 40% Student Name/Id: Tutor marked: Moderator: Final Mark: Evidence Distinction (70+) Merit (60-69%) Pass (40-59%) Fail (0-39%) General comment mark 1.Extended ERM(EERD, attributes, assumption, occurrences, identifiers); top-down [12 Marks] Validation questions [3 Marks] Excellent deployment of extended modelling skills, with complete and accurate coverage of advanced entity models, cardinality, optionality, relationship naming in accordance with the requirements. Full and accurate list of attributes, including identifier(s), occurrences, entity and attribute and entity definitions. Excellent research conducted into relevant literature, analysed and used to evidence of a synthesis of the views within the report. Validation questions meaningful. Mostly accurate entities identified need to address requirements with correct cardinality. Additional requirements specified and applied. Almost a full list of attributes, including identifier(s), occurrences, entity and attribute and entity definitions. Validation questions have not much relevance to potential implementation. Partly addressed all issues on the model, with errors in relationships naming, cardinality and wrong appliance of advance entities models. Additional requirements specified and applied, but not fully correct. Some consideration for entity attributes and keys, and/or cardinality. No literature review conducted nor any views within your report shown. Only few specified, with no specific validation needs. Requires further work to address errors in relationships naming, cardinality and incorrect appliance of advance models. Additional requirements specified but not applied. No reference to theory to support arguments No validation questions. 2.Normalisation (bottom up) [10 Marks] 3. Composite EERD [3 Marks] Final list of attributes, definitions, data and keys [7 Marks] One NF has few anomalies but all normal forms, rules correctly applied. Pro-forma used Final EERD includes outputs from bottom up and top down approaches. Full list of attributes completed, attribute def., PK meaningful, all FKs correctly derived from the EERD, data values defined for each attribute meaningful, and clear evidence of M: N relations data included. Shows good understanding of 2 of 3 Normalisation rules, with some anomalies in one of the NF, not both. 1NF and UN are correctly specified. Pro-forma used. Parts of the findings included in the composite model. Full list of attributes and definitions completed, PK correctly defined, all FKs correctly derived with minor anomalies, data values defined for each attribute. Shows understanding of 1 of 3 NF, with some errors in tables and with keys. UNF is correctly specified. Pro-forma not used. Minor changes to the top-down EERD made. List of attributes incomplete, no attributes definition, PK defined, major anomalies with FKs, no occurrences presented for each attribute. Correct use of candidate key and list of attributes in un-normalised form but not completely. 1, 2 and 3 NF has a number of anomalies. No reference to theory to support arguments. No attached composite model or no attempt made to include findings from the normalisation. List of attributes poor, some keys defined, the majority of FKs incorrectly derived. No reference to theory to support arguments 4.Physical Design [ 5 Marks] Excellent list of tables and definitions completed, considering Physical design rules. Full list of tables and definitions completed, considering Physical design rules. List of tables and definitions considering some Physical design rules. tables and definitions incompleted. Part 2: Implementation and Report 60% Student Name/Id: Tutor marked: Moderator: Final Mark: Evidence Distinction (70+) Merit (60-69%) Pass (40-59%) Fail (0-39%) General comment mark 1. Tables and Data (15 Marks) In addition to previous: Sound mappings used of table designs to implementation stage. Fully implemented all declarative constraints. The excellent population of the database with data to test the accuracy of SQL. In addition to previous: Moderate implementation of constraints (unique or check) used. Data designed to show queries work effectively. View Implemented and tested. Subsystem tables implemented, column definitions very well planned. Planning has gone into detail and all PKs and FKs constraints are fully implemented, with some anomalies. View Implemented, based on only one table. Few tables implemented, without any data and/or constraints. Missing clear linkage to the case study and Apex databases. Student shows very little or poor understanding or used CASE tool to create tables. No reference to theory to support arguments 2.SQL (5 statements) [15 Marks] Please note that each query is marked on its own merits, therefore, you would be expected to implement 5 advanced type of Queries if you want to achieve maximum mark for each query. In addition to previous: Well specified business rules. The query is based on advance (self-join, advance necessary sub-query, self-join, functions that is not necessarily covered in class , e.g. operators (x*col+y), parentheses (x*(col+y), outer/inner joins, union, use of CASE. Originality of code. There is a clear evidence that the student has gone beyond syntax, functions, ways of testing the tables that has been taught in the class and module materials. In addition to previous: Well specified business rules. The query is based on intermediate level using build in functions (TO_CHAR, TO_DATE), join of 3-4 table, calculation included, necessary with sub query IN/ALL/EXSIST, GROUP BY. SQL statement attempted is basic (1-2 joins and one condition in WHERE statement) and produces some inaccurate or incomplete results. Query is not necessary sub-query, use of DISTINCT, ORDER BY, functions (eg. MIN, MAX, AVG, COUNT). SQL statement attempted, on the right lines but incorrect or no data retrieved. SQL statement attempted is basic e.g. Select * from tableX; The student used Query Builder to write SQL statements. Student shows no understanding of the code demoed to tutor student is not able to answer correctly any of the questions. SQL1: SQL2: SQL3: SQL4: SQL5: 3a. Apex Interface/ Application builder (10 Marks) In addition to previous: Validation fully implemented for the majority of the fields. LOV – based on 3rd table. All features presented specified in previous sections. Messages. Plug-ins. Dynamic Actions. Map. Pages planned well. Dash Boards. Meaningful Apex Reports. Security Log in features. Excellent understanding of run form will be faultless. In addition to previous: More than one Form implemented. Forms’ Functionality will be complete. You should consider HCI design issues (navigation-ref.data, colour, fonts, positioning, and multi-record block). These features presented: charts, item validation, default value, dynamic LOV, images integrated on the form. Understanding of running form will be faultless. Reference to theory used to support arguments Master-detail page. Partial data manipulation available. The student will have considered some of the HCI design issues. Some of these features presented: Radio/check buttons or default value. Constraints and Validation will be minimal. Demonstration of the requirement will be competent. No reference to theory to support arguments Very limited implementation done, form not working, no data displayed or can be added. No awareness of integrity issues. Requirement not implemented. No reference to theory to support arguments 3b.SAS integration and programming (5 Marks) Imported all relevant tables and/or produced excellent and advanced set of SAS program units to use for data manipulation Imported all relevant tables and/or produced a good set of SAS program units to use for data manipulation Imported only one table and/or produced one SAS program unit Not done SAS programming an reports (5 Marks ) Produced 3 advanced reports using SAS program Produced 2 advanced reports using SAS program Produced 1 advanced reports using SAS program None done Reflective Report 20% Evidence Distinction (70+) Merit (60-69%) Pass(40-59%) Fail (0-39%) General comment mark Reflective Report (20 Marks) Complete, perceptive and accurate evaluation of strengths and weaknesses of the data models and database principles; Detailed comparison with well-argued alternative approaches, with clear conclusions; Clear and detailed assessment of suitability to case, with innovative insight and reference to particular requirements; Clear insightful and appropriate strategy described Clear insightful and appropriate evaluation and recommendations of the appropriate technologies and processes for the client organisation; All arguments well supported by appropriate referencing 14-20 Complete and accurate evaluation of strengths and weaknesses of the data models and database principles Detailed comparison with alternative approaches, with clear conclusions; Clear and detailed assessment of suitability to case, with reference to particular requirements; Clear and appropriate strategy described; Clear and appropriate evaluation and recommendations of the appropriate technologies and processes for the client organisation; All arguments well supported by appropriate referencing. 12-13 Evaluation of strengths and weaknesses of the data models and database principles; Comparison with alternative approaches, with some appropriate conclusions; Assessment of suitability to case, with reference to particular requirements Appropriate strategy described; Appropriate evaluation and recommendations of the appropriate technologies and processes for the client organisation; Arguments well supported by appropriate referencing 8-11 No or limited reference to the case study; Little evaluation and few recommendations of the appropriate technologies and processes for the client organisation. Poor reference to literature; Little attempt made to compare and contrast research sources. Strategy barely described; Description with little evaluation or argument No reference to theory to support arguments 0-7 General Feedback: 3 Appendix: Case Study and Normalisation Forms (using a bottom up approach) Case study: The SUMMER Olympics in Brazil You and your team have been lucky enough to be selected by Rio - Brazil 2016 Olympic Games Organisers to produce a sophisticated system to record the attendance and activities of the thousands of competitors. The following business rules have been distilled from interviews with organisers and represent the basis from which a database system must be designed and implemented to cover this function. • Teams may come from any country which is part of the Olympic Movement. Each national team will consist of a varying number of competitors, trainers, medical personnel and administrators. • Most team members stay in the Olympic Village to which they can only gain access by using their biometric identity pass. This is also used to gain access to the training facilities. Not all competitors will use these facilities. • The Village is designed in clusters, each cluster containing a number of accommodation units linked to communal facilities, such as a kitchen and a lounge. • A competitor may compete in more than one event. • There are many different types of event, which may be individual events (e.g. high jump) or team events (e.g. hockey). • Events are held at different venues. • Some events (e.g. decathlon) may have a series of events associated with them. • Many events have various stages, i.e. heats e.g. “competitor X is competing in the semi-final heat A of the 100m freestyle for women” and a Final. • Each event is officiated over by differing combinations of starters, referees, timekeepers, judges, etc. depending on the roles required by each event. • Each individual’s or team result in every heat and final must be recorded. • Competitors can be drug tested immediately after any stage of any event. • There are likely to be World and Olympic records for each event. • There is a medal ceremony for each event at which the national anthem of the winning competitor / team is played. The awards are presented by a variety of senior officials of the Olympic committee, senior members of each nation’s teams and other dignitaries. The organisation of the ceremony is in the hands of employees of the host organisation. • There are a variety of officials employed by the host organisation running the Games.