CSCI 5333.4 DBMS
Solution to Homework #8

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

(1) No, it is not in 1NF. This is because the attribute "special_features" is not atomic. It is multi-valued. For example, the value can be:

"Trailers, Commentaries, Deleted Scenes, Behind the Scenes"

Note that it is specified that the values of special_features "can be zero or more of: Trailers, Commentaries, Deleted Scenes, Behind the Scenes." Thus, the followings are possible redesign to remove the violation of 1NF.

(a) Remove the attribute special_features from the relation file. Create four field of type Boolean (i.e. TINYINT(1)) with default value of 0: Trailers, Commentaries, Deleted_Scenes, Behind_the_Scenes.

Advantages: no decomposition and thus no need of additional JOIN for queries.
Disadvantages: SQL statements may be more complicated for certain queries. Less flexible for addition of possible special feature values.

(b) Remove the attribute special_features from the relation file. Create two tables to store the many to many relations between films and special features.

(i) special_feature(feature_id, feature, last_update)

primary key: feature_id
candiate keys: (1) feature_id and (2) feature

(ii) film_feature(film_id, feature_id, last_update)

primary key: film_id, feature_id
candidate key: film_id, feature_id
foreign keys: (1) film_id references file(film_id) and (2) feature_id references special_feature(feature_id)

Note that the attribute last_update is added to conform to the design of exsiting tables.

Advarntages: flexible for extension.
Disadvantages: decomposition may mean slower and more complicated queries

(2) For example:

DROP VIEW category_film_count;

CREATE VIEW category_film_count
AS
SELECT
   cat.name AS category,
   count(DISTINCT fc.film_id) AS "Number of distinct films",
   count(i.inventory_id) AS "Total copies of films"
FROM category AS cat
   LEFT JOIN film_category AS fc ON cat.category_id = fc.category_id
   JOIN inventory AS i ON fc.film_id = i.film_id
GROUP BY category;

(3) For example:

DROP PROCEDURE rewards_report_month;

delimiter //

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
)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT 'Provides a customizable report on best customers'
proc: BEGIN

    DECLARE month_start DATE;
    DECLARE month_end DATE;

    /* Some sanity checks... */
    IF min_monthly_purchases = 0 THEN
      -- Return a table that can then be used. 
        SELECT 'Minimum monthly purchases parameter must be > 0';
        LEAVE proc;
    END IF;
    IF min_dollar_amount_purchased = 0.00 THEN
        SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00';
        LEAVE proc;
    END IF;

    /* Determine start and end time periods */
    SET month_start = STR_TO_DATE(CONCAT_WS('-',year,month,1),'%Y-%m-%d');
    SET month_end = LAST_DAY(month_start);

    /*
        Create a temporary storage area for
        Customer IDs.
    */
    CREATE TEMPORARY TABLE tmpCustomer (
      customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
      number_purchased SMALLINT UNSIGNED,
      total_amount_purchased DECIMAL(10,2) UNSIGNED);

    /*
        Find all customers meeting the
        monthly purchase requirements
    */
  
   --  May modify to add number of purchases and total amount as output.
    INSERT INTO tmpCustomer (customer_id, number_purchased, total_amount_purchased)

    SELECT p.customer_id, COUNT(customer_id), SUM(p.amount)
    FROM payment AS p
    WHERE DATE(p.payment_date) BETWEEN month_start AND month_end
    GROUP BY customer_id
    HAVING SUM(p.amount) > min_dollar_amount_purchased
    AND COUNT(customer_id) > min_monthly_purchases;

    /* Populate OUT parameter with count of found customers */
    SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;

    /*
        Output
    */
  
   SELECT * from tmpCustomer;

    /* Clean up */
    DROP TABLE tmpCustomer;
END //

delimiter ;