GEE 22 - BUSINESS LOGIC

DATABASE
NORMALIZATION
CHAPTER 2
NORMALIZATION
Normalization is a systematic process of organizing data in a database to reduce
redundancy and enhance data integrity.

Structured Data
Organizes data in a structured and systematic way.

Minimize Redundancy
Reduces duplicate data, optimizing storage and preventing inconsistencies.

Improve Integrity
Ensures data accuracy and reliability by reducing dependencies.

Efficient Management
Breaks large tables into smaller ones, making data easier to store, update, and maintain.
Evolution of Normalization in SQL

2nd Normal 3rd Normal Boyce- 4th Normal 5th Normal 6th Normal
1st Normal Form
Form Form Codd NF Form Form Form

• 1NF (First Normal Form): Ensures atomic values; no repeating groups or multi-valued columns.
• 2NF (Second Normal Form): Removes partial dependencies; all non-key attributes depend on the full primary key.
• 3NF (Third Normal Form): Eliminates transitive dependencies; non-key attributes depend only on the primary key.
• BCNF (Boyce-Codd Normal Form): A stricter version of 3NF that resolves additional dependency issues.
• 4NF (Fourth Normal Form): Eliminates multi-valued dependencies.
• 5NF (Fifth Normal Form): Ensures tables cannot be further decomposed without losing data.
• 6NF (Sixth Normal Form): Handles temporal and historical data (advanced and rarely used).
WHY NORMALIZE?
 CustomerOrder
CustomerID Name OrderID OrderDate Product Total Amount
Laptop (Qty: 1, Price: 1200),
1 Anjan Dys 101 2025-02-01 Mouse (Qty: 2, Price: 25), 1300
Keyboard (Qty: 1, Price: 50)
Laptop (Qty: 1, Price: 1200)
2 Gee Ford 102 2025-02-02 1500
Monitor (Qty: 1, Price: 300)
3 Dyes Selle 103 2025-02-03 Printer (Qty: 1, Price: 200) 200

Example of a Non-Normalized Table

A non-normalized table contains redundant data, repeated groups, or multiple values

in a single column. These issues can cause inefficiencies, errors, and inconsistencies
in the database.
Problems with a Non-Normalized Table
CustomerOrder
CustomerID Name OrderID OrderDate Product Total Amount
Laptop (Qty: 1, Price: 1200),
1 Anjan Dys 101 2025-02-01 Mouse (Qty: 2, Price: 25), 1300
Keyboard (Qty: 1, Price: 50)
Laptop (Qty: 1, Price: 1200)
2 Gee Ford 102 2025-02-02 1500
Monitor (Qty: 1, Price: 300)
3 Dyes Selle 103 2025-02-03 Printer (Qty: 1, Price: 200) 200

Repeating Groups (Non-Atomic Values)

• Product column contains multiple values in one cell
• Violates the rule of atomicity
• Makes filtering, querying, and reporting difficult
Problems with a Non-Normalized Table
CustomerOrder
CustomerID Name OrderID OrderDate Product Total Amount
Laptop (Qty: 1, Price: 1200),
1 Anjan Dys 101 2025-02-01 Mouse (Qty: 2, Price: 25), 1300
Keyboard (Qty: 1, Price: 50)
Laptop (Qty: 1, Price: 1200)
2 Gee Ford 102 2025-02-02 1500
Monitor (Qty: 1, Price: 300)
3 Dyes Selle 103 2025-02-03 Printer (Qty: 1, Price: 200) 200

Data Redundancy
• Customer information and order date are repeated across rows
• Wastes storage space
• Increases the risk of inconsistent data
Problems with a Non-Normalized Table
CustomerOrder
CustomerID Name OrderID OrderDate Product Total Amount
Laptop (Qty: 1, Price: 1200),
1 Anjan Dys 101 2025-02-01 Mouse (Qty: 2, Price: 25), 1300
Keyboard (Qty: 1, Price: 50)
Laptop (Qty: 1, Price: 1200)
2 Gee Ford 102 2025-02-02 1500
Monitor (Qty: 1, Price: 300)
3 Dyes Selle 103 2025-02-03 Printer (Qty: 1, Price: 200) 200

