CSCI 5333.3 DBMS
Homework #4

Due date: 3/19 (Monday)

(1) SQL

This question is based on the Supply database we discussed in the class (with the relations Supplier, Part and Supply).

A drastically simplified database:

Supplier(SNum, SName, SCity, Status)
Part(PNum, PName, Color, Weight)
Supply(SNum, PNum, Quantity)

An example of relation instance:
Supplier:

Part:

Supply:

Install MySQL and prepare SQL CREATE TABLE statements and data files (SUPPLIER.DAT, SUPPLY.DAT and PART.DAT) to populate MySQL using the instance for the supply database above. For example, you should be able to populate the Supplier table by the following SQL statement:

LOAD DATA LOCAL INFILE "supplier.dat"
INTO TABLE SUPPLIER
FIELDS ENCLOSED BY "\"" TERMINATED BY ","
LINES TERMINATED BY '\r\n'
;

(2) Provide the SQL statements to implement the following queries. They must be tested in MySQL.

(a) List all city names with a supplier supplying a green part.

+---------+
| SCity   |
+---------+
| Dallas  |
| Houston |
| Phoenix |
+---------+
 

(b) List the supplier numbers of all suppliers that more than three counts of any green part.

+------+
| SNum |
+------+
| S1   |
| S2   |
| S5   |
+------+
 

(c) List the part number of parts supplied by every supplier in Houston.

+------+
| PNum |
+------+
| P4   |
+------+
 

(d) List the names of parts with a supplier with status greater than 4.

+-------------+
| PName       |
+-------------+
| Drum        |
| Hammer      |
| Micropod    |
| Blue Spur   |
| Musical Box |
| Bear        |
| Minipod     |
+-------------+
 

(e) List the part information supplied by suppliers in Houston in the following exact format:

+------------------+-------------+-------+----------+
| Houston Supplier | Part        | Color | Quantity |
+------------------+-------------+-------+----------+
| DEF              | Drum        | Green |       11 |
| DEF              | Hammer      | Green |        1 |
| DEF              | Micropod    | Red   |        6 |
| Go go            | Micropod    | Red   |        1 |
| Go go            | Blue Spur   | Blue  |        2 |
| Go go            | Musical Box | Blue  |       12 |
| Go go            | Bear        | Blue  |        5 |
+------------------+-------------+-------+----------+
 

(f) List the average weight of the parts.

+----------------------+
| Total Average Weight |
+----------------------+
|               9.1250 |
+----------------------+

(g) List the number of parts supplied by every supplier in the following exact format. Note the row for SName 'Yue'.

+-------+--------------------------+
| SName | Number of Parts Supplied |
+-------+--------------------------+
| ABC   |                        2 |
| DEF   |                        3 |
| Go go |                        4 |
| P&G   |                        4 |
| Yap   |                        4 |
| Yue   |                        0 |
+-------+--------------------------+

(h) This question tests your skill for searching solution for taught in class. Tips: use MySQL's regular expression. It is artificial (and not easy if you do not know regular expressions.)

List all information of all suppliers whose names are composed only of alphabets and white spaces. Output example below (note that 'P&G' is not in the answer.)

+------+-------+---------+--------+
| SNUM | SNAME | SCITY   | STATUS |
+------+-------+---------+--------+
| S1   | ABC   | Dallas  |     10 |
| S2   | DEF   | Houston |     20 |
| S3   | Go go | Houston |     12 |
| S5   | Yap   | Phoenix |      5 |
| S6   | Yue   | Dallas  |      1 |
+------+-------+---------+--------+
 

Email the TA a file <<Yourname>>_h4_sql.txt containing all SQL statements. Also turn in <<Yourname>>_Supplier.dat, <<Yourname>>_Supply.dat and <<Yourname>>_part.dat.