CS 457/557 Fall 2016
Assignment #6 – PHP or Go and MYSQL
Mode: 1-2 persons (same as HW#1) Groups of 2 must do Task 4 of Step 3.
Due Date: Nov. 28 – email the url by 11:59 pm to jarobinson3@crimson.ua.edu Note: you should design your solution so that you need to only email ONE link to the grader. That link should point to a page which contains separate links for each of the tasks described in Step 3 (4,5 for CS557) below.
This assignment is a continuation of Assignment #1.
You can use PHP and MySQL Info on PHP/MySQL
Alternatively, you can use Go and MySQL. Info on Go/MySQL
Step 1: First, map the ER diagram from your database specification from HW#1 to the relational model. You must use the rules we discussed in class. One easy way to do this is to have the tool you used to draw your ER diagram generate the create table statements.
Next, actually create the tables in MySQL. You can use a development to create the tables and insert tuples. Several such tools for MySQL are: http://software.dell.com/products/toad-for-mysql/ (free version for Mac in appstore), http://www.sequelpro.com/ for mac and http://www.heidisql.com/ for windows. When using Toad or some other tool, the Host is: cs-sql2014.ua-net.ua.edu User: your first inital and last name all lower case, Password: your CWID, Database: same as the User.
Step 2: Insert tuples into your tables (you can use the insert command from SQL ). You should have a total of at least 50 tuples in the tables (that means if you have 5 tables you have an average of 10 rows per table). Again, you can use a tool to insert tuples (e.g. Toad, etc.) OR You can embed these SQL statements in PHP or Go.
Step 3: Using PHP and MySQL, or Go and MySQL, you must create an application to do the following:
Task 1) Create a form which allows the user to display the tables in your DB. You should format the output so that it is printed in table format, meaning column names only at the top and each tuple in a row.
Task 2) Create a form which allows the user to select one of two queries to your database. These two queries must use different tables and one of the queries must utilize a join operation. Both queries must also prompt the user to input a host variable(s) that is used by the query in the where clause. An example is a GPA value to find all students with a value > than the specified GPA. This means your query must prompt the user input a value into a form. Make sure someone who is not familiar with your DB is able to input a valid value for the host variable. Again, you should format the output so that it is printed in table format with column names only at the top and each tuple in a row.
Task 3) Create a form which allows you to insert tuples into one of your tables. Allow the user to display and query the resulting table using Task 1 and Task 2 above.
Task 4) To be done only if 2 in a group. Create a view, then create a form which uses that view.
NOTE: In this assignment you will lose points if it is not obvious how to use the form or if it is difficult to use the form (e.g. on insert, specify if a variable is number or char, if it is a FK, etc).
Step 4 – For CS557 and Extra Credit for CS457:
Allow the user to type in any SELECT query on one of the tables from your database. The output must be nicely formatted so that it is printed in table format with column names and each tuple in a row.
Step 5 – CS557 only:
Implement your derived attribute.
Deliverables:
Task 3: e-mail url before 11/59 pm on 11/28 to jarobinson3@crimson.ua.edu
In the subject indicate if you used Go or PHP and any additional group member email
Also attach your Go or PHP code.