Normalize sale relation up to 4NF

Assumptions (read from the sample data)

• A customer has one shipping address (cust_name → ship_add).
• Newsletter, supplier and price depend on the item (item → newsletter, supplier, price). Customer C bought items a and b; her price 300 is the sum 100 + 200 of the item prices, so price is taken as the item price.
• Each supplier has one phone (supp → supp_phone).

Unnormalized form (UNF)

Customer C has repeating groups (two items, two newsletters, two suppliers, two phones in one row).

1NF: remove repeating groups (one value per cell)

Each item bought becomes a separate row. Primary key: (cust_name, item).

FDs: cust_name → ship_add; item → newsletter, supp, price; supp → supp_phone.

(Data problem: S has phone BX in row B and CX in row D. We treat CX as an entry error or a different supplier; in a real design supp → supp_phone must hold.)

2NF: remove partial dependencies

ship_add depends only on cust_name, and newsletter, supp, supp_phone, price depend only on item. Both are parts of the composite key, so split:

• CUSTOMER(cust_name, ship_add)
• ITEM(item, newsletter, supp, supp_phone, price)
• SALE(cust_name, item) (both foreign keys)

3NF: remove transitive dependencies

In ITEM, item → supp and supp → supp_phone, so supp_phone depends on the key through supp. Split:

• CUSTOMER(cust_name, ship_add)
• ITEM(item, newsletter, supp, price) (supp is a foreign key)
• SUPPLIER(supp, supp_phone)
• SALE(cust_name, item)

BCNF check

In every relation the left side of each FD is a key, so the schema is also in BCNF.

4NF: remove multivalued dependencies

4NF requires that every non-trivial MVD X →→ Y has X as a superkey. In the original UNF, a customer's items, and an item's newsletters and suppliers, were the multi-valued sets. After the split:

• SALE(cust_name, item) is all key; the only MVD is trivial.
• ITEM: newsletter and supp are single-valued per item (functional, not multi-valued), so there is no non-trivial MVD.

So the four relations are already in 4NF:

CUSTOMER(cust_name, ship_add)
SALE(cust_name, item)
ITEM(item, newsletter, supp, price)
SUPPLIER(supp, supp_phone)

If a business rule allowed an item to have several independent newsletters and several independent suppliers (item →→ newsletter | supp), ITEM would be split further into ITEM_NEWSLETTER(item, newsletter) and ITEM_SUPPLIER(item, supp, price) to reach 4NF.