Update Anomalies
• Changes (e.g., customer name) must be updated in multiple rows
• Time-consuming and error-prone
• May result in inconsistent records
Problems with a Non-Normalized Table
CustomerOrder
CustomerID Name OrderID OrderDate Product Total Amount
Laptop (Qty: 1, Price: 1200),
1 Anjan Dys 101 2025-02-01 Mouse (Qty: 2, Price: 25), 1300
Keyboard (Qty: 1, Price: 50)
Laptop (Qty: 1, Price: 1200)
2 Gee Ford 102 2025-02-02 1500
Monitor (Qty: 1, Price: 300)
3 Dyes Selle 103 2025-02-03 Printer (Qty: 1, Price: 200) 200

Insertion Anomalies
• Cannot add a product unless an order exists
• Forces unnecessary dependencies between data
• Reduces flexibility
Problems with a Non-Normalized Table
CustomerOrder
CustomerID Name OrderID OrderDate Product Total Amount
Laptop (Qty: 1, Price: 1200),
1 Anjan Dys 101 2025-02-01 Mouse (Qty: 2, Price: 25), 1300
Keyboard (Qty: 1, Price: 50)
Laptop (Qty: 1, Price: 1200)
2 Gee Ford 102 2025-02-02 1500
Monitor (Qty: 1, Price: 300)
3 Dyes Selle 103 2025-02-03 Printer (Qty: 1, Price: 200) 200

Deletion Anomalies
• Deleting an order may remove important customer or product data
• Leads to unintended data loss
• Causes database inconsistency
Problems with a Non-Normalized Table
CustomerOrder
CustomerID Name OrderID OrderDate Product Total Amount
Laptop (Qty: 1, Price: 1200),
1 Anjan Dys 101 2025-02-01 Mouse (Qty: 2, Price: 25), 1300
Keyboard (Qty: 1, Price: 50)
Laptop (Qty: 1, Price: 1200)
2 Gee Ford 102 2025-02-02 1500
Monitor (Qty: 1, Price: 300)
3 Dyes Selle 103 2025-02-03 Printer (Qty: 1, Price: 200) 200

Inefficient Querying
• Queries become complex due to non-atomic columns
• Slower performance
• Difficult to compute totals or summaries (e.g., total sales per product)
HOW TO NORMALIZE?
Step 1: First Normal Form (1NF)
Goal: Eliminate repeating groups and ensure atomic values.

CustomerOrder

CustomerID Name OrderID OrderDate ProductID ProductName Quantity Price

1 Anjan Dys 101 2025-02-01 501 Laptop 1 1200

1 Anjan Dys 101 2025-02-01 502 Mouse 2 25

1 Anjan Dys 101 2025-02-01 503 Keyboard 1 50

2 Gee Ford 102 2025-02-02 501 Laptop 1 1200

2 Gee Ford 102 2025-02-02 504 Monitor 1 300

3 Dyes Selle 103 2025-02-03 505 Printer 1 200

Step 2: Second Normal Form (2NF)
Goal: Remove partial dependencies by ensuring all non-key attributes depend on the entire
primary key.

Customer Order
CustomerID Name OrderID CustomerID OrderDate Price
1 Anjan Dys 101 1 2025-02-01 1300
2 Gee Ford 102 2 2025-02-02 1500
3 Dyes Selle 103 3 2025-02-03 200

OrderDetails
OrderDetailID OrderID ProductID ProductName Quantity Price
1 101 501 Laptop 1 1200
2 101 502 Mouse 2 25
3 101 503 Keyboard 1 50
4 102 501 Laptop 1 1200
5 102 504 Monitor 1 300
6 103 505 Printer 1 200
Step 3: Third Normal Form (3NF)
Goal: Eliminate transitive dependencies by ensuring non-key attributes depend only on the
primary key.
Customer Order Product
ProductID ProductName Price
CustomerID Name OrderID CustomerID OrderDate
501 Laptop 1200
1 Anjan Dys 101 1 2025-02-01 502 Mouse 25
503 Keyboard 50
2 Gee Ford 102 2 2025-02-02
504 Monitor 300
3 Dyes Selle 103 3 2025-02-03 505 Printer 200

