i just need problem 3 done urgently
MySQL + R + WWW Quiz IMPORTANT NOTE. Given these special COVID-19 induced circumstances we have to be particularly strict in assessment procedures. Therefore it is CRITICAL that every student follow all instructions carefully. Some of this may seem overly burdensome, but it is a necessary consequence of the situation. Thank you! INSTRUCTIONS. You MUST complete the following tasks INDIVIDUALLY and submit your work by March 27, 2020 11:59pm EST. Failure to adhere to the request for individual effort will result in an automatic grade of 0, and be subject to Purdue’s Academic Misconduct Policies and Regulations (see syllabus). There is ZERO tolerance for academic integrity concerns as these principles should be upheld at all times. Critically, the instructors will have access to Purdue’s various new plagiarism softwares that will be used when evaluating the quiz. These softwares do not only perform text-based similarity tests, but are specialized to look at program structure and logic (i.e., they also have tests that ignore variable names and examine the structure of your code). Moreover, the comparison is not just between students in the course, but against all material on Blackboard as well as many thousands of other online resources (non-Purdue courses, pdfs, web pages, textbooks, etc.). Hence, using examples from these sources and changing variable/table/attribute names, etc. WILL result in being properly detected as not performing the quiz on your own: i.e., the quiz is not about whether you can find example code and change variable names or make minor changes. Some notes of importance: • Do no post questions on Piazza about debugging specific codes. Use the forum as you would ask questions of your TA in a lab quiz or exam. • When submitting database logins, you can obfuscate your password. • READ THE FULL QUIZ BEFORE STARTING!!! To reiterate, it is imperative that you MUST complete the ENTIRE quiz on your own. Do not copy or revise solutions from classmates, course notes, or online sources. Given the freedom in question structure it is expected that ZERO students will submit logically similar solutions. TO SUBMIT. Failure to submit ALL of the below will result in a grade of ZERO, no exceptions granted: • 1 single pdf showing all of your code (pasted not screen shots), in the order of questions below, as well as a screen shot showing the corresponding output. Include a URL to your web page at the top of the pdf. Clearly label each question so that the TAs and Graders can quickly and accurately grade them. Plots, screenshots and images should also be embedded into this document as indicated below. You must include a cover page just as with your assignments! • Your database must also be submitted as a .sql file. Ensure that you only include the tables used for the quiz, including the data that is stored in them. To do this, please see this tutorial https: //www.youtube.com/watch?v=ffmCGVse8yg (select custom→ select the tables of interest→ save output to file, compression=zipped, format=SQL → Go). After downloading the file ensure that the correct CREATE TABLE and INSERT statements are in the .sql file. • 1 .zip containing all of your source code (you can put your .sql file into this .zip file). https://www.youtube.com/watch?v=ffmCGVse8yg https://www.youtube.com/watch?v=ffmCGVse8yg The quiz is composed of 3 questions over 2 pages, and is worth 18% of your final grade. The maximum attainable score is 180. 1. DATABASE (a)20 Design a simple database composed of ≥ 5 entities and ≥ 3 relationships that has a personal inter- est/theme (i.e., based on your pets, hobbies, etc.). Do not use1 any examples from course materials, fellow students or online - the solution should be solely your own. Non-key attributes of each entity must include a mix of attribute types: in your full design you must use each of integer, real, date, varchar, and char types at least once. At least one auto-increment is also required, and at least one relationship must have at least two non-key attributes (of any type). Submit a complete Crow’s Foot ERD. (b)20 Using MySQL CREATE your database, ensuring proper design principles (redudundancy, xNF, at- tribute constraints, etc). Submit the SQL code. 2. WEBSITE + SQL (a)5 Create a webpage in your Purdue CAREER account (your www folder), name it quiz.html. Since the webpage and database must correspond you will write a paragraph on the webpage that explains the system being modeled: the entities, their attributes, etc. in a similar way to how some homework and lab questions are posed. Include an image of your ERD with this paragraph. (b)10 The page must use ≥ 3 images (not including the ERD image), at least 2
tags, must also have a
tag, and be centered in the browser. You can use a simple online template for this, but ensure that you include the exact link where it is available - failure to do so will result in a grade of 0 on the quiz. Note, by use a tag we mean in a meaningful way, not trivially (ex.,
that has no practical impact on the webpage layout). (c)25 Include a web form on your page. The form must ask for input that corresponds to your database design. That is, valid data entered from your website will be used to populate the entire database. The form will submit to a file called quiz.php. However, before the data is submitted you must validate it using Javascript, with code stored in an external file called quiz.js. Submit 2-3 screenshots to show functionality: an empty form, two screens showing different errors detected by Javascript. (d)15 In your quiz.php file you will connect to your database and INSERT the data from the web form. If any errors occur when attempting the insertion you will report this on the web page output to the user. In either case, you will select all data from every table in your database, and use php to output it as a nicely formatted HTML table. Use
to indicate the table name when formatting the output. The HTML tables should clearly indicate column names using bold font. Show 1 screenshot of the output HTML tables. (e)0 Include in quiz.php the following, show a screenshot of the result failure to do so will result in a grade of 0 on the quiz: (f)5 Using your web form, insert lots of data! You should insert enough realistic data that the results in Question 3 are meaningful and not overly simplified! This likely will require at least 20-30 rows per table, if not 40-50. 1That is, copy-paste and renaming of variables, or substantially doing so. The design should come from your head, not any other source. Page 2 of 3 3. R + SQL The below questions involve SQL queries called from an R script. It is necessary that all subsetting, grouping and aggregating of data be done with SQL queries, not post-processing results in R! That is, R should just be used for plotting not taking averages, etc. Note, create queries whose results are appropriate to plot in some meaningful way! Moreover, the queries state “meaningfully different”, which means they cannot be minor variations of the same basic query. (a)0 Execute the following command to display your IP address (show a screenshot). You will likely need to first install the rjson package. failure to do so will result in a grade of 0 on the quiz. library(rjson) fromJSON(readLines("http://api.hostip.info/get_json.php", warn=F))$ip (b)16 Create two meaningfully different non-trivial SQL queries (i.e., do not just select every record), each using two at least tables. Indicate the English statement equivalent of your query. (c)16 Create two meaningfully different non-trivial queries using GROUP BY, and HAVING, each using at least two tables. Indicate the English statement equivalent of your query. (d)16 Create two meaningfully different non-trivial queries using JOINs, each using at least two tables. Indicate the English statement equivalent of your query. (e)16 Create two meaningfully different non-trivial query that uses at least one other embedded query in the WHERE clause. (f)16 For each of the above questions select one (of the two) query and provide an appropriately labeled and colored R plot (histogram, pie chart, line chart, point plot, etc.) to visualize the result. Page 3 of 3