Database design and development
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 – The Food Specialist (TFS) Pty. Ltd.
You will continue to use the tables created in the previous assignment to work on the advanced SQL scripting. You’re allowed to modify your tables and data as you see fit.
The dataset about TFS has been collected and you were given the dataset in Canvas for the purpose of applying data analytics. Further information that is relevant to 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 catering service related data for corporate organisations. Note: All members of an assignment team equally share the assignment mark unless an arrangement is made between the members about an alternative distribution of marks based on member contributions. Any team issue(s) must be resolved within the team and communicated to the Course Coordinator 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:
1) 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.
2) 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.
• 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 TFS. 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 and Responsibility Matrix o Provide meeting minutes and responsibility matrix using the 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 query 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.
• You need to download and use the following two files for data analytics: o the dataset (TFS-CorporateCateringDataset.csv) o the header description (TFS-CorporateCateringDataset-header-
• Produce Orange loadable file based on your experience in processing the data in the previous workshop.
• Produce a prediction model as requested in the question in previous section.
• You need to submit the Orange formatted file that can be loaded into Orange without errors.
• You need to submit Orange files that could be loaded into Orange to test your prediction model.
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
For each team, the following documents / files must be submitted via Canvas:
• a single copy of final report (PDF version)
• Assignment Cover Sheet
• a Turnitin generated report (PDF version)
• Orange file(s) You can zip your Orange files together with other files into a single archive and submit it via the Canvas submission link. 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.