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@…	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@…	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@…	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@…	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@…	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@…
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

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