Difference between revisions of "Module 5"

From CSE330 Wiki
Jump to navigationJump to search
Line 33: Line 33:
=== Django Tutorial===
=== Django Tutorial===
====Install Djgano====
====Install Djgano====
<p> This procedure assumes you are using and Amazon AMI Linux on an ec2 instance. An in-depth install guide can be found on the [https://docs.djangoproject.com/en/dev/topics/install/# Django website], but a quick version is below. Run these commands as root. </p>
<li>Install Distribute (prerequisite for pip):</li>
<code>curl http://python-distribute.org/distribute_setup.py | python</code>
<li>Install pip:</li>
<code>curl https://raw.github.com/pypa/pip/master/contrib/get-pip.py | python</code>
<li><p>Install Django using pip:</p></li>
<code>pip install Django</code>
<p> That's it. </p>
====Django Assignment====
====Django Assignment====
You may find the following article to be very helpful: [[Introduction to MySQL]]
# Create a database named '''wustl'''
# 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:
# 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.
# 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? The ID numbers are sometimes reused across schools. For instance, department 33 in The School 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.
# 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, 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"
#: ''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:
#* '''pk_grade_ID''' of an appropriately-sized unsigned integer type (should be larger than the one you chose for the '''students''' table)
#*: 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'''
#* '''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:
* students: http://research.engineering.wustl.edu/~todd/cse330/students_data.txt
* courses: http://research.engineering.wustl.edu/~todd/cse330/course_data.txt
* departments: http://research.engineering.wustl.edu/~todd/cse330/departments_data.txt
* grades: http://research.engineering.wustl.edu/~todd/cse330/grade_data.txt
=== Insert More Data ===
Insert the following data into the tables that you have created and populated:
# Insert an entry for CSE 330
#: ''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 330 (who wouldn't want to take that class: I hear the professor and TAs are amazing!)
#* '''Ben Harper'''
#** E-mail: '''bharper@ffym.com'''
#** Student ID: '''88'''
#** Grades:
#*** 5.5
#*** 0
#*** 20
#* '''Matt Freeman'''
#** E-mail: '''mfreeman@kickinbassist.net'''
#** Student ID: '''202'''
#** Grades:
#*** 100
#*** 90.5
#*** 94.8
#* '''Marc Roberge'''
#** E-mail: '''mroberge@ofarevolution.us'''
#** Student ID: '''115'''
#** Grades:
#*** 15
#*** 37
#*** 45.5
#: 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 ===
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:
# Select the entire grades table.
# Select all fields describing the courses offered in the school of arts and sciences (school code L).
# The names, student IDs, and grades of the students who are in CSE330.
# The names and e-mails 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.''
# An individual report card for Jack Johnson, consisting of two tables (these can be done using separate queries):
## 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.''
## The classes he is in and his grades for those classes. To give you an idea, one row in the table should look like this:
##: <code>E 81 400 Independent Study 98.5</code>
== Group Project ==
== 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.
You may work in pairs on this project.  
'''Important Reminder:''' frequently commit your work to your subversion repository as a backup!
=== Simple News Web Site ===
* http://digg.com/
* http://slashdot.org/
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.
=== Image Tagging/Browsing Site ===
You may find this wiki article helpful: [[PHP and MySQL]]
You may find this wiki article helpful
==== Requirements ====
==== Requirements ====

Revision as of 23:28, 18 October 2012


In Module 6, you will learn about python, a scripting language, and Django, a web framework.

This article contains your assignments for Module 6.

Individual Assignments

Python tutorial Django tutorial

Python Tutorial

Install Python Tools

  • sudo yum install python-setuptools
  • sudo yum install python-devel

Python Assignment

  • You will write a python script that reads a set of student grades in from a file and does some basic parsing and processing. here is the section of the python tutorial on reading and writing files.
  • An example grades file is here. For the sake of simplicity you can assume that the file name is always going to be grades.txt.
  • The first line of the file is of the form: NUM_LABS,NUM_EXAMS,LAB_WEIGHT
  • All of the other lines in the file are of the form: FIRSTNAME LASTNAME|GRADE|TYPE
  • The FIRSTNAME and LASTNAME fields are the student's full name (always only a first and last name), the GRADE is one grade for the student, and the TYPE describes what kind of assignment the grade was for, in this case either a 'lab' or an 'exam'.
  • Your script should read in a grades file with the above format and perform the following:
    1. Compute the final grade for every student, given that there were a total of NUM_LABS labs, NUM_EXAMS exams, and that the labs account for a total of LAB_WEIGHT percent of the grade.
    2. Compute the final letter grade based on the final numeric grade (A = 90 or above, B = 80 - 90, C = 70 - 80, D = 60 - 70, F = below 60). 
    3. Ignore any blank lines.
    4. Sort the students by last name, and print out final grades in that sorted order.
    5. No names should be hard coded into the script (you will run your script on another file with different student names when we grade your assignment).
    6. Finally, the script should take one optional command line argument, which is a string to match against student names.  Only names that match the string in full or in part, should be printed out with their final grades.
      • For example, with the argument 'John' grades for both John Smith and John Locke should be printed (if those are the only two Johns in the grades.txt file)

Django Tutorial

Install Djgano

This procedure assumes you are using and Amazon AMI Linux on an ec2 instance. An in-depth install guide can be found on the Django website, but a quick version is below. Run these commands as root.

That's it.

Django Assignment

Group Project

You may work in pairs on this project.

Image Tagging/Browsing Site

You may find this wiki article helpful


  • 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 stories: either a link with summary or news text.
    You do not have to make a distinction between the two types of stories, although if you want to, you could do something with this for the creative portion of your project.
  • Registered users can comment on any story.
  • Administrator users can delete stories and comments.
  • Unregistered users can only view stories and comments.
  • Registered users can edit their stories and can delete their comments.
  • All data must be kept in a MySQL database (user information, stories, comments, and categories).
  • As before, please check with a TA to see if your creative portion is okay or not before you proceed.

Web Security and Validation

Your project needs to demonstrate that thought was put into web security and best practice. For more information, see this week's Web Application Security guide: Web Application Security, Part 2

In particular:

  • Your application needs to be secure from SQL injection attacks. If you are using prepared queries, you should already be safe on this front.
  • All of your output needs to be sanitized using htmlentities().

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

  • You should pass tokens in forms to prevent CSRF attacks.
  • Your page should validate with no errors through the W3C validator.


Due Date: Wednesday October 10th, by 1pm (both individual and group)

Assignment Points
Tables Correct 2
Data Queries Correct 2
Group Portion:
User Authentication 1
User Registration 1
Salted One-Way Encryption 1
Main page displays all stories (or most recent stories) 1
Page with individual story and comments 1
Story Submission 1
Comment System 1
Administrator Deletion of Stories/Comments 1
User Edit/Delete of Story (1 pt) and Delete of Comment (1 pt) 2
Protect Against SQL Injection Attack 1
Sanitize Output 1
CSRF Safe and Validation 1
Creative Portion 2

Total Points = 19