Changes between Version 16 and Version 17 of Normalization

Timestamp:

07/14/26 08:29:56 (12 days ago)

Author:

235018

Comment:

--

Normalization

@@ -142 +142 @@
 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.
+
+
+
+
+
+
+
+
+
+