Module 3

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

This article contains your assignments for Module 3.

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.

Set Up a Database

You will probably find the following article to be very helpful:

• Introduction to MySQL

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

Create Tables

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? 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.

Group Project

You will work in pairs on this project.

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

Web Security

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

Grading

Due Date: _____ (both individual and group)

Assignment	Points
Birthday Card	2
Group Portion:
Multi-User Login	1