COLUMBIA UNIVERSITY COMS W4111.002

Information

Staff

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 several optional extra-credit assignments that will dive deeper into concepts introduced in class. Some of them will involve extending a simple Python-based database engine with additional functionality! They are labeled AA# in the schedule. There is no obligation to do these, but they are available if you want to do then in addition to, or in lieu of the normal assignments.

Automated Exercises/resources

Developed for W4111

Developed by others:

Announcements

Schedule

Date

Topic

Assigned

Due

22-Jan Intro
optional: Textbook Ch 1
HW 0
24-Jan Entity-Relationship Modeling : ER Modeling
optional: Textbook Ch 2
HW 1
Project 1 Part 1.
LOOK FOR TEAMMATE
HW 0 Fri 1/25 10AM
You will receive a 0 in this class if HW0 not completed on time -- no exceptions

Deadline to sign up for proj1 matching system 1/25 11:59PM est
29-Jan Entity-Relationship Modeling + Nonrelational Models : More constraints, using ER models
optional: Textbook Ch 2
31-Jan Relational Model : Dawn before time: non relational models.
optional: What goes around comes around
optional: NoSQL data modeling techniques
optional: Textbook Ch 3
AA1 Formed team for Project 1 (no deliverable)

HW 1 Part 1 (ER diagram) 10AM (digital copy)
5-Feb Relational Model : ER and Relational Review.
optional: What goes around comes around
optional: Textbook Ch 3
Project 1 Part 1 approval phase
7-Feb ER to Relational Model Demo
optional: Original Relational Model paper
optional: Textbook Ch 3
Project 1 Part 1 approval phase
12-Feb Relational Algebra
optional: Textbook Ch 4
Project 1 Part 1 approval phase
14-Feb Relational Alg
optional: Textbook Ch 4
Project 1 Part 2.
AA2
Project 1 Part 1 10AM digital copy
AA1 10AM
15-Feb HW1 Part 2 Schema 11:59PM
19-Feb SQL: Basics, Joins
optional: Textbook Ch 5
21-Feb SQL: Aggregations, Order by and UDFs
optional: Textbook Ch 5
HW 2
26-Feb SQL: Triggers, WITH, Views.
optional: Textbook Ch 5
Midterm f18 (sol)
Midterm sp16 (sol)
Midterm f15 (sol)
28-Feb SQL: WITH, review
optional: Textbook Ch 5
5-Mar Midterm Review HW 2 Due 10AM.
7-Mar Midterm: closed book : one 8x11 page cheat sheet both sides
optional: Textbook Midterm (sol)
Project 1 Part 3
HW 3
12-Mar APIs: DBAPIs, Impedance Mismatches, SQL Injection
optional: Textbook Ch 6
AA2 10AM
14-Mar Normalization: Redundancy, Functional dependencies
optional: Textbook Ch 19
AA3 Project 1 Part 2 Fri 10AM
26-Mar Normalization: Normalization algorithms
optional: Textbook Ch 19
28-Mar Physical Design : Disk storage, Basic Files
optional: Textbook Ch 8
2-Apr Physical Design : Index files and costs
optional: Textbook Ch 8
4-Apr Query Proc : Query plans, statistics, join algorithms
optional: Textbook Ch 12
HW 4 HW 3 10AM
AA3 10AM (extended: 4/8 10AM)
9-Apr Query Proc : Join algorithms and Selinger
optional: Selinger Paper
optional: Textbook Ch 12
11-Apr Transaction Processing : Concurrency Control
optional: Textbook Ch 16
16-Apr Transaction Processing : Concurrency Control
optional: Textbook Ch 16
Midterm 2 F15 (sol)
Midterm 2 S16 (sol)
Project 2
AA4
Project 1 Part 3 10AM
18-Apr Transaction Processing : Recovery
optional: Textbook Ch 18
23-Apr Misc Lecture : TBA HW 4 2:30PM
25-Apr Review
30-Apr Exam 2 (Cumulative): Closed Book : 1 page cheat sheet both sides. 309 Havemeyer
2-May No lecture AA4 10AM
Project 2 10AM