Difference between revisions of "Module 3"

From CSE330 Wiki
Jump to navigationJump to search
 
(52 intermediate revisions by 8 users not shown)
Line 3: Line 3:
  
 
This article contains your assignments for Module 3.
 
This article contains your assignments for Module 3.
 +
 +
== Using the Wiki ==
 +
{{RequiredInstructions|content=
 +
Text enclosed by <syntaxhighlight lang="bash" inline><</syntaxhighlight> and <syntaxhighlight lang="bash" inline>></syntaxhighlight> should be replaced by content unique to you.
 +
 +
'''Example'''
 +
 +
<source lang="bash">
 +
$ sudo useradd -r -m -c "<My Full Name>" <usernameHere>
 +
</source>
 +
becomes
 +
<source lang="bash">
 +
$ sudo useradd -r -m -c "Zach Cohn" zcohn
 +
</source>
 +
}}
 +
  
 
== Reading ==
 
== Reading ==
Line 12: Line 28:
 
* [[PHP and MySQL]]
 
* [[PHP and MySQL]]
 
* [[Web Application Security, Part 2]]
 
* [[Web Application Security, Part 2]]
 +
* [[FAQ - Mod 3]]
  
 
== Individual Assignments ==
 
== Individual Assignments ==
Line 21: Line 38:
 
=== Install MySQL and phpMyAdmin ===
 
=== Install MySQL and phpMyAdmin ===
  
Before you start, '''you need to install MySQL''' on your EC2 instance.  You may also install phpMyAdmin, a web frontend to MySQL, if you choose to do so.
+
Before you start, '''you need to install MySQL''' on your EC2 instance.  You may also install phpMyAdmin, a web frontend to MySQL, if you choose to do so. Instructions to install both can be found on the [[Introduction to MySQL]] page.
  
 
=== Set Up a Database ===
 
=== Set Up a Database ===
  
