Data Modeling Concepts

Study Notes

Data modeling is the process of creating a conceptual representation of an organization's data structures and relationships.
Goals:
- To create a consistent and integrated view of the organization's data
- To provide a common understanding of the data among stakeholders
- To facilitate communication between business users and IT professionals
Types of data models:
- Conceptual data model: High-level, abstract representation of the data
- Logical data model: Detailed, technology-independent representation of the data
- Physical data model: Detailed, technology-dependent representation of the data
Data modeling concepts:
- Entities: Objects or concepts that have existence and can be described with attributes
- Attributes: Characteristics or features of entities
- Relationships: Associations between entities
- Constraints: Rules that govern the relationships between entities

Query optimization is the process of improving the efficiency of a query to minimize its execution time and resource usage.
Goals:
- To reduce the response time of queries
- To minimize the resource usage (e.g., CPU, I/O, memory)
- To improve the overall system performance
Query optimization techniques:
- Query rewriting: Rewriting the query to use more efficient algorithms or data structures
- Indexing: Creating indexes on frequently accessed columns to speed up query execution
- Caching: Storing frequently accessed data in memory to reduce I/O operations
- Join optimization: Optimizing the order and method of joining tables to reduce the number of rows being joined
- Subquery optimization: Optimizing subqueries to reduce the number of times they are executed
Query optimization factors:
- Query syntax and structure
- Database schema and design
- Indexing and caching strategies
- System resources and configuration
- Data distribution and statistics

Data modeling creates a conceptual representation of an organization's data structures and relationships.
Goals of data modeling include:
- Creating a consistent and integrated view of the organization's data
- Providing a common understanding of the data among stakeholders
- Facilitating communication between business users and IT professionals
There are three types of data models:
- Conceptual data model: High-level, abstract representation of the data
- Logical data model: Detailed, technology-independent representation of the data
- Physical data model: Detailed, technology-dependent representation of the data
Key data modeling concepts include:
- Entities: Objects or concepts that have existence and can be described with attributes
- Attributes: Characteristics or features of entities
- Relationships: Associations between entities
- Constraints: Rules that govern the relationships between entities

Query optimization improves the efficiency of a query to minimize its execution time and resource usage.
Goals of query optimization include:
- Reducing the response time of queries
- Minimizing resource usage (e.g., CPU, I/O, memory)
- Improving overall system performance
Query optimization techniques include:
- Query rewriting: Rewriting the query to use more efficient algorithms or data structures
- Indexing: Creating indexes on frequently accessed columns to speed up query execution
- Caching: Storing frequently accessed data in memory to reduce I/O operations
- Join optimization: Optimizing the order and method of joining tables to reduce the number of rows being joined
- Subquery optimization: Optimizing subqueries to reduce the number of times they are executed
Factors that affect query optimization include:
- Query syntax and structure
- Database schema and design
- Indexing and caching strategies
- System resources and configuration
- Data distribution and statistics