Databases and Normalization Concepts

Study Notes

Databases are structured collections of data stored electronically.
They facilitate the efficient storage, retrieval, and manipulation of data.
Examples include simple systems like Excel spreadsheets and complex systems like SQL databases.

The process of organizing data in a database to reduce redundancy and improve data integrity.
It's a structured approach to improve efficiency in databases.
Its main goals are:
- Eliminate Redundancy
- Ensure Data Integrity
- Optimize Queries

A methodology for designing a database that is structured to minimize redundancy and dependency.
It involves:
- Defining Tables: Identifying the tables needed to store data, with columns and data types for each table.
- Defining Relationships: Establishing relationships between tables, utilizing foreign keys, to relate data across tables.
- Normalization: Applying normalization rules for efficient database design and minimizing redundancy.

A visual representation of the data models and relationships within a database.
Consists of:
- Entities: Represent tables.
- Attributes: Represent columns within tables.
- Relationships: Define how tables relate to one another.
They are essential during the database design phase, helping with:
- Visualizing Database Structure: Providing a graphical overview of interconnected data elements.
- Communicating Design: Acting as a communication tool for understanding the database design.
- Guiding Database Creation: Serving as blueprints for creating the actual database.

Normalization ensures data consistency and quality, preventing redundancy.
This is crucial in data warehouses where data is integrated from multiple sources.
Normalization and relational models optimize data storage, important for handling large datasets.
A well-structured relational model facilitates efficient reporting and analytics.

Denormalization in Data Warehousing:
- While normalization is central in OLTP (Online Transaction Processing) databases, data warehouses sometimes utilize denormalization for improved query performance.
- This involves combining tables to reduce the number of joins needed, speeding up read operations.
- This approach is common in data warehouses that are optimized for read-heavy operations, rather than write-heavy operations.
Design Complexity:
- Both ERDs and relational modeling demand careful planning and understanding of business requirements.
- Over-normalization can result in complex queries and slower performance.
- Under-normalization can lead to data inconsistency.