Business Data Technologies (GEN BUS 760)
Wisconsin School of Business, University of Wisconsin - Madison, Fall 2022
| Instructor: Emaad Manzoor (emanzoor[at]wisc.edu) | Office Hours: By appointment, at Grainger 4284B |
|---|---|
| Section Meeting Times, Locations, Canvas Sites: | Format: Lecture (75m), Break (15m), Lab (60m) |
| Section 010: Tue 2.30-5.00PM, Grainger 2520 (Canvas) | Grading: Exam (50%), HW (34%), Project (16%) |
| Section 011: Tue 5.40-8.10PM, Grainger 2520 (Canvas) | Resources: Syllabus |
In this course, you will develop expertise in on-premise and cloud
data analytics using SQL and Snowflake. You will
also learn how to conceptualize data warehouse designs using entity-relationship
diagrams, how to translate conceptual designs to logical designs using
QuickDatabaseDiagrams, and how to translate
logical designs to physical data warehouse designs in SQL.
| Date | Topic | Readings and Assignments |
|---|---|---|
| Part I: Data Warehouse Analytics | ||
| Sep 13 | Lecture: Introduction (slides) Course Goals & Logistics Designing Data Intensive Architectures Stages of Data Sophistication On-Premise & Cloud Data Lakes Data Warehouses Lab 1: Getting Started with MySQL Introduction to MySQL MySQL Setup (Sec 010, Sec 011) Importing the Lecture Databases |
Optional Readings: Designing Data Intensive Applications Released Assignments: Homework 1 (Sec 010, Sec 011) (due Oct 18) |
| Sep 20 | Lecture: Single Table Analytics (slides, code) Essential Clauses Filtering & Logical Operators Lab 2: Load Yelp Data (HW1 Part A-B) |
Optional Readings: Learning SQL: Ch. 1, 3, 4 |
| Sep 27 | Lecture: Multi-Table Analytics (slides, code) Inner, Outer, & Self-Joins Natural Joins, Cross Joins, & Unions Lab 3: (questions, solutions) |
Optional Readings: Learning SQL: Ch. 5, 6, 10 |
| Oct 04 | Lecture: Summarizing Data (slides, code) Grouping & Aggregation Functions Lab 4: (questions, solutions) |
Optional Readings: Learning SQL: Ch. 8 |
| Oct 11 | Lecture: Advanced SQL I (code) Subqueries Lab 5: Snowflake (Sec 010, Sec 011), (code) |
Optional Readings: Learning SQL: Ch. 9 |
| Oct 18 | No in-person class due to INFORMS Lecture: Advanced SQL II Functions (video) Views (video) |
Optional Readings: Learning SQL: Ch. 7, 14 Assignments Due: Homework 1 (submit on Canvas) Peer Grade 1 (submit on Canvas) |
| Oct 25 | Lecture: Cloud Data Warehouses Snowflake Architecture (slides) Lab 6: More SnowSQL SQL review w/ Snowflake (code) SnowSQL commands, functions |
Optional Readings: Learning SQL: Ch. 11, 16 Released Assignments: Homework 2 (Sec 010, Sec 011) (due Nov 08) Practice Exam: (see Canvas) (ungraded, no due date) |
| Part II: Data Warehouse Design | ||
| Nov 01 | Lecture: Conceptual & Logical Design (slides) Entity-Relationship Diagrams Logical Schemas Lab 7: QuickDatabaseDiagrams |
Optional Readings: Database Design for Mere Mortals |
| Nov 08 | Lecture: Physical Design (code) Creating Databases, Tables, & Relationships Data Types Inserting & Updating Data Lab 8: HW2 Help / Exam Practice |
Optional Readings: Learning SQL: Ch. 2 Assignments Due: Homework 2 (submit on Canvas) Peer Grade 2 (submit on Canvas) |
| Nov 15 | In-Class Midterm Exam Tests all material upto & including Nov 01 Open notes, laptop, internet, etc. allowed No collaboration of any form allowed |
SQL Practice Resources: Data School LeetCode, w3schools, SQL-practice, HackerRank, SQL Zoo, SQL Bolt |
| Nov 22 | Virtual Lecture: Normalization (slides) Recordings: See Canvas |
Optional Readings: Learning SQL: Ch. 13 The Data Warehouse Toolkit |
| Thanksgiving Recess: Nov 24 — 27, 2022 | ||
| Nov 29 | Lecture: SQL Indexing (slides, code) Lab: Project Hackathon |
|
| Dec 06 | Lecture: MapReduce (slides, code) SQL Window Functons (code) Lab: Project Hackathon |
Assignments Due: Project Presentation Signup (link) |
| Dec 13 | In-Class Project Presentations Team Red (slides) Team Yellow (slides) Team Green (slides) Team Blue (slides) Team Pink (slides) Team Purple (slides) Team Orange (slides) Team White (slides) Team Black (slides) |
Assignments Due: Presentation Slides (Canvas submission) Peer Grade 3 (Canvas submission) |