Assignment 2 – Accessing Databases

In the previous assignment we created two databases in MySQL that we will be using throughout the semester.

In this assignment we will learn the typical ways databases are accessed.

Part 1: Constraints

In phpMyAdmin add the following constraints to the Suppliers Parts database:

  1. S.SNO is the primary key and up to 8 characters
  2. SNAME is up to 30 characters and can’t be NULL
  3. STATUS is an integer between 1 and 100 inclusive with default 1
  4. S.CITY is up to 30 characters and can’t be NULL
  5. At most one supplier can be located in Athens at any one time
  6. There must be one at least one London supplier
  7. P.PNO is the primary key and up to 8 characters
  8. PNAME can be up to 30 characters and can’t be NULL
  9. COLOR can be up to 20 characters and can’t be NULL
  10. WEIGHT can be up to 9999.9 and can’t be NULL (don’t worry about zero or negative weights)
  11. P.CITY can be up to 30 characters and can’t be NULL
  12. { SP.SNO , SP.PNO } is the primary key for SP
  13. QTY can be an integer up to 11 digits and can’t be NULL (don’t worry about zero or negative quantities)
  14. Add the foreign key constraints to SP. Don’t cascade deletes or updates. (Note all your tables must use the InnoDB Storage Engine.)

Use constraints on primitive field types as much as possible. Use triggers when necessary. Be sure to consider inserts, updates and deletes.

Check Your Primative Constraints
UH Userame: UH 8-digit ID:
Check Your Constraints Enforced With Triggers
UH Userame: UH 8-digit ID:

Part 2: Create Database User

In cPanel in MySQL Admin, create a new user with full access privileges to both of your databases. Make sure you remember the username and password. Note that as with your database names, your username will be preceded with your UH username followed by an underscore.

Part 3: PHP Web Page Access

In cPanel, go to the File Manager. Create a folder in your public_html directory named MyAssignments (case sensitive). Create the following PHP programs inside MyAssignments.

  1. a1_partsInTransit.php that displays a table with all information about each part in transit. Note that S.CITY is different than P.CITY but SNO and PNO mean the same in all relations. There should be as many tuples in your answer as there are in SP. There should be as many columns as there are unique attributes. Do not show foreign key columns as they would be redundant. Your code should look something like this:
    <?php
    
    $sqlHost = 'localhost';
    $sqlUser = '???????';
    $sqlDatabase = '?????????';
    $sqlPass = '??????????';
    
    $conn = mysql_connect($sqlHost, $sqlUser, $sqlPass)
       or die("Couldn't connect to MySQL server on $sqlHost: " . mysql_error() . '.');
    
    $db = mysql_select_db($sqlDatabase, $conn)
       or die("Couldn't select database $sqlDatabase: " . mysql_error() . '.');
    
    $sql="SELECT S.SNO, SNAME, STATUS, S.CITY AS SCITY, QTY, P.PNO, PNAME, COLOR, WEIGHT, P.CITY AS PCITY FROM SP LEFT JOIN S ON S.SNO=SP.SNO LEFT JOIN P ON P.PNO=SP.PNO";
    $return = mysql_query($sql,$conn) or die("Couldn't perform query $sql (".__LINE__."): " . mysql_error() . '.');
    ?>
    <table border=1 cellspacing=0 cellpadding=5 style="font-family:arial,helvetica; font-size:10pt;">
    <tr>
        <td>SNO</td>
        <td>SNAME</td>
        <td>STATUS</td>
        <td>SCITY</td>
        <td>QTY</td>
        <td>PNO</td>
        <td>PNAME</td>
        <td>COLOR</td>
        <td>WEIGHT</td>
        <td>PCITY</td>
    </tr>
    
    <?php
    while($record=mysql_fetch_array($return))
    {
    ?>
    
    <tr>
        <td><?php echo $record['SNO']; ?></td>
        <td><?php echo $record['SNAME']; ?></td>
        <td><?php echo $record['STATUS']; ?></td>
        <td><?php echo $record['SCITY']; ?></td>
        <td><?php echo $record['QTY']; ?></td>
        <td><?php echo $record['PNO']; ?></td>
        <td><?php echo $record['PNAME']; ?></td>
        <td><?php echo $record['COLOR']; ?></td>
        <td><?php echo $record['WEIGHT']; ?></td>
        <td><?php echo $record['PCITY']; ?></td>
    </tr>
       
    <?php 
    }
    
    echo "</table>";
     mysql_close($conn);
    
    ?>
  2. a1_employeeAssignments.php that displays a table with all the information about each project assignment. Note that the DEPARTMENT relation will be used twice. There should be as many tuples in your answer as there are in ASSIGNMENT. Show all unique attributes without foreign keys.

Part 4: API Access

API programs output JSON objects instead of HTML. In this part you will create API programs similar to the ones you created in Part 2. However, we are going add the ability to accept an input which specifies only a subset of attributes to return. Create the following PHP programs inside MyAssignments.

  1. a1_suppliersPartsAPI.php that outputs the same table that displays from a1_partsInTransit.php except it returns (displays) the table as a JSON object. Now, add the ability to accept as input a set of attributes (i.e., a relation heading) and return a JSON object containing a relation with just those attributes. If there is no input then it outputs the full relation as in Part 2. Start with the code you wrote for a1_partsInTransit.php. Use $_REQUEST['projection'] to input a commalist of SELECT column-fields for the output relation. Remember you want to return a relation, so use the DISTINCT in the SELECT statement. Use echo json_encode($retObj, JSON_PRETTY_PRINT) to output the return array. Use array_push($retObj,$record) to create the array by pushing MySQL return records into the return object.
  2. a1_employeeProjectsAPI.php  that outputs the same table that displays from a1_employeeAssignments.php except it returns (displays) the table as a JSON object. Now, add the ability to accept as input a set of attributes (i.e., a relation heading) and return a JSON object containing a relation with just those attributes. If there is no input then it outputs the full relation as in Part 2. Use $_REQUEST['projection'] to input a commalist of SELECT column-fields for the output relation.

Note that any subset relation can be easily reproduced simply by specifying the attributes.