Normalization
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 | product | product_images | product_colors | product_price | availability | weight | width_x_length_x_depth | aprox_production_time | delivery_cost | order_quantity | store_ID | store_name | date_of_founding | physical_address | store_email | store_rating | personal_SSN | personal_name | permission_type | authorisation | date_of_hire | order_status | last_date_mod | payment_method | discount | review_comment | order_rating | review_last_mod_date | request_num | request_date_and_time | request_problem | notes_of_communication | costumer_satisfaction | report_date | report_profit | sales_trend | marketing_growth | owner_signature | month_and_year | monthly_profit | change_date | changes | worked_wage | worked_pay_method | worked_total_hours | worked_week | sells_discount | approval_date | exchange_monthly_profit | exchange_date | sales | damages | order_total
|
|---|
| 0012025000001 | Marija Kostova | marija@… | st.Turisticka 5, Bitola 7000, Macedonia | 00100001 | Handmade wooden chair with oak wood | chair.jpg | Brown | 700 | 50 | 2.5 | 30x20x10 | 10 | 50 | 1 | 001 | WoodCraft Skopje | 2015-03-12 | st.Ilindenska 45, Skopje 1000, Macedonia | contact@… | 4.6 | 9876543210987 | Antonio Trajkovski | EMPLOYEE | M.Petrovski | 2019-09-01 | delivered | 2025-12-02 14:30:00 | cash | 4.0 | Great quality, slightly late delivery | 4.0 | 2024-12-05 18:00:00 | 001112025001 | 2024-11-03 11:20:00 | Late delivery | Apologized and offered discount | 4.0 | 2024-11-30 23:59:59 | 125000.00 | Increasing | Stable growth | M.Petrovski | 2024-11-01 | 12500.00 | 2024-11-10 09:00:00 | FROM aprox_production_time=14 TO aprox_production_time=10 | 75 | hourly | 38 | 2025-11-24 - 2025-11-30 | 0.0 | 2025-12-01 09:56:30 | 38750.00 | 2024-12-01 08:00:00 | 52 | 750 | 700
|
| 0022025000001 | Ivan Stojanov | ivan@… | st.Partizanska 10, Skopje 1000, Macedonia | 00200002 | Decorative wall hanging made with beads | wall_hanging.jpg | White and Pink, Black and Gold, Orange, Green and Purpule | 199 | 100 | 0.75 | 40x40x1 | 14 | 0 | 2 | 002 | Fox Crochets | 2023-06-01 | st.Kej Makedonija 12, Ohrid 6000, Macedonia | ohrid@… | 4.8 | 4567891234567 | Sara Vaneva | BOSS | admin | — | placed order | 2025-12-01 10:15:00 | credit card 6750 | 0.0 | Excellent craftsmanship | 5.0 | 2025-12-03 18:00:00 | 002122025001 | 2024-12-04 09:10:00 | Military discount | Discount approved | 5.0 | 2024-11-30 23:59:59 | 98000.00 | Stable | Moderate growth | S.Vaneva | 2024-11-01 | 8000.00 | — | — | 450 | weekly | 52 | 2025-11-24 - 2025-11-30 | 0.5 | 2025-12-03 13:06:12 | 26150 | 2024-12-01 08:00:00 | 40 | NULL | 398
|
| 0022025000002 | Ivan Stojanov | ivan@… | st.Partizanska 10, Skopje 1000, Macedonia | 00200001 | Heart crochet | crochet-heart-red.jpg, crochet-heart-blue.jpg | Blue, Red, Yellow, Green | 150 | 10 | 0.2 | 20x20x15 | 2 | 0 | 3 | 002 | Fox Crochets | 2023-06-01 | st.Kej Makedonija 12, Ohrid 6000, Macedonia | ohrid@… | 4.8 | 4567891234567 | Sara Vaneva | BOSS | admin | — | packaging | 2025-12-10 18:00:00 | PayPal account * | 0.0 | Beautiful handmade product | 5.0 | 2025-12-11 18:00:00 | — | — | — | — | — | 2024-11-30 23:59:59 | 98000.00 | Stable | Moderate growth | S.Vaneva | 2024-11-01 | 8000.00 | 2024-11-12 15:30:00 | Added new color | 450 | weekly | 52 | 2025-11-24 - 2025-11-30 | 0.0 | 2025-12-03 13:06:12 | 26150 | 2024-12-01 08:00:00 | 40 | NULL | 450
|
| 0012025000002 | Antoneta Mariovska | mariovskaantoneta@… | st.32 4, s.Cucer-Sandevo, Skopje, Macedonia | 00100001, 00200001 | Handmade wooden chair with oak wood, Heart crochet | chair.jpg, crochet-heart-red.jpg | Brown, Blue, Red | 850 | 50, 10 | 2.5, 0.2 | 30x20x10, 20x20x15 | 10, 2 | 50, 0 | 2 | 001,002 | WoodCraft Skopje & Fox Crochets | 2015-03-12; 2023-06-01 | st.Ilindenska 45, Skopje 1000, Macedonia; st.Kej Makedonija 12, Ohrid 6000, Macedonia | contact@…; ohrid@… | 4.6, 4.8 | 9876543210987; 4567891234567 | Antonio Trajkovski; Sara Vaneva | EMPLOYEE; BOSS | M.Petrovski; admin | 2019-09-01; — | packaging | 2025-12-12 16:30:00 | credit card 4582 | 5.0 | Very satisfied | 5.0 | 2025-12-14 12:00:00 | 003122025001 | 2024-12-10 14:00:00 | Packaging question | Explained packaging process | 5.0 | 2024-11-30 23:59:59 | 223000.00 | Increasing | Stable growth & Moderate growth | M.Petrovski; S.Vaneva | 2024-11-01 | 20500.00 | 2024-11-10 09:00:00; 2024-11-12 15:30:00 | FROM aprox_production_time=14 TO aprox_production_time=10; Added new color | 75; 450 | hourly; weekly | 38; 52 | 2025-11-24 - 2025-11-30 | 0.0; 0.0 | 2025-12-01 09:56:30; 2025-12-03 13:06:12 | 64900.00 | 2024-12-01 08:00:00 | 92 | 750 | 850
|
| 0022025000003 | Ivan Stojanov | ivan@… | st.Partizanska 10, Skopje 1000, Macedonia | 00200002 | Decorative wall hanging made with beads | wall_hanging.jpg | Black and Gold | 199 | 100 | 0.75 | 40x40x1 | 14 | 0 | 1 | 002 | Fox Crochets | 2023-06-01 | st.Kej Makedonija 12, Ohrid 6000, Macedonia | ohrid@… | 4.8 | 4567891234567 | Sara Vaneva | BOSS | admin | — | placed order | 2025-12-15 11:45:00 | credit card 3281 | 2.0 | Wonderful decoration | 5.0 | 2025-12-20 15:00:00 | 004122025001 | 2024-12-16 10:30:00 | Shipping question | Tracking number provided | 5.0 | 2024-11-30 23:59:59 | 98000.00 | Stable | Moderate growth | S.Vaneva | 2024-11-01 | 8000.00 | — | — | 450 | weekly | 52 | 2025-11-24 - 2025-11-30 | 0.5 | 2025-12-03 13:06:12 | 26150.00 | 2024-12-01 08:00:00 | 40 | NULL | 195.02
|
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.
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.
PRODUCT
| product_code | price | availability | weight | dimensions | aprox_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 | 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
|
STORE
| store_ID | name | date_of_founding | physical_address | store_email | rating
|
|---|
| 001 | WoodCraft Skopje | 2015-03-12 | st.Ilindenska 45, Skopje | contact@… | 4.6
|
| 002 | Fox Crochets | 2023-06-01 | st.Kej Makedonija 12, Ohrid | ohrid@… | 4.8
|
PERSONAL
| personal_SSN | first_name | last_name | email | password | type | date_of_hire
|
|---|
| 1234567890123 | Marko | Petrovski | marko@… | BuHuhjgholko8u7T76TVYFgfChHBJHVGHh | STORE OWNER | /
|
| 9876543210987 | Antonio | Trajkovski | antonio@… | kgvhjgbJgfHGvHhbhg6777t%dcGfyR%G | EMPLOYEE | 2019-09-01
|
| 4567891234567 | Sara | Vaneva | s.vaneva@… | UGUgt&ghjGFu6tgVfCTYt6VghyT6yvHJg | STORE OWNER | /
|
PERMISSIONS
| personal_SSN | type | authorization
|
|---|
| 1234567890123 | Boss | Admin
|
| 9876543210987 | Employee | M.Petrovski
|
| 4567891234567 | Boss | Admin
|
CLIENT
| client_id | first_name | last_name | email
|
|---|
| 1 | Ivan | Stojanov | ivan@…
|
| 2 | Marija | Kostova | marija@…
|
| 3 | Antoneta | Mariovska | mariovskaantoneta@…
|
DELIVERY ADDRESS
| client_id | address
|
|---|
| 1 | st.Partizanska 10, 1000 Skopje
|
| 2 | st.Turisticka 5,7000 Bitola
|
| 3 | st.Ilindenska 20, 1000 Skopje
|
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
|
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
|
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
|
REQUEST
| request_num | date_and_time | problem | notes_of_communication | 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
|
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
|
CHANGE
| date_and_time | product_code | changes
|
|---|
| 2024-11-10 09:00:00 | 00100001 | 'FROM aprox_production_time=14 TO aprox_production_time=10'
|
| 2024-11-12 15:30:00 | 00200001 | 'Added new color'
|
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).
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.
PRODUCT
| product_code | price | availability | weight | dimensions | aprox_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
|
STORE
| store_ID | name | date_of_founding | physical_address | store_email | rating
|
|---|
| 001 | WoodCraft Skopje | 2015-03-12 | st.Ilindenska 45, Skopje | contact@… | 4.6
|
| 002 | Fox Crochets | 2023-06-01 | st.Kej Makedonija 12, Ohrid | ohrid@… | 4.8
|
PERSONAL
| employee_SSN | first_name | last_name | email | password
|
|---|
| 1234567890123 | Marko | Petrovski | marko@… | BuHuhjgholko8u7T76TVYFgfChHBJHVGHh
|
| 9876543210987 | Antonio | Trajkovski | antonio@… | kgvhjgbJgfHGvHhbhg6777t%dcGfyR%G
|
| 4567891234567 | Sara | Vaneva | s.vaneva@… | UGUgt&ghjGFu6tgVfCTYt6VghyT6yvHJg
|
PERMISSIONS
| personal_SSN | type | authorization
|
|---|
| 1234567890123 | Boss | Admin
|
| 9876543210987 | Employee | M.Petrovski
|
| 4567891234567 | Boss | Admin
|
BOSS
| boss_SSN
|
|---|
| 1234567890123
|
| 4567891234567
|
EMPLOYEE
| employee_SSN | date_of_hire
|
|---|
| 9876543210987 | 2019-09-01
|
CLIENT
| client_ID | first_name | last_name | email | password
|
|---|
| 1 | Ivan | Stojanov | ivan@… | HLUaguhoBJKbjhVjkgKJkjVHK
|
| 2 | Marija | Kostova | marija@… | CHVLKDNVJKnkjbkjbjkVKnkNkjbKJ
|
| 3 | Antoneta | Mariovska | mariovskaantoneta@… | 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
|
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_and_year | profit
|
|---|
| 2024-11-30 | 001 | Nov 2024 | 12500
|
| 2024-11-30 | 002 | Nov 2024 | 8000
|
REQUEST
| request_num | date_and_time | problem | notes_of_communication | 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
|
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
|
CHANGE
| date_and_time | product_code | changes
|
|---|
| 2024-11-10 09:00:00 | 00100001 | 'FROM aprox_production_time=14 TO aprox_production_time=10'
|
| 2024-11-12 15:30:00 | 00200001 | 'Added new color'
|
WORKS_IN_STORE
| personal_SSN | store_ID
|
|---|
| 1234567890123 | 001
|
| 9876543210987 | 001
|
| 4567891234567 | 002
|
WORKED
| personal_SSN | report_date | store_ID | wage | pay_method | total_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
|
SELLS
| store_ID | product_code | discount
|
|---|
| 001 | 00100001 | 0
|
| 002 | 00200001 | 0
|
| 002 | 00200002 | 0.5
|
INCLUDES
| order_num | product_code | quantity
|
|---|
| 0012025000001 | 00100001 | 1
|
| 0022025000001 | 00200002 | 2
|
| 0022025000002 | 00200001 | 3
|
| 0012025000002 | 00100001 | 1
|
| 0012025000002 | 00200001 | 1
|
| 0022025000003 | 00200002 | 1
|
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.
A relation is in Third Normal Form (3NF) if:
1. It is already in Second Normal Form (2NF).
2. There are no transitive dependencies.
3. Every non-key attribute depends only on the primary key, and not on another non-key attribute.
A transitive dependency occurs when:
Primary Key -> Non-key Attribute A
Non-key Attribute A -> Non-key Attribute B
which means
Primary Key → Non-key Attribute B
indirectly.
Example:
| employeeSSN | store_ID | store_name | store_address
|
|---|
If
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.
PRODUCT
| product_code | price | availability | weight | dimensions | aprox_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
|
STORE
| store_ID | name | date_of_founding | physical_address | store_email | rating
|
|---|
| 001 | WoodCraft Skopje | 2015-03-12 | st.Ilindenska 45, Skopje | contact@… | 4.6
|
| 002 | Fox Crochets | 2023-06-01 | st.Kej Makedonija 12, Ohrid | ohrid@… | 4.8
|
PERSONAL
| employee_SSN | first_name | last_name | email | password
|
|---|
| 1234567890123 | Marko | Petrovski | marko@… | BuHuhjgholko8u7T76TVYFgfChHBJHVGHh
|
| 9876543210987 | Antonio | Trajkovski | antonio@… | kgvhjgbJgfHGvHhbhg6777t%dcGfyR%G
|
| 4567891234567 | Sara | Vaneva | s.vaneva@… | UGUgt&ghjGFu6tgVfCTYt6VghyT6yvHJg
|
PERMISSIONS
| personal_SSN | type | authorization
|
|---|
| 1234567890123 | Boss | Admin
|
| 9876543210987 | Employee | M.Petrovski
|
| 4567891234567 | Boss | Admin
|
BOSS
| boss_SSN
|
|---|
| 1234567890123
|
| 4567891234567
|
EMPLOYEE
| employee_SSN | date_of_hire
|
|---|
| 9876543210987 | 2019-09-01
|
CLIENT
| client_ID | first_name | last_name | email | password
|
|---|
| 1 | Ivan | Stojanov | ivan@… | HLUaguhoBJKbjhVjkgKJkjVHK
|
| 2 | Marija | Kostova | marija@… | CHVLKDNVJKnkjbkjbjkVKnkNkjbKJ
|
| 3 | Antoneta | Mariovska | mariovskaantoneta@… | 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
|
REPORT
| date | store_ID | overall_profit | sales_trend | marketing_growth | owner_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_and_year | profit
|
|---|
| 2024-11-30 | 001 | Nov 2024 | 12500
|
| 2024-11-30 | 002 | Nov 2024 | 8000
|
REQUEST
| request_num | date_and_time | problem | notes_of_communication | customer_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
|
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
|
CHANGE
| date_and_time | product_code | changes
|
|---|
| 2024-11-10 09:00:00 | 00100001 | 'FROM aprox_production_time=14 TO aprox_production_time=10'
|
| 2024-11-12 15:30:00 | 00200001 | 'Added new color'
|
WORKS_IN_STORE
| personal_SSN | store_ID
|
|---|
| 1234567890123 | 001
|
| 9876543210987 | 001
|
| 4567891234567 | 002
|
WORKED
| personal_SSN | report_date | store_ID | wage | pay_method | total_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
|
SELLS
| store_ID | product_code | discount
|
|---|
| 001 | 00100001 | 0.0
|
| 002 | 00200001 | 0.0
|
| 002 | 00200002 | 0.5
|
INCLUDES
| order_num | product_code
|
|---|
| 0012025000001 | 00100001
|
| 0022025000001 | 00200002
|
| 0022025000002 | 00200001
|
| 0012025000002 | 00100001
|
| 0012025000002 | 00200001
|
| 0022025000003 | 00200002
|
APPROVES
| boss_SSN | report_date | store_ID | owner_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 | damages |
|
|---|
| 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.
|
