Search:

Announcements

Instructor

Schedule

Syllabus

Useful Links

Lab1-MySQL

January 31, 2013

Introduction

  • 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 sainsworth418 or the phpmyadmin interface at https://sainsworth418.cs.odu.edu/phpmyadmin.

Setup

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

Exercises

Normalization

Given a database with two tables:

Customers

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


Orders

Order Number Customer Number Item Count Price Item Count Price Item Count Price Order Size Total Amount
121 65 34 418 1100 15 344 900 762 769400
122 12 42 3 4000 21 51 750 54 50250
123 23 15 552 900 42 4 4000 34 65 1100 621 584300
124 23 15 772 900 34 45 1100 21 54 740 871 784260


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

  1. 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).

Store Inventory

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

lab1_store

id name qty price
1 apple 10 1
2 pear 5 2
3 banana 10 1.5
4 lemon 100 0.1
5 orange 10 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 1perunitprice
  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.

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

course_id count
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.

History Print Recent Changes Search

Page last modified on January 30, 2013, at 08:27 PM