BADM 554 - Enterprise Database Management

Program-level details: See program/curriculum.md

Soft launch 2026-08-17 (Week 0 + Module 1); Modules 2–8 unlock on Day 1, 2026-08-24. Assessment numbers below are final (Cheng Li’s corrected assessment plan, 2026-08-14, live in Canvas).

Live LD team data (module items, video lengths, production status): see badm554/sync/ — machine-generated snapshots from the canonical Box Excel files, re-synced ~4x/day (check _last-synced.md for freshness). Canonical build detail lives in the course repo (~/teaching/badm554/semesters/fall2026-online/).

Course Vision

Learners arrive able to query data. They leave able to design it. BADM 554 follows an OLAP-first arc: weeks 1–3 build fluency querying a real analytical warehouse (BigQuery public datasets), weeks 4–6 shift to designing and building that warehouse from transactional source data, and weeks 7–8 validate, document, and defend the result. By course end, each team has a working ETL pipeline, a dimensional schema, and a defensible oral presentation — a portfolio artifact, not a tutorial completion.

Learning Outcomes

Two naming schemes are used in parallel: the L-C-E framework (Vishal’s design language, used in week-by-week planning and this file) and CLO numbers (Cheng’s LD framework, used in Course Map.xlsx and the activity roster). The mapping below is the bridge. When Cheng populates the Excel, he uses CLO numbers; this file shows both.

Excel amendment needed (for Cheng): Add an “L-C-E Label” column to the Course Overview CLO table in Course Map.xlsx. Populate with the L-C-E labels below. This lets the sync files carry both schemes and eliminates ambiguity for K-ai.

Source of truth for outcome text (decided 2026-08-13, Vishal): the course repo’s design spec (~/teaching/badm554, 2026-05-10 design spec §”Revised L-C-E learning outcomes”), as carried into the Fall 2026 syllabus. The eight CLOs below are that revised set in the existing slot structure — the slot numbering Cheng’s Course Map already uses is unchanged.

L-C-E detail (for planning, pedagogy, and AIAS labels)

Literacy (Foundational Awareness)

• L1: Understand relational database concepts (tables, keys, relationships) and explain why normalization matters
• L2: Read and interpret SQL queries to identify what data they retrieve
• L3: Recognize when data needs cleaning and describe common data quality issues

Competency (Applied Skills)

• C1: Write SQL queries — SELECT, JOIN, GROUP BY, window functions, CTEs — to extract and aggregate data
• C2: Design a normalized schema (3NF) and a dimensional schema (star) for a given analytical question
• C3: Build a complete ETL pipeline using Python (pandas, sqlalchemy) from a transactional source to a dimensional warehouse
• C4: Connect to and query cloud and local databases in multiple environments (Colab notebook, CLI, MCP agent)
• C5: Choose the appropriate SQL access pattern (notebook vs. CLI/MCP) for a given task and articulate the trade-off

Expertise (Advanced Application)

• E1: Evaluate design trade-offs (normalization vs. denormalization, 3NF vs. star) for specific use cases
• E2: Build a production-ready data pipeline with validation checks, error handling, and documentation
• E3: Justify data architecture and pipeline design decisions to a technical hiring manager — cost, scalability, fitness for the analytical use case

Process Learning Outcome (Black Box — see assessment_strategy.md §3.5)

• P1: Document the evolution of at least one key design decision across milestones — including the initial approach, what prompted a revision or pivot, and what was learned from the change

Note on query optimization: Learners gain awareness that slow queries exist and why (indexes, scan patterns), covered in Week 7. Deep optimization is a DBA/data engineering concern beyond the scope of this first course — revisited in later courses as appropriate.

Course Arc (3 + 3 + 2)

Every week: 4 framing videos (5–8 min) → curated practice → Jupyter lab → Live Session (90 min) → Project Studio (90 min) → concept assignment → Canvas discussion post → project milestone.

Week-by-Week Breakdown

Team Project: Stakeholder-Driven Analytical Data Product (Team of max 3, Weeks 1–8)

Learner teams pick a stakeholder + dataset pairing from a curated list or propose their own (subject to instructor approval). The pairing defines a real analytical question the team will answer with a designed, built, and validated data product.

Curated dataset pairings (BigQuery public datasets — no download required):

• NYC Taxi Trips + city transportation planner
• Austin 311 Service Requests + city operations director
• Stack Overflow survey + engineering hiring manager
• USA Names (SSA) + school district demographer
• Iowa Liquor Sales + regional retail buyer
• Google Analytics sample + e-commerce growth analyst

