Chapter AE, Problem AE9.2

Explanation of Solution

a.

Create an Access Database with a table named Order_Data:

The table Order_Data contains the following columns: Order_Number, ItemName, Quantity with the respective data types Number (LongInteger), Shorttext(50) and Number(LongInteger)...

Explanation of Solution

b.

Import the data from excel file ch09Ex02_U10e.xlsx into the Order_Data table:

The excel file ch09Ex02 contains attributes Order_Number, ItemName, and Quantity as attributes...

Explanation of Solution

c.

Run the query to perform market basket analysis:

The query is:

SELECTT1.ItemName as FirstItem,

T2.ItemName as SecondItem

FROMOrder_Data T1, Order_Data T2

WHERET1.OrderNumber = T2.OrderNumber

ANDT1.ItemName <> T2.ItemName;

In order to run the query, one need to click the red exclamation point in the toolbar and the query is saved under the name TwoItemBasket...

Explanation of Solution

d.

Enter another SQL Statement by clicking CREATE/Query Design:

The query is:

SELECTTwoItemBasket.FirstItem,

TwoItemBasket.SecondItem,

Count(*)AS SupportCount

FROMTwoItemBasket

GROUP BYTwoItemBasket.FirstItem,

TwoItemBasket.SecondItem;

First click on CREATE/ Query Design and when the Show Table dialog box appeared one needs to click close. One needs to select SQL View by right clicking in the gray section over the grid in window and enter the SQL statement...

Explanation of Solution

e.

By examining the two queries, verify whether the two query statements have correctly calculated the number of times the two items have appeared together.

Here the first query provides list of items bought together and the second query provides count of two items purchased together.  Hence, second query can be used as a base to develop another query which in turn will provide more insight for order analysis...

Explanation of Solution

f.

Explain calculations one need to make to compute Lift:

• Lift is the probability of purchasing item A based on the base probability that item B is pur...

Explanation of Solution

g.

Explain the role of query in part C and part D:

• A table that consists of every pair of items in an order is created by the Query in part C...