= Normalization

== Denormalized form

Below is an example of a denormalized table representing all information in one large relation. Notice that several columns contain multiple values (images, colors, addresses, products, etc.), which violates First Normal Form.

||= //order_num =||= //client_name =||= //client_email =||= //delivery_address =||= //product_code(s) =||= //product(s) =||= //product_images =||= //product_colors =||= //product_price =||= //order_quantity =||= //store_ID =||= //store_name =||= //store_rating =||= //employee =||= //store_owner =||= //order_status =||= //payment =||= //discount =||= //review-comment =||= //order_rating=||= //request=||= //request_problem =||= //report_profit =||= //monthly_profit =||= //order_total =||= //sales_trend =||
|| 0012025000001 || Marija Kostova     || marija@yahoo.com || st.Turisticka 5, Bitola   || 00100001         || Handmade Wooden Chair     || chair.jpg                  || Brown  || 700      || 1   ||  001  || WoodCraft Skopje           || 4.6          || Antonio Trajkovski || Marko Petrovski || Delivered    || Cash  || 0.04      || Great quality, slightly late delivery || 4.5    || 001112025001 || Late delivery      || 125000 ||   12500       || 700 || Increasing  ||
|| 0022025000001 || Ivan Stojanov     || ivan@gmail.com   || st.Partizanska 10, Skopje || 00200002    || Decorative Wall Hanging     || wall_hanging.jpg               || White & Pink, Black & Gold, Orange-Green-Purple || 199      || 2   || 002 || Fox Crochets                    || 4.8          || Sara Vaneva   ||  Sara Vaneva    || Placed Order || Credit Card || 0.0       || Excellent craftsmanship               || 5.0    || 002122025001 || Military Discount || 98000         || 8000           ||  350 || Stable      ||
|| 0022025000002 || Ivan Stojanov      || ivan@gmail.com       || st.Partizanska 10, Skopje || 00200001          || Heart Crochet    || crochet-heart-red.jpg, crochet-heart-blue.jpg || Blue, Red, Yellow, Green                        || 150      || 3   ||  002 || Fox Crochets                || 4.8          || Sara Vaneva        || Sara Vaneva || Packaging    || PayPal      || 0.0       || Beautiful handmade product            || 5.0    || —            || —                  || 98000         || 8000           ||  50  || Stable      ||
|| 0012025000002 || Antoneta Mariovska || mariovskaantoneta@finki.ukim.mk || st.32 4, s.Cucer-Sandevo  || 00100001,00200001 || Wooden Chair, Heart Crochet || chair.jpg, crochet-heart-red.jpg              || Brown, Blue, Red               || 850      || 2   || 001,002 || WoodCraft Skopje & Fox Crochets || 4.0    || Antonio Trajkovski || Marko Petrovski || Packaging    || Credit Card || 0.05       || Very satisfied                        || 5.0    || 003122025001 || Packaging question || 223000        || 20500          || 750 || Increasing  ||
|| 0022025000003 || Ivan Stojanov      || ivan@gmail.com      || st.Partizanska 10, Skopje || 00200002          || Decorative Wall Hanging     || wall_hanging.jpg                     || Black & Gold            || 199      || 1   || 002 || Fox Crochets                    || 4.8          || Sara Vaneva        || Sara Vaneva     || Placed Order || Credit Card || 0.02       || Wonderful decoration                  || 5.0    || 004122025001 || Shipping question  || 98000         || 8000           || 350 || Stable      ||


==== Problems with the Denormalized Table

This table contains several issues:

*  Multiple products may appear in one order.
*  A product may have multiple colors.
*  A product may have multiple images.
*  Client information repeats for every order.
*  Store information repeats for every product sold.
*  Employee and boss information repeats.
*  Reports and profits repeat for every order.
*  There are repeating groups, making updates difficult.
*  Insertion, deletion, and update anomalies may occur.

Because of these problems, the table is not in First Normal Form.


== First Normal Form(1NF)

To achieve 1NF:

*  Every column must contain atomic values.
*  No repeating groups.
*  No lists inside cells.
*  Every row represents exactly one occurrence.

Instead of storing several colors or images in one field, each value gets its own row.

==== CLIENT

||= // client_id =||= // first_name =||=// last_name =||=// email  =||                                                                     
|| 1        || Ivan       || Stojanov  || ivan@gmail.com       ||
|| 2        || Marija     || Kostova   || marija@yahoo.com            ||
|| 3        || Antoneta   || Mariovska || mariovskaantoneta@finki.ukim.mk ||


