-Write and execute 5 SQL statements to find the following:
1. The average, minimum and maximum price of strawberries.
2. The minimum price of each main category of items.
3. The average weight of each subcategory of fruit.
4. The average prices of each fruit between the years 2018 and 2020.
5. The number of different fruits recorded before 2021.
Assessment Task Information Key details: Assessment title: Written Report submitted to Pathways VLE via Turn-it-in Module Name: Database Design and Implementation Module Code: IY466 Tutor’s Name: Jasper Christian Assessment will be set on: March. 21. 2022 Assessment is due on: April. 04. 2022 Assessment weighting: 50% Assessment Instructions What do you need to do for this assessment? -You are required to attempt to cover all sections in the report. The aim of the report is to show your in-depth knowledge of database design and implementation and your ability to apply your knowledge by writing a report showing your careful steps undertaken to create a functional database. -Before start writing your report, you need to study all the material provided for students, e.g. the main textbook, lecture notes and the material uploaded via VLE. - You have been tasked with designing and implementing a database structure to store information about fruit and vegetable prices in the UK between the years 2017 and 2022. The CSV file is located with this coursework brief, titled: fruitvegprices-2017_2022.csv Inspect the csv file and carefully plan how you are to split this unnormalized data into a relational database which can be efficiently queried using SQL statements. -Normalise the database to 1NF and then 2NF, continue to 3NF if you see fit. Include your steps and workings in your report, explaining your process. (You do not have to draw out tables, just describe your steps and write down your entities and attributes once it is fully normalised.) -Draw your normalised database in an entity-relation diagram. You may use any diagram drawing software of your choice or alternatively you could draw the diagram by hand and scan your work. -Implement the database in the MYSQL application of your choosing (PHPMYADMIN, MySQL Workbench, etc). In your report you must show screenshots of your finished tables as well as the SQL code you used to create the database. -Import the dataset into your database application. You must figure out a solution to splitting up the dataset ready to import to your relational tables. Show screenshots of your imported data in your report, including the number of records successfully imported into your tables. -Write and execute 5 SQL statements to find the following: 1.The average, minimum and maximum price of strawberries. 2.The minimum price of each main category of items. 3.The average weight of each subcategory of fruit. 4.The average prices of each fruit between the years 2018 and 2020. 5.The number of different fruits recorded before 2021. Include your SQL statements and screenshots of their results in your report. Good SQL syntax and practices are required for full marks here. Write a reflective report of ~500 words detailing where you think the coursework went well and where you think you could improve if you were to do it again. Analyse the techniques you took over the course of this project, how would you change your approach if you were to undertake a similar task again? - The assignment specification and marking criteria will be released online via VLE. You can download the assignment specification. After finishing the report, you must upload the report via the VLE module page through two links. The first link called “Submit via TurnitIn” and the second link called “the Assignment”. Also, you are required to send a copy of the report to your tutor. · It is the student responsibility to submit a low similarity report (i.e. not more than 25%). · Make sure that the submitted document is in PDF format. Otherwise it will not be accepted. Structure: 1. Title Page with Student name and Student ID 2. Introduction and context 3. Normalisation process 4. Entity-relationship diagram 5. SQL Database Implementation 6. Data importing 7. SQL statements to show correct implementation 8. Reflective Segment. 9. References Total marks of the report are 100. Theory and/or task resources required for the assessment: The report should cover topics regarding relational databases, design and all SQL programming learned up to this date. Referencing style: You are encouraged to use Harvard referencing style. you can refer to the following websites https://www.uwe.ac.uk/study/study-support/study-skills https://www.uwe.ac.uk/study/study-support/study-skills/referencing/introduction-to-referencing Expected word count: ~1500. Learning Outcomes Assessed: · MLO 1 · MLO 2 · MLO 3 · MLO 4 · MLO 5 Submission Requirements: · You must submit your report by using your computer. Then, you must submit the assessment electronically via the VLE module page through the Assignment activity that called “Submit via Turnitin”. · you must submit your report online via the VLE before the deadline · You must send a copy of the report via email to your tutor. · The document must be in PDF format. Assessment violating any of the requirements above will incur penalties or will not be accepted. How will this assessment be marked? The assignment will be marked using the following criteria: Normalisation of your database (15%): how accurately you were able to break the data into a relational model. Making sure you justified your decisions and took care with your process. Drawing an entity-relation diagram (15%): how consistent was your diagram with your normalised database? Did you include everything needed from an E-R diagram and would a database designer be able to understand the structure of your database without seeing a finished product? Implementation of your database (20%): Were your tables created properly, taking into account constraints and correct data types; with appropriate names for your database and tables. Importing of data (10%): Data is imported in the correct tables and is split up in accordance with your relational database. SQL Statements (20%): Each statement has been attempted with correct outputs and clear evidence of such. Reflective report (10%): how well you were able to reflect on your overall implementation, understanding where you may improve if you were to undertake this coursework again. Language and structure (10%): Your report is well structured, with clear language and justification of each of your steps. The total marks are 100 and weighs 50% of the module How will you get feedback? You can get the result after it is moderated. | Kaplan International Pathways | 1 | kaplanpathways.com | Kaplan International Pathways | 2 | kaplanpathways.com