COLUMBIA UNIVERSITY COMS W4111.002

Information

Prereqs

Grading

Overview

The goal of this class is two-fold. First, to introduce you to core database concepts (e.g., data modeling, logical design, SQL) so that you too can build a billion dollar application. Second, to teach enough about database engine internals (e.g., physical database design, query optimization, transaction processing) so you have a good sense of why queries may be running slowly/incorrectly.

Advanced Assignments There will be an experimental set of optional extra-credit assignments that will dive into more advanced database topics. These assignments will involve extending a simple Python-based database engine with additional functionality, or optimizations described in class..

Recent Announcements

Schedule

Date

Topic

Readings

Assigned

Due

9/4 Intro
Chapter 1
9/6 Entity-Relationship Modeling
ER Modeling
Chapter 2
9/11 Entity-Relationship Modeling
More constraints, using ER models
Chapter 2
9/13 Relational Model
Dawn before time: non relational models.
Chapter 3
9/18 Relational Model
ER and Relational Review.
Chapter 3
9/20 Relational Model
Intro to Relational Algebra
Chapter 3
9/25 Relational Algebra
9/27 Relational Algebra
ER to Relational, supplemental notes
Sections 4.1 and 4.2
10/2 Relational Algebra
10/4 SQL
Knee deep in SQL
Chapter 5
Aggregation Slides
10/9 SQL
Order by and UDFs
Chapter 5
Midterm Practice 1
Midterm Practice 2
10/11 SQL
Triggers, WITH, Views.
Chapter 5
(optional)Scalable Trigger support
10/16 Midterm Review
10/18 Midterm: closed book
one 8x11 page cheat sheet both sides
10/23 APIs
DBAPIs, Impedance Mismatches, SQL Injection
Chapter 6
10/25 Normalization
Redundancy and Functional Dependencies
Chapter 19 except 19.8 (you do need to know 3NF at the level of lecture)
10/30 Normalization
Chapter 8 (level of detail covered in class)
11/1 Physical Design
Disk storage, Heap Files
11/8 Physical Design
B+Trees, Hash Files, Single operator optimizations
11/13 Query Processing
Query plans, statistics, join algorithms
Chapter 12 (level of detail covered in class)
11/15 Query Processing
Join algorithms and Selinger
Chapter 12 (level of detail covered in class)
11/20 Transaction Processing
Concurrency Control
Chapter 16 (level of detail covered in class). Ignore 16.5, 16.6.
11/27 Transaction Processing
Concurrency Control
Chapter 16 (level of detail covered in class). Ignore 16.5, 16.6.
11/29 Transaction Processing
Recovery
12/4 Final Exam (Cumulative): Closed Book
1/2 page cheat sheet
12/6 Advanced DB and Research Topics
The Future and More