You may find the following article to be very helpful: [[Introduction to MySQL]]
+
You may find the following article to be very helpful: [[Introduction to MySQL#Managing Databases]]
  
 
# Create a database named '''wustl'''  
 
# Create a database named '''wustl'''  
Line 63: Line 80:
 
#: The appropriate fields in this table should make a foreign key reference to the corresponding fields in '''department'''.
 
#: The appropriate fields in this table should make a foreign key reference to the corresponding fields in '''department'''.
 
#: In addition, it is up to you to determine the field(s) for the primary key.
 
#: In addition, it is up to you to determine the field(s) for the primary key.
#: '''Note:''' If you are unsure which fields to make the foreign keys and feel the need to beg your friends or the TAs to give you the answer, bang your head on your desk whilst repeating "I WILL EXERCISE REASON AND COMMON SENSE AND I WILL CURTAIL MY DESIRE TO BLINDLY FOLLOW DIRECTIONS"
+
#: '''Note:''' If you are unsure which fields to make the foreign keys and feel the need to beg your friends or the TAs to give you the answer, we recommend exercising reason and common sense.
#: ''Disclaimer: don't actually bang your head on your desk, the resulting head trauma may in fact make it harder for you to determine which fields should have associated foreign keys.''
 
 
# Create a table named '''grades''' with the following fields:
 
# Create a table named '''grades''' with the following fields:
#* '''pk_grade_ID''' of an appropriately-sized unsigned integer type (should be larger than the one you chose for the '''students''' table)
+
#* '''pk_grade_ID''' of an appropriately-sized unsigned integer type (should be larger than the one you chose for the '''students''' table). There will never be more than 1,000 grades per student.
 
#*: You should declare this field to be '''auto_increment''' and make it the primary key.
 
#*: You should declare this field to be '''auto_increment''' and make it the primary key.
 
#*: This is called a ''surrogate key'' as opposed to a ''natural key'' because it doesn't arise "naturally" from the data; that is to say that it is not derived from application data. The use of surrogate keys vs. natural keys is a polemic issue amongst database designers.  For a decently unbiased take on the issue, see this page: http://www.agiledata.org/essays/keys.html#Comparison
 
#*: This is called a ''surrogate key'' as opposed to a ''natural key'' because it doesn't arise "naturally" from the data; that is to say that it is not derived from application data. The use of surrogate keys vs. natural keys is a polemic issue amongst database designers.  For a decently unbiased take on the issue, see this page: http://www.agiledata.org/essays/keys.html#Comparison
Line 87: Line 103:
  
 
The files containing the data are:
 
The files containing the data are:
* students: http://classes.engineering.wustl.edu/cse330/content/students_data.txt
+
* students: https://classes.engineering.wustl.edu/cse330/content/students_data.txt
* courses: http://classes.engineering.wustl.edu/cse330/content/courses.txt
+
* courses: https://classes.engineering.wustl.edu/cse330/content/courses.txt
* departments: http://classes.engineering.wustl.edu/cse330/content/departments_data.txt
+
* departments: https://classes.engineering.wustl.edu/cse330/content/departments_data.txt
* grades: http://classes.engineering.wustl.edu/cse330/content/grades_data.txt
+
* grades: https://classes.engineering.wustl.edu/cse330/content/grades_data.txt
 +
 
 +
To download the files to your AWS instance, run wget command below for each file.
 +
 
 +
For example, to download the students_data.txt file to my AWS instance I would type the following:
 +
 
 +
<source lang="bash">
 +
wget --no-check-certificate https://classes.engineering.wustl.edu/cse330/content/students_data.txt
 +
</source>
  
 
=== Insert More Data ===
 
=== Insert More Data ===
Line 96: Line 120:
 
Insert the following data into the tables that you have created and populated:
 
Insert the following data into the tables that you have created and populated:
  
# Insert an entry for CSE 330S
+
# Insert entries for CSE 330S, CSE 131, and CSE 260M.
 
#: ''If you don't know CSE's department code, how can you find out using the tables in the database?''
 
#: ''If you don't know CSE's department code, how can you find out using the tables in the database?''
# Insert the grades given for the students named below. You may choose to make the entries for whichever classes you like, with the one condition that all of the students must have a grade for CSE 330S (who wouldn't want to take that class: I hear the professor and TAs are amazing!)
+
# Insert the grades given for the students named below.  
 
#* '''Ben Harper'''
 
#* '''Ben Harper'''
 
#** E-mail: '''bharper@ffym.com'''
 
#** E-mail: '''bharper@ffym.com'''
 
#** Student ID: '''88'''
 
#** Student ID: '''88'''
 
#** Grades:
 
#** Grades:
#*** 35.5 (CSE 330S grade)
+
#*** 35.5 in CSE 330S
#*** 0
+
#*** 0 in CSE 131
#*** 95
+
#*** 95 in CSE 260M
 
#* '''Matt Freeman'''
 
#* '''Matt Freeman'''
 
#** E-mail: '''mfreeman@kickinbassist.net'''
 
#** E-mail: '''mfreeman@kickinbassist.net'''
 
#** Student ID: '''202'''
 
#** Student ID: '''202'''
 
#** Grades:
 
#** Grades:
#*** 100 (CSE 330S grade)
+
#*** 100 in CSE 330S
#*** 90.5
+
#*** 90.5 in CSE 131
#*** 94.8
+
#*** 94.8 in CSE 260M
 
#* '''Marc Roberge'''
 
#* '''Marc Roberge'''
 
#** E-mail: '''mroberge@ofarevolution.us'''
 
#** E-mail: '''mroberge@ofarevolution.us'''
 
#** Student ID: '''115'''
 
#** Student ID: '''115'''
 
#** Grades:
 
#** Grades:
#*** 75 (CSE 330S grade)
+
#*** 75 in CSE 330S
#*** 37
+
#*** 37 in CSE 131
#*** 45.5
+
#*** 45.5 in CSE 260M
#: You may choose any 3 classes you'd like for them to be enrolled in, with at least one class, "CSE 330S", in which they are all enrolled.
 
  
 
=== Querying Your Database ===
 
=== Querying Your Database ===
Line 126: Line 149:
 
Now that you have a fully-functional, populated database, let's do some queries on it!
 
Now that you have a fully-functional, populated database, let's do some queries on it!
  
Please take a screenshot of running the following '''select''' queries and show them to a TA (be sure to include your query command and the response:
+
Please Running the following <code>SELECT</code> queries, then copy the output to a text file of the form <code>query1.txt</code>, <code>query2.txt</code>, and so on, and commit them with the rest of your repository (be sure to include your query command and the entire response, including the number of results found). For each query, only the requested columns should be present. You will receive 0 points for any query that includes additional columns.
  
 
# Select the entire grades table.
 
# Select the entire grades table.
# Select all fields describing the courses offered in the school of arts and sciences (school code L).
+
# Select all fields describing the courses offered in the College of Arts & Sciences (school code <code>L</code>).
# The names, student IDs, and grades of the students who are in CSE330S.
+
# The names, student IDs, and CSE330 grades of all students who are in CSE330S.
 
#: '''Note:''' This query should involve joins.  You don't need to use any aggregation functions.
 
#: '''Note:''' This query should involve joins.  You don't need to use any aggregation functions.
 
# The names, e-mails, and average grades of any student with an average below 50 so that the dean can send them an email notification that they are now on academic probation.
 
# The names, e-mails, and average grades of any student with an average below 50 so that the dean can send them an email notification that they are now on academic probation.
#: ''You should be able to do this in only one query, without making any temporary tables.  You will need to use aggregation functions and the '''having''' keyword.''
+
#: ''You should be able to do this in only one query, without making any temporary tables.  You will need to use aggregation functions and the <code>HAVING</code> keyword.''
# An individual report card for Jack Johnson, consisting of his student ID, e-mail address, and average grade.
+
# An individual report card for Jack Johnson, consisting of only his student ID, e-mail address, and average grade.
 
#: ''Again, you should be able to do this in just one query, using the correct combination of aggregation functions and joins.''
 
#: ''Again, you should be able to do this in just one query, using the correct combination of aggregation functions and joins.''
#: '''Note:''' Your query must look for students with the name Jack Johnson instead of hard-coding the student ID.
+
#: '''Note:''' Your query must look for students with the name "Jack Johnson" instead of hard-coding the student ID.
  
 
== Group Project ==
 
== Group Project ==
Line 142: Line 165:
 
You will work in pairs on this project.  You may work with the same partner from Module 2, or change to someone else.
 
You will work in pairs on this project.  You may work with the same partner from Module 2, or change to someone else.
  
'''Important Reminder:''' frequently commit your work to your subversion repository as a backup!
+
'''Important Reminder:''' frequently commit your work to your Git repository as a backup!
  
 
=== Simple News Web Site ===
 
=== Simple News Web Site ===
Line 149: Line 172:
 
* http://digg.com/
 
* http://digg.com/
 
* http://slashdot.org/
 
* http://slashdot.org/
 +
* https://news.ycombinator.com/
  
 
You should use '''PHP''' unless you get permission to use another web technology from the instructor.  You may also use phpMyAdmin to help set up databases.
 
You should use '''PHP''' unless you get permission to use another web technology from the instructor.  You may also use phpMyAdmin to help set up databases.
Line 160: Line 184:
 
*: For more information on password security, refer to [[Web Application Security, Part 2#Password Security|the Web Application Security guide]].
 
*: For more information on password security, refer to [[Web Application Security, Part 2#Password Security|the Web Application Security guide]].
 
* Registered users can submit story commentary.
 
* Registered users can submit story commentary.
*: A link can be associated with each story, and they should be stored in a separate database field from the story
+
*: A link can be associated with each story, and they should be stored in a separate database field from the story.
 +
*: For example, a story that included a link could look like the following:
 +
*:: Title: Check out this cool search engine I found!
 +
*:: Body: This search engine that I found has been very helpful with all of my homework assignments. Searching my error messages helps me debug.
 +
*:: Link: http://google.com
 
* Registered users can comment on any story.
 
* Registered users can comment on any story.
 
* Unregistered users can only view stories and comments.
 
* Unregistered users can only view stories and comments.
 
* Registered users can edit and delete their stories and comments.
 
* Registered users can edit and delete their stories and comments.
* All data must be kept in a MySQL database (user information, stories, comments, and categories).
+
* All data must be kept in a MySQL database (user information, stories, comments, and links).
* Creative Portion
+
* Creative Portion.
  
 
=== Web Security and Validation ===
 
=== Web Security and Validation ===
  
No application is finished until much thought is put into web security and best practice.  Throughout this course, we heavily emphasize the dogma of [http://coderesponsibly.org/ responsible] coding.
+
No application is finished until much thought is put into web security and best practice.  Throughout this course, we heavily emphasize the dogma of [https://mirrors.meiert.org/coderesponsibly.org/ responsible] coding.
  
 
Read this week's Web Application Security guide: [[Web Application Security, Part 2]].  In particular, your project needs to:
 
Read this week's Web Application Security guide: [[Web Application Security, Part 2]].  In particular, your project needs to:
  
* '''Your application needs to be secure from SQL injection attacks'''. If you are using prepared queries, you should already be safe on this front.
+
* '''Your application needs to be secure against SQL injection attacks'''. If you are using prepared queries, you should already be safe on this front.
* '''Your passwords need to be securely salted''' to prevent rainbow table attacks.
+
* '''Your passwords need to be securely hashed salted''' to prevent rainbow table attacks.
 
* '''You should pass tokens in forms''' to prevent CSRF attacks.
 
* '''You should pass tokens in forms''' to prevent CSRF attacks.
 
* '''You should check all preconditions on the server side''' to prevent Abuse of Functionality attacks.
 
* '''You should check all preconditions on the server side''' to prevent Abuse of Functionality attacks.
Line 180: Line 208:
 
You shouldn't forget the practices you learned last week:
 
You shouldn't forget the practices you learned last week:
  
* '''Your page should validate''' with no errors through the W3C validator.
+
* '''Your page should validate''' with no errors or warnings through the W3C validator.
 
* '''Filter Input and Escape Output''', but not the other way around.
 
* '''Filter Input and Escape Output''', but not the other way around.
  
Line 188: Line 216:
  
  
'''Assignments must be demoed within 2 weeks of the assignment being due. We will grade late penalties based on Bitbucket submission time-stamps, so you are allowed to demo at TA office hours. However, after two weeks of not demoing, you will receive a zero for the assignment.'''
+
'''Assignments (including code) must be committed to Github by the end of class on the due date (commit early and often). Failing to commit by the end of class on the due date will result in a 0. '''  
 
 
#''You must save your repo as:''
 
#*"<Season><year>-Module<#>-FirstName-LastName-StudentID"
 
#*(ie. Spring2025-Module3-John-Doe-201343)
 
#''For the group project, you must save your repo as:''
 
#*"<Season><year>-Module<#>-FirstName1-LastName1-StudentID1-FirstName2-LastName2-StudentID2"
 
#*(ie. Spring2025-Module3-John-Doe-201343-Jane-Doe-201243)
 
  
'''If you do not name your repo correctly, the TA's are not responsible for grading your assignment. If we cannot find it, you will receive a zero.'''
 
  
 +
'''If you do not include a link to your group portion running on your instance in your group portion README.md, you will receive a 0 for the group portion.'''
 
________
 
________
  
 
# '''MySQL Queries (25 Points):'''
 
# '''MySQL Queries (25 Points):'''
#* A MySQL server is running on your instance (2 points)
+
#* A MySQL server is running on your instance. Put a screenshot in your repository of your terminal connected to your MySQL server. ( (2 points)
#* Tables fields, including data types, are correct (4 points)
+
#* Tables' fields, including data types and foreign keys, are correct (8 points)
#* Foreign keys are correct (4 points)
+
#*: ''Note: To demonstrate the structure of your tables, you should commit a text file containing the output of the <code>SHOW CREATE TABLE</code> command '''for each table.''' Each table's file should have a name of the form <code>tablename.sql</code> (e.g. <code>students.sql</code>).''
#* The output of each of the five queries is correct (3 points each)
+
#* The output of each of the five queries is correct. (3 points each)
 +
#*: ''Note: To demonstrate the output of your queries, text files containing the 'entire' result (including the query and number of records found) of running each query should committed to Github. The file for query 1 should be named <code>query1.txt</code>, the file for query 2 should be named <code>query2.txt</code>, and so on.''
 
# '''News Site (60 Points):'''
 
# '''News Site (60 Points):'''
 
#* '''''User Management (20 Points):'''''
 
#* '''''User Management (20 Points):'''''
 
#** A session is created when a user logs in (3 points)
 
#** A session is created when a user logs in (3 points)
 
#** New users can register (3 points)
 
#** New users can register (3 points)
#** Passwords are hashed using salted one-way encryption (3 points)
+
#** Passwords are hashed, salted, and checked securely (3 points)
 +
#**: ''Note: You will receive 0 points for this section if you use the <code>==</code> or <code>===</code> operators to compare password hashes, or if you use the <code>crypt</code> or <code>md5</code> functions at any point.''
 
#** Users can log out (3 points)
 
#** Users can log out (3 points)
 
#** A user can edit and delete his/her own stories and comments but cannot edit or delete the stories or comments of another user (8 points)
 
#** A user can edit and delete his/her own stories and comments but cannot edit or delete the stories or comments of another user (8 points)
 
#* '''''Story and Comment Management (20 Points):'''''
 
#* '''''Story and Comment Management (20 Points):'''''
 
#** Relational database is configured with correct data types and foreign keys (4 points)
 
#** Relational database is configured with correct data types and foreign keys (4 points)
 +
#*: ''Note: To demonstrate the structure of your database, you should commit a 'single' text file containing the output of the <code>SHOW CREATE TABLE</code> command '''for all tables in your database,''' called <code>tables.sql</code>. This one file should contain the output for all of your tables.
 
#** Stories can be posted (3 points)
 
#** Stories can be posted (3 points)
#** A link can be associated with each story, and they should be stored in a separate database field from the story (3 points)
+
#** A link can be associated with each story, and is stored in a separate database column from the story (3 points)
 
#** Comments can be posted in association with a story (4 points)
 
#** Comments can be posted in association with a story (4 points)
 
#** Stories can be edited and deleted (3 points)
 
#** Stories can be edited and deleted (3 points)
Line 225: Line 249:
 
#** Safe from SQL Injection attacks (2 points)
 
#** Safe from SQL Injection attacks (2 points)
 
#** Site follows the FIEO philosophy (3 points)
 
#** Site follows the FIEO philosophy (3 points)
#** All pages pass the W3C validator (2 points)
+
#** All pages pass the W3C HTML and CSS validators (2 points)
 
#** CSRF tokens are passed when creating, editing, and deleting comments and stories (5 points)
 
#** CSRF tokens are passed when creating, editing, and deleting comments and stories (5 points)
 
#* '''''Usability (5 Points):'''''
 
#* '''''Usability (5 Points):'''''
Line 231: Line 255:
 
#** Site is visually appealing (1 point)
 
#** Site is visually appealing (1 point)
 
# '''Creative Portion (15 Points)''' (see below)
 
# '''Creative Portion (15 Points)''' (see below)
#* '''''Make sure you have a creative.txt file in your group repo with the following:'''''
+
#* '''''Make sure you have a README.md file in your group repo with the following:'''''
 
#** Names and Student IDs of all the group members
 
#** Names and Student IDs of all the group members
#** A link to your running ec2 instance
+
#** A link to your homepage of the site
 
#** A brief description of what you did for your creative portion
 
#** A brief description of what you did for your creative portion
 
#** Any additional login details needed for the TA
 
#** Any additional login details needed for the TA

Latest revision as of 18:05, 22 August 2024

In Module 3, you will learn about MySQL, a web application database.

This article contains your assignments for Module 3.

Using the Wiki

Text enclosed by < and > should be replaced by content unique to you.

Example

$ sudo useradd -r -m -c "<My Full Name>" <usernameHere>

becomes

$ sudo useradd -r -m -c "Zach Cohn" zcohn


Reading

The following articles on the online class wiki textbook contain information that will help you complete the assignments.

Individual Assignments

You will create a simple grades database containing four tables.

IMPORTANT: The individual assignment is to be done by writing queries by hand. (You can use phpMyAdmin to run the queries, but do not let phpMyAdmin write your queries for you!) Writing your own queries gives you a greater understanding of the underlying mechanisms and databases in general. Additionally, having SQL knowledge and experience is a very marketable trait.

Install MySQL and phpMyAdmin

Before you start, you need to install MySQL on your EC2 instance. You may also install phpMyAdmin, a web frontend to MySQL, if you choose to do so. Instructions to install both can be found on the Introduction to MySQL page.

Set Up a Database

You may find the following article to be very helpful: Introduction to MySQL#Managing Databases

  1. Create a database named wustl
  2. Create a user named wustl_inst and give them the password wustl_pass

Create Tables

You may find the following article to be very helpful: MySQL Schema and State

When creating tables, keep the following items in mind:

  • You should create all tables such that they use the InnoDB storage engine, since we wish to make use of its support of foreign keys. In other database engines, foreign key constraints are not enforced. (If you mess up, you can use ALTER TABLE to change the storage engine.)
  • Where it is appropriate, you should take advantage of the ability to define fields NOT NULL so that you do not inadvertently insert incomplete data for a row.

Create the following tables:

  1. Create a table named students with the following fields:
    • id of an appropriately-sized unsigned integer type (we will never need to process more than a million students)
    • first_name of type VARCHAR(50)
    • last_name of type VARCHAR(50)
    • email_address of type VARCHAR(50)
    The primary key should be on the id field.
  2. Create a table named departments with the following fields:
    • school_code of type ENUM (e.g. "L" for ArtSci and "E" for Engineering). The options are as follows:
      'L', 'B', 'A', 'F', 'E', 'T', 'I', 'W', 'S', 'U', 'M'
    • dept_id of an appropriately-sized unsigned integer type (in the foreseeable future there will never be more than 200 departments in a school)
    • abbreviation of type VARCHAR(9) (e.g. CSE, ChemE, etc)
    • dept_name of type VARCHAR(200) (e.g. Computer Science and Engineering)
    The primary key should be on two fields: school_code and dept_id
    Note: Why not just use the department ID for the primary key? The ID numbers are sometimes reused across schools. For instance, department 33 in The College of Arts and Sciences is Psychology while department 33 in The School of Engineering and Applied Sciences is Energy, Environmental and Chemical Engineering. Thus we must also include the school code to differentiate them such that the record for E33 is distinct from that of L33.
  3. Create a table named courses with the following fields:
    • school_code of type ENUM
      This should have the same letters in the same order as the school_code ENUM field in the departments table.
    • dept_id of the same size of integer as in the departments table
    • course_code of type CHAR(5) (this will hold course codes; e.g., '330S', '131', '2960')
    • name of type VARCHAR(150)
    The appropriate fields in this table should make a foreign key reference to the corresponding fields in department.
    In addition, it is up to you to determine the field(s) for the primary key.
    Note: If you are unsure which fields to make the foreign keys and feel the need to beg your friends or the TAs to give you the answer, we recommend exercising reason and common sense.
  4. Create a table named grades with the following fields:
    • pk_grade_ID of an appropriately-sized unsigned integer type (should be larger than the one you chose for the students table). There will never be more than 1,000 grades per student.
      You should declare this field to be auto_increment and make it the primary key.
      This is called a surrogate key as opposed to a natural key because it doesn't arise "naturally" from the data; that is to say that it is not derived from application data. The use of surrogate keys vs. natural keys is a polemic issue amongst database designers. For a decently unbiased take on the issue, see this page: http://www.agiledata.org/essays/keys.html#Comparison
      Food for thought: Aside from simply exposing you to the concept, can you think of any reasons why a surrogate key might be preferable for this table? Alternatively, what attributes, if any, could you use as a natural key, and what might be the drawbacks of doing so?
    • student_id of the same integer (unsigned) type that you chose in the students table
    • grade of type decimal(5,2)
    • school_code of type ENUM
      Can you guess what entries to use for the enum?
    • dept_id of the same integer type that you've used for this field in other tables
    • course_code of the same type you chose for the course code in the courses table
    The grades table should have foreign keys to both the students table and the courses table.

Populate Tables

Now, populate the tables by downloading the text files given below and loading them into the appropriate tables.

Note: Do not insert all the values manually; instead, use the MySQL's ability to load in data from text files.

You may find the following article to be very helpful: MySQL Schema and State

The files containing the data are:

To download the files to your AWS instance, run wget command below for each file.

For example, to download the students_data.txt file to my AWS instance I would type the following:

wget --no-check-certificate https://classes.engineering.wustl.edu/cse330/content/students_data.txt

Insert More Data

Insert the following data into the tables that you have created and populated:

  1. Insert entries for CSE 330S, CSE 131, and CSE 260M.
    If you don't know CSE's department code, how can you find out using the tables in the database?
  2. Insert the grades given for the students named below.
    • Ben Harper
      • E-mail: bharper@ffym.com
      • Student ID: 88
      • Grades:
        • 35.5 in CSE 330S
        • 0 in CSE 131
        • 95 in CSE 260M
    • Matt Freeman
      • E-mail: mfreeman@kickinbassist.net
      • Student ID: 202
      • Grades:
        • 100 in CSE 330S
        • 90.5 in CSE 131
        • 94.8 in CSE 260M
    • Marc Roberge
      • E-mail: mroberge@ofarevolution.us
      • Student ID: 115
      • Grades:
        • 75 in CSE 330S
        • 37 in CSE 131
        • 45.5 in CSE 260M

Querying Your Database

Now that you have a fully-functional, populated database, let's do some queries on it!

Please Running the following SELECT queries, then copy the output to a text file of the form query1.txt, query2.txt, and so on, and commit them with the rest of your repository (be sure to include your query command and the entire response, including the number of results found). For each query, only the requested columns should be present. You will receive 0 points for any query that includes additional columns.

  1. Select the entire grades table.
  2. Select all fields describing the courses offered in the College of Arts & Sciences (school code L).
  3. The names, student IDs, and CSE330 grades of all students who are in CSE330S.
    Note: This query should involve joins. You don't need to use any aggregation functions.
  4. The names, e-mails, and average grades of any student with an average below 50 so that the dean can send them an email notification that they are now on academic probation.
    You should be able to do this in only one query, without making any temporary tables. You will need to use aggregation functions and the HAVING keyword.
  5. An individual report card for Jack Johnson, consisting of only his student ID, e-mail address, and average grade.
    Again, you should be able to do this in just one query, using the correct combination of aggregation functions and joins.
    Note: Your query must look for students with the name "Jack Johnson" instead of hard-coding the student ID.

Group Project

You will work in pairs on this project. You may work with the same partner from Module 2, or change to someone else.

Important Reminder: frequently commit your work to your Git repository as a backup!

Simple News Web Site

Examples:

You should use PHP unless you get permission to use another web technology from the instructor. You may also use phpMyAdmin to help set up databases.

You may find this wiki article helpful: PHP and MySQL

Requirements

  • Users can register for accounts and then log in to the website.
  • Accounts should have both a username and a secure password. NEVER store plaintext passwords in a database!
    For more information on password security, refer to the Web Application Security guide.
  • Registered users can submit story commentary.
    A link can be associated with each story, and they should be stored in a separate database field from the story.
    For example, a story that included a link could look like the following:
    Title: Check out this cool search engine I found!
    Body: This search engine that I found has been very helpful with all of my homework assignments. Searching my error messages helps me debug.
    Link: http://google.com
  • Registered users can comment on any story.
  • Unregistered users can only view stories and comments.
  • Registered users can edit and delete their stories and comments.
  • All data must be kept in a MySQL database (user information, stories, comments, and links).
  • Creative Portion.

Web Security and Validation

No application is finished until much thought is put into web security and best practice. Throughout this course, we heavily emphasize the dogma of responsible coding.

Read this week's Web Application Security guide: Web Application Security, Part 2. In particular, your project needs to:

  • Your application needs to be secure against SQL injection attacks. If you are using prepared queries, you should already be safe on this front.
  • Your passwords need to be securely hashed salted to prevent rainbow table attacks.
  • You should pass tokens in forms to prevent CSRF attacks.
  • You should check all preconditions on the server side to prevent Abuse of Functionality attacks.

You shouldn't forget the practices you learned last week:

  • Your page should validate with no errors or warnings through the W3C validator.
  • Filter Input and Escape Output, but not the other way around.

Grading

We will be grading the following aspects of your work. There are 100 points total.


Assignments (including code) must be committed to Github by the end of class on the due date (commit early and often). Failing to commit by the end of class on the due date will result in a 0.


If you do not include a link to your group portion running on your instance in your group portion README.md, you will receive a 0 for the group portion. ________

  1. MySQL Queries (25 Points):
    • A MySQL server is running on your instance. Put a screenshot in your repository of your terminal connected to your MySQL server. ( (2 points)
    • Tables' fields, including data types and foreign keys, are correct (8 points)
      Note: To demonstrate the structure of your tables, you should commit a text file containing the output of the SHOW CREATE TABLE command for each table. Each table's file should have a name of the form tablename.sql (e.g. students.sql).
    • The output of each of the five queries is correct. (3 points each)
      Note: To demonstrate the output of your queries, text files containing the 'entire' result (including the query and number of records found) of running each query should committed to Github. The file for query 1 should be named query1.txt, the file for query 2 should be named query2.txt, and so on.
  2. News Site (60 Points):
    • User Management (20 Points):
      • A session is created when a user logs in (3 points)
      • New users can register (3 points)
      • Passwords are hashed, salted, and checked securely (3 points)
        Note: You will receive 0 points for this section if you use the == or === operators to compare password hashes, or if you use the crypt or md5 functions at any point.
      • Users can log out (3 points)
      • A user can edit and delete his/her own stories and comments but cannot edit or delete the stories or comments of another user (8 points)
    • Story and Comment Management (20 Points):
      • Relational database is configured with correct data types and foreign keys (4 points)
      Note: To demonstrate the structure of your database, you should commit a 'single' text file containing the output of the SHOW CREATE TABLE command for all tables in your database, called tables.sql. This one file should contain the output for all of your tables.
      • Stories can be posted (3 points)
      • A link can be associated with each story, and is stored in a separate database column from the story (3 points)
      • Comments can be posted in association with a story (4 points)
      • Stories can be edited and deleted (3 points)
      • Comments can be edited and deleted (3 points)
        Note: Although there are only 6 points allocated for editing/deleting in this section, there are 8 more points at stake in the User Management section that cannot be earned unless editing/deleting is implemented. Implementing editing but not deleting, or vice-versa, will result in earning half the points.
    • Best Practices (15 Points):
      • Code is well formatted and easy to read, with proper commenting (3 points)
      • Safe from SQL Injection attacks (2 points)
      • Site follows the FIEO philosophy (3 points)
      • All pages pass the W3C HTML and CSS validators (2 points)
      • CSRF tokens are passed when creating, editing, and deleting comments and stories (5 points)
    • Usability (5 Points):
      • Site is intuitive to use and navigate (4 points)
      • Site is visually appealing (1 point)
  3. Creative Portion (15 Points) (see below)
    • Make sure you have a README.md file in your group repo with the following:
      • Names and Student IDs of all the group members
      • A link to your homepage of the site
      • A brief description of what you did for your creative portion
      • Any additional login details needed for the TA