== 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).



== Second Normal Form (2NF)


A relation is in Second Normal Form (2NF) if:

**1.**  It is already in First Normal Form (1NF).
**2.**  Every non-key attribute is fully functionally dependent on the whole primary key.
**3.**  There are no partial dependencies.

A partial dependency occurs when part of a composite primary key determines a non-key attribute.


==== CLIENT

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

==== DELIVERY ADDRESS

||=// client_ID =||=// address                   =||
|| 1         || st.Partizanska 10, Skopje ||
|| 2         || st.Turisticka 5, Bitola   ||
|| 3         || st.32 4, s.Cucer-Sandevo  ||


==== STORE

||=// store_ID =||=// name             =||= date_founded =||=// address                     =||=// email            =||=// rating =||
|| 001      || WoodCraft Skopje || 2015-03-12   || st.Ilindenska 45, Skopje    || contact@woodcraft.mk   || 4.6    ||
|| 002      || Fox Crochets     || 2023-06-01   || st.Kej Makedonija 12, Ohrid || ohrid@woodcraft.mk     || 4.8    ||


==== PRODUCT

||= product_code =||=// price =||=// availability =||=// weight =||=// dimensions =||=// production_time =||=// description                =||=// delivery_cost =||
|| 00100001     || 700   || 50           || 2.5    || 30×20×10   || 10             || Handmade wooden chair with oak wood     || 50            ||
|| 00200001     || 150   || 10           || 0.2    || 20×20×15   || 2              || Heart crochet                           || 0             ||
|| 00200002     || 199   || 100          || 0.75   || 40×40×1    || 14             || Decorative wall hanging made with beads || 0             ||


==== IMAGE

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


==== COLOR

||=// product_code =||=// color        =||
|| 00100001     || Brown                  ||
|| 00200001     || Blue                   ||
|| 00200001     || Red                    ||
|| 00200001     || Yellow                 ||
|| 00200001     || Green                  ||
|| 00200002     || White & Pink           ||
|| 00200002     || Black & Gold           ||
|| 00200002     || Orange, Green & Purple ||


==== SELLS

||=// store_ID =||=// product_code =||=// discount =||
|| 001      || 00100001     || 0        ||
|| 002      || 00200001     || 0        ||
|| 002      || 00200002     || 0.5      ||


==== ORDER

||=// order_num      =||=// client_ID =||=// status       =||=// last_modified =||=// payment_method =||=// discount =||
|| 0012025000001 || 2         || Delivered    || 2025-12-02    || Cash           || 4        ||
|| 0022025000001 || 1         || Placed Order || 2025-12-01    || Credit Card    || 0        ||
|| 0022025000002 || 1         || Packaging    || 2025-12-10    || PayPal         || 0        ||
|| 0012025000002 || 3         || Packaging    || 2025-12-12    || Credit Card    || 5        ||
|| 0022025000003 || 1         || Placed Order || 2025-12-15    || Credit Card    || 2        ||


==== INCLUDES

||=// 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      =||=// comment             =||=// rating =||=// last_modified =||
|| 0012025000001 || Great quality, slightly late delivery || 4      || 2024-12-05    ||
|| 0022025000001 || Excellent craftsmanship               || 5      || 2025-12-03    ||
|| 0022025000002 || Beautiful handmade product            || 5      || 2025-12-11    ||
|| 0012025000002 || Very satisfied                        || 5      || 2025-12-14    ||
|| 0022025000003 || Wonderful decoration                  || 5      || 2025-12-20    ||

==== PERSONAL

||=// SSN           =||=// first_name =||=// last_name  =||=// email                           =||=// password =||
|| 1234567890123 || Marko      || Petrovski  || marko@woodcraft.mk        || BuHuhjgholko8u7T76TVYFgfChHBJHVGHh ||
|| 9876543210987 || Antonio    || Trajkovski || antonio@woodcraft.mk      || kgvhjgbJgfHGvHhbhg6777t^%dcGfyR%^G ||
|| 4567891234567 || Sara       || Vaneva     || s.vaneva@foxcrochets.mk   || UGUgt&^ghjGFu6tgVfCTYt6VghyT6yvHJg ||


