DAY 17

🔗 Data Modeling & Relationships

Ghaziabad Mohan Nagar

Design Drafted by: HR Prateek Sharma

🔑 Key Concepts & Definitions

🔢

Distinct

Shows unique values with count

Returns all different values in a column, including those that appear multiple times. Only shows the number of distinct values, not how many times each appears.

DISTINCT = Repeat Allowed
(Only counts unique types)

Example: [1, 2, 2, 3] → DISTINCT gives 3 values

1️⃣

Unique

Only values that appear once

Returns only those values that appear exactly one time in the dataset. Excludes any value that is repeated.

UNIQUE = Present Only Once
(No duplicates allowed)

Example: [1, 2, 2, 3] → UNIQUE gives [1, 3]

🔐

Primary Key

When Distinct = Unique

A column becomes a primary key when every value in it is both distinct AND unique - meaning each value appears exactly once with no duplicates.

Primary Key = Distinct AND Unique
(Each value appears only once)

Example: Employee_ID, Student_ID, Order_ID

❓

Else If Statement

Runs when IF condition fails

ELSE IF is executed only when the initial IF condition evaluates to FALSE. It provides an alternative condition to check.

IF (condition1) → Execute
ELSE IF (condition2) → Execute
ELSE → Default

📦

Shipment Calculation

Time between order and delivery

Shipment duration is calculated by subtracting the order date from the ship date to determine processing time.

Shipment = Ship Date - Order Date

Example: Ship: Jan 10, Order: Jan 5 → 5 days

🔗

Data Modeling

Creates relationships between tables

Data modeling establishes connections and relationships between multiple tables in a database, enabling complex queries and analysis across related data.

Purpose:

Organize data efficiently, reduce redundancy, maintain data integrity, and enable powerful analytics through table relationships.

🔄 Four Types of Relationships

∞→1

Many-to-One

Multiple records in one table relate to a single record in another table.

Example: Many orders → One customer

1→∞

One-to-Many

One record in a table relates to multiple records in another table.

Example: One customer → Many orders

1→1

One-to-One

One record in a table relates to exactly one record in another table.

Example: One employee → One passport

∞→∞

Many-to-Many

Multiple records in one table relate to multiple records in another table.

Example: Many students → Many courses

⭐ Database Schemas

📊 Star Schema vs Snowflake Schema

⭐ Star Schema

⭐

Central Fact Table Structure

Dimension: Customer

↓

FACT TABLE
(Sales)

↓

Dimension: Product

↓

Dimension: Time

↓

Dimension: Location

Characteristics:

• One central fact table
• Multiple dimension tables
• Direct connections to fact table
• Denormalized structure
• Faster query performance

❄️ Snowflake Schema

❄️

Normalized Dimension Structure

Sub-Dimension: City

↓

Dimension: Location

↓

FACT TABLE
(Sales)

↓

Dimension: Product

↓

Sub-Dimension: Category

Characteristics:

• Dimension tables have sub-dimensions
• Normalized structure
• Indirect connections via dimensions
• Saves storage space
• More complex queries

⚠️ Important Rule:

The fact table does NOT directly connect to sub-dimension tables. In a snowflake schema, the fact table connects only to dimension tables, and dimension tables connect to their sub-dimension tables.

📋 Schema Comparison Table

Aspect	⭐ Star Schema	❄️ Snowflake Schema
Structure	Simple, denormalized	Complex, normalized
Dimension Tables	Connect directly to fact table	Connect to sub-dimensions
Query Performance	Faster (fewer joins)	Slower (more joins)
Storage Space	More space (redundancy)	Less space (normalized)
Complexity	Easy to understand	More complex
Maintenance	Easier to maintain	More difficult
Best For	Quick queries, reporting	Large datasets, storage efficiency

💡 Key Learning Points

🔢 Distinct vs Unique

🔑 Primary Key

❓ Else If Logic

📦 Shipment Formula

🔗 Data Modeling

∞→1 Many-to-One

1→∞ One-to-Many

1→1 One-to-One

∞→∞ Many-to-Many

⭐ Star Schema

❄️ Snowflake Schema

Data Modeling & Relationships - Day 17

Master Database Design & Schema Architecture

Ghaziabad Mohan Nagar

Designed by HR Prateek Sharma

Last Updated: December 6, 2025