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 - SOLUTIONS

January 23, 2014

There are likely several ways that these tasks could be done, but here are my solutions.

### 1) Table Normalization

Here are some of the problems with the table:

• Wasted space: Some customers did not order a third item, so we're wasting space on those records. This is not a big deal with four records, but with 200 or more, it can build up.
• Data insertion problems: What if a customer comes along and she wants to order a fourth item?
• Redundant data entry. What if the price of item #34 goes up to \$1200? We'd have to find each record with item 34 in one of its three item columns, and change the price.
• Querying problems: What if I want to find all customers that ordered item #34? I'd have to query all three item columns.
• Redundant data storage: If 6 customers order item #34, we have to enter both the item and the price each time.
• Inconsistent data: Notice that there are conflicting prices for item #21? Should it be \$740 or \$750? This happens when one record is updated and another isn't.

The customer table is fine and doesn't need to change.

customer

 Customer Number Name Address Phone 23 Fred Yo 123 Main 772-8831 43 Mike Boo 321 West 772-9813 65 Ed Tibbs 222 East 772-8163

Step 1 - Remove the recurring items from the table, that is, item, count and price (1NF). Order size and total amount don't make sense here, so remove as well.

orders

 Order Number Customer Number Item Count Price 121 65 34 418 1100 121 65 15 344 900 122 12 42 3 4000 122 12 21 51 750 123 23 15 552 900 123 23 42 4 4000 123 23 34 65 1100

Step 2 - create separate tables for duplicated data, so split out item and price into its own table and split order number and customer number into own table

item

 Item Number Price 15 900 34 1100 42 4000

order-customer

 Order Number Customer Number 121 65 122 12 123 23 124 23

orders

 Order Number Item Count 121 34 418 121 15 344 122 42 3 122 21 51 123 15 552 123 42 4 123 34 65

The order size and total amount can be calculated from these tables.

### 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.

``` SELECT *
FROM lab1_store
ORDER BY name
```

2. List only the first 3 items.

``` SELECT *
FROM lab1_store
LIMIT 3
```

3. List only the last 3 items.

``` SELECT *
FROM lab1_store
LIMIT 2,3
```

4. List only the items that are more than \$1 per unit price

``` SELECT *
FROM lab1_store
WHERE Price > 1
```

5. List all the items with their extended price (quantity * price)

``` SELECT *, (qty * price) AS extended_price
FROM lab1_store
```

6. List the total cost of all the items in the store

``` SELECT SUM(qty*price) AS total_cost
FROM lab1_store
```

7. List the total number of items we have in the store.

``` SELECT SUM(qty) AS num_items
FROM lab1_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
```

1. List all the CS classes.

``` SELECT *, lab1_courses.name as course
FROM lab1_courses
JOIN lab1_dept
ON dept_id = lab1_dept.id
WHERE lab1_dept.name = "CSC"
```

2. What is the total enrollment over all the classes?

``` SELECT SUM(count) AS total_enrollment
FROM lab1_enrollment
```

3. How many different classes are taught?

``` SELECT COUNT(id) AS num_classes
FROM lab1_courses
```

4. How many different departments are there?

``` SELECT COUNT(id) AS num_depts
FROM lab1_dept
```

5. List all the classes in the database, with the department name and the class name on the same line, e.g. CSC 111, CSC 112, ..., EGR 250, ... CHM 111

``` SELECT lab1_dept.name as dept_name, lab1_courses.name
FROM lab1_courses
JOIN lab1_dept
ON dept_id = lab1_dept.id
```

6. List the name of the CS classes so that they are output as "CSC111", "CSC112", etc... (in other words, concatenate department with class number.)

``` SELECT CONCAT(lab1_dept.name, lab1_courses.name) as course_name
FROM lab1_courses
JOIN lab1_dept
ON dept_id = lab1_dept.id
WHERE lab1_dept.name = "CSC"
```

7. List all the information in the database, where each class appears on 1 line, along with its department, and its enrollment.

``` SELECT *, lab1_courses.name as course_name
FROM lab1_courses
JOIN (lab1_dept, lab1_enrollment)
ON (lab1_courses.dept_id = lab1_dept.id AND lab1_courses.id = lab1_enrollment.course_id)
```

Nicely formatted (w/o ids)

``` SELECT lab1_dept.name as dept_name, lab1_courses.name, lab1_enrollment.count
FROM lab1_courses
JOIN (lab1_dept, lab1_enrollment)
ON (lab1_courses.dept_id = lab1_dept.id AND lab1_courses.id = lab1_enrollment.course_id)
```

8. List all the information as formatted in Question 7, but only the classes with an enrollment larger than 50.

``` SELECT *, lab1_courses.name as course_name
FROM lab1_courses
JOIN (lab1_dept, lab1_enrollment)
ON (lab1_courses.dept_id = lab1_dept.id AND lab1_courses.id = lab1_enrollment.course_id)
WHERE lab1_enrollment.count > 50
```

Nicely formatted (w/o ids)

``` SELECT lab1_dept.name as dept_name, lab1_courses.name, lab1_enrollment.count
FROM lab1_courses
JOIN (lab1_dept, lab1_enrollment)
ON (lab1_courses.dept_id = lab1_dept.id AND lab1_courses.id = lab1_enrollment.course_id)
WHERE lab1_enrollment.count > 50
```

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.