📚 Main Topics

1. Overview of Assignment Five

   • Continuation of Assignment Four focused on Data Warehousing.
   • Development of a smaller Data Mart as a test platform.

2. CFO's Request

   • Expansion of data model functionality to include detailed sales reports.
   • Metrics to be included: product, customer, sales representative, time, and location.

3. Data Normalization

   • Breakdown of time into year, quarter, month, week, day, and seconds.
   • Expansion of location attributes to include region, state, city, and store.

4. Deliverables

   • Use of Microsoft SQL Server for the assignment.
   • Requirements for unique records in various tables:
     • 25 unique records in the sale table.
     • At least 5 unique records in all other tables.
     • 15 records in the person table (can be split between customers and sales representatives).

5. Differences Between Assignments Four and Five

   • Introduction of City ID in the store table.
   • Normalization of location into a structured hierarchy (region, state, city, store).

6. Database Design Concepts

   • Understanding of fact tables and dimension tables.
   • Distinction between star schema and snowflake schema.
   • Importance of primary keys and foreign keys in database design.

✨ Key Takeaways

• Data Mart as a Testing PlatformSmaller businesses often use a Data Mart to test data warehousing capabilities before investing in a full data warehouse.
• Importance of Data VolumeA substantial amount of data is necessary to effectively test the data model and design.
• NormalizationBreaking down data into more manageable and structured formats is crucial for effective data analysis and reporting.
• Schema UnderstandingFamiliarity with different database schemas (star and snowflake) is essential for proper data organization.

🧠 Lessons Learned

• Practical ApplicationHands-on experience with SQL Server is critical for understanding database design and implementation.
• Data RelationshipsRecognizing the relationships between different data entities (e.g., parent-child relationships in tables) is vital for effective database design.
• Attention to DetailEnsuring that all tables have the correct primary and foreign keys is essential for maintaining data integrity and referential integrity in the database.