Weeks 5–6 source data: Learners also work with their own personal-data exports (Spotify, YouTube, Strava) as a real-world messy source — uniquely AI-resistant because the data is individual. Provider-groups (learners with the same export source) collaborate via a shared GitHub repo to negotiate a shared schema.

3NF reference dataset (Weeks 5–6): Sakila via the Pagila Postgres port — a clean textbook 3NF schema used as a contrast to the real-world mess of personal-data exports.

Project Milestones

Weights below are final (Cheng Li’s corrected assessment plan, 2026-08-14; 1 Canvas point = 1% of the course grade). The project group totals 59%.

Final Deliverable (15% of grade) — key components

• DuckDB warehouse with fact + dimension tables, populated from source data
• ETL notebook (idempotent, documented, with validation checks)
• 2–3 analyses answering the stakeholder’s original questions
• Full AI Attribution Log + 1-paragraph reflection on how AI use evolved M1 → Final
• GitHub repo with all code, schema documentation, and setup guide
• Architecture diagram (data flow: source → transform → warehouse → analysis)

Rubric (6 dimensions):

Oral Defense (20% of grade)

Week 8, live only (decided 2026-08-15). This is a purely individual assessment, distinct from the team presentation scored under the Final Deliverable — each student defends their own contribution and their AI-use evolution across the term in a single ~5-minute defense plus follow-up Q&A, sat live with a person (Project Studio, mentor office hours, or professor office hours). There is no Canvas-recorded submission path. The session is recorded and graded from that recording. Slots are booked during Week 7 for Week 8.

This is a deliberate exception to the program’s “no graded milestone is attendance-gated” default, made for cohort 1 specifically: at ~30 learners the personal, high-touch version is affordable and the enrollment payoff of a strong first-year experience outweighs the scalability cost. Anyone who cannot make a published slot is guaranteed an accommodation slot — attendance is handled, not left to chance. The in-house viva tool (not yet named to learners) is expected to supply an async accommodation path when it ships; it will not replace the live default.

Rubric (3 dimensions):

Weekly Assignments (31%), Progress Checks (4%), Discussion (4%)

Each of Modules 1–7 has one graded individual weekly assignment run as a pre-AI / AI / post-AI cycle with an AI Attribution Log, in Jupyter (M1 3% · M2 5% · M3 3% · M4 5% · M5 5% · M6 5% · M7 5% = 31%; Module 8 has none). Progress-check quizzes in M1 and M3 (2% each, AIAS 0). One Live Session discussion post per module (0.5% × 8 = 4%). Learner-facing vocabulary is always practice → assignment → project (never “lab”).

AI Tools Integration

Where AI Accelerates Learning:

1. Week 1 (SQL Fundamentals): Use Claude/ChatGPT to:
   • Explain SQL errors (“Why does this JOIN return NULL?”)
   • Generate sample SQL queries for practice
   • Validate your schema design
   • Suggest refactoring for clarity
2. Week 4-5 (Python ETL): Use AI to:
   • Debug pandas errors (“How do I reshape this DataFrame?”)
   • Optimize code performance (“How do I vectorize this loop?”)
   • Generate error handling patterns
   • Suggest pandas functions for complex transformations
3. Week 7-8 (Validation & Defense Prep): Use AI to:
   • Propose validation checks for the ETL pipeline (then verify them yourself)
   • Stress-test documentation (“could another analyst repeat these checks?”)
   • Generate hard practice questions for the oral-defense rehearsal
   • Review query correctness on edge cases

Three-Options Prompting Protocol (Weeks 4-8, AIAS 2):

For all milestone submissions (M4-M7) and the final deliverable, students must use the three-options format when using AI for design or architectural decisions:

1. Prompt AI for three distinct options (e.g., “Give me three different approaches to structuring this ETL pipeline”)
2. Summarize each option briefly
3. Select one and justify the choice in 2-3 sentences based on your specific project context

Submission format:

AI Prompt: [paste exact prompt]
Option A: [1-sentence summary]
Option B: [1-sentence summary]
Option C: [1-sentence summary]
Chosen: [A/B/C]
Rationale: [why this option fits your context better than the others]

This replaces generic AI attribution (“I used Copilot”) with evidence of evaluative judgment. Options must differ on at least one meaningful dimension (e.g., performance vs. simplicity vs. maintainability). The three-options protocol does not apply to Weeks 1-3 (pre-AI / AIAS Level 0-1 phases).

Per-Milestone AI Attribution Log (program-standard format):

Aligned with design/project_milestone_template.md. Required at four checkpoints:

