Business Data Technologies (GEN BUS 760)
Wisconsin School of Business, University of Wisconsin - Madison, Summer 2022
| Instructor: Emaad Manzoor | Office Hours: Wednesdays, 6:15 - 8:55PM CT (on Zoom) |
|---|---|
| Email: emanzoor[at]wisc.edu | Resources: Syllabus, Canvas |
| Weekly Meeting Times: Asynchronous | In-Person Weekend: Aug 6, 7 (8AM-5PM) |
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.
| Week | Topic | Readings and Assignments |
|---|---|---|
| Week 1 (W1) Jul 18 — 22 |
M0: Introduction to The Data Storytelling Badge M1: Storing and Analyzing Big Data 1. Course Goals & Logistics (4:01) 2. Data Intensive Architectures (12:48) 3. Stages of Data Sophistication (8:12) 4. Distributed Data Lakes (8:55) 5. Cloud Data Lakes (12:21) 6. Data Warehouses (6:29) M2: Data Warehouse Analytics with SQL 1. Introduction (4:04) 2. MySQL Setup on a Mac (7:04) 3. MySQL Setup on Windows (5:41) 4. Importing the Course Databases (7:24) 5. Retrieving Data from a Single Table (1:05:56) 6. Combining Data in Multiple Tables (1:35:16) 7. Grouping & Aggregating Data (51:06) |
Hadoop: The Definitive Guide Designing Data Intensive Applications Learning SQL Week 1 Assignments: Survey (optional, due July 22) Week 1 Quiz (due July 25) Homework 1 (due July 28) |
| Week 2 (W2) Jul 25 — 29 |
M1: Creating and Designing Data Warehouses 1. Introduction (01:54) 2. Conceptual & Logical Data Warehouse Design (1:13:52) 3. Creating Databases, Tables, & Relationships (29:31) 4. Data Types (13:20) 5. Inserting, Updating, & Deleting Rows (33:18) 6. Data Warehouse Normalization (25:16) M2: Advanced Data Warehouse Analytics with SQL 1. Using Subqueries (01:03:03) 2. Using Functions (45:37) 3. Using Views (11:10) |
Database Design for Mere Mortals The Data Warehouse Toolkit Week 2 Assignments: Week 2 Quiz (due Aug 1) Homework 2 (due Aug 4) |
| Week 3 (W3) Aug 1 — 5 |
M1: Indexing for High Performance SQL 1. Introduction to Indexes (05:43) 2. Creating, Deleting, & Viewing Indexes (07:17) 3. Prefix Indexes (15:45) 4. Full-Text Indexes (10:17) 5. Composite Indexes (11:34) 6. Common Indexing Pitfalls (11:31) M2: Hadoop and MapReduce (35:47) |
Week 3 Assignments: Week 3 Quiz (due Aug 7) |
| Weekend Aug 6, 7 |
Saturday: 08:30-10:00 Amazon S3 & Snowflake Hands-on 10:00-04:00 Collaborative Project Work Sunday: 08:00-10:00 Project Presentations 10:30-12:00 SQL Review 01:00-02:30 SQL Practice Exam 03:00-04:00 Schema Design Practice Resources: setup.sql, queries.sql, countyjoin.sql coviddata.sql, covidstates.sql, zipcodeincome.sql schema.pdf, practiceexam.sql, review.sql Data: Yelp, New York Times COVID19 Data |
Group Presentations: 1. Income, Reviews, & COVID19 2. Consumer Loyalty & COVID19 3. Ratings & COVID19 Cases 4. Ratings & Geography 5. Best Pizza by State 6. Reputation Deflation 7. Ratings & Income 8. Yelp Influencers 9. COVID19 & Ratings 10. Useful Reviewers & COVID19 |
| Week 4 (W4) Aug 8 — 12 |
Final Exam: Aug 10, 2022, 6:15 - 8:55PM CT Open laptop/notes, no collaboration |
SQL Practice Resources: Data School LeetCode, w3schools, SQL-practice, HackerRank, SQL Zoo, SQL Bolt |
