Jyotishka RaySpring 2014Due Date: April 30, 2014

University of Texas at Dallas

MIS 4300 / ACCT 4300 Database Fundamentals Spring 2014

Homework Assignment Four

In this assignment youare required to use PHP program to connect to the XAMPP web server. You will then create a database which will include tables with records.

Connect to the web server using PHP program.

  1. Download XAMPP application from or any other authorized website.
  2. Run the web server from xampp-control application. You need to start the Apache and MySQL application in the control panel.
  3. Click the Admin tab to open the web server.
  4. To view the databases, click the phpMyAdminlink under Tools. This will show all the existing databases and tables in the server.
  5. To connect to MySQL use the template testmysql.php file already provided to you. The mysql_connect function uses the shortcut ‘localhost’ and the username ‘root’ to connect. There is no password. Save the file using your last name with a phpextension inside the ‘htdocs’ folder.You can use any text editor to write the php program. To run the php file open a web browser. Write the url to run the file.

Create a database and tables.

  1. Use mysql_queryfunction in the php file to create a database named ‘Assignment4’ in the web server. It is always preferred to use ‘IF NOT EXISTS’ keywords while creating a database and a table. All the queries should be inside a quotation mark and should end with a semicolon.
  2. Use mysql_select_db function to connect to the database ‘Assignment4’.
  3. Create a table named ‘Inventory’ in the database ‘Assignment4’ with following fields:

Inventory

INV_ID (PK) / ITEM_ID / ITEM_QTY / VEND_ID / LOC_ID / STORE_QTY / LOC_DESCRIPTION

INV_ID is the primary key and should not be NULL. Use AUTO_INCREMENT for the primary key. Both VEND_ID and LOC_DESCRIPTION should be character type while other fields are integer type.

Insert multiple rows in the table.

  1. To insert multiple rows use the following query (see lecture 8, slide 22):

INSERT INTO table_name (column1, column2) VALUES

(R1C1, R1C2), (R2C1, R2C2),…;

Populate the Inventory table with the following datalines:

2,6,'P14',1,17,'WAREHOUSE1'

3,9,'D07',1,7,'WAREHOUSE1'

4,6,'D08',1,5,'WAREHOUSE1'

7,10,'P11',1,14,'WAREHOUSE1'

4,6,'D08',2,13,'WAREHOUSE2'

5,5,'D09',2,23,'WAREHOUSE2'

10,6,'D06',2,18,'WAREHOUSE2'

5,5,'D09',3,8,'WAREHOUSE3'

8,10,'P12',3,13,'WAREHOUSE3'

9,9,'P13',3,6,'WAREHOUSE3'

11,8,'D07',3,5,'WAREHOUSE3'

1,2,'P13',4,5,'WAREHOUSE4'

3,9,'D07',4,20,'WAREHOUSE4'

6,4,'P10',4,11,'WAREHOUSE4'

11,8,'D07',4,6,'WAREHOUSE4'

12,8,'P12',4,9,'WAREHOUSE4'

Create more tables.

  1. Use the Inventory table to create the following three tables.

Item

ITEM_ID (PK) / ITEM_QTY / VEND_ID

Storage

ITEM_ID (PK) / LOC_ID (PK) / STORE_QTY

Location

LOC_ID (PK) / LOC_DESCRIPTION
  1. Use foreign keys to link the above three tables. There is no need to link these tables with the Inventory table.
  2. Insert values in the above tables from the Inventory table. Use the DISTINCT keyword if necessary to reduce redundancy while inserting the values. Both Inventory and Storage tables should have 16 rows. Item table should have 12 rows and Location table should have 4 rows.

All the above queries should be written in the php file using mysql_queryfunction and saved as your last name. Check the web server to see if all the tables are populated as desired. You can check the relationships among the tables by clicking the Designer tab inside the database.

Write queries.

Open the SQL tab in the Assignment4 database for writing queries. Use the tables Item, Storage and Location in the queries as required. Do not use the Inventory table for any of the queries. You can run the query by clicking the ‘Go’ tab at the bottom right corner of the query window.

  1. List the item IDs and their location description of the items whose item quantity is more than their storage quantity. (Query should return 5 rows)
  1. Suppose warehouse 2 and 4 charges $1.2 per item for all the items with store quantity more than 15. For example, ITEM_ID = 10 has STORE_QTY = 18 in the warehouse 2, hence the total storage cost for this item is 18*1.2 = 21.6. List all such items in the warehouse 2 and 4 along with their total storage cost. Name the cost as TOTCOST. (Query should return 3 rows)

Save the queries and the results in a Word document.Use your last name as the file name.

Note:

The submission should be a PHP file and a Word document. Use your last name as the files name.The PHP file should be able to connect to the web server, create the database and the tables, and insert the values in all the tables when run. The Word document should have the two queries and the results obtained from each query. This assignment is due by 11:00 pm on April 30, 2014.