# 🛒 E-Commerce Database Design & Normalization
### From Flat File to 3NF — Eliminating Data Anomalies in a Magazine Subscription System

---

## 📌 Executive Summary

| | |
|---|---|
| **Objective** | Redesign a denormalized flat-table database for an online magazine subscription service to eliminate update, insert, and delete anomalies. |
| **Problem** | A single-table schema caused duplicated customer data, inconsistent category labeling, and inability to add a magazine without a subscriber. |
| **Solution** | Decomposed into 4 normalized relations in Third Normal Form (3NF) by identifying and removing all partial and transitive functional dependencies. |
| **Business Impact** | Reduced duplicated entity storage risk, enabled accurate subscriber counts per category, and eliminated the orphaned-record risk caused by the original flat table. |
| **Skills** | Conceptual modeling (ERD), functional dependency analysis, 1NF → 2NF → 3NF normalization, relational schema design |

---

## 💼 Business Problem

An online magazine subscription platform stored all data in a single table:

```
MID | Title | Category | Market_Size | Sub_Start | Sub_End | CustID | Customer_Email
```

**The anomalies this created:**

- **Update anomaly:** Changing a magazine's category required updating every row for every subscriber to that magazine.
- **Insert anomaly:** A new magazine could not be added until at least one customer subscribed.
- **Delete anomaly:** Deleting the last subscriber to a magazine wiped the magazine record entirely.

The business needed a schema that could independently manage magazines, customers, categories, and subscriptions.

---

## 🗂️ Starting Point: Functional Dependency Analysis

Before drawing any diagram, functional dependencies in the original table were mapped:

```
FD1: MID → Title, Category, Market_Size
     (The magazine ID fully determines all magazine attributes)

FD2: Category → Market_Size
     (TRANSITIVE: Market_Size depends on Category, not directly on MID)

FD3: CustID → Customer_Email
     (The customer ID fully determines the customer's email)

FD4: MID, CustID → Sub_Start, Sub_End
     (The subscription record requires BOTH the magazine AND the customer)
```

**Violation identified:**
- FD2 is a **transitive dependency** — `Market_Size` is determined by `Category`, not by `MID` directly. This violates 3NF.
- FD3 means customer data partially repeats for every subscription row a customer has. This violates 2NF when viewed in the full composite context.

---

## 🧩 Normalization Steps

### Step 1 → First Normal Form (1NF)
The original table already satisfied 1NF (atomic values, single-valued cells, unique rows).
No structural changes needed at this step.

---

### Step 2 → Second Normal Form (2NF)
**Remove partial dependencies.** Every non-key attribute must depend on the *whole* primary key, not just part of it.

The composite key for the original table is `{MID, CustID}`. But:
- `Title`, `Category`, `Market_Size` depend only on `MID` — not on `CustID`.
- `Customer_Email` depends only on `CustID` — not on `MID`.

These partial dependencies are resolved by splitting into separate tables:

```
Magazine(MID, Title, Category, Market_Size)
Customer(CustID, Customer_Email)
Subscription(MID, CustID, Sub_Start, Sub_End)   ← only composite-dependent attributes remain
```

---

### Step 3 → Third Normal Form (3NF)
**Remove transitive dependencies.** Every non-key attribute must depend *directly* on the primary key, not on another non-key attribute.

In the `Magazine` table: `Market_Size` depends on `Category`, not on `MID`.
This transitive chain (`MID → Category → Market_Size`) must be broken.

Split into:

```
Category(CategoryID, Category_Name, Market_Size)
Magazine(MID, Title, CategoryID)                 ← FK reference to Category
```

---

## 📐 Final Relational Schema (3NF)

```
Category(CategoryID, Category_Name, Market_Size)
         ─────────
               ↑ FK
Magazine(MID, Title, CategoryID)
         ───
               ↑ FK          ↑ FK
Subscription(MID, CustID, Sub_Start, Sub_End)
                  ─────────────────
Customer(CustID, Customer_Email)
         ───────
```

**4 relations, all in 3NF.** Each non-key attribute depends on the key, the whole key, and nothing but the key.

---

## 📐 Entity Relationship Diagram (Conceptual Model)

```
┌──────────────┐          ┌────────────────┐         ┌─────────────┐
│   CATEGORY   │  1    M  │    MAGAZINE    │  M    M │  CUSTOMER   │
│ ─────────── │─────────>│ ────────────── │<───────>│ ─────────── │
│ CategoryID  │          │ MID            │         │ CustID      │
│ CategoryName│          │ Title          │         │ Email       │
│ MarketSize  │          │ CategoryID(FK) │         └─────────────┘
└──────────────┘          └────────────────┘
                                                          │
                                               ┌──────────────────┐
                                               │   SUBSCRIPTION   │
                                               │ ──────────────── │
                                               │ MID (FK)         │
                                               │ CustID (FK)      │
                                               │ Sub_Start        │
                                               │ Sub_End          │
                                               └──────────────────┘
```

**Cardinality rules:**
- One `Category` can classify many `Magazines` (1:M).
- One `Magazine` can have many `Subscriptions`; one `Customer` can have many `Subscriptions` (M:M resolved via associative entity `Subscription`).

---

## 🧩 SQL: Creating the Normalized Schema

```sql
-- ─────────────────────────────────────────────────────────────────
-- DDL: Normalized Magazine Subscription Schema (3NF)
-- Technique: Primary keys, foreign key constraints, referential integrity
-- ─────────────────────────────────────────────────────────────────

CREATE TABLE Category (
    CategoryID      NUMBER PRIMARY KEY,
    Category_Name   VARCHAR2(50) NOT NULL,
    Market_Size     VARCHAR2(20)
);

CREATE TABLE Magazine (
    MID             VARCHAR2(10) PRIMARY KEY,
    Title           VARCHAR2(100) NOT NULL,
    CategoryID      NUMBER,
    CONSTRAINT fk_mag_cat FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
);

CREATE TABLE Customer (
    CustID          NUMBER PRIMARY KEY,
    Customer_Email  VARCHAR2(100) NOT NULL
);

CREATE TABLE Subscription (
    MID             VARCHAR2(10),
    CustID          NUMBER,
    Sub_Start       DATE NOT NULL,
    Sub_End         DATE,
    CONSTRAINT pk_sub  PRIMARY KEY (MID, CustID),
    CONSTRAINT fk_sub_mag  FOREIGN KEY (MID)    REFERENCES Magazine(MID),
    CONSTRAINT fk_sub_cust FOREIGN KEY (CustID) REFERENCES Customer(CustID)
);
```

---

## 🧩 SQL: Business Queries Enabled by the New Schema

```sql
-- ─────────────────────────────────────────────────────────────────
-- Q1: How many active subscribers does each magazine category have?
-- Previously impossible in the flat table without row duplication errors
-- ─────────────────────────────────────────────────────────────────

SELECT
    c.Category_Name,
    COUNT(DISTINCT s.CustID)    AS active_subscribers
FROM Category c
JOIN Magazine m   ON c.CategoryID = m.CategoryID
JOIN Subscription s ON m.MID = s.MID
WHERE s.Sub_End >= SYSDATE OR s.Sub_End IS NULL
GROUP BY c.Category_Name
ORDER BY active_subscribers DESC;

-- ─────────────────────────────────────────────────────────────────
-- Q2: Which customers have overlapping subscriptions to the same category?
-- Useful for detecting duplicate billing or loyalty tier tracking
-- ─────────────────────────────────────────────────────────────────

SELECT
    s1.CustID,
    m1.Title      AS magazine_1,
    m2.Title      AS magazine_2,
    c.Category_Name
FROM Subscription s1
JOIN Subscription s2  ON s1.CustID = s2.CustID
                      AND s1.MID   < s2.MID         -- Avoid self-match and duplicates
JOIN Magazine m1      ON s1.MID   = m1.MID
JOIN Magazine m2      ON s2.MID   = m2.MID
JOIN Category c       ON m1.CategoryID = c.CategoryID
                      AND m2.CategoryID = c.CategoryID  -- Same category
ORDER BY s1.CustID;
```

---

## 📊 Business Impact

**Before normalization:**
- Updating Elle magazine's category required touching every row where Elle appeared (n rows for n subscribers).
- Deleting customer #101 (the only subscriber to *Runner Sports*) would have destroyed the magazine record.
- Running a "subscribers per category" query returned inflated counts due to duplicate rows.

**After normalization:**
- Category changes require updating 1 row in `Category`, cascading correctly through foreign keys.
- Magazine records persist independently of subscriber status.
- All aggregate queries are accurate and performant with proper indexing on FK columns.

---

## 🛠️ Design Decisions

- `CategoryID` was introduced as a surrogate key because Category Name is mutable (e.g., "Fashion" could be renamed "Lifestyle") — using it directly as a FK would cause cascading update problems.
- `Subscription` uses a composite PK `{MID, CustID}` rather than a surrogate ID — this enforces the business rule that a customer can only have one active subscription record per magazine at any time.
- `Sub_End` is nullable to represent open-ended (currently active) subscriptions.

---

*Course: COMM437 Database Technology — UBC Sauder School of Business*
