Changes between Version 21 and Version 22 of Normalization

Timestamp:

07/16/26 07:24:11 (10 days ago)

Author:

235018

Comment:

--

Normalization

@@ -219 +219 @@
 
 ||=// 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        ||
+|| 0012025000001 || 2         || Delivered    || 2025-12-02    || Cash           || 0.04        ||
+|| 0022025000001 || 1         || Placed Order || 2025-12-01    || Credit Card    || 0.00        ||
+|| 0022025000002 || 1         || Packaging    || 2025-12-10    || PayPal         || 0.00        ||
+|| 0012025000002 || 3         || Packaging    || 2025-12-12    || Credit Card    || 0.05        ||
+|| 0022025000003 || 1         || Placed Order || 2025-12-15    || Credit Card    || 0.02        ||
 
 
@@ -249 +249 @@
 ==== PERSONAL
 
-||=// SSN           =||=// first_name =||=// last_name  =||=// email                           =||=// password =||
+||=// employee_SSN           =||=// first_name =||=// last_name  =||=// email                           =||=// password =||
 || 1234567890123 || Marko      || Petrovski  || marko@woodcraft.mk        || BuHuhjgholko8u7T76TVYFgfChHBJHVGHh ||
 || 9876543210987 || Antonio    || Trajkovski || antonio@woodcraft.mk      || kgvhjgbJgfHGvHhbhg6777t^%dcGfyR%^G ||
@@ -353 +353 @@
 indirectly.
 
-
-
-
-
-
-
+Example:
+
+||=// employeeSSN =||=// store_ID =||=// store_name =||=// store_address =||
+
+If
+
+{{{#!c++
+Primary Key -> Non-key Attribute A
+Non-key Attribute A -> Non-key Attribute B
+}}}
+
+then Store Name and Store Address should not be stored with Employee records. They belong in the STORE table.
+
+3NF removes these remaining redundancies.
+
+==== PERSONAL
+
+**Primary key: ** employee_SSN
+
+||=// employee_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
+
+**Primary key: ** employee_SSN
+
+**Forgein key: ** employee_SSN -> PERSONAL
+
+||=// personal_SSN  =||=//type     =||=// authorization =||
+|| 1234567890123 || Boss     || Admin         ||
+|| 9876543210987 || Employee || M.Petrovski   ||
+|| 4567891234567 || Boss     || Admin         ||
+
+
+==== BOSS
+
+**Primary key: ** boss_SSN
+
+**Forgein key: ** boss_SSN -> PERSONAL(employee_SSN)
+
+||=// boss_SSN      =||
+|| 1234567890123 ||
+|| 4567891234567 ||
+
+==== EMPLOYEE
+
+**Primary key: ** employee_SSN
+
+**Forgein key: ** employee_SSN -> PERSONAL
+
+||=// employee_SSN  =||=// date_of_hire =||
+|| 9876543210987 || 2019-09-01   ||
+
+==== 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    ||
+
+==== WORKS_IN_STORE
+
+||=// personal_SSN  =||=// store_ID =||
+|| 1234567890123 || 001      ||
+|| 9876543210987 || 001      ||
+|| 4567891234567 || 002      ||
+
+==== 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      ||
+
+
+==== 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  ||
+
+==== ORDER
+
+||=// order_num      =||=// client_ID =||=// status       =||=// last_modified =||=// payment_method =||=// discount =||
+|| 0012025000001 || 2         || Delivered    || 2025-12-02    || Cash           || 0.04        ||
+|| 0022025000001 || 1         || Placed Order || 2025-12-01    || Credit Card    || 0.00        ||
+|| 0022025000002 || 1         || Packaging    || 2025-12-10    || PayPal         || 0.00        ||
+|| 0012025000002 || 3         || Packaging    || 2025-12-12    || Credit Card    || 0.05        ||
+|| 0022025000003 || 1         || Placed Order || 2025-12-15    || Credit Card    || 0.02        ||
+
+
+==== INCLUDES
+
+||=// order_num     =||=// product_code =||
+|| 0012025000001 || 00100001     ||
+|| 0022025000001 || 00200002     ||
+|| 0022025000002 || 00200001     ||
+|| 0012025000002 || 00100001     ||
+|| 0012025000002 || 00200001     ||
+|| 0022025000003 || 00200002     ||
+
+
+==== 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    ||
+
+
+==== 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 ||
+
+
+==== REQUEST_FOR_STORE
+
+||=// request_number =||=// store_ID =||
+|| 001112025001   || 001      ||
+|| 002122025001   || 002      ||
+
+Instead of storing store information inside REQUEST, a relationship table is used.
+
+==== 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   ||
+
+==== 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 ||
+
+==== APPROVES
+
+||=// boss_SSN      =||=// report_date =||=// store_ID =||=// signature   =||
+|| 1234567890123 || 2025-12-01  || 001      || M.Petrovski ||
+|| 4567891234567 || 2025-12-03  || 002      || S.Vaneva    ||
+
+The approval relationship is separated from the report because a report is approved by a boss, and this relationship should not duplicate boss information.
+
+==== STORE_MONTHLY_DATA
+
+||=// report_date =||=// store_ID =||=// monthly_profit =||=// date       =||=// sales =||=// samages |
+|| 2024-11-30  || 001      || 38750          || 2024-12-01 || 52    || 750     ||
+|| 2024-11-30  || 002      || 26150          || 2024-12-01 || 40    || NULL    ||
+
+== Explanation: 2NF -> 3NF
+
+Although the database satisfied Second Normal Form by eliminating partial dependencies, some transitive dependencies still existed. In Third Normal Form, these remaining dependencies were removed by ensuring that every non-key attribute depends directly and only on the primary key.
+
+For example, employee role information was separated from the PERSONAL table into the PERMISSIONS relation, while employee specializations were represented by the BOSS and EMPLOYEE tables. This prevents storing attributes that apply only to certain personnel in every employee record. Likewise, relationships such as WORKS_IN_STORE, APPROVES, FOR_STORE, MAKES_REQUEST, and ANSWERS isolate many-to-many associations into dedicated tables instead of embedding foreign entity information in other relations.
+
+The PRODUCT, STORE, CLIENT, and ORDER tables now contain only attributes that describe their own entities. Information about colors, images, delivery addresses, reviews, reports, monthly profits, and request handling is stored in separate relations connected through foreign keys. This design eliminates update, insertion, and deletion anomalies and greatly reduces data redundancy.
+
+The resulting schema is fully normalized to Third Normal Form, where each relation represents a single entity or relationship, each non-key attribute depends only on its table's primary key, and all associations between entities are maintained through foreign keys.
+
+= Summary of Normalization process
+
+||=// Normal Form         =||=// Main Objective        =|| Changes Made        ||
+|| **Denormalized (UNF)**       || Store all data in one large table                   || Repeating groups and multi-valued attributes exist, leading to redundancy and anomalies.           ||
+|| **First Normal Form (1NF)**  || Eliminate repeating groups and ensure atomic values || Multi-valued attributes (e.g., colors, images, products in an order) are split into separate rows or relations so that each field contains a single value.                         ||
+|| **Second Normal Form (2NF)** || Remove partial dependencies                         || Attributes that depend on only part of a composite key are moved to separate tables (e.g., product details, store details). Relationship tables retain only attributes dependent on the full composite key.                        ||
+|| **Third Normal Form (3NF)**  || Remove transitive dependencies                      || Attributes that depend on other non-key attributes are separated into dedicated relations (e.g., permissions, employee roles, report approvals, request handling), leaving each table to describe a single entity or relationship. ||
+
+
+
+
+