Creative Project 5 Project Specification

Overview

For your fifth and final(!) Creative Project, you will get practice creating your own SQL table and querying it. As with other CPs, this Creative Project is designed to prepare you for the last homework of the quarter and you have freedom to have more ownership in your work, as long as you meet the requirements listed below.

Ideas for CP5

Being that this is our last Creative Project, it is also your chance to wrap up your work in CSE 154 with a final portfolio you can publish, showcasing your exploration of web programming technologies. As always, we encourage you to explore the new material covered in class, as well as related (but optional) content we may link to along the way, as long as you follow the CSE 154 Code Quality Guidelines and adhere to Academic Integrity policies.

As long as you meet the requirements outlined below, you have freedom in what kind of website you create. Here are some ideas for Spring 2019 (you can also check out Autumn 2018's CP5 Showcase to see other creative ways students used APIs in their websites!):

• The code from lectures and section are always good places to start. However, if you choose to use course materials as "starter" code you must:
  1. Cite that you are using the course code as a starting point (e.g. in an HTML page footer or APIDOC.md) and
  2. You must substantively change the resulting output/webpage. You may not use the data from our provided .sql files as your own.
• Can you think of any interesting data you might model in a table that could be used in SQL queries with your PHP web service? It may help to think about how you've used Excel previously. Perhaps you have some data you can export as a .csv file, for instance. After you have imported your setup.sql file, you can also import the extra data from the .csv file (see the external correctness section for instructions on how todo this AND how to turn in this extra data.). You may also create/use multiple tables in your CP5 database, though it's not required.
• Consider building off of your work for CP4 but add a database component to the project.
• While the focus of this CP is to primarily give you practice with working with PHP connecting to a database, it could also be an opportunity to work on a full stack application including HTML/CSS/JS front end as well as your PHP/MySQL web service! This can be a great way to showcase what you've learned for a web development portfolio.

External Requirements

• Your project must include the following 4 files at a minimum (2 are included in this repo but must be completed):
  • setup.sql - This file should contain code to create at least one table with at least 3 different columns, and must also include at least one query.
  • A .php file that can be called as a web service (using GET or POST requests) to connect and query your database.
  • A finished common.php file that contains starter code with a function get_PDO() to connect to your database/tables. You must fill in the two TODOs in this file to properly connect to your database/tables and handle possible DB errors, and include this file name at the top of your .php file with the PHP include statement as shown in lecture. Remember to also write your own file comment for common.php.
  • An APIDOC.md file to document your .php web service.
• Write at least two different queries using the table(s) created in setup.sql (one INSERT and one SELECT). Specifically:
  • One query should INSERT at least one row into the table.
  • One query should SELECT a filtered result (omitting some column or row) from a table.
  • One of these queries must be in setup.sql and at least one query must used in your .php web service with a successfully-connected PDO object to perform the query on the database. We recommend trying both types of queries with PDO in your .php web service to help you practice for HW5.
• Your code in setup.sql must be valid MySQL - that is, if we were to import it into a database on phpMyAdmin, it would be run without any errors to create/populate the table(s). The setup-wpl.sql from Friday's lecture is an example SQL file that would be successfully populated when imported this way.
• All interactions between PHP and SQL should be made with the PDO object, as demonstrated in class. Use the get_PDO() function you complete in common.php to get a PDO object in your .php code. Do not use other connection methods such as MySQLi.
• Your code in setup.sql must contain queries that you wrote yourself and were not generated by exporting from phpMyAdmin or the like.
• Handle any PDO exceptions with header("HTTP/1.1 503 Service Unavailable") error as shown in lecture/section. Note the difference between a 400 Invalid Request (which is used to tell a client they made an invalid request, which is usually separate from an error occurring with the database).

Important Notes:

• If you would like to add more data to your CP without having to manually type all of the data, you should follow these steps:
  1. With your database selected, start by importing your setup.sql file into phpMyAdmin through the import tab (if you haven't already). The assumption is that this .sql file will contain the command to create your table.
  2. With your table selected, import the .csv file containing the extra data into phpMyAdmin.
  3. With your table selected, click on the Export tab and select Custom - display all possible options. Choose Dump some row(s) and choose the rows to export. Press the Go button when you're ready. Save the resulting SQL as more-inserts.sql. (Your more-inserts.sql should not have any any code that is irrelevant to INSERT statements (e.g. CREATE table statements)). Note that only setup.sql will be graded on the requirements for this CP5, but this additional file can be used in any CP5 showcase submissions.
• If you would like your CP5 to be featured in the final showcase, you must have a front-end view (HTML/CSS/JS) for the showcase to link to your project. It is alternatively fine to link to your APIDOC.md to describe how to use your API, but it will be more motivating to include an interactive web page :) Your HTML/CSS/JS will not be graded.

Internal Requirements

Requirements continuing from previous CP/HW assignments:

• Your PHP web service should use isset to check for required parameters before using them.
• All file names and links in your project must be lowercased (e.g. img/puppy.jpg but not img/puppy.JPG or img/Puppy.jpg).
• Your PHP web service should specify the correct content type with the header function before outputting any response (including 400/503 errors), and should only set this when necessary
• Your PHP code should not generate any HTML (though you may check with instructors for exceptions to this rule depending on the context of your project)
• Decompose your PHP by writing smaller, more generic functions that complete one task rather than a few larger "do-everything" functions - no function should be more than 30 lines of code, and your PHP should have at least one function defined and used. Consider factoring out important behavior for your different GET/POST requests into PHP functions.
• Limit global variables in PHP and do not use the $GLOBAL array.
• Similar to JS, use === over == in PHP for strict equality checks.
• Do not define variables, parameters, or functions that are never used.

CP5-Specific Requirements (in addition to following the PHP section of the Code Quality Guide):

• Do not construct a PDO object when you do not use it (e.g. do your checks for required GET/POST parameters first, then construct the PDO if the checks pass). See more information here.
• Your PHP file should appropriately use GET parameters for read-only requests, and POST parameters for any requests that modify the database in any way.
• Your PHP should use query, prepare, and exec appropriately to handle different SQL statements securely.

Style and Documentation

Requirements continuing from previous CP/HW assignments:

• Your files should demonstrate consistent and readable source code aesthetics as demonstrated in class and detailed in the CSE 154 Code Quality Guidelines. Part of your grade will come from using consistent indentation, proper naming conventions, curly brace locations, etc.
• Place a comment header in each file with your name, section, and a brief description of the file (examples have been given on previous assignments). Remember to include your own file comment for common.php.
• Use proper under_score naming conventions for PHP variables and functions, as specified in the Code Quality Guide.
• Document your PHP functions in a similar manner to our JSDoc requirements (e.g. @param and @returns). You may also use official PHPDoc if you'd like.
• Include a brief description of your PHP web service and the parameters/responses that would be important for you/other developers to understand the program. See the Code Quality Guide for an example. Use your APIDOC.md for a more descriptive public documentation of your API (used by clients).
• To keep line lengths manageable, do not place more than one block element on the same line or begin a block element past the 100th character on a line. There is an exception with line length in .sql files, as long as you maintain readability.

CP5-Specific Requirements (in addition to following the PHP section of the Code Quality Guide):

• When documenting your SQL file, a brief header comment is sufficent with your student information and what the database/table(s) represent. You must use -- for comments, not /* ... */ to submit with GitGrade in this class.
• Use consistent spacing and indentation with your SQL code (see SQL Whitespace and Indentation in the Code Quality Guide)
• Follow consistent naming/casing conventions in your SQL code as discussed in the Code Quality Guide.
• Use appropriate datatypes for columns (e.g. INT to store integer ids). As mentioned in lecture, this is often a design decision, and we are more than happy to talk about different trade-offs! Refer to Friday lecture slides for common datatypes.

Grading

This CP will be out of 8 points and will likely be distributed as:

• External Correctness (4 pts) - The external requirements listed in this document are met.
• Internal Correctness (2 pts) - The internal requirements listed in this document are met.
• Style and Documentation (2 pts) - The style and documentation requirements in this document are met.

Late Day Challenge

You can earn one extra late day for successfully meeting the following additional requirements:

• Creating a second table in setup.sql and populating it with at least 3 rows using INSERT.
• Correctly using UPDATE or DELETE that is used as part of some request by your PHP web service (note that you will use these in HW5, so this is a good chance to practice!)

Academic Integrity

Creative Projects are unique in that students may look for outside resources for inspiration or assistance in accomplishing their goals. On occasion students may wish to use portions of sample code that has been obtained on our course website or others. In order to avoid academic misconduct for a Creative Project in CSE 154 you must:

1. Ensure that substantive original work is submitted that can be evaluated by the course staff.
2. Cite the ideas or materials of others that are used. The citation format is not that important - as long as the source material can be located and the citation verified (a url in a comment is generally fine), it's OK.
3. Clearly indicate (e.g. with comments) which portions of your code are completely original and which are used or modified from external sources, if any code is used that builds off of/is inspired by external sources (e.g. adaption of an example CSE 154 exercise, online tutorial you find on Bootstrap or a JS library, etc.). We will only grade your original work. Note that solely changing identifier names or rearranging other source material is not considered your original work - see the examples of appropriate use below for details.

A good analogy to this is if you were writing a history paper: You can use quotes in your paper as long as you give attribution to the sources of the quote, but you can not write a history paper out of the quotes of others (particularly with no citations).

Some examples of appropriate use:

• A student closely follows a tutorial to understand a new concept in web development (e.g. CSS3 animations). The student cites the tutorial they used in the file header then substantially modifies the tutorial code to include what is specified for the Creative Project assignment, documenting which portions of the code are their own so TAs know which portions to grade (and to determine whether the material cited as being learned from the tutorial is sufficiently adapted to be considered the student’s own work).
• A student is having difficulty styling their website. They look for a solution and find one on a site such as Stack Overflow. The student uses the code they find in their solution, documents that small piece of code was not their own with a comment that includes where it was found. The TAs will not use that portion of the code in grading.

Students with questions about any specific situation should ask the instructors for clarification.

More information on the CSE 154 course policy is here, the Paul G Allen School also has an entire page on Academic Misconduct within the context of Computer Science, and the University of Washington has an entire page on how Academic Misconduct is handled on their Community Standards and Student Conduct. Please acquaint yourself with both of those pages, and in particular how academic misconduct will be reported to the University.