CS 418/518 - Web Programming Spring 2014: Tues/Thurs 9:30-10:45am, E&CS 2120

Staff

• Dr. Michele Weigle
• mweigle at cs.odu.edu
• E&CS 3214
• Office Hours:
MW 9:30-11am
• TA: Sawood Alam
salam at cs.odu.edu

# Lab 1 - MySQL

January 23, 2014

## Intro

• You are welcome to work in groups or alone on these exercises.
• These exercises are just for your practice -- they will not graded.
• You may use the command line version of MySQL on weiglevm or the phpmyadmin interface at https://weiglevm.cs.odu.edu/phpmyadmin.

## Setup

Create a new directory `~/cs418_html/labs` for your lab exercises and set the permissions so that it the web server (anyone) can access it (`chmod 755 ~/cs418_html/labs/`).

## Lab Exercises

### 1) Table Normalization

We have a database with two tables.

The second table (orders) shows customer orders for different items with the quantity and price for each item.

Go through the data normalization process to improve this database. Write out your new database structure on paper and discuss it with another student (if working alone) or group (if working in a group).

### 2) Store Inventory

In your MySQL database on weiglevm, create a table named `lab1_store` with the following contents:

``` id	name	qty	price
1	apple	10	1
2	pear	5	2
3	banana	10	1.5
4	lemon	100	0.1
5	orange	50	0.2
```

Using SELECT statements, complete the following tasks:

1. List all the items sorted alphabetically.
2. List only the first 3 items.
3. List only the last 3 items.
4. List only the items that are more than \$1 per unit price
5. List all the items with their extended price (quantity * price)
6. List the total cost of all the items in the store
7. List the total number of items we have in the store.

First check your SELECT statements using the MySQL command line or the phpmyadmin interface. Then, write a web page named `lab1-2.php` that outputs the results of each query.

### 3) Courses

Create the following tables:

`lab1_courses`

``` id	name	dept_id
1	111	1
2	112	1
3	250	1
4	231	1
5	111	2
6	250	3
7	111	4
```

`lab1_dept`

``` id	name
1	CSC
2	MTH
3	EGR
4	CHM
```

`lab1_enrollment`

``` course_id	count
1	        40
2	        15
3	        10
4	        12
5	        60
6	        14
7	        200
```

First check your SELECT statements using the MySQL command line or the phpmyadmin interface. Then, write a web page named `lab1-3.php` that outputs the results of each query.