The item ids and the location ids

G51DBS– Database Systems 2013
Coursework Part 3 Requirements, DRAFT v0.9

Part 3: Using a database and setting up PHP

2. Download the required file from the school servers using the following command: wget http://cs.nott.ac.uk/~jaa/dbs/dbscw2013.tar.gz

This command downloads the file straight to your home directory. This file contains all the files we need to start a website with PHP.

You should get a message like this: “Failed to connect to MySQL server: Access denied for user 'username'@'avon.cs.nott.ac.uk' (using password: YES)”

IMPORTANT: You can only use PHP pages from within the university.These pages will not be visible from off campus for security reasons (PHP pages let you potentially change the server or database). So please make sure that you are using a PC within the school (e.g. the A32 lab) when you test your PHP files.

6. Create the database files:

Login to MySQL as your user, from the directory which you are in.

you should now see your initial coursework 3 pages. As long as you can see both pages correctly you have installed your files correctly.

If you have set up all your database and your dbconnect.php correctly, then when you view the 3a file you should see a list of descriptions shown, pulled from the database, as shown on the next page.

Part 3a is probably the easier of the two parts to understand, and involves producing a number of SQL queries to display the contents of the database in a PHP page, so that you can see what is in the database.

Part 3b then uses some similar queries to those in part a, along with some updates which allow you to modify the database contents. You are in control of a person who can move around the locations and can pick up and put down items. The single PHP file will give details of the current location and will allow you to change things. The instructions will guide you through the changes one by one.

Q12: Counts of items per location

Q13: Items in the same location

Q14: The locations of all forks

• All code should be written in the coursework_3a.php or coursework_3b.php files, which have already been started for you. You should not need to worry about formatting because I have done it all for you. In each case, all that you need to do is to alter the query string which is used, and alter the PHP code which specifies which columns are shown in the table.

Basically this tests your ability to write SQL code while showing you how embedded SQL works in PHP so that you get used to it. It is not meant to force you to have to learn complex PHP, and you will not need to do so.

Requirements for Part 3a:

The purpose of coursework_3a.php is to provide queries to view the database contents.

IMPORTANT: Some questions have simpler versions available. In each case EITHER do the simpler version or the full version (it will be obvious from the output table which you chose to do). The simpler versions need fewer joins, to simplify some of the more complex joins, and usually remove the need to do multiple joins to the Description table. The sample answer that I gave has both, for illustration, but you should provide EITHER the full answer or the simplified answer, not both.

To access your answer, go to the following URL:
http://avon.cs.nott.ac.uk/~yourusername/dbscw2013/cw3a.php (Please note that this is different to the URL for the demo answer!)

This simpler question requires you to display a list of the exits from each location. In this version you should just display a list of the location ids, location description IDs and the description id for the name of the exit direction.

e.g. “100 | 100 | 2”
Sort the list in order of increasing location ID then exit description ID.

description.

• Ensure that locations without exits are also shown in the list (for these fill in only the location ID and the Location Description, leave all other fields blank).

e.g. “100 | 100 | 12, 4 | 101”

Question 9: In this question you need to display a list of location ids for locations which are two locations apart. i.e. you should show locations A and B if it is possible to go from location A, through an exit to any location, through an exit from that location and arrive at location B. Only show each pair of locations once. Even though it is usually possible to go from location A to another location and back to location A, do not show any rows where the same location is shown twice.

e.g. “In the NE Corner of the Living Room | Ornate Vase | 2”

Question 12: Provide a list of the location names for locations with objects and the total number of objects in each location. (Think about your answer to Q11 to help you.) Sort the list into decreasing order or objects in the location. Your columns should be the location name, the location id and the count of the items in the location.

Simpler version of question 13: If you find Q13 to be too hard, you can do this simpler version instead. You will get fewer marks, but it is substantially easier. Doing this version may also help you to work out how to do the full version of Q13. This question is exactly the same as Q13 except that you display description IDs rather than the descriptions themselves, so you do not need to look up the description text in the Description table.

Provide a list of the pairs of items which are in the same location. Show the IDs of the two items and the ID of the description of the location they are located at, followed by the IDs of the two items and the location ID. Only show each pair once (i.e. do not show A and B together as well as B and A together). Do not show that an item is in the same location as itself. Sort the list in order of location id then ascending numerical order of item 1 description id then item 2 description id.

Show the location of all Forks of any kind (i.e. items which have the text Fork in their item name – be careful, you may want to look at the item list). Provide a list of the item names, the location description ids, the item IDs and the location ids. Sort the list by location id then item id.

e.g. “Fork | 106 | 41 | 106”

(Please note that this is different to the URL for the example answer, above!)

You should see something like this:

Although these questions are in a logical order for implementing and testing functionality, you should be able to do them in any order, so if you get stuck I suggest you move to the next question.

Question 15: The first thing you will do is to add some code to implement a move around the house when the correct variable is passed into the page.

e.g. $query = “SET dataValue = ” . $_GET[‘newloc’] . “ WHERE ” would set $query to the string “SET dataValue = 6 WHERE ” if the value of newloc was 6.

Do the same thing as this, using the . operator to create the correct SQL query string.

Question 16: Create an SQL query in the $query string, to determine the exit description name, exit direction name and new location id for all exits from the current location. Set the $exitdescname, $exitdirectionname and $newlocationid variables to the values extracted from the rows of the table.

Hint 1: Check the previous question and the example at the top of the file to see how to work out the current location.

Hint 6: The code works by creating an HTML form in each table row. The action will make it display the current page with the correct “?newloc=<locid>” suffix. Try it by pressing the button to see what it does.

Question 17: This is very similar to Question 16, but you need to create a list of items at the location instead of exits for the location. Create an SQL query in the $query string, to determine the item description name and item ID for every item at the current location. Again, store the item
description for each row in the $itemdesc variable and the item id in the $itemID variable.

Find the text near the top of the file which is labelled “QUESTION 19”.

Again you need to set the $query to change the database appropriately.

value of the “dropitem” variable to be the same value as the current location number. The answer to this question is VERY similar to the answer for Q19.