1. (30 points) This problem is going to give you practice with combining an html page with a backend PHP script that interacts with a mysql database. You are going to create a small web-site that allows the user to vote on their favorite color and that displays the number of votes cast for each color. On the back end you are going to keep a relation in your sql database that records the number of votes for each color. You will need to create a php file named colorVote.php. This php page will create both the initial html page that the user sees and the updated vote count after the user votes for a color. For both the initial front page and the updated vote counts, your php script should retrieve all the colors and their votes from the color relation and then create an html form that has radio buttons and a label for each color, and the votes associated with each color. The action for the form should be "colorVote.php". You will need to be able to have your php script distinguish between the initial invocation of the script, and subsequent invocations which will contain votes that need to be added to the database. The first time the script is invoked there should be no form data available, so you should be able to use that fact to distinguish between the initial and subsequent cases. You will not create a .html page for this problem because you need to invoke the .php script in order to retrieve the initial vote counts from the database. This was the type of dynamic page creation that PhP was originally invented for. Here are a couple additional requirements for this problem:
   1. Please submit a second file, named createColor.php, that creates the color relation that will be used by your colorVote.php file. Please name your relation ColorVote, and please put a Drop Table query at the beginning of your php file to drop ColorVote, so that when the TA tests your code, the previous students' ColorVote relation gets deleted.
   2. At a minimum use the colors red, orange, yellow, green, blue, violet, black and white. You may use additional colors if you like.
   3. You should try to come up with an attractive design for your vote count page.

2. (40 points) Check out this sample account management form. You can play with it but you will be sharing the .xml file that it uses with other students so please try to play only with usernames based on your username (note that you will not be using a xml file, but I did to avoid giving away the answer to the problem). Your task is to write a php script named account.php that implements the three operations shown on this form:
   1. Login: The php script should verify that the entered user id exists, then verify the password, and finally generate an html page with the appropriate response. The possible responses are:
      • "Welcome xxxx!": xxxx should be the name associated with the user id. For example, "Welcome Brad Vander Zanden!".
      • "Invalid userid: xxxx": xxxx should be the invalid user id entered by the user.
      • "Invalid password"

   2. Add Account: The script should verify that the user id does not already exist in the account database and then add the user id, name, and password to the database. The script should generate an html page with the following possible messages:
      • Account xxxx created--Welcome xxxx!: The first xxxx should be replaced with the user id and the second xxxx should be replaced with the user's name.

      • xxxx already exists--please select another user id: The xxxx should be replaced with the duplicate user id.

   3. Delete Account: The script should verify that the user id exists and then delete the entry from the account database. The script should generate an html page with the following possible messages:
      • xxxx successfully deleted: xxxx should be the name of the person, not the user id.

      • xxxx not found: xxxx should be the invalid user id

   The problem requirements are as follows:

   1. You must use a mysql table to store the account information associated with each user.
   2. You need to design and create the table. Please call the table Accounts.
   3. Write a short php script named createAccountTable.php to create the table. Please also have this script first delete the table (use SQL's DROP command). This will make it easier for the TA to grade your table since your script will first drop the previous student's table.
   4. It is okay for the name to be kept as a single name (i.e., you do not need to break it into a last name and a first name).

3. (10 points) Describe two advantages that XML has over a database. Please state each advantage in 3 sentences or less. Place your answer to this question in a file named answer.txt.

4. (20 points) Suppose I want to design an XML file that encodes exam questions and their answers. There are four types of exam questions: 1) true/false, 2) multiple choice, 3) essay, and 4) matching. The questions have the following information associated with them:
   • true/false questions have a question statement and the answer.
   • multiple choice questions have a question, some number of choices, and the correct answer.
   • essay questions have a general statement, and then one or more parts, each of which consists of a question and an answer. Finally an essay question may have an optional hint.
   • matching questions have one or more potential answers, one or more statements that are to be matched with the answers, and an answer key that contains statement-answer pairs (i.e., each pair has two parts, a statement and the correct matched answer for that statement).
   Additionally each question contains the number of points that it is worth (the parts of an essay question do not have point values associated with them--only the overall question does).

   Your solution should show me the hierarchy of element tags you will use. Place an asterisk (*), plus (+), or question mark (?) next to an element if it can occur 0 or more times, 1 or more times, or 0/1 times respectively. If you require one of two elements or sets of elements, you can write <element1> | <element2> and use the following syntax:

   <element1> ... | <element2> ...

   Hints:

   1. Formatting, such as horizontal rules and question numbers/letters, should not be part of your structure.
   2. Only use attributes if you want points deducted.
   3. Here is a sample specification for the address book example presented in class:

      addressbook
          contact+
              name
                  firstname
                  middlename?
                  lastname
              address
                  street
                      street_number
                      street_name
                      street_suffix
                |
                  pobox?
                  apt
                  aptname
              city
              state
              zip
      

   4. Do not worry about the types of the data contained in the elements (e.g., integer, string, etc.). All I care about is the way you structure your data.
   5. Things you might want to think about:
      • What appear to be the major parts of the exam? The major parts might serve as tags.
      • Do the questions seem to fall into certain types of categories? The categories might serve as tags.
      • Do the questions seem to share some parts in common? How could you cleanly handle the fact that questions share some parts in common and have some differences?

Please submit the following files:

1. colorVote.php and createColor.php
2. account.php and createAccountTable.php (we will be calling this file from our account management form)
3. answers.txt (questions #3 and 4)