From CS 418/518 Fall 2012

CS418-F12: Lab 1 - MySQL

September 6, 2012

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 mweigle418 or the phpmyadmin interface at https://mweigle418.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

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

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 mweigle418, 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

Using SELECT statements, answer the following questions/tasks:

1. List all the CS classes.
2. What is the total enrollment over all the classes?
3. How many different classes are taught?
4. How many different departments are there?
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
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.)
7. List all the information in the database, where each class appears on 1 line, along with its department, and its enrollment.
8. List all the information as formatted in Question 7, but only the classes with an enrollment larger than 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.