Format for each entry:

Milestone: [M2 / M4 / M6 / Final]
AI Tool Used: [Claude / Copilot / ChatGPT / other]
Prompt (summarized): [what you asked]
Output Used: [what you kept]
Output Rejected: [what you discarded and why]
Human Decision: [what judgment you applied that AI could not]

Studio Session Topics:

• Week 1: ER diagram design walkthrough + SQL SELECT fundamentals
• Week 2: Advanced SQL patterns (window functions, CTEs, complex JOINs)
• Week 3: Database normalization decisions + denormalization trade-offs
• Week 4: SQL→Python translation — SELECT→filter, GROUP BY→groupby, JOIN→merge
• Week 5: Error handling patterns in Python + logging best practices
• Week 6: Data quality + API authentication — cleaning messy data, managing credentials safely
• Week 7: Validation walkthrough + oral-defense rehearsal — 5-min check-ins with Q&A practice
• Week 8: Final presentations + portfolio feedback

Assessment Summary

Final weights (Cheng Li’s corrected assessment plan, 2026-08-14; matches live Canvas, 1 point = 1%):

Total: 100%. No traditional exam. All assessment is project-based, skills-based, and participation.

AI Usage Levels (AIAS)

Technology Stack

• Query targets: BigQuery public datasets (Wks 1–4, cloud notebook); local Postgres with Sakila/Pagila (Wks 5–6, 3NF reference); personal-data exports in DuckDB (Wks 5–6, real-world source)
• Analytical warehouse: DuckDB (local, embedded — fast setup, no server; learners build their star schema here)
• Python libraries: pandas, sqlalchemy, psycopg2, requests, duckdb
• Access patterns: Jupyter/Colab notebook (primary Wks 1–4); VS Code + bq CLI + MCP agent (introduced Wk 3, load-bearing Wks 4–7)
• MCP config: Course-provided pin-versioned bundle (BigQuery + Postgres + DuckDB + filesystem + GitHub MCP servers) — imported in Wk 3
• IDE: VS Code with GitHub Copilot; Google Colab (browser fallback)
• Version control: GitHub (individual repos Wks 1–5; provider-group shared repos Wk 6)
• Diagramming: Lucidchart or DrawIO for ERDs and architecture diagrams

Prerequisites & Assumptions

• No SQL experience required — this course starts from zero
• Python fundamentals required (loops, functions, data types) — covered in the pre-program Python orientation
• Comfortable installing software + troubleshooting (Jupyter setup guide provided in onboarding)

Preparatory Coursework Alignment (Staggered Model, 2026-05-18)

Per the program-wide staggered preparatory coursework model (see program/curriculum.md “Preparatory Coursework” and the 2026-05-18 decision, recorded in the internal registry), students arrive in BADM 554 with the following prep status:

Implications for BADM 554:

• Students have GitHub + VS Code set up by Day 1 (course can assume the environment)
• Some students may still be building Python fluency through Week 4 (especially career pivoters); design weekly assignments accordingly
• Gen AI literacy may be in-progress during the first 4 weeks; calibrate AI-aware framing in early weeks
• Stats are not yet assumed; defer any statistics-dependent content to BDI 513 or later

Faculty office hours during Weeks 1-3 are recommended for students still finishing Python self-diagnostic prep — see Ron Guymon’s 2026-04-15 note on zero-coding-experience learners.

Pedagogical Notes for Faculty

Design suggestions grounded in program research — not requirements. Adapt to your course and teaching style. Full references in reference/articles/.

The scenic route (cognitive friction) Pre-AI phases aren’t punishment — they’re the scenic route. Schultz’s dopamine research shows the brain registers learning through prediction errors (the gap between expectation and outcome), not through frictionless delivery. When students design their ERD by hand before asking Copilot to generate CREATE TABLE statements, that struggle is the mechanism — not the obstacle. Consider where in each week students should formulate their own approach before consulting AI. The AIAS progression in this course (0→1→2→3 across weeks) already builds this in; the key is framing it for students as investment, not restriction. → Machulla (2026), Schultz et al. (1997)

The IKEA effect (completion matters) Students value what they build — but only when labor leads to a finished artifact. Building and then discarding produces no ownership effect. The M1→M7→Final Deliverable pipeline already does this well: each milestone closes a loop. The oral defense is the ultimate completion signal — the moment where effort converts to demonstrated competence. When possible, make each milestone feel like a working thing (a queryable schema, a running pipeline), not just a checkpoint document. → Norton, Mochon & Ariely (2012)

