Normalizing Table from UNF to 3NF

Question

I'm very new to Normalization and I'd just like to know if I converted my table from UNF to 3NF correctly. I'll show all my steps.

Inventory Report

PRODUCT ID:   DESCRIPTION         INVENTORY AISLE#  SUPPLIER
  10        6 foot garden rake       5        1     Sheffield-Gander inc.
  20        7 foot leaf rake         5        1     Sheffield-Gander inc.
  30        Round mouth shovel       4        1     Husky Inc.
  40        Flat-nosed Shovel        2        1     Husky Inc.
  50        Garden pitch-fork        6        1     Husky Inc.
  60        8 inch hand shears       9        2     Sheffield-Gander inc.
  70        12 inch trimming shears 10        2     Sheffield-Gander inc.
  80        10 inch tamper           3        2     Husky Inc.
  90        Cedar sapling            34       5     Northwood Farms inc.
  100       Golden cedar sapling     23       5     Northwood Farms inc.
  110       Mulberry sapling         12       4     Sherwood Nursery
  120       Juniper sapling          15       4     Northwood Farms inc.
  130       Premium lawn fertilizer  4        6     Sherwood Nursery
  140       General grade lawn       12       6     Sherwood Nursery
  150       Premium garden fertilize 14       6     Sherwood Nursery
  160       General grade garden f   12       6     Sherwood Nursery
  170       120 foot watering hose    9       3     Diemar Garden Center
  180       12 inch aluminum sprinkl  5       3     Diemar Garden Center
  190       Rotating sprinkler jet    4       3     Diemar Garden Center


UNF INVENTORY[ProductID(fk), Description, (Aisle#(fk),Inventory,supplier)]

1NF INVENTORY[ProductID(fk), Aisle#(fk), Description,Inventory, Supplier]

2NF INVENTORY_AISLE [ProductID(fk), Aisle#(fk)]
    INVENTORY       [ProductID(fk), Description, inventory]
    AISLE           [Aisle#(fk), supplier]

3NF: INVENTORY_AISLE [ProductID(fk), Aisle#(fk)]
     INVENTORY       [ProductID(fk), Description(fk)]
     AISLE           [Aisle#(fk), supplier]
     DESCRIPTION     [Description(fk), Inventory]

Answer 1

You have a good first attempt there.

In UNF table, if there is a situation that product ID 10 is in Aisle 1 and 3, the current table structure will create repeating values in Aisle column like so:

PRODUCT ID:   DESCRIPTION         INVENTORY AISLE#  SUPPLIER
  10        6 foot garden rake       5        1,3   Sheffield-Gander inc.

If such situation arises:

1NF

Remove repeating values by structuring the data like this:

PRODUCT ID:   DESCRIPTION         INVENTORY AISLE#  SUPPLIER
  10        6 foot garden rake       3        1     Sheffield-Gander inc.
  10        6 foot garden rake       2        3     Sheffield-Gander inc.

2NF

Non-key fields will rely on primary key. To do that, let's determine what the primary keys will be.

For Product: Product ID. Other fields are not keys and are dependent on ID For Supplier: Supplier ID

Let's see how we can ensure that non-keys directly depend on the primary key. Product has a description that usually stays consistent. Product has inventory (it may change rapidly).

Product

ProductID
Description
Inventory (stay with me on this one)
AisleID

Supplier

SuppierID
Name

It is important to note the comment from a commenter to your question. It is important to understand functional dependencies. That means, how is one related to . I am assuming that your product is supplied by one or more suppliers. A product can sit in one or more aisles.

3NF

Non-key should not depend on another non-key

The above design may appear OK, but it's not. Product can be different Aisles. Aisle may have properties like AisleWidth and AisleLength. It may feel like these two fields can be added right after AisleID column in Product table. Doing so will violate 3NF since those two fields have nothing to do with ProductID.

• Let's create a Aisle table that describes features of an Aisle
• Since inventory can change for a product and Product can be placed in multiple aisles, let's account for that
• Since product can come from different suppliers, let's account for that too

Product

ProductID
Description

Aisle

AisleID
AisleWidth
AisleLength (you don't have some of these fields; they are just for illustration)

ProductPlacement

ProductID
AisleID
Inventory

Supplier

SuppierID
Name

ProductSupplier

ID
ProductID
SupplierID

Now, when the inventory changes, data gets changed in one table. Inventory is much more than just product placement; inventory can be in warehouses, which can be in different physical locations etc. We won't get into that but you get the point that Inventory in ProductPlacement table is for illustration only.

Hope that gives you an idea of distributing your tables as necessary. This is not the only way to normalize. Invite more answers and hope you get to learn about other philosophies than this.