CSCI 5333.4 DBMS
Homework #8

Due date: 11/28 at the beginning of the class.

The following questions refer to the Sakila database that comes preloaded with MySQL installation. Refer to the documentation at: http://dev.mysql.com/doc/sakila/en/sakila.html. Download the database at downloads.mysql.com/docs/sakila-db.zip to review the SQL statements for declaring and populating the database. (Note that XAMPP does not include Sakila and you will need to install it yourself.)

(1) Consider the table film of the Sakila database:

http://dev.mysql.com/doc/sakila/en/sakila.html#sakila-structure-tables-film

Is the table in first normal form? Why? If it is not in 1NF, describe a redesign to remove the violation of 1NF.

(2) Define the view category_film_count to show the category names, number of inventoried films and the total number of copies of films in the catelgories.

For example:

mysql> SELECT * from category_film_count;
+-------------+--------------------------+-----------------------+
| category    | Number of distinct films | Total copies of films |
+-------------+--------------------------+-----------------------+
| Action      |                       61 |                   312 |
| Animation   |                       64 |                   335 |
| Children    |                       58 |                   269 |
| Classics    |                       54 |                   270 |
| Comedy      |                       56 |                   269 |
| Documentary |                       63 |                   294 |
| Drama       |                       61 |                   300 |
| Family      |                       67 |                   310 |
| Foreign     |                       67 |                   300 |
| Games       |                       58 |                   276 |
| Horror      |                       53 |                   248 |
| Music       |                       51 |                   232 |
| New         |                       60 |                   275 |
| Sci-Fi      |                       59 |                   312 |
| Sports      |                       73 |                   344 |
| Travel      |                       53 |                   235 |
+-------------+--------------------------+-----------------------+
16 rows in set (0.01 sec)
 

(3) Write the stored procedure rewards_report_month. Its prototype is:

CREATE PROCEDURE rewards_report_month (
    IN min_monthly_purchases TINYINT UNSIGNED
    , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
    , IN year INT(4) UNSIGNED
    , IN month INT(2) UNSIGNED
    , OUT count_rewardees INT
)

This is modeled on the stored procedure rewards_report in Sakila. rewards_report provides the rewards report of the last month by listing all customer information that satisfy the report criteria of min_dollar_amount_purchased and min_dollar_amount_purchased. On the other hand, rewards_report_month provides the report for a specific month and year. Also, the ouptut will only be the customer_id with the total number of purchases and the amount.

The following session explains the required input and output.

mysql> set @count_rewardees = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> call rewards_report_month(7,30.00,2005,6,@count_rewardees);
+-------------+-----------------+------------------------+
| customer_id | number_purchase | total_amount_purchased |
+-------------+-----------------+------------------------+
|          27 |               8 |                  30.92 |
|          31 |              11 |                  35.89 |
|         176 |               8 |                  42.92 |
|         178 |               8 |                  44.92 |
|         196 |               8 |                  31.92 |
|         197 |               8 |                  31.92 |
|         213 |               9 |                  35.91 |
|         234 |               8 |                  36.92 |
|         236 |               8 |                  37.92 |
|         260 |               8 |                  34.92 |
|         267 |               9 |                  38.91 |
|         295 |               9 |                  32.91 |
|         340 |               8 |                  35.92 |
|         454 |              10 |                  52.90 |
|         457 |               9 |                  30.91 |
|         510 |               8 |                  33.92 |
|         526 |               9 |                  41.91 |
|         561 |               9 |                  33.91 |
+-------------+-----------------+------------------------+
18 rows in set (0.07 sec)

Query OK, 0 rows affected (0.10 sec)

mysql> select @count_rewardees;
+------------------+
| @count_rewardees |
+------------------+
|               18 |
+------------------+
1 row in set (0.00 sec)

mysql>