Normalize to 1NF and 2NF, also 3NF (if possible)

Question

I have a sample table with the dataset unnormalized, So I need to make it normalize in 1NF and 2NF and answer 5 different questions from those tables.

Part of my dataset: (unnormalized)

category |item            |variety              |date      |price|unit|
---------+----------------+---------------------+----------+-----+----+
fruit    |pears           |conference           |2021-11-12| 1.04|kg  |
fruit    |pears           |doyenne_du_comice    |2021-11-12| 1.08|kg  |
fruit    |raspberries     |raspberries          |2021-11-12|  4.4|kg  |
fruit    |strawberries    |strawberries         |2021-11-12| 6.24|kg  |
vegetable|beetroot        |beetroot             |2021-11-12| 0.51|kg  |
vegetable|brussels_sprouts|brussels_sprouts     |2021-11-12| 0.98|kg  |
vegetable|pak_choi        |pak_choi             |2021-11-12| 2.72|kg  |
vegetable|curly_kale      |curly_kale           |2021-11-12| 3.26|kg  |
vegetable|cabbage         |red                  |2021-11-12| 0.55|kg  |
vegetable|cabbage         |savoy                |2021-11-12|  0.5|head|
vegetable|spring_greens   |prepacked            |2021-11-12| 0.84|kg  |
vegetable|cabbage         |summer_autumn_pointed|2021-11-12| 0.55|kg  |
vegetable|cabbage         |white                |2021-11-12| 0.46|kg  |
vegetable|cabbage         |round_green_other    |2021-11-12| 0.46|head|
vegetable|calabrese       |calabrese            |2021-11-12| 1.49|kg  |

What I think after 1NF and 2NF: So basically I want to split the category and items to a separate table and reference them using the category_id and item_id to parent table

Categories:

id|category   |
--+-----------+
 1|fruit      |
 2|vegetable  |
 3|cut_flowers|
 4|pot_plants |

Items:

 id|item                |
--+--------------------+
 1|apples              |
 2|pears               |
 3|beetroot            |
 4|brussels_sprouts    |
 5|pak_choi            |
 6|curly_kale          |
 7|cabbage             |
 8|spring_greens       |
 9|carrots             |
10|cauliflower         |
11|celeriac            |

Main Table: will be like this after splitting the tables

id |variety              |date      |price|unit|category_id|item_id|
---+---------------------+----------+-----+----+-----------+-------+
  1|bramleys_seedling    |2022-03-11| 2.05|kg  |          1|      1|
  2|coxs_orange_group    |2022-03-11| 1.22|kg  |          1|      1|
  3|egremont_russet      |2022-03-11| 1.14|kg  |          1|      1|
  4|braeburn             |2022-03-11| 1.05|kg  |          1|      1|
  5|gala                 |2022-03-11| 1.03|kg  |          1|      1|
  6|other_late_season    |2022-03-11| 0.85|kg  |          1|      1|
  7|conference           |2022-03-11| 0.77|kg  |          1|      2|
  8|doyenne_du_comice    |2022-03-11| 1.24|kg  |          1|      2|
  9|beetroot             |2022-03-11| 0.52|kg  |          2|      3|
 10|brussels_sprouts     |2022-03-11| 0.78|kg  |          2|      4|
 11|pak_choi             |2022-03-11| 3.17|kg  |          2|      5|
 12|curly_kale           |2022-03-11| 3.17|kg  |          2|      6|
 13|red                  |2022-03-11| 0.59|kg  |          2|      7|

I need to get these queries done, and I did after splitting the table.

Write and execute 5 SQL statements to find the following:

1. The average, minimum, and maximum price of strawberries.
2. The minimum price of each main category of items.
3. The average weight of each subcategory of fruit.
4. The average prices of each fruit between the years 2018 and 2020.
5. The number of different fruits recorded before 2021.

So my basic question is, is my normalization to 1NF and 2NF is correct or I need to do any modification? Also I am in doubt how I can I answer No 3.

---

variety is unique within items and category is unique within items.

Will you be saying WHERE item = "red cabbage" or will you sometimes ask for WHERE item = "cabbage" and not care where it is "red" or "white"?

I will say item="cabbage" and variety="red", if I need.

I think I did a mistake to split tables into category and item. Seems I need to split the category and variety because item is the main.

Do you think I need to do any 1NF, 2NF or 3NF here because 1) All columns contains atomic values, 2) the category, item and variety seems the composite primary key and all non key e.g price, date, unit fully depends on it so it also satisfied 2NF, 3) Also no transitive dependency present from non key to other columns.

Do you think these should be a table with 1. Items > id, category, item and 2. Variety > id, category, variety, 3. Product > id, item, date, price, unit?

EDIT After some deep lookups I think I did a mistake o. Splitting tables, please correct me if I am wrong, From main table there is a transitive dependency between category to items in main table so I think I need to move the category, items to a separate table, so category> Id , category,item and product> Id, item, variety, date, prices,unit. The item, variety, date could be my composite primary key that will uniquely identify each row, as other non key column like price, date and unit fully depends on it. So now it is fully satisfied 1nf , 2nf and 3nf as per my understanding.

Answer 1

1NF - Does it have a repeating group?

• None of your table has.

2NF - Every non candidate-key attribute must depend on the whole candidate key

• This seems a bit controversial. Your main table seems to have multiple candidate keys. And, you also defined an surrogate ID. I'm not fully sure the requirements. So, it automatically satisfies 2NF because every non non candidate key attribute depends on the primary key.

• This depends on the requirements. You need to know the candidate key but that's a part of the requirements. Is the variety unique among within items? do you need a category ID ? Isn't the category inferred from the product (and therefore you're missing a relation between category and product)?

---

The full requirement is needed. Based on what you said, it's not 3NF. There is a transitive dependency in the main table (category_id can be inferred from variety name, which is not a key, item_id can be inferred from category_id).