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, XXXXXXXXXX: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 co
esponding 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 co
ect
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, ho
ies, 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
elationship 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 co
espond 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
owser. 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., div> 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 co
esponds 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 e
ors 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 e
ors 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:
?php
echo "Data submitted by IP address " . $_SERVER[’REMOTE_ADDR’];
?
(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 pe
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.
li
ary(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