>Digital-flame

Denormalization is the process of intentionally introducing redundancy into a database by combining normalized tables — often for performance or simplicity reasons.

It’s the reverse of normalization, done not because of bad design, but to optimize read-heavy operations.

🎯 Why Use Denormalization?

1. Improve Read Performance
   • Reduces the number of joins needed in complex queries.
   • Speeds up data retrieval in analytics and reporting.
2. Simplify Queries
   • Fewer tables to query means easier SQL for developers.
3. Caching or Materialized Views
   • Pre-aggregated or summary data can be denormalized to improve performance.

❌ Downsides of Denormalization

• Data redundancy → more storage
• Update anomalies → harder to keep redundant data consistent
• Complex inserts/updates → more logic needed to maintain correctness

✅ Example: From Normalized to Denormalized

🔹 Normalized Structure

Customers

Orders

To get a customer’s name and product, you’d need a JOIN:

SELECT Orders.OrderID, Customers.Name, Orders.Product
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

🔸 Denormalized Structure

Orders

Now:

• ✅ No join needed
• ❌ But CustomerName is duplicated, and must be kept consistent across records

🧠 When to Use Denormalization?

Use it carefully when:

• Your database is read-heavy
• Joins are slowing down performance
• Data doesn’t change often (e.g., reporting systems)
• You’re working with NoSQL or data warehouses

📘 Third Normal Form (3NF)

A table is in 3NF if:

1. ✅ It is in 2NF
2. ✅ It has no transitive dependencies — i.e., non-key columns do not depend on other non-key columns

🔍 What is a Transitive Dependency?

A transitive dependency happens when:

A non-key column depends on another non-key column, which in turn depends on the primary key.

In short:
Primary Key → A → B
Then B is transitively dependent on the primary key via A → ❌ violates 3NF.

❌ Example: Not in 3NF

Table: Employees

• Primary key: EmpID
• DeptName depends on DeptID
• DeptID depends on EmpID

So:

• EmpID → DeptID → DeptName → ❌ transitive dependency
• This violates 3NF

✅ Convert to 3NF: Remove Transitive Dependency

Table 1: Employees

Table 2: Departments

• ✅ Now DeptName depends only on DeptID
• ✅ No transitive dependency in Employees
• ✅ The schema is in 3NF

🧠 Summary of Normal Forms:

Absolutely! Let’s walk through a clear example where a composite key is used, and no partial dependency exists — meaning the table is in 2NF.

✅ Example: No Partial Dependency (2NF is satisfied)

Table: ExamResults

• Composite Primary Key: (StudentID, ExamID)
• Non-key column: Score

🔍 Analysis:

• Score depends on both StudentID and ExamID together.
  • You need both values to uniquely determine a Score.
• Score does not depend on only StudentID (a student has many scores)
• Score does not depend on only ExamID (many students take the same exam)

✅ Therefore, there is no partial dependency — all non-key columns fully depend on the whole key.

➡️ This table is in 2NF.

📗 Second Normal Form (2NF)

2NF (Second Normal Form) builds on 1NF and adds one more rule:

A table is in 2NF if:

1. It is in 1NF, and
2. All non-key attributes are fully functionally dependent on the entire primary key.

🔍 What Does “Fully Dependent” Mean?

If the primary key is composite (made of two or more columns), then:

• A non-key column must depend on both parts of the key, not just one.

❌ Example: Not in 2NF

Table: StudentCourses

• Composite Primary Key: (StudentID, CourseID)
• Problem:
  • StudentName depends only on StudentID
  • CourseName depends only on CourseID

❌ These are partial dependencies, so this table is not in 2NF.

✅ Convert to 2NF: Split into Two Tables

Table: Students

Table: Courses

Table: StudentCourses

• ✅ Now, all non-key attributes fully depend on their entire primary key.
• ✅ This structure is in 2NF.

💡 Summary:

📘 First Normal Form (1NF)

1NF (First Normal Form) is the basic level of normalization.
A table is in 1NF if:

1. All columns contain atomic (indivisible) values
2. Each row is unique
3. Each column contains values of a single type
4. There are no repeating groups or arrays

❌ Example: Not in 1NF

Table: Students

• ❌ The Subjects column has multiple values in one field → not atomic
• This is not in 1NF

✅ Example: In 1NF

To convert to 1NF, we split multivalued fields into separate rows:

Table: StudentSubjects

• ✅ Now each field is atomic
• ✅ No repeating groups
• ✅ 1NF is satisfied

🧠 Tip to Remember:

“No lists or arrays in cells, just one value per column per row.”

📚 What is Normalization in Databases?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large, complex tables into smaller, related tables and defining relationships between them.

🎯 Goals of Normalization:

• Eliminate duplicate data (e.g., repeating groups or columns)
• Ensure data dependencies make sense
• Simplify updates, inserts, and deletes
• Prevent anomalies (update, insert, delete anomalies)

