1 ISYS2038 Database Design and Development Semester 1, 2020 Individual Assignment – Specifications Due Date: Wednesday 17 June, 11.59pm Mark: 50% of the total assessment of this course 1) Background In this assignment you are required to demonstrate and apply the concepts and knowledge covered in Week 1 – Week 11. For the purposes of the report, assume that you are employed as a business analyst at Best Innovative Solution (BIS) Pty Ltd. You are assigned to investigate the questions from a new client and provide the solution. The dataset about Just Decorate Pty. Ltd. has been collected and you are given the following two files for the purpose of applying data analytics: • dataset (JDCustomers.xlsx) • header description (JDCustomersDataset-header-description.txt) These two files are available for download on Canvas | Assignments | Assessment Task 3: Individual Final Assessment | Individual Assignment – Case Study, Specifications and Dataset 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 1000 cases (or instances). • There are some missing values in the dataset. • The dataset covers mainly on the aspect of customer related data. 2) Questions from new client You need to provide answers to all the following questions based on the case study. Question 1 Data Modelling • Provide an Entity Relationship Diagram (ERD). For each entity, provide at least one attributes/ fields with a maximum of two attributes (excluding primary key). • Provide a Relational Model and show the most appropriate attributes/ fields, primary keys and foreign keys. In addition, include the data type for each attribute/ field listed in each table/ entity. • You should apply third normal form (3NF) and show consistency in the use of notation. • If you discover while drawing the diagrams that the narrative of case study is incomplete, then provide justifications, assumptions, or reasonable explanations to complete the case study along with your diagrams. You could include the justifications, assumptions or reasonable explanations under Appendix section. Question 2 SQL Scripts • Based on data modelling from Question 1, create 3 queries that have the following requirements. Note that each of the query can cover one or more of the requirements. o One of the 3 queries should have a calculation o One of the 3 queries should make use of Group By 2 o One of the 3 queries should include a scalar function (one that returns a value) o One of the 3 queries should demonstrate Nested Query (could be either standard or correlated sub-query) o One of the 3 queries should demonstrate joining tables o One of the 3 queries should demonstrate the creation and testing of Trigger with both valid and invalid test data • For each query, o Explain and justify its business purpose o Provide the SQL script o Explain the design of SQL script o Make use of table(s) with sample data to show potential result/ outcome of query Question 3 Data Analytics with Orange After you have dealt with the missing values, discuss how you would produce a model to predict the spending score of customers for Just Decorate Pty. Ltd. using the knowledge of data visualisation and regression. In your explanations, include the screenshots of using Orange to produce the visualisation and prediction model. In addition, explain how your results from data visualisation and regression could help Just Decorate Pty. Ltd. to expand its business. Question 4 Database Administration Approaches Recommend two security features of database management system that Just Decorate Pty. Ltd. can use to protect the data of the company. Justify each of the recommended security feature. Question 5 Big Data and Analytics Suggest two varieties of data sources that Just Decorate Pty. Ltd. can use to further improve the understanding of its business. Provide justifications for each of the suggested data source. 3) Report Word Limit: 1500 – 2000 words (+/- 10%), excluding print screens, figures, diagrams, tables, appendix and references. Font Size: 11pt or 12pt Font Style: Calibri or Times New Roman Spacing: Single or 1.5 Spacing Some notes and guidelines (in addition to Lecture 12 slides): • Report needs to include and discuss all the questions given in Part 2) Questions from new client. • You do not need to access MySQL server to work on Question 2 SQL Scripts. You can make use of tables with sample data (or examples of records) to support your explanations. The tables could be created by typing in MS Word or MS Excel. • You need to use diagramming tool(s) to produce your diagrams for Question 1 Data Modelling. Do not submit diagrams that are drawn using pens/ pencils and papers. • Organise the report with sections and subsections. • Report needs to include justifications and explanations of the SQL queries. • Report covers a wider audience, including management and business users as well as developers. 3 • Label the print screens, figures, diagrams, and tables in the report properly. • Include References and Appendix sections in your report as you see fit. • Use Harvard referencing style if you are citing references from web resources to support your discussions. Submission Format: For each individual, the following documents must be submitted via Canvas. You can zip the following files and submit as a zip file. • a single copy of final report (PDF version) • Assignment Cover Sheet • a Turnitin generated report (PDF version) • Orange files (Orange loadable dataset(s) in .xlsx, Orange workflow(s) in .ows, Excel calculations in .xlsx) You can go to Assignments | Assessment Task 3: Individual Final Assessment | Assessment Task 3 – Individual Assignment 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 (or receipt/ evidence of submission to Turnitin if it takes too long to generate the 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. 4) Marking Rubric: Note: Marking rubric shows the mark out of 100 points and the total mark will then be converted to 50%. Assessed Components Exceptional Very Good Acceptable Needs Improvement Poor Data Modelling with ERD and Relational Model (30 points) Outstanding demonstration of using appropriate data modelling techniques. Diagrams produced are outstanding, elegant and have very few or no mistakes. Sound demonstration of using appropriate data modelling techniques. Diagrams produced are sound and have few mistakes. (21.00 – 23.99 points) Satisfactory demonstration of using appropriate data modelling techniques. Diagrams are produced at satisfactory level but still have some mistakes. Limited demonstration of using appropriate data modelling techniques. Diagrams are somewhat complete but still have a lot of mistakes. (15.00 – 17.99 points) No demonstration or very little demonstration of using appropriate data modelling techniques. Diagrams are incomplete and have a lot of mistakes. 4 (24.00 – 30.00 points) (18.00 – 20.99 points) (0.00 – 14.99 points) SQL Scripts (15 points) S
QL scripts are sophisticatedly and logically well structured, use appropriate syntax, contain no error and are able to address all business purpose. Discussions/ justifications are critical, well articulated and rigorous. (12.00 – 15.00 points) SQL scripts are logically well structured, use correct syntax but contain very little errors and are able to address most/ all business purpose. Discussions/ justifications are sound. (10.50 – 11.99 points) SQL scripts reasonably use some appropriate syntax but contain reasonable amount of errors and are able to address some business purpose. Discussions/ justifications are sufficient. (9.00 – 10.49 points) SQL scripts somehow use appropriate syntax but full of errors and are able to address limited business purpose. Discussions/ justifications are limited or insufficient. (7.50 – 8.99 points) SQL scripts do not use appropriate syntax or overly simplistic and are unable to address any/ very limited business purpose. Discussions/ justifications are missing or very limited. (0.00 – 7.49 points) Data Analytics with Orange (15 points) Excellent model chosen to produce accurate prediction and visualisation. Discussions/ justifications are insightful, critical, well articulated and rigorous. (12.00 – 15.00 points) Good model chosen to produce accurate prediction and visualisation. Discussions/ justifications are insightful and sound. (10.50 – 11.99 points) Appropriate model chosen to produce satisfactory prediction and visualisation. Discussions/ justifications are somewhat insightful and sufficient. (9.00 – 10.49 points) Appropriate model chosen to produce sensible prediction and visualisation. Discussions/ justifications are limited or insufficient and barely have insights. (7.50 – 8.99 points) Inappropriate model chosen and unable to produce sensible prediction and visualisation. Discussions/ justifications are missing or very limited and have no insights. (0.00 – 7.49 points) Database Administratio n Approaches (20 points) Convincing, critical, well articulated and rigorous discussions/ justifications for the required recommendati ons. Sound discussions/ justifications for the required recommendati ons. (14.00 – 15.99 points) Satisfactory discussions/ justifications for the required recommendati ons. (12.00 – 13.99 points) Limited or insufficient discussions/ justifications for the required recommendati ons. (10.00 – 11.99 points) No or very limited discussions/ justifications for the required recommendati ons. (0.00 – 9.99 points) 5 (16.00 – 20.00 points) Big Data and Analytics (20 points) Convincing, critical, well articulated and rigorous discussions/ justifications for the required suggestions. (16.00 – 20.00 points) Sound discussions/ justifications for the required suggestions. (14.00 – 15.99 points) Satisfactory discussions/ justifications for the required suggestions. (12.00 – 13.99 points) Limited or insufficient discussions/ justifications for the required suggestions. (10.00 – 11.99 points) No or very limited discussions/ justifications for the required suggestions. (0.00 – 9.99 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
