Assignment 5 provides experience writing PostgreSQL statements to generate input for data mining algorithms used for association rules and classification. All problems use the Inventory data warehouse tables. Each problem has equal value of 10 points. For each problem, you should adapt templates and examples given in the notes. Each problem associates with a similar problem in the class notes. The textbook does not cover this material as it was developed after the last edition of the textbook. I doubt that you will find other sources as it is largely original material.

Problem 3: Association rules of size 2 with evaluation measures (support, confidence, and list) Write an SQL statement with three CTEs and a SELECT statement using the CTEs to generate association rules of size 2 along with evaluation measures of support, confidence, and lift. An association rule indicates the LHS and RHS of a rule. A rule in the result contains a permutation of a combination to generate the LHS and RHS of the rule. Each row should contain the rule text (concatenation of LHS -> RHS), LHS item master key in the rule, RHS item master key in the rule, and evaluation measures (support, confidence, and lift) for the rule. Create three CTEs for the pairs (like statement for problem 1), rules, and counts followed by a SELECT statement using the CTEs. As in problem 1, baskets are identified by a combination of customer vendor key, date key, and branch plant key. Only consider shipments (TransTypeKey = 5). Sort the result by the rule text.

Problem 4: Association rule input as a cross product of baskets and itemsa Write a SELECT statement to generate baskets identified by customer vendor key, date key, and branch plant key. Generate baskets containing two or more items for customers residing in CA, calendar year of 2022, and company key = 1. The result should contain customer vendor key, date key, branch plant key, item master key, and a basket indicator (1 if item is in the basket, 0 otherwise). Order the result by customer vendor key, date key, and branch plant key in ascending order.

Problem 5: Association rule input as a nested list of items in each basket Write a SELECT statement to generate shipment baskets (TransType = 5) with baskets identified by a combination of customer vendor key date key, and branch plant key. Only generate baskets with two or more items. The result should contain customer vendor key, date key, branch plant key, item master key, and an array of item master keys. Order the result by customer vendor key, date key, and branch plant key in ascending order.

Please explain the whole inner Join and ON operations as well