CSCI 5333.4 DBMS
Homework #4

Due date: 10/12

(1) Assume that you have installed MySQL in Windows and is running mysql monitor.

mysql>

Give the command to enable logging into the output file c:\users\li\mysql_out_1.txt.

(Note: logging is a great tool for learning and debugging.)

For questions 2 to 3, Turn in a text file "H4a_Yourname.txt" so your TA can test run it easily. Use the following format:

-- H4 Q2
answer of question 2 follows here.

-- H4 Q3
answer of question 3 follows here.

At the end of the file, add a houseig keeping section to drop all tables you have created.

-- House cleaning
SQL statements for dropping all tables here.

Email the file to the TA with the heading "H4 solution: Your name".

(2) Create the following table in MySQL if it does not already exist:

H4_employee(EmployeeID, Firstname, Lastname, Address, Status)

The data types of the fields are:

• EmployeeID varchar(7)
• Firstname varchar(20)
• Lastname varchar(20)
• Address varchar(100)
• Status integer(2)

The following constraints should be applied:

• Every row must have values in Firstname and Lastname.
• Status should have a default value of 1.
• A B-Tree index should be built on Firstname and Lastname.

Insert a record with EmployeeID '1000000', Sharon Hall, Address 'Somewhere in Texas' and default status into the table.

(3) Create the following table in MySQL if it does not already exist:

H4_department(DeptCode, DeptName, Office, HeadId)

• DeptCode: varchar(4)
• DeptName: varchar(20), must be unique
• Office: varchar(50), must not be null
• HeadId is a foreign referencing EmployeeID in H4_employee.

Use a single SQL statement to insert two rows into the table:

('1000','ASTR','Delta 110',NULL)
('2000','CSCI','Delta 120','1000000')

The following questions are 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)

"H4b_Yourname.txt" so your TA can test run it easily. For each question, use the following format:

-- H4 Q4
answer of question 2 follows here.
and so on.

Email the file to the TA together with H4a_Yourname.txt.

(4) List the supplier name, part name, part weight and quantity for all Blue part. The output format must exactly be:

+----------+------------+--------+--------+
| Supplier | Blue Part  | Weight | Number |
+----------+------------+--------+--------+
| Go go    | Blue Spur  | 3      | 2      |
| P&G      | Blue Spur  | 3      | 10     |
| Yap      | Blue Spur  | 3      | 14     |
+----------+------------+--------+--------+

(5) Show all supplier names that supply more than two parts.

(6) For each active supplier, show the supplier names, the number of parts it supplied and the average weight of these parts. The output format must exactly be:

+----------+------------------+---------------------+
| Supplier | # Parts supplied | Average part weight |
+----------+------------------+---------------------+
| ABC      | 2                | 15.0000             |
| DEF      | 3                | 11.3333             |
| Go go    | 2                | 3.5000              |
| P&G      | 2                | 11.5000             |
| Yap      | 4                | 5.2500              |
+----------+------------------+---------------------+

(7) 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 supplier information that are in a city whose name is composed of the characters a, d, l and s only. Output example:

+------+-------+--------+--------+
| SNUM | SNAME | SCITY  | STATUS |
+------+-------+--------+--------+
| S1   | ABC   | Dallas | 10     |
| S4   | P&G   | Dallas | 2      |
+------+-------+--------+--------+

(8) List the SNUM of supplier that supplies a part that is not Green. The output format must exactly be:

+------+
| SNUM |
+------+
| S2   |
| S3   |
| S4   |
| S5   |
+------+