CH13

# Comprehensive Notes: Database Design & Management

## Learning Objectives

### 13.1 Understanding Databases

- Definition: A formally defined, centrally controlled data repository for multiple applications

- Key Objectives:

- Enable data sharing across applications

- Maintain accuracy and consistency

- Ensure long-term data availability

- Support evolving user needs

- Allow personalized data views independent of physical storage

### 13.2 Fundamental Data Concepts

- Reality: The actual world being modeled

- Data: Representations of real-world entities/events

- Metadata: "Data about data" describing structure and meaning

- Entities: Distinct objects/events (e.g., customers, orders)

- Relationships: Connections between entities (1:1, 1:M, M:N)

### 13.3 Efficient Data Storage via Normalization

- Purpose: Eliminate redundancy and anomalies

- Process:

- 1NF: Remove repeating groups

- 2NF: Eliminate partial dependencies

- 3NF: Remove transitive dependencies

- Benefits: Data integrity, storage efficiency, easier maintenance

### 13.4 Database Design Guidelines

- Key Design Principles:

- Each entity → separate table

- Single-field responsibility (no duplicate data)

- CRUD operations for all tables

- Integrity Constraints:

- Entity: Primary keys cannot be null

- Referential: Foreign keys must match primary keys

- Domain: Valid value ranges/forms

### 13.5 Data Warehousing & Business Intelligence

- Data Warehouse Features:

- Subject-oriented (vs. transaction-focused)

- Summarized, historical data

- Optimized for OLAP (complex queries)

- Business Intelligence (BI):

- Combines data storage, knowledge management, and analytics

- Supports decision-making through:

- Slice-and-dice drilldowns

- Ad-hoc queries

- Real-time analysis

- Forecasting models

### 13.6 Emerging Data Technologies

- Data Lakes:

- Store raw, unstructured data

- Flexible schema vs. rigid warehouse structure

- Blockchains:

- Immutable, decentralized ledgers

- Applications: Supply chain tracking, secure transactions

- Permissioned blockchains for enterprise use

- Web 3.0:

- Decentralized data ownership

- Blockchain-based architecture

---

## Core Concepts in Detail

### Database Models

| Model Type | Structure | Use Case |

|-----------------------|----------------------------------------|----------------------------------|

| Relational | Tables with rows/columns | Most common (SQL databases) |

| Hierarchical | Tree-like parent-child relationships | Legacy systems |

| Network | Graph with flexible connections | Complex relationships |

### Normalization Process

Unnormalized → 1NF:

- Problem: Repeating groups (e.g., multiple order items in one record)

- Solution: Create separate ORDER_ITEMS table

1NF → 2NF:

- Problem: Partial dependencies (e.g., product description relying only on productID part of composite key)

- Solution: Move dependent attributes to new table

2NF → 3NF:

- Problem: Transitive dependencies (e.g., warehouse location depending on warehouseID which depends on orderID)

- Solution: Isolate dependent attributes

### Data Warehouse vs. Data Lake

| Characteristic | Data Warehouse | Data Lake |

|------------------------|----------------------------------|----------------------------------|

| Data Structure | Processed, structured | Raw, any format |

| Schema | Schema-on-write | Schema-on-read |

| Users | Business analysts | Data scientists/engineers |

| Processing | OLAP (analytical) | Batch/real-time processing |

| Cost | High (cleaning/transforming) | Lower (store-as-is) |

### Business Intelligence Techniques

1. Data Mining Patterns:

- Associations: Frequently co-occurring items (market basket analysis)

- Sequences: Time-based patterns (purchase paths)

- Clustering: Natural groupings (customer segmentation)

- Trends: Longitudinal changes (sales patterns)

2. Analytical Methods:

- Decision trees

- Neural networks

- Fuzzy logic

- Data visualization

### Blockchain Characteristics

- Decentralization: No single point of control/failure

- Immutability: Tamper-evident record keeping

- Consensus Mechanisms: Proof-of-Work/Proof-of-Stake validation

- Smart Contracts: Self-executing agreement terms

---

## Practical Considerations

### Database Security

- Key Risks:

- SQL injection

- Unauthorized access

- Data breaches

- Mitigation Strategies:

- Encryption (at rest and in transit)

- Regular audits

- Disaster recovery planning

### Common Data Anomalies

| Anomaly Type | Description | Solution |

|------------------------|------------------------------------------|----------------------------------|

| Insertion | Cannot add due to missing key data | Surrogate keys, proper null handling |

| Deletion | Unintended loss of related data | Cascading rules |

| Update | Inconsistent changes across records | Normalization |

### Physical Design Considerations

- Denormalization: Intentional redundancy for performance

- Indexing: Faster query execution on key fields

- Partitioning: Dividing large tables for manageability

---

## Key Takeaways

1. Database Design: Proper normalization (3NF) ensures efficiency and integrity

2. Modern Data Systems: Warehouses (structured analysis) vs. lakes (flexible storage)

3. Analytics Evolution: BI and data mining transform raw data into actionable insights

4. Emerging Tech: Blockchains enable secure, transparent transactions

5. User-Centric Approach: Web 3.0 prioritizes data ownership and decentralization

By mastering these concepts, professionals can design systems that balance performance, integrity, and scalability while leveraging cutting-edge data technologies.