==== DELIVERY ADDRESS

||= // client_id =||= // address =||                                                                     
|| 1        || st.Partizanska 10, 1000 Skopje  || 
|| 2        || st.Turisticka 5,7000 Bitola  || 
|| 3        || st.Ilindenska 20, 1000 Skopje   ||

==== STORE

||=// store_ID =||=// store_name     =||=// store_rating =||
|| 001     || WoodCraft Skopje || 4.6    ||
|| 002     || Fox Crochets     || 4.8    ||
 

==== PRODUCT

||=// product_code =||=// description                =||=// price =||=// weight =||=// delivery_cost =||
|| 00100001     || Handmade wooden chair with oak wood     || 700   || 2.5    || 50            ||
|| 00200001     || Heart crochet                           || 50   || 0.2    || 0             ||
|| 00200002     || Decorative wall hanging made with beads || 350   || 0.75   || 0             ||


==== PRODUCT COLORS

||=// product_code =||=// description                   =||=// price =||=// weight =||=// delivery_cost =||
|| 00100001     || Handmade wooden chair with oak wood     || 700   || 2.5    || 50            ||
|| 00200001     || Heart crochet                           || 150   || 0.2    || 0             ||
|| 00200002     || Decorative wall hanging made with beads || 199   || 0.75   || 0             ||

==== PRODUCT IMAGES

||=// product_code =||=// image              =||
|| 00100001         || chair.jpg              ||
|| 00200001         || crochet-heart-red.jpg  ||
|| 00200001         || crochet-heart-blue.jpg ||
|| 00200002         || wall_hanging.jpg       ||


==== ORDER

||=// order_num      =||=// clien_ID =||=// status       =||=// payment     =||=// discount =||
|| 0012025000001 || 2        || Delivered    || Cash        || 4        ||
|| 0022025000001 || 1        || Placed Order || Credit Card || 0        ||
|| 0022025000002 || 1        || Packaging    || PayPal      || 0        ||
|| 0012025000002 || 3        || Packaging    || Credit Card || 5        ||
|| 0022025000003 || 1        || Placed Order || Credit Card || 2        ||


==== ORDER PRODUCTS

|| order_num      || product_code || quantity ||
|| 0012025000001 || 00100001     || 1        ||
|| 0022025000001 || 00200002     || 2        ||
|| 0022025000002 || 00200001     || 3        ||
|| 0012025000002 || 00100001     || 1        ||
|| 0012025000002 || 00200001     || 1        ||
|| 0022025000003 || 00200002     || 1        ||


==== REVIEW

||=// order_num      =||=// rating =||=// comment                     =||
|| 0012025000001 || 4.0    || Great quality, slightly late delivery ||
|| 0022025000001 || 5.0    || Excellent craftsmanship               ||
|| 0022025000002 || 5.0    || Beautiful handmade product            ||
|| 0012025000002 || 5.0    || Very satisfied                        ||
|| 0022025000003 || 5.0    || Wonderful decoration                  ||


==== REQUEST

||=// request_num   =||=// client_ID =||=// problem            =||
|| 001112025001 || 2        || Late delivery      ||
|| 002122025001 || 3        || Military discount  ||
|| 003122025001 || 3        || Packaging question ||
|| 004122025001 || 1        || Shipping question  ||


==== REPORT

||=// store_ID =||=// date       =||=// overall_profit =||=// monthly_profit =||=// sales_trend =||
|| 001   || 2024-11-30 || 125000         || 12500          || Increasing  ||
|| 002   || 2024-11-30 || 98000          || 8000           || Stable      ||


=== Explanataion: UNF -> 1NF

The denormalized table violated First Normal Form because several attributes stored multiple values in a single cell. Examples include product colors, product images, multiple products in one order, and multiple stores associated with an order. These repeating groups made the data difficult to search, update, and maintain.

To convert the database into First Normal Form, all repeating groups were removed and each attribute was made atomic. Separate tables were created for product colors, product images, delivery addresses, order contents, and reviews. Each row now contains only one value for every attribute, eliminating multi-valued cells and ensuring that every record can be uniquely identified by a primary key.

Although the database is now in First Normal Form, redundancy still exists. For example, client information is repeated in the Order table, and product details are repeated whenever the same product appears in different orders. These partial dependencies will be removed when converting the schema to Second Normal Form (2NF).