Introduction
In this blog, I wrote everything about database normalization, what is it, why to normalize a database and how to normalize a database, and trade-offs, how far should you go to normalize a database(generally we go till 3). So, this will be a short blog for this topic, so lets get started.
Database Normalization
What??
Database Normalization is the process to reduce data redundancy and improve data integrity in the whole database. Now there could be column redundancy e.g. repetition of certain columns or row redundancy. So, db normalization includes various steps to normalize the db, in general, people use until 3rd normal form.
Why??
As wrote before, it is used to reduce data redundancy and improve data integrity and also there are certain anomalies that can occur if we don’t normalize db. Anomalies include:
- Insertion anomaly:
- Updation anomaly:
- Deletion anomaly:
Cool, now you know why to implement database normalization. Lets see how many normal forms are there and what are they
- 1NF (First Normal Form)
- 2NF (Second Normal Form)
- 3NF (Third Normal Form)
- 4NF (Fourth Normal Form)
- BCNF (Boyce-Codd Normal Form)
- 5NF
Lets normalize the database, for this blog’s sake I have taken a dataset from kaggle, source- https://www.kaggle.com/datasets/bjaising/adventureworks-2022-denormalized These are sales data, and I have taken first 30 rows.
Definition and Declaration of database
First the sales data has these columns: sales_order_number, sales_order_date, sales_order_date_day_of_week,sales_order_date_month, sales_order_date_day, sales_order_date_year, quantity, unit_price, total_sales, cost, product_key, product_name, reseller_key, reseller_name, reseller_business_type, reseller_city, reseller_state, reseller_country, employee_key, employee_id, salesperson_fullname, salesperson_title, email_address, sales_territory_key, assigned_sales_territory, sales_territory_region, sales_territory_country, sales_territory_group, target, target_date, target_date_day_of_week, target_date_month, target_date_day, target_date_year .
You can read about the columns in the above source where I took the data.
Finally, the table looks like this:
| sales_order_number | sales_order_date | sales_order_date_day_of_week | sales_order_date_month | sales_order_date_day | sales_order_date_year | quantity | unit_price | total_sales | cost | product_key | product_name | reseller_key | reseller_name | reseller_business_type | reseller_city | reseller_state | reseller_country | employee_key | employee_id | salesperson_fullname | salesperson_title | email_address | sales_territory_key | assigned_sales_territory | sales_territory_region | sales_territory_country | sales_territory_group | target | target_date | target_date_day_of_week | target_date_month | target_date_day | target_date_year |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SO43897 | Friday, August 25, 2017 | Friday | August | 25 | 2017 | 2 | $28.84 | $57.68 | $63.45 | 235 | Long-Sleeve Logo Jersey, XL | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $450,000 | Tuesday, August 1, 2017 | Tuesday | August | 1 | 2017 |
| SO43897 | Friday, August 25, 2017 | Friday | August | 25 | 2017 | 2 | $2,024.99 | $4,049.98 | $3,796.19 | 351 | Mountain-100 Black, 48 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $450,000 | Tuesday, August 1, 2017 | Tuesday | August | 1 | 2017 |
| SO43897 | Friday, August 25, 2017 | Friday | August | 25 | 2017 | 2 | $2,024.99 | $4,049.98 | $3,796.19 | 348 | Mountain-100 Black, 38 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $450,000 | Tuesday, August 1, 2017 | Tuesday | August | 1 | 2017 |
| SO43897 | Friday, August 25, 2017 | Friday | August | 25 | 2017 | 2 | $28.84 | $57.68 | $63.45 | 232 | Long-Sleeve Logo Jersey, L | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $450,000 | Tuesday, August 1, 2017 | Tuesday | August | 1 | 2017 |
| SO44544 | Saturday, November 18, 2017 | Saturday | November | 18 | 2017 | 2 | $818.7 | $1,637.4 | $1,413.62 | 292 | HL Mountain Frame - Silver, 48 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Wednesday, November 1, 2017 | Wednesday | November | 1 | 2017 |
| SO44544 | Saturday, November 18, 2017 | Saturday | November | 18 | 2017 | 2 | $20.19 | $40.38 | $24.06 | 220 | Sport-100 Helmet, Blue | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Wednesday, November 1, 2017 | Wednesday | November | 1 | 2017 |
| SO44544 | Saturday, November 18, 2017 | Saturday | November | 18 | 2017 | 2 | $2,024.99 | $4,049.98 | $3,796.19 | 351 | Mountain-100 Black, 48 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Wednesday, November 1, 2017 | Wednesday | November | 1 | 2017 |
| SO44544 | Saturday, November 18, 2017 | Saturday | November | 18 | 2017 | 2 | $2,024.99 | $4,049.98 | $3,796.19 | 349 | Mountain-100 Black, 42 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Wednesday, November 1, 2017 | Wednesday | November | 1 | 2017 |
| SO44544 | Saturday, November 18, 2017 | Saturday | November | 18 | 2017 | 2 | $2,039.99 | $4,079.98 | $3,824.31 | 344 | Mountain-100 Silver, 38 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Wednesday, November 1, 2017 | Wednesday | November | 1 | 2017 |
| SO45321 | Sunday, February 18, 2018 | Sunday | February | 18 | 2018 | 2 | $2,039.99 | $4,079.98 | $3,824.31 | 346 | Mountain-100 Silver, 44 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $500,000 | Thursday, February 1, 2018 | Thursday | February | 1 | 2018 |
| SO45321 | Sunday, February 18, 2018 | Sunday | February | 18 | 2018 | 2 | $2,039.99 | $4,079.98 | $3,824.31 | 347 | Mountain-100 Silver, 48 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $500,000 | Thursday, February 1, 2018 | Thursday | February | 1 | 2018 |
| SO46082 | Wednesday, May 23, 2018 | Wednesday | May | 23 | 2018 | 2 | $20.19 | $40.38 | $24.06 | 220 | Sport-100 Helmet, Blue | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Tuesday, May 1, 2018 | Tuesday | May | 1 | 2018 |
| SO46082 | Wednesday, May 23, 2018 | Wednesday | May | 23 | 2018 | 2 | $2,039.99 | $4,079.98 | $3,824.31 | 346 | Mountain-100 Silver, 44 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Tuesday, May 1, 2018 | Tuesday | May | 1 | 2018 |
| SO46082 | Wednesday, May 23, 2018 | Wednesday | May | 23 | 2018 | 2 | $2,039.99 | $4,079.98 | $3,824.31 | 345 | Mountain-100 Silver, 42 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Tuesday, May 1, 2018 | Tuesday | May | 1 | 2018 |
| SO46082 | Wednesday, May 23, 2018 | Wednesday | May | 23 | 2018 | 2 | $28.84 | $57.68 | $63.45 | 232 | Long-Sleeve Logo Jersey, L | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Tuesday, May 1, 2018 | Tuesday | May | 1 | 2018 |
| SO46082 | Wednesday, May 23, 2018 | Wednesday | May | 23 | 2018 | 2 | $2,039.99 | $4,079.98 | $3,824.31 | 344 | Mountain-100 Silver, 38 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Tuesday, May 1, 2018 | Tuesday | May | 1 | 2018 |
| SO46082 | Wednesday, May 23, 2018 | Wednesday | May | 23 | 2018 | 2 | $2,024.99 | $4,049.98 | $3,796.19 | 348 | Mountain-100 Black, 38 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Tuesday, May 1, 2018 | Tuesday | May | 1 | 2018 |
| SO46082 | Wednesday, May 23, 2018 | Wednesday | May | 23 | 2018 | 2 | $20.19 | $40.38 | $24.06 | 212 | Sport-100 Helmet, Red | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $750,000 | Tuesday, May 1, 2018 | Tuesday | May | 1 | 2018 |
| SO47028 | Friday, August 24, 2018 | Friday | August | 24 | 2018 | 2 | $36.45 | $72.9 | $53.94 | 410 | LL Mountain Front Wheel | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $1,200,000 | Wednesday, August 1, 2018 | Wednesday | August | 1 | 2018 |
| SO47028 | Friday, August 24, 2018 | Friday | August | 24 | 2018 | 2 | $14.13 | $28.26 | $19.43 | 464 | Half-Finger Gloves, M | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $1,200,000 | Wednesday, August 1, 2018 | Wednesday | August | 1 | 2018 |
| SO47028 | Friday, August 24, 2018 | Friday | August | 24 | 2018 | 2 | $180.13 | $360.26 | $266.59 | 412 | HL Mountain Front Wheel | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $1,200,000 | Wednesday, August 1, 2018 | Wednesday | August | 1 | 2018 |
| SO47028 | Friday, August 24, 2018 | Friday | August | 24 | 2018 | 2 | $141.62 | $283.24 | $209.59 | 420 | ML Mountain Rear Wheel | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $1,200,000 | Wednesday, August 1, 2018 | Wednesday | August | 1 | 2018 |
| SO47028 | Friday, August 24, 2018 | Friday | August | 24 | 2018 | 2 | $22.79 | $45.58 | $31.34 | 468 | Full-Finger Gloves, S | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $1,200,000 | Wednesday, August 1, 2018 | Wednesday | August | 1 | 2018 |
| SO47028 | Friday, August 24, 2018 | Friday | August | 24 | 2018 | 2 | $1,229.46 | $2,458.92 | $2,211.62 | 360 | Mountain-200 Black, 42 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $1,200,000 | Wednesday, August 1, 2018 | Wednesday | August | 1 | 2018 |
| SO47028 | Friday, August 24, 2018 | Friday | August | 24 | 2018 | 2 | $209.26 | $418.52 | $371.64 | 409 | ML Mountain Frame - Black, 38 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $1,200,000 | Wednesday, August 1, 2018 | Wednesday | August | 1 | 2018 |
| SO47028 | Friday, August 24, 2018 | Friday | August | 24 | 2018 | 2 | $744.27 | $1,488.54 | $1,321.83 | 308 | HL Mountain Frame - Silver, 38 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $1,200,000 | Wednesday, August 1, 2018 | Wednesday | August | 1 | 2018 |
| SO47028 | Friday, August 24, 2018 | Friday | August | 24 | 2018 | 2 | $209.26 | $418.52 | $371.64 | 428 | ML Mountain Frame - Black, 48 | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $1,200,000 | Wednesday, August 1, 2018 | Wednesday | August | 1 | 2018 |
| SO48049 | Monday, November 19, 2018 | Monday | November | 19 | 2018 | 2 | $65.6 | $131.2 | $97.09 | 401 | HL Mountain Handlebars | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $800,000 | Thursday, November 1, 2018 | Thursday | November | 1 | 2018 |
| SO48049 | Monday, November 19, 2018 | Monday | November | 19 | 2018 | 2 | $5.19 | $10.38 | $10.46 | 224 | AWC Logo Cap | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $800,000 | Thursday, November 1, 2018 | Thursday | November | 1 | 2018 |
| SO48049 | Monday, November 19, 2018 | Monday | November | 19 | 2018 | 2 | $180.13 | $360.26 | $266.59 | 412 | HL Mountain Front Wheel | 312 | Resale Services | Value Added Reseller | Culver City | California | United States | 282 | 191644724 | Linda Mitchell | Sales Representative | linda-mitchell@adventureworks.com | 4 | 34 | Southwest | United States | North America | $800,000 | Thursday, November 1, 2018 | Thursday | November | 1 | 2018 |
is this good??
First Normal Form (1NF)
A database said to be in first normal form, if it has following properties:
- Every field should be atomic(contains indivisible data)
- A table should have a primary key (a candidate key chosen for primary key)
- Data in each column should have same data type.
- Rows shouldn’t be repeated.
- Columns shouldn’t be repeated.