Prompt sophistication as skill progression Anthropic data shows r > 0.92 correlation between prompt sophistication and output quality, and multi-turn interactions succeed 67% of the time vs. 49% for single-turn. This course is most students’ first encounter with AI-assisted coding (AIAS 1→3). Consider explicitly teaching prompt patterns early — not as a sidebar, but as a core data foundations skill: “asking the right question of an AI is the same skill as writing the right SQL query.” → Means (2026, “How You Ask”)

Three AI iterations before human review For project milestones M4-M7 (ETL pipeline work at AIAS 2-3), consider requiring students to iterate with AI at least 3 times before submitting for human feedback. This builds the habit of treating AI as a drafting partner, not an answer machine, and produces richer AI Attribution Logs. → Means (2026, “Practice Gap”)

Attack your assessments Before the semester starts, have a confident AI user (TA, LD, or yourself) attempt each major assignment using current AI tools from a student’s perspective. Where can AI complete the task without genuine understanding? Those are the spots to add pre-AI phases or shift weight toward the oral defense. Repeat each semester — AI capabilities shift fast. → Furze (2026)

The cognitive offloading U-curve (Zone 2 is the danger zone) Learning outcomes follow a three-zone pattern: Zone 1 (no AI) = full cognitive load, slow but real learning; Zone 2 (scattered, unstructured AI use) = worse outcomes than no AI at all — coordination overhead without genuine cognitive reallocation; Zone 3 (committed, structured delegation) = superior outcomes through deliberate offloading of routine work while investing freed capacity in higher-order reasoning. The AIAS progression in this course (0→1→2→3) is designed to move learners through Zone 1 early, through Zone 2 quickly, and into Zone 3 by Weeks 5-8. The risk is AIAS Level 1 (“AI for ideation only”) becoming a Zone 2 trap if students engage with AI vaguely and inconsistently. Mitigate by giving Level 1 tasks a clear structure: “Before using AI, write your own answer. Then ask AI. Then compare and justify any changes.” That sequence is Zone 3 behavior, even at low AI permission levels. Separately: a 2026 study of 912 learners found that “partnership orientation” — treating AI as a thinking partner rather than an answer machine — independently predicted deeper learning regardless of how much AI was used. The mental model learners bring to AI matters as much as the permission level. Frame Week 1’s AI orientation around this: “Using AI well is the same skill as writing the right SQL query — the quality of your question determines the quality of the answer.” → Hardman (2026), “The Cognitive Offloading Paradox”; Wang & Zhang (2026)

AI as material for thinking, not a shortcut around cognition (design thinking checklist) Before finalizing any assignment or milestone, apply the three diagnostic questions from assessment_strategy.md §3.4: (1) What is the learning outcome this task targets? (2) What cognitive work must remain with the student to achieve that outcome? (3) What kind of AI assistance makes that cognitive work more visible, more rigorous, or more equitable — rather than replacing it? For BADM 554: the cognitive work that must stay with students is schema design judgment (Wks 1-3) and pipeline architecture decisions (Wks 4-7). AI assistance at those stages should make the student’s reasoning more explicit — not substitute for it. The per-milestone attribution log is the mechanism. → Vander (2026), “Design Thinking for AI-Integrated Pedagogy”

Faculty action required: Before T&L course design review, document your answers to the three diagnostic questions above in writing for this course. See assessment_strategy.md §3.4 for the submission format.

Recruiting Talking Points

Drafted by Vishal Sachdev. Adopted as the model for program-wide faculty talking points (Ravi Mehta directive, 2026-05-27). Recorded in the internal decision registry as “Faculty course talking points: three-question framework for recruiting” — full record from K-ai.

What does the course cover? BADM 554 is the first course of MSBAi: 8 weeks of database fluency for working professionals pivoting toward analyst work. Students learn SQL, schema design, and ETL on real enterprise data; no prior SQL required. Each week pairs a stakeholder conversation with a pre-AI / AI-mediated / post-AI workflow that treats AI as a thinking partner, not a shortcut.

What do students build? Teams of three adopt a stakeholder and a real dataset in Week 1, then spend 8 weeks building a portfolio GitHub repository: a dimensional warehouse, an ETL notebook ingesting from a live public data source, an AI Attribution Log, and a cloud-hosted version of the warehouse with a queryable URL. Each student defends the team’s work in an individual oral examination at Week 8.

Why does it matter for careers? SQL and schema judgment are baseline. What separates hireable analysts is talking to a non-technical stakeholder, working with AI without being replaced by it, and shipping a defensible artifact another person can audit. BADM 554 builds those three skills explicitly.