<jupyter_start><jupyter_text>The following is an excerpt from Chapter 5 of Watson's Data Management 6th Edition> Consider the case when items are sold. We can immediately identify two entities: SALE and ITEM. A sale can contain many items, and an item can appear in many sales. We are not saying the same item can be sold many times, but the particular type of item (e.g., a compass) can be sold many times; thus we have a many-to-many (m:m) relationship between SALE and ITEM. When we have an m:m relationship, we create a third entity to link the entities through two 1:m relationships. Usually, it is fairly easy to name this third entity. In this case, this third entity, typically known as an associative entity, is called LINE ITEM. A typical sales form lists the items purchased by a customer. Each of the lines appearing on the order form is generally known in retailing as a line item, which links an item and a sale.The following ERD shows the data schema![ERD](images/sale_erd.png)The file `data/sales_data.xlsx` is an Excel spreadsheet containing three workbooks with data about `sale`, `item`, and `lineitem`. The code below loads the data into Pandas data frames. Use the data frames to answer the following questions:- List the name, quantity, price, and value of items sold on January 16, 2021- List items that were sold on January 16, 2021, or are brown- List items that were sold on January 16, 2021, and are brow- Report all clothing items (type “C”) for which a sale is recorded- Report all clothing items that have not been sold- Find the items that have appeared in all sales<jupyter_code>import pandas as pd
sale = pd.read_excel('data/sales_data.xlsx', sheet_name='sale')
item = pd.read_excel('data/sales_data.xlsx', sheet_name='item') 
lineitem = pd.read_excel('data/sales_data.xlsx', sheet_name='lineitem') 
sale
item
lineitem<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.063264221158958, 'fraction_numerical': 0.011164274322169059, 'mean_word_length': 4.7907692307692304, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 6, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '479575', 'license': 'MIT', 'n_tokens_mistral': 538, 'n_tokens_neox': 496, 'n_words': 286, 'path': '20-problem-begin.ipynb', 'repo_name': 'cmw2011/advanced-data-management-and-analytics-spring2021', 'size': '1881'}