辅导案例-ISYS2038-Assignment 2
1 ISYS2038 Database Design and Development Semester 1, 2020 Team Assignment 2 – Specifications Due Date: Monday 1 June, 11.59pm Mark: 20% of the total assessment of this course 1) Background In this assignment you are required to demonstrate and apply the concepts covered in Week 6 – Week 11. There are three different parts in this assignment and it is aim to get students to demonstrate and apply the knowledge of advanced SQL, database administration approaches and data analytics for the given questions in relation to the case study. Following the previous case study and assignment you and your team members, employed by Best Innovative Solution (BIS) Pty Ltd, continue to investigate and provide solution to existing client – Doncaster Tai Chi (DTC) Pty. Ltd. The dataset about DTC has been collected and you were given the dataset on Canvas for the purpose of applying data analytics. Further information about the dataset is listed below: • The dataset and header description are stored in two separate files. • The dataset consists of binary, categorical and numerical data. • There are 6 different attributes and 2000 cases (or instances). • There are some missing values in the dataset. • The dataset covers mainly on the aspect of program related data for corporate organisations. Note: All members of an assignment group equally share the assignment mark unless an arrangement is made between the members about an alternative distribution of marks based on member contributions. Any group issue(s) must be resolved within the group and communicated to your Lecturer prior to the submission of the assignment. 2) Required Deliverables As a contracted team, you need to produce and discuss the following deliverables/ questions based on the case study. In the report: • Advanced SQL scripts of 5 queries For each query, o Explain and justify its business purpose o Provide the SQL script with comments included o Explain the design of SQL statement o Provide screenshot of the result/ outcome of query • Database Administration Approaches Discuss the following questions: o Data may need to be protected for both privacy and integrity reasons. What data needs to be protected and why? Provide two examples to support the discussion. o As the company grows in size over time, what data backup challenges that the database administrator may encounter? What would be your recommendations? Provide two examples to support the discussion. 2 • Data Analytics Answer the following questions: o Using your knowledge of regression and data exploration, discuss how you would produce a model to predict the likely operating cost for DTC. In your explanations, include the screenshots of using Orange to produce the prediction model. o As there are some missing values in the dataset, discuss what you would do with these missing values. • Meeting Minutes o Provide both meeting minutes and responsibility matrix using appropriate templates o Include under Appendix In the MySQL Server: • Data Manipulations Create 5 Advance SQL statements that have the following requirements. Note that each of the statements can cover one or more of the requirements. o One of the 5 statements need to be creation of Trigger o One of the 5 statements need to be creation of Procedure o One of the 5 statements need to be creation of Function o One of the 5 statements need to be creation of View o One of the 5 statements need to be creation of Nested Query (could be either standard or correlated sub-query) o Three out of the 5 statements need to make use of joining tables o Three of the 5 statements need to have a calculation Make sure that you save your SQL scripts into .sql file extension or store them in a word or notepad file. You need to provide these scripts and the screen shots of results in the report. For trigger and procedure, you need to provide test data (both valid and invalid data) to show that your code is fully tested. In Orange: • You need to download two files – the dataset (DTC-CorporateProgramDataset.xlsx) and the header description (DTC-CorporateProgramDataset-header-description.txt). These datasets are available in Canvas | Assignments | Assessment Task 2 | Team Assignment 2 – Dataset and Specifications. • You need to produce a prediction model as requested in the question in the “Data Analytics section”. • You need to submit Orange files that could be loaded into Orange without error(s) for testing your prediction model. • You can zip your Orange files and the report (PDF version) into a single zip file and submit it via the Canvas submission link. 3) Report Word Limit: 2500 – 3000 words Font Size: 11pt or 12pt Font Style: Calibri or Times New Roman Spacing: Single or 1.5 Spacing 3 Submission Format: For each team, the following documents must be submitted via Canvas: • a single copy of final report (PDF version) • Assignment Cover Sheet • a Turnitin generated report (PDF version) You can go to Assignments | Assessment Task 2 | Assessment Task 2B – Team Assignment 2 and click on Submit Assignment. Use of Turnitin: Please note that you need to use Turnitin to self-check your report for compliance of academic integrity and plagiarism detection. Report that is not checked by Turnitin will not be marked. Turnitin is not the submission link of your final report (PDF version). After you have self- checked your report, you need to submit the final version of your report together with its Turnitin report via the designated submission link. To self-check your report, you can go to Assignments | Important – Other Assignment Related Info. | Turnitin - Self-Check Your Report *Not Final Submission* and click on Load Turnitin - Self-Check Your Report *Not Final Submission* in a new window. Some notes and guidelines: • Report needs to include and discuss the required deliverables, as mentioned previously in section 2. • Report needs to include print screens, explanations and justifications of the SQL statements created. • The report covers a wider audience, including management and business users as well as developers. • Use headings for each section and subsection (i.e. 1.0 Advanced SQL Statements, 1.1. Trigger, 1.2. Procedure, 2.0 Database Administration Approaches, 2.1 Data Protection, 2.2. Data Backup, 3.0 Data Analytics, etc.). • Label the figures and tables in the report properly. • Use Appendix as you see fit. 4) Marking Rubric: Note: Marking rubric shows the mark out of 100 and the total mark will then be converted to 20%. Assessed Components Exceptional Very Good Acceptable Needs Improvement Poor SQL Scripts (30 points) SQL scripts are sophisticated ly and logically well structured, use appropriate syntax, contain no SQL scripts are logically well structured, use correct syntax but contain very little errors and are able to address most/ all SQL scripts reasonably use some appropriate syntax but contain reasonable amount of errors and are able to SQL scripts somehow use appropriate syntax but full of errors and are able to address limited business purpose. SQL scripts do not use appropriate syntax or overly simplistic and are unable to address any/ very limited 4 error and are able to address all business purpose. Discussions/ justifications are critical, well articulated and rigorous. (24.00 – 30.00 points) business purpose. Discussions/ justifications are sound. (21.00 – 23.99 points) address some business purpose. Discussions/ justifications are sufficient. (18.00 – 20.99 points) Discussions/ justifications are limited or insufficient. (15.00 – 17.99 points) business purpose. Discussions/ justifications are missing or very limited. (0.00 – 14.99 points) Database Administration Approaches (30 points) Convincing, critical, well articulated and rigorous discussions/ justifications for the required deliverables. (24.00 – 30.00 points) Sound discussions/ justifications for the required deliverables. (21.00 – 23.99 points) Satisfactory discussions/ justifications for the required deliverables. (18.00 – 20.99 points) Limited or insufficient discussions/ justifications for the required deliverables. (15.00 – 17.99 points) No or very limited discussions/ justifications for the required deliverables. (0.00 – 14.99 points) Data Analytics (30 points) Excellent model chosen to produce accurate prediction. Discussions/ justifications are critical, well articulated and rigorous. (24.00 – 30.00 points) Good model chosen to produce accurate prediction. Discussions/ justifications are sound. (21.00 – 23.99 points) Appropriate model chosen to produce satisfactory prediction. Discussions/ justifications are sufficient. (18.00 – 20.99 points) Appropriate model chosen to produce sensible prediction. Discussions/ justifications are limited or insufficient. (15.00 – 17.99 points) Inappropriate model chosen and unable to produce sensible prediction. Discussions/ justifications are missing or very limited. (0.00 – 14.99 points) Overall Presentation of Report (5 points) Quality of report is truly professional, clear and easy to follow, has good structure, cohesive and well thought with the Quality of report is professional, clear and easy to follow and has good structure. (3.50 – 3.99 points) Quality of report is professional, clear and easy to follow but it contains some minor spelling or grammatical errors. (3.00 – 3.49 points) Quality of report is somewhat professional, but still difficult to follow and/or it contains some spelling or grammatical errors. Quality of report is unprofessional , difficult to follow and/or it contains numerous spelling or grammatical errors. (0.00 – 2.49 points) 5 reader(s) in mind. (4.00 – 5.00 points) (2.50 – 2.99 points) Meeting Minutes (5 points) Quality of meeting minutes are truly professional, clear and easy to follow, has good structure and well thought with the reader(s) in mind. Outstanding and rigorous demonstrati on of team work. (4.00 – 5.00 points) Quality of meeting minutes are professional, clear and easy to follow and has good structure. Sound demonstration of team work. (3.50 – 3.99 points) Quality of meeting minutes are professional, clear and easy to follow but it contains some minor spelling or grammatical errors. Satisfactory demonstration of team work. (3.00 – 3.49 points) Quality of meeting minutes are somewhat professional, but still difficult to follow and/or it contains some spelling or grammatical errors. Limited or insufficient demonstration of team work. (2.50 – 2.99 points) Quality of meeting minutes are unprofessional , difficult to follow and/or it contains numerous spelling or grammatical errors. No or very limited demonstration of team work. (0.00 – 2.49 points) Overall High Distinction (HD) Distinction (D) Credit (C) Pass (P) Fail (N) 80 or more 70 or more 60 or more 50 or more 0 or more