🧱 Normal Forms (NF) – The Stages of Normalization:

Each level is stricter than the previous. The first three are most commonly applied.

✅ 1NF (First Normal Form)

• Eliminate repeating groups
• Ensure atomic values (each field contains only one value)

✅ Good:

Hobbies: ["reading", "biking"]
❌ Bad:
Hobbies: "reading, biking"

✅ 2NF (Second Normal Form)

• Must be in 1NF
• Remove partial dependencies (i.e., no non-key column depends on part of a composite key)

✅ Example: Split a table where some data only depends on part of the key.

✅ 3NF (Third Normal Form)

• Must be in 2NF
• Remove transitive dependencies (i.e., non-key columns should not depend on other non-key columns)

✅ Example: Instead of storing city_name and city_zip in the same table, move city_zip to a separate table.

🔄 Example Before and After Normalization:

❌ Unnormalized Table

✅ After Normalization

Customers Table

Orders Table

🧠 Why Normalize?

A foreign key is a column (or set of columns) in one table that refers to the primary key in another table. It is used to establish a relationship between two tables and enforce referential integrity.

🔁 Purpose of a Foreign Key:

• To link related data across tables.
• To prevent invalid data from being inserted (e.g., you can’t reference a customer that doesn’t exist).
• To enable joins in queries.

📊 Example:

Table: Customers

Table: Orders

In this case:

• customer_id in Orders is a foreign key that references customer_id in Customers.

🔐 Foreign Key Constraints:

1. ON DELETE CASCADE – Delete related rows automatically.
2. ON DELETE SET NULL – Set foreign key to NULL when the parent is deleted.
3. ON DELETE RESTRICT / NO ACTION – Prevent deletion if references exist.

🛠️ SQL Example:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product VARCHAR(100),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

✅ Key Properties:

A primary key is a column or a set of columns in a table that uniquely identifies each row.
It ensures that:

• No two rows have the same value in the primary key.
• The key value is never NULL.

✅ Example:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Here, customer_id is the primary key, ensuring each customer has a unique identifier.

📌 Criteria for Choosing a Primary Key:

1. Uniqueness
   • It must uniquely identify every row in the table.
2. Non-nullability
   • It must never be NULL (every row must have a value for the key).
3. Immutability
   • The value should rarely or never change. Changing primary key values can break relationships.
4. Minimality
   • Use as few columns as possible (prefer simple over composite keys if possible).
5. Stability and simplicity
   • Prefer short, numeric keys (like integers or UUIDs) over long text fields (like email or names).

🚫 Bad Choices for Primary Keys:

• Names (can change and are not unique)
• Emails (can change, even though often unique)
• Phone numbers (can change)
• Composite keys when a surrogate key would do better

🔑 1. Simple Key

• A single attribute (column) that uniquely identifies a record.
• Example:
  In a Users table, user_id is a simple key if it alone uniquely identifies each user.

user_id: 101, 102, 103

🧩 2. Composite Key

• A combination of two or more attributes that together uniquely identify a record.
• Used when no single column can serve as a unique identifier.
• Example:
  In a CourseRegistrations table, the combination of student_id and course_id may form a composite key.

(student_id = 10, course_id = "CS101")

🎯 3. Candidate Key

• Any attribute or combination of attributes that can uniquely identify a row in a table.
• A table can have multiple candidate keys, but only one becomes the primary key.
• Example:
  In a Users table:
  • user_id (unique number)
  • email (also unique)
  Both are candidate keys.

🔁 4. Alternate Key

• A candidate key that was not chosen as the primary key.
• It is still unique and may be used for lookup purposes.
• Example:
  If user_id is the primary key, then email becomes the alternate key.

Summary Table

The relational data model is a way to organize data in a database using tables (also called relations). It was introduced by E. F. Codd in 1970 and is the foundation of relational databases like MySQL, PostgreSQL, and Oracle.

🧱 Key Concepts:

1. Relation (Table)
   • A set of rows and columns.
   • Each table represents a type of entity (e.g., Users, Orders).
2. Tuple (Row)
   • A single record in a table.
   • Represents one instance of the entity.
3. Attribute (Column)
   • A field or property of the entity (e.g., Name, Email).
   • Each column has a data type (e.g., INTEGER, VARCHAR).
4. Primary Key
   • A column (or group of columns) that uniquely identifies each row in a table.
5. Foreign Key
   • A column that links to the primary key of another table, establishing a relationship.

📊 Example: Two Related Tables

Customers

Orders

In this example:

• CustomerID in Customers is the primary key.
• CustomerID in Orders is a foreign key linking to Customers.

✅ Benefits of the Relational Model:

• Data integrity via keys and constraints
• Easier querying using SQL
• Normalization helps reduce data redundancy
• Strong theoretical foundation