==== PERMISSIONS

||=// personal_SSN  =||=//type     =||=// authorization =||
|| 1234567890123 || Boss     || Admin         ||
|| 9876543210987 || Employee || M.Petrovski   ||
|| 4567891234567 || Boss     || Admin         ||

==== WORKS_IN_STORE

||=// personal_SSN  =||=// store_ID =||
|| 1234567890123 || 001      ||
|| 9876543210987 || 001      ||
|| 4567891234567 || 002      ||


==== WORKED

||=// personal_SSN  =||=// report_date =||=// store_ID =||=// wage =||=// pay_method =||=// hours =||=// week      =||
|| 1234567890123 || 2025-11-30  || 001   || 75   || Hourly     || 48    || 24–30 Nov ||
|| 9876543210987 || 2025-11-30  || 001   || 75   || Hourly     || 38    || 24–30 Nov ||
|| 4567891234567 || 2025-11-30  || 002   || 450  || Weekly     || 52    || 24–30 Nov ||


==== REPORT

||=// date       =||=// store_ID =||=// overall_profit =||=// sales_trend =||=// marketing_growth =||=// signature   =||
|| 2024-11-30 || 001   || 125000         || Increasing  || Stable Growth    || M.Petrovski ||
|| 2024-11-30 || 002   || 98000          || Stable      || Moderate Growth  || S.Vaneva    ||


==== MONTHLY_PROFIT

||=// report_date =||=// store_ID =||=// month    =||=// profit =||
|| 2024-11-30  || 001   || Nov 2024 || 12500  ||
|| 2024-11-30  || 002   || Nov 2024 || 8000   ||


==== REQUEST

||=// request_num   =||=// date       =||=// problem            =||=// notes              =||=// satisfaction =||
|| 001112025001 || 2024-11-03 || Late delivery      || Discount offered   || 4            ||
|| 002122025001 || 2024-12-04 || Military discount  || Approved           || 5            ||
|| 003122025001 || 2024-12-10 || Packaging question || Explained shipping || 5            ||
|| 004122025001 || 2024-12-16 || Shipping question  || Tracking provided  || 5            ||


==== MAKES REQUEST

||=// client_ID =||=// request_num   =||
|| 2         || 001112025001 ||
|| 3         || 002122025001 ||
|| 3         || 003122025001 ||
|| 1         || 004122025001 ||


==== ANSWERS

||=// client_ID =||=// request_num   =||
|| 2         || 001112025001 ||
|| 3         || 002122025001 ||
|| 3         || 003122025001 ||
|| 1         || 004122025001 ||


== Explanation: 1NF -> 2NF

The First Normal Form removed repeating groups by ensuring that each attribute contained only atomic values. However, some relations still contained composite primary keys, creating the possibility of partial dependencies, where non-key attributes depended on only part of the key instead of the entire key.

For example, in the INCLUDES relation, the composite key is (Order_Number, Product_Code). Product attributes such as description, price, weight, and production time depend only on Product_Code, not on the entire composite key. These attributes were therefore moved into the separate PRODUCT table. Similarly, store-specific discounts were placed in the SELLS relation because the discount depends on the combination of a store and a product rather than on either entity alone.

The same principle was applied throughout the schema. Tables such as IMAGE, COLOR, WORKS_IN_STORE, WORKED, MAKES_REQUEST, and ANSWERS contain only attributes that depend on their complete composite keys. As a result, every non-key attribute in every relation is fully functionally dependent on the whole primary key, satisfying the requirements of Second Normal Form.

Despite these improvements, some transitive dependencies still remain. For instance, information such as employee roles and permissions, report approvals, and specialized employee types (Boss and Employee) can still be further separated. Removing these transitive dependencies leads to Third Normal Form (3NF), which will be covered in Part 3.