1. (30 points) Suppose you are given the following tables in an SQL database. Each is shown with 1 or 2 lines of sample data but you should assume that the tables are arbitrarily long:

    Projects
    IdNameCostOffice_Id
    123Claxton Renovation10500037996

    Offices
    IdCityStateStreet_AddressPhone_Number
    37996KnoxvilleTN1119 Volunteer Blvd865-974-1000

    EmployeeProjects
    Employee_IdProject_Id
    783123
    783875

    EmployeeInfo
    IdNameEmailArea_CodePhoneOffice_Idsalary
    783Herculesherc@buildit.com423789-13933203025000

    You can create example tables to test your queries on by copying the script /home/bvz/cs594/hw/hw9/createProjects.php to your homework directory, editing the $database, $username, and $password strings, and then executing the script (remember to execute it on a cetus or hydra machine--other machines in the department do not have the php mysql module installed).

    Write the following SQL queries (do not worry about upper/lower case) and put your answers in a file named answers.txt:

    1. Print the Offices table
    2. Print the names of all employees in the 423 area code
    3. Print the names and salaries of all employees in the 423 area code who make more than $50000.
    4. Insert a new entry into the Projects table with the following information:

      • id: 457
      • name: Parthenon
      • cost: 1000000
      • office id: 8596

    5. Delete all entries from the EmployeeProjects table that have a project id of 123.
    6. Update the salary of employee 783 to 30000 in the EmployeeInfo table.
    7. Print the name of each project and the street address of the office that serves it (the office id is the id of the office that serves it).
    8. Print the names, email addresses, and phone numbers (area code and phone number) of all employees that are working on project 123.

  2. (30 points) Write a php script named avgcost.php that computes and prints the average cost of a project in the Projects table. The output should look something like:

    Average project cost = $xxxxx.xx

    The only important thing about the output is that it be restricted to two decimal digits.

  3. (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. 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. You should use the mysql database that has been created for you to store the table.
    4. 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 Bo to grade your table since your script will first drop the previous student's table.
    5. 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).