Curriculum
Course: SQL
Login

Curriculum

SQL

1.1 SQL Overview

0/7

ER Diagram

0/7

Normalization and Denormalization

0/6

0/0
Text lesson

What is normalization

Normalization

A normalization process removes redundant (repeated) data from the database by dividing the table in a well-defined manner and ensuring data integrity. In essence, normalization involves dividing large tables into smaller, manageable ones and defining relationships between them. By having each table contain data related to one theme or entity, duplicate data is minimized across tables.The goal of SQL normalization is to make sure that a database is organized logically and that data dependencies are understood. It reduces redundancy and makes sure that a database is built efficiently.

The commonly used normal forms are outlined below:

First Normal Form (1NF): It is a very basic level of normalization form. Separate related data into separate tables by eliminating duplicate columns.

Key functionalities of 1NF

  • Atomicity
  • uniqueness
  • Consistent Data Type
  • Primary Key

Second Normal Form (2NF): Separate data subsets that are applicable to multiple rows of a table.

Key functionalities of 2NF

  • Elimination of Partial Dependencies
  • Full Functional Dependency
  • Candidate Keys
  • Atomic Columns

Third Normal Form (3NF): Columns without a primary key should be excluded

Key functionalities of 3NF

  • Elimination of Transitive Dependencies
  • Full Functional Dependency
  • No Transitive Dependencies
  • Improved Data Integrity
When to apply Normalization?

Normalizing a database happens during its design process. A database must be designed from scratch when developing a new system. The database should be refactored to optimize performance and efficiency. In addition, when dealing with data anomalies or inconsistencies, a systematic troubleshooting process should be followed.

Importance of normalization

A database’s normalization is a fundamental concept. Here are some reasons why it is important

Data Integrity: The integrity of data ensures that information remains accurate, consistent, and reliable throughout its lifecycle.

Fundamental elements
Data Integrity
Should be Consistency Accuracy Reliability
Reason Prevent Conflict Accurately Illustrates Authorized 

Tools of Data Integrity:

  • Implement integrity constraints
  • Verify data
  • Maintain sequence of database functions
  • Record data modifications
 

Reduced Redundancy: the process of removing duplicate or unnecessary data

Fundamental elements
Reduced Redundancy
Should be Minimize Duplicate Date Minimize Error
Reason Improve Storage Efficiency Rationalised Updates 

Tools of reduced redundancy:

  • Apply singel entity concept
  • Develop relationship among the entities
  • Utilizing Keys field
  • Eliminating duplicate or unnecessary table or data
 

Enhanced Data Retrieval: Allow for system optimization and simplify the query processing.

Fundamental elements
Enahanced Data Retrieval
Should be effective query technique Simplifies queries
Reason Optimized database structure Reduces the complexity

Tools of Enhanced Data Retrieval:

  • Detailed Relationship
  • Minimize attribute
  • Reduce interconnected entity
  • Placed data into distinct tables
 

Scalability and Maintainable:

Fundamental elements
Scalability and Maintainable
Should be Handling performance and growth  Adaptable
Reason Fast and efficient System runs smoothly

Tools of Scalability and Maintainable:

  • Monitor performance and optimized queries
  • Divide column (Vertical partitioning) and row (horizontal partitioning)
  • Scan and upgrade indexes 
  • Allow multiple server
  • Enforce load balancers
  • Utilize caching technic
  • Regular backup and implement security
 

Elimination of Anomalies: When data is inserted, updated, or deleted, a database structure needs to be designed so that specific problems do not occur. Normalization is key to eliminating anomalies

Fundamental Elements
Elimination of Anomalies
Should be Establish a framework for data entry, updates, and deletions, and implement it. Data Validation
Reason

Prevent error

  Identify faults and fraud detection

Tools of Elimination of Anomalies

  • Analyze attributes and entity
  • Applying data validation rule
  • Data cleaning
  • Data Review
  • Established standard structures
 

Enhanced Collaboration

Fundamental Elements
Enhanced Collaboration
Should be Transparent Structure Standardization
Reason Easy to understand with effectively Motivate to upgrade

Tools of Enhanced Collaboration

  • Recognize business queries
  • facilitate code reviews
  • encourage to discussions about database changes
  • ensuring that all modifications are align with best practices
 

 

deftnote