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

Print - Admin

Announcements

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

Schedule

Syllabus

Groups

Useful Links

Lab 1 - MySQL - SOLUTIONS

January 23, 2014

Lab1-MySQL

(Exercises come from http://cs.smith.edu/dftwiki/index.php/CSC220_Exercises_with_MySQL and http://web.archive.org/web/20120808033719/http://code.google.com/edu/tools101/mysql.html)

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 NumberNameAddressPhone
23Fred Yo123 Main772-8831
43Mike Boo321 West772-9813
65Ed Tibbs222 East772-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 NumberCustomer NumberItemCountPrice
12165344181100
1216515344900
122124234000
122122151750
1232315552900
123234244000
1232334651100

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 NumberPrice
15900
341100
424000

order-customer

Order NumberCustomer Number
12165
12212
12323
12423

orders

Order NumberItemCount
12134418
12115344
122423
1222151
12315552
123424
1233465

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

Using SELECT statements, answer the following questions/tasks:

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.