Imagine the following scenario: Pine Valley Furniture Company maintains a list of order records for the city Edmonton. The table PVFC Order Line shows a portion of that list. As an experienced database designer, you are asked to help Pine Valley Furniture Company create a better database design to mitigate data redundancy and avoid update anomalies.

Note: Order ID and Product ID form a composite PK. This table is a relation (i.e., in its 1NF). Please examine the data table and perform the following tasks:

Task 1. The table is NOT in 2NF because there are two sets of partial FDs. One is Order ID Order Date, Customer ID, Customer Name, Customer State. (PVFC Order) Remove this set of partial FDs by separating the data table into two. (Be sure to remove redundant data and depict PKs and FKs.)

Task 2. Examine the revised PVFC Order Line table and identify and depict the second set of partial FDs using the written notation (XY).

Task 3. Remove the second set of partial FDs and include actual data in the tables. (Be sure to remove redundant data and depict PKs and FKs.) Hint: You should present three data tables. The revised PVFC Order Line table will consist of only two columns, both of which are FKs and form a composite PK.

Task 4. Two of the three data tables are in 3NF, and one is in 2NF because it contains a set of transitive FDs. Identify and depict the set of transitive FDs using the written notation (e.g., XY). Task 5. Remove the set of transitive FDs and include actual data in the tables. (Be sure to remove redundant data and depict PKs and FKs.) Hint: You should present four data tables