6. Normalization, Cascades

Normalization helps prevent data redundancy and ensures efficient database organization.

Normalization Rules and Forms

The three primary normalization forms are essential, though additional forms exist.

• If two columns can be stored as one and later split, they should be combined.

  Example: Instead of storing name and surname separately, they can be stored as fullname and split when needed.

• First Normal Form (1NF): Each table must have a primary key, and all columns should be dependent on it.

• Second Normal Form (2NF): If a column's data can be derived from another column, it should not be stored separately.

  Example: A student table should not store the group's average score since it can be calculated dynamically.

Following normalization rules helps avoid anomalies in database design.

ON DELETE & ON UPDATE Actions

These constraints define how related records behave when a referenced record is modified or deleted.

ON DELETE

• CASCADE: Deletes related records when the referenced record is deleted.Example: If an Instagram user is deleted, their posts are also deleted.

• NO ACTION: Prevents deletion if related records exist.

• SET NULL: Sets foreign key values to NULL when the referenced record is deleted.

• SET DEFAULT: Resets foreign key values to a default value.

ON UPDATE

• CASCADE: Updates related records when the referenced record changes.

  Example: If a user’s ID is updated, all references to that ID are also updated.

• NO ACTION: Prevents updates to referenced records.

SQL Query Writing Order

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY

SQL Query Execution Order

1. FROM – Identifies the data source.

2. WHERE – Filters records based on conditions.

3. GROUP BY – Groups records with similar values.

4. HAVING – Filters grouped records.

5. SELECT – Chooses columns to display.

6. ORDER BY – Sorts the final result.

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version