1.4.1 Volunteering Database Management System Implementation For this assignment you will be expected to use tables’ structure given to you, as a result of feedback on Phase Test tasks. This feedback will be available on the VLE, by week 7. Please read carefully the marking schema when doing your tasks. The following are the assessment tasks: 1) Implement four related tables and insert suitable test data. Also, create a view, based on your sub-system table(s). [20 marks] 2) Implement 3 suitable SQL statements (Apex Reports). To test your system, you are asked to produce a set of SQL statements (queries) which will be specified in plain English (business statement) and implemented. Your queries should be implemented as an APEX report, in the Application Builder. Your SQL statements will need to demonstrate appropriate complexity in order to achieve a high mark. You will be penalised when using Query Builder to complete the tasks. [30 marks] 3) Implement two triggers as procedural constraints to use within the Apex application. [20 marks] 4) Build appropriate APEX Forms/Pages to manage the data (insert/update/delete) of your sub-system. At least one page has to be 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. [20 marks] 5) Create a User Guide for one of your Apex Pages and Reflection using the proforma, (see Appendix 2) [10 marks] Note: You can check with your tutor for the suitability of the reports/triggers/Apex features before you implement the code. 1.4.2 Deliverables Submit a single compressed file i.e. zip- not a rar file! The file should include: • A document file with screen shots, evidencing where the code for the task is working successfully. (Where appropriate show evidence of code/application being tested. E.g. View images) • A brief discussion of your implementation, for each screen shot have between 50-100 words. • An export of the Apex Application (including tables (DDL and DML scripts), SQL code, PL/SQL code for triggers etc.). This assessment is assessed via a product demonstration during your tutorial session. You must upload your work to the VLE using the Assessment Template (found on VLE) for this submission, found on the VLE, by the deadline. Your uploaded work will be used for moderation purpose and by the External Examiner therefore providing evidence of your work is very important. If you do not attend the scheduled demonstration for the assessment, you will be given one further opportunity at a time which will be announced via the VLE. A 5% penalty will be applied to your mark for late demo. If you miss the late demonstration, your mark for the assignment will be recorded as Non-Submission and you will fail the module. 1.4.3 Preparation Guidance for the Demo There will normally be two tutors in the lab marking the work, but only one per student. In some cases two tutors will mark your work for moderation purposes. • We suggest you demo your tasks in the order of 1, 2, 3 etc. to the marker. • 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? o Show that you can insert, update and delete data from your master/detail form. o Why did you choose this Chart? How is it useful? What advanced features has your report got? o What advance Apex features have you implemented? • You are being assessed on your understanding of your developed application and the underlying principles, NOT just your ability to navigate round it. Marks may be reduced due to poor understanding or explanation. • Be ready to do the demo when the tutor approaches you. • It is your responsibility to demonstrate your application in a way that provides evidence to your tutor that you have met the specification of the application, the marking criteria provided and have a good understanding of the subject. • You are strongly advised to PRACTICE your demo, so that you make full use of the limited time. • We realise that a maximum of 10 minutes may not seem much – however, experience suggests that an application can be adequately demonstrated at this time if the demonstration is WELL PLANNED. 2 Assessment Marking Criteria Assignment 2 – (60%) Student Name/Id: Tutor: Group: TOTAL: /100 Task 1 Out of 80+ 70-79 60-69 50-59 40-49 30-39 0-29 Tables, View and Data 20 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. View implemented and tested correctly. In addition to previous: Moderate implementation of constraints (unique or check) used. Data designed to show queries work effectively. View implemented and testing conducted. All four tables implemented, column definitions very well planned. Some planning has gone into detail and all FKs constraints implemented correctly. View implemented, with poor purpose. All four tables implemented, with some anomalies in column definition. View not implemented. Few tables implemented, with basic data, not designed with queries in mind. Only PKs addressed, issues with FKs. Implemented view extremely basic. Few tables implemented, without any data and/or constraints. Missing clear linkage to the case study and Apex databases. Tool. A view not implemented. Student shows very little or poor understanding. Only 2 tables implemented with no data population and no constraints. The Student shows little or no understanding of the work. Mark Task 2 80+ 70-79 60-69 50-59 40-49 30-39 0-29 Apex Report/ SQL Query 30 In addition to previous: Originality of the SQL and Report Code. There is 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. Queries demonstrate advanced SQL structures. Use of CASE. Clearly considered Report presented to a high professional standard. In addition to previous: Well specified business rules for the query. SQL Statement based on the combination of intermediate and advanced level (necessary sub-query, functions (eg. TO_CHAR, TO_DATE that is not necessarily covered in class), operators (x*col+y), parentheses (x* (col+y), outer/inner joins. Student shows an excellent understanding. In addition to previous: Well specified business rules for the query. SQL statement 2-3 joins. SQL Statement of an intermediate level (self-join, functions (eg. MIN, MAX, AVG, COUNT), min 3 table joins, not necessary sub query with IN/ALL/EXSIST, GROUP BY/HAVING etc.). Moderate understanding of the work done by the student. The report includes calculation. Poor specification of business rule, Report has been headed and data displayed from the SQL statement. SQL statement has 1-2 joins, not necessary sub query, DISTINCT, ORDER BY, LIKE, BETWEEN. The data output is not meaningful. Some understanding of the work done by the student. SQL statement attempted is very basic (no or 1 join) and one condition in WHERE statement) and produces some inaccurate or incomplete results. No Apex Report integration of the SQL statement above. Little understanding of the work done by the student. SQL statement attempted, on the right lines, but incorrect or no data retrieved. No Apex Report. 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 SQL statement produced is of a very basic (Select * From x_table) level and/or student shows no understanding of the code demoed to tutor student is not able to answer correctly any of the questions No Report. Report/SQL 1 Mark Report/SQL 2 Mark Report/SQL 3 Mark Task 3 80+ 70-79 60-69 50-59 40-49 30-39 0-29 PL/SQL& Triggers 20 In addition to previous: Triggers implemented are original, meaningful and work correctly. The student knows how to test the trigger. Excellent understanding of the work presented. In addition to previous: Both triggers have a unique features and different. Excellent understanding of the work presented. In addition to previous: There is a partial originality to the code, however code works fully. Evidence of code tested in Apex or SQL Commands. Moderate understanding of the work done by the student. Trigger(s) code produced is not practical and not tested. Code is not relevant to the case study. Trigger runs, but cannot be tested on the data Attempt made, but not completed. The Student shows little or no understanding of the work. No Trigger done. Trigger presented is produced by Apex system, on default when creating a table. The Student shows little or no understanding of the work. Trigger 1 Mark Trigger 2 Mark Task 4 Mark 80+ 70-79 60-69 50-59 40-49 30-39 0-29 FORMS 20 In addition to previous: There is a clear evidence that the student has gone beyond functions and developments, that has been taught in the class and module materials. Features such as, LOV at the application level, Libraries, Find icons, CSS, controlling access pages, connection to Website - PHP etc, publishing form… In addition to previous: Validation fully implemented for the majority of the fields. Ajax/JavaScript used. All features presented specified in previous sections. Trigger(s) integrated within the Apex Application. Messages. Plug-ins. Dynamic Actions. Pages planned well. Images integrated on the form. Dash Boards. Security Log in features. Excellent understanding of running 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, Map, and Calendar. Understanding of running form will be faultless. Master-detail page. Full data manipulation available. The student will have considered HCI design issues. Some of these features presented: Radio/check buttons, chart, default value or static LOV. Constraints and Validation will be minimal. Demonstration of the requirement will be competent. The form is based on one table using some of the Apex features and data can be added. OR the form is master/detail and no new records can be added. The student shows some understanding. Very limited implementation done, form not working, no data displayed or can be added. No awareness of integrity issues. Requirement not implemented. One single form implemented and it is not running. The Student shows little or no understanding of the work. Mark Task 5 80+ 70-79 60-69 50-59 40-49 30-39 0-29 User Doc (5 marks) And Reflection (5 marks) 10 In addition to previous: User guide for a Form (Page), A4 size in the document, suitable very professional and well structured. All relevant code/screen shots well described and presented. Reflection completed with exceptional discussion of maximum 300 words. In addition to previous: User guide suitable for presentation to client well structured. All relevant code/screen shots well described and presented over more than one page. Reflection completed with excellent detail. In addition to previous: User guide suitable for presentation to client, well structured, some errors in presentation. It is presented on several pages. Reflection completed with some detail. In addition to previous: User guide suitable for presentation to the client, with a few amendments, well structured. Reflection completed with poor detail. User guide could be presented to client after some editing. The student has not followed instructions and has produced a document of several pages. No Reflection. Poor attempt made to produce user guide and write a reflection. Student hasn’t done what the task specified. Mark 8-10 7 6 5 4 3 0-2 6.2 Appendix 2 – Assignment 2 task 5 1. For each of the SDLC stages (conceptual, logical, physical, implementation, testing) discuss, where appropriate, what are the ‘inputs’, and what are the outputs, (i.e. what are produced at the end). [ 2 Marks] 2. What steps did we use in class to verify the outcomes of each stage, and how effective were they? [2 Mark] 3. List two strengths and two weaknesses of your prototype database in terms of its ability to meet the requirements? [1 Marks]