# 📊 Database Technology Portfolio
### UBC Sauder | COMM437 | Arsalan Nassiri

---

## About This Portfolio

Three end-to-end case studies demonstrating the full data workflow: **model the data → design the schema → query for insight → communicate the finding**.

Each project follows the masterclass structure: business problem first, technical approach second, actionable output always. SQL code is annotated to explain *why* a technique was chosen and *what* it does.

---

## 🗂️ Case Studies

### [01 — Music Streaming Revenue Analysis](./01_Music_Streaming_Revenue_Analysis.md)
**Business context:** A digital music platform needs to identify pricing inefficiencies across its 25-album catalog.

**Techniques:** CTEs, `CASE` statements, correlated subqueries, aggregate functions, window function (`OVER()`), `GROUP BY`/`HAVING`

**Key output:** Genre-level revenue share analysis + album deal classifier for pricing review.

> *"Pop is the top revenue genre in the checked output, but the pricing claim stays a recommendation until a pricing model is attached."*

---

### [02 — E-Commerce DB Design & Normalization](./02_Ecommerce_DB_Design_Normalization.md)
**Business context:** An online magazine subscription service has a flat-file database causing update, insert, and delete anomalies. Needs a redesign to 3rd Normal Form.

**Techniques:** ERD design, functional dependency analysis, 1NF → 2NF → 3NF normalization, DDL (CREATE TABLE with FK constraints), multi-table JOINs

**Key output:** 4-table normalized schema with referential integrity; subscriber and overlap queries now run against separated entities.

> *"The delete anomaly was wiping magazine records whenever their last subscriber churned. One normalization pass fixed it."*

---

### [03 — Non-Profit Donor Fundraising Analytics](./03_Donor_Fundraising_Analytics.md)
**Business context:** A non-profit development team needs to segment donors by giving tier, identify campaign breakout years, and build a lapsed-donor reactivation list.

**Techniques:** `NOT IN` subqueries, `UNION`, nested scalar subqueries, `HAVING` with aggregate comparison, multi-table JOINs, performance optimization (`LEFT JOIN ... WHERE IS NULL`)

**Key output:** Major donor tier identified, 1992 stewardship extremes surfaced, 1994 state-level giving ranked, and the lapsed 1995 cohort isolated for reactivation prioritization.

> *"The lapsed segment gave consistently for years. They did not disengage; they missed one cycle. That is the most actionable segment in the database."*

---

## 🛠️ Technical Skills Index

| Skill | Project(s) |
|---|---|
| Entity Relationship Diagrams (ERD) | 02 |
| Functional Dependency Analysis | 02 |
| 1NF / 2NF / 3NF Normalization | 02 |
| DDL — CREATE TABLE, FK Constraints | 02 |
| SELECT, WHERE, GROUP BY, ORDER BY | 01, 03 |
| Aggregate Functions (SUM, AVG, COUNT, MAX, MIN) | 01, 03 |
| Subqueries (scalar, correlated, `IN`/`NOT IN`) | 01, 03 |
| `HAVING` with aggregate thresholds | 01, 03 |
| Common Table Expressions (CTEs) | 01 |
| `CASE` statements | 01 |
| `UNION` operator | 03 |
| INNER JOIN, Multi-table JOIN | 02, 03 |
| Self-Join pattern | Covered in coursework |
| LEFT/RIGHT/FULL OUTER JOIN | Covered in coursework |
| Query optimization (indexing, avoiding `SELECT *`) | 01, 03 |
| Data Warehouse concepts (ETL, star schema, OLAP) | Covered in coursework |

---

## 📚 Course Context

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

Topics covered: conceptual and logical data modeling, relational database design, SQL querying (basic through advanced), normalization theory, data warehousing, OLTP vs. OLAP systems.

Datasets used across projects: album catalog, magazine subscription records, donor/gift/campaign data, music database (tracks, playlists, tags), textbook rental inventory, multi-store retail operations.

---

*All analysis and schema design are original work derived from course exercises and project deliverables.*