OrderDetails
OrderDetailID OrderID ProductName Quantity Price
1 101 Laptop 1 1200
2 101 Mouse 2 25
3 101 Keyboard 1 50
4 102 Laptop 1 1200
5 102 Monitor 1 300
6 103 Printer 1 200
BASIC SQL CONCEPTS
BASIC SQL QUERIES (SELECT, FROM, WHERE)
SQL (Structured Query Language) is the standard language for interacting with relational
databases. The SELECT, FROM, and WHERE clauses are the cornerstone of any data
retrieval operation.

SELECT: Choose Your Data FROM: Identify the Source WHERE: Filter for Precision

Specify the columns you wish to Indicate the table (or tables) from Apply conditions to filter your results,
retrieve from a table. This dictates which the data will be extracted. This ensuring you only get the data that meets
what information will appear in your is where your chosen columns reside. specific criteria. You can use comparison
result set. operators (=, , <=, >=, !=) and logical
operators (AND, OR, NOT)

SELECT CustomerName, City FROM Customers WHERE CustomerID = 123

DATA MANIPULATION LANGUAGE (DML) COMMANDS
DML commands allow you to manage and modify the records within your database tables.

INSERT: Adding New UPDATE: Modifying DELETE: Removing

Records Existing Data Records
Used to add new rows of data Allows you to change values Permanently removes rows
into a table. Essential for in existing rows based on from a table. Use with caution
populating your database with specified conditions. Crucial to avoid accidental data loss.
fresh information. for data accuracy and
maintenance. DELETE FROM Orders
INSERT INTO Customers WHERE OrderID = 500;
(CustomerID, CustomerName, UPDATE Products SET
City) VALUES (1003, 'New Price = 25.00 WHERE
Customer', 'Paris'); ProductID = 508;
SORTING AND ORDERING (ORDER BY)
The ORDER BY clause is used to sort the result-set of a SELECT query in ascending or
descending order.

By default, ORDER BY sorts in ascending order (ASC). To sort from highest to lowest, you
must explicitly use the DESC keyword.

For instance, to view the most expensive products first:

SELECT ProductName, Price FROM Products ORDER BY Price DESC;
GROUPING AND AGGREGATION
GROUP BY: Grouping rows based on one or more columns.
• Example: SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID;

Aggregate Functions: Performing calculations on grouped data.

• COUNT(): Counting the number of rows.
• SUM(): Calculating the sum of a column.
• AVG(): Calculating the average of a column.
• MAX(): Finding the maximum value.
• MIN(): Finding the minimum value.
• Example: SELECT Category, AVG(Price) FROM Products GROUP BY Category;

HAVING: Filtering grouped data.

• Example:
SELECT CustomerID, COUNT(OrderID) FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) > 5;
JOINING TABLES (INNER JOIN, LEFT JOIN, RIGHT JOIN)
Combine rows from two or more tables based on a related column between them, providing a
richer dataset for analysis.

INNER JOIN LEFT JOIN

Returns only the rows that have matching values in both tables. Ideal Returns all rows from the left table, and the matching rows from the
for finding common data. right table. If no match, NULLs are used.

SELECT [Link], [Link] FROM Orders O INNER JOIN SELECT [Link], [Link] FROM Customers C LEFT
Customers C ON [Link] = [Link]; JOIN Orders O ON [Link] = [Link];

RIGHT JOIN FULL JOIN

Returns all rows from the right table, and the matching rows from the Returns all rows when there is a match in either the left or right table,
left table. Similar to LEFT JOIN, but prioritized from the right. effectively combining results from both. Useful for seeing all data,
matched or not.
SELECT [Link], [Link] FROM Products P
RIGHT JOIN Suppliers S ON [Link] = [Link]; SELECT [Link], [Link] FROM Customers C FULL
JOIN Orders O ON [Link] = [Link];