In this problem, you need to create views and triggers given the relational schema about the grocery purchase database given below. You do not need to consider returns or exchange of items in this grocery, and we do not store any information about the customers.

Note: Please answer the following questions within two days

The following Bold attributes are the Primary Keys of their respective tables. Here are the tables:

Item (iid, item_name, category, price, origin, description)

Purchase (pid, p_timestamp, total_amount, payment_method, card_number, status)

Purchase_items (pid, iid, quantity, total_price)

Some explanation for the schema:

For each item, we have a unique id, the item name, the category of the item (e.g. food, furniture, snack...), the price (e.g. $3 for each pound of pork, $10 for one chair), the origin country, and some description of this item. For each purchase, we record the unique purchase id, when this purchase happened, the total amount of the purchase including tax, the payment_method (e.g. credit card, debit, cash), the card number if using a credit or debit card, and the status (e.g. fail, pending, finished). For each purchase, we also need to record which and how many items are included in this purchase. To do so, we record the purchase id, the item id, the count of items purchased, and the price of all items in another table Purchase_items.

(a) Define a view that stores, for each id of item, the item id, the date, and the total count of items purchased on that date.

(b) Using this view, output for each month in 2020, the id of the item that was purchased the most during that month (if you buy the same item five times in one purchase, it should be treated as 5, not 1).

(c) Write a trigger that rejects any insertion of a new purchase with a credit card for which there is either a failed or at least 3 pending purchases in the database.

(d) Create a new table star_item_each_day, containing the attributes date, iid, item name, and total purchase value, for the item(s) that had the highest sales (in $) on that date. Write a query to create and initialize this table given the current database. Then write a trigger that keeps this table up to date as new measurements are inserted into the database. If there is a tie between several items on a given day, you need to output several iids and item_names. Also, keep in mind that when a new day arrives, you may have to insert a new record into the table on the first purchase that happens.