This assignment needs proficiency in MYSQL server. Please only accept the assignment if you are proficient in MYSQL server

Consider the following relation for published books:
BOOK (Book_title, Author_name, Book_type, List_price, Author_affil, Publisher)
Author_affil refers to the affiliation of author. Suppose the following dependencies
exist:
Book_title → Publisher, Book_type
Book_type → List_price
Author_name → Author_affil
*) Is BOOK in 2NF? Explain your answer.
*) Is BOOK in 3NF? Explain your answer.
*) Showing all your work ad all intermediate steps, apply normalization until you cannot decompose the relations further.

 4

*******
SUPPLIER (SUPNR, SUPNAME, SUPADDRESS, SUPCITY, SUPSTATUS)
SUPPLIES (SUPNR, PRODNR, PURCHASE_PRICE, DELIV_PERIOD)
PRODUCT ( PRODNR,PRODNAME, PRODTYPE, AVAILABLE_QUANTITY)
PO_LINE (PONR, PRODNR, QUANTITY)
PURCHASE_ORDER (PONR, PODATE, SUPNR)
*) Select the number of NOT NULL purchase price values in the SUPPLIES table for tuples where the product number equals 222.
*) Retrieve all pairs of suppliers who are located in the same city. Be sure that you do not list the same pair twice. Also, be sure that you do not list a supplier paired with itself.
*) Find the product number, name, and total ordered quantity for each product with at least 3 orders.
*) Use a nested query to retrieve the product names of all products with at least four orders.
*) Use a nested query with the SQL keyword IN to find the product names that supplier number 19 and supplier number 53 can supply.