CS 418 — Web Programming | Lab1-MySQL

CS418-S13.Lab1-MySQL History

Hide minor edits - Show changes to output

January 30, 2013, at 08:27 PM by scott -

Changed lines 19-24 from:

'''Customers''' \\
Attach:lab1-~~customers.png~~

'''~~Orders~~''' \\
Attach:~~lab1~~-~~orders.png~~

to:

(:div style='text-align:justify; padding-left:1em; padding-bottom:5px;':)
'''Customers'''

(:table align=left:)
(:head:) Customer Number
(:head:) Name
(:head:) Address
(:head:) Phone
(:cellnr:) 23
(:cell:) Fred Yo
(:cell:) 123 Main
(:cell:) 772-8831
(:cellnr:) 43
(:cell:) Mike Boo
(:cell:) 321 West
(:cell:) 772-9813
(:cellnr:) 65
(:cell:) Ed Tibbs
(:cell:) 222 East
(:cell:) 772-8163
(:tableend:)

(:divend:)
[[<<]]

(:div style='text-align:justify; padding-left:1em; padding-bottom:5px;':)
'''Orders'''

(:table align=left:)
(:head:) Order Number
(:head:) Customer Number
(:head:) Item
(:head:) Count
(:head:) Price
(:head:) Item
(:head:) Count
(:head:) Price
(:head:) Item
(:head:) Count
(:head:) Price
(:head:) Order Size
(:head:) Total Amount
(:cellnr:) 121
(:cell:) 65
(:cell:) 34
(:cell:) 418
(:cell:) 1100
(:cell:) 15
(:cell:) 344
(:cell:) 900
(:cell:)
(:cell:)
(:cell:)
(:cell:) 762
(:cell:) 769400
(:cellnr:) 122
(:cell:) 12
(:cell:) 42
(:cell:) 3
(:cell:) 4000
(:cell:) 21
(:cell:) 51
(:cell:) 750
(:cell:)
(:cell:)
(:cell:)
(:cell:) 54
(:cell:) 50250
(:cellnr:) 123
(:cell:) 23
(:cell:) 15
(:cell:) 552
(:cell:) 900
(:cell:) 42
(:cell:) 4
(:cell:) 4000
(:cell:) 34
(:cell:) 65
(:cell:) 1100
(:cell:) 621
(:cell:) 584300
(:cellnr:) 124
(:cell:) 23
(:cell:) 15
(:cell:) 772
(:cell:) 900
(:cell:) 34
(:cell:) 45
(:cell:) 1100
(:cell:) 21
(:cell:) 54
(:cell:) 740
(:cell:) 871
(:cell:) 784260
(:tableend:)

(:divend:)
[[<<]]

Changed lines 124-258 from:

to:

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

(:div style='text-align:justify; float:left; width:10em; padding-right:1em; padding-left:1em; padding-bottom:5px;':)
'''lab1_store'''

(:table align=left:)
(:head:) id
(:head:) name
(:head:) qty
(:head:) price
(:cellnr:) 1
(:cell:) apple
(:cell:) 10
(:cell:) 1
(:cellnr:) 2
(:cell:) pear
(:cell:) 5
(:cell:) 2
(:cellnr:) 3
(:cell:) banana
(:cell:) 10
(:cell:) 1.5
(:cellnr:) 4
(:cell:) lemon
(:cell:) 100
(:cell:) 0.1
(:cellnr:) 5
(:cell:) orange
(:cell:) 10
(:cell:) 0.2
(:tableend:)

(:divend:)
[[<<]]

Using SELECT statements, complete the following tasks:

# List all the items sorted alphabetically.
# List only the first 3 items.
# List only the last 3 items.
# List only the items that are more than 1perunitprice
# List all the items with their extended price (quantity * price)
# List the total cost of all the items in the store
# 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:

(:div style='text-align:justify; float:left; width:10em; padding-right:1em; padding-left:1em; padding-bottom:5px;':)
'''lab1_courses'''

(:table align=left:)
(:head:) id
(:head:) name
(:head:) dept_id
(:cellnr:) 1
(:cell:) 111
(:cell:) 1
(:cellnr:) 2
(:cell:) 112
(:cell:) 1
(:cellnr:) 3
(:cell:) 250
(:cell:) 1
(:cellnr:) 4
(:cell:) 231
(:cell:) 1
(:cellnr:) 5
(:cell:) 111
(:cell:) 2
(:cellnr:) 6
(:cell:) 250
(:cell:) 3
(:cellnr:) 7
(:cell:) 111
(:cell:) 4
(:tableend:)

(:divend:)
(:div style='text-align:justify; float:left; width:10em; padding-right:1em; padding-bottom:5px;':)
'''lab1_dept'''

(:table align=left:)
(:head:) course_id
(:head:) count
(:cellnr:) 1
(:cell:) CSC
(:cellnr:) 2
(:cell:) MTH
(:cellnr:) 3
(:cell:) EGR
(:cellnr:) 4
(:cell:) CHM
(:tableend:)

(:divend:)
(:div style='text-align:justify; float:left; width:10em; padding-right:1em; padding-bottom:5px;':)
'''lab1_enrollment'''

(:table align=left:)
(:head:) course_id
(:head:) count
(:cellnr:) 1
(:cell:) 40
(:cellnr:) 2
(:cell:) 15
(:cellnr:) 3
(:cell:) 10
(:cellnr:) 4
(:cell:) 12
(:cellnr:) 5
(:cell:) 60
(:cellnr:) 6
(:cell:) 14
(:cellnr:) 7
(:cell:) 200
(:tableend:)

(:divend:)
[[<<]]

Using SELECT statements, answer the following questions/tasks:

# List all the CS classes.
# What is the total enrollment over all the classes?
# How many different classes are taught?
# How many different departments are there?
# 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
# List the name of the CS classes so that they are output as "CSC111", "CSC112", etc... (in other words, concatenate department with class number.)
# List all the information in the database, where each class appears on 1 line, along with its department, and its enrollment.
# 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.

Restore

January 30, 2013, at 07:49 PM by scott -

Restore

January 30, 2013, at 07:44 PM by scott -

Restore

January 30, 2013, at 07:44 PM by scott -

Added lines 1-32:

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''' \\
Attach:lab1-customers.png

'''Orders''' \\
Attach:lab1-orders.png

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

!!!! Store Inventory

Restore