Syllabus

Note:

This is tentative and we are refining during the first week.

Lecture No. Date Topics Exams and Homework
1 10-Sep

Introduction, Course Overview, Foundational Concepts

  • Course overview, assignments, exams, grading, logistics, ...
  • Topics
    • Data, database systems, data models, programming languages, application architectures, user roles.
    • Relational model, keys, schema, schema diagrams.
    • Introduction to SQL, schema, DDL, basic queries.
    • Overview of the Entity-Relationship Model.
  • Reference material: Database Systems Concepts (Silberschatz)
    • Chapter 1.
    • Sections 2.1 through 2.4
    • Sections 3.1, 3.2, 3.3
    • Chapter 6: Sections 6.1, 6.2

Homework 0 assigned.
Homework 1 assigned.

2 17-Sep

Relational Model and Algebra
Intro to SQL, Data Modeling,and Applications

  • Topics
    • Relational Model, Relational Algebra.
    • SQL: Data Definition Language
    • SQL: Advanced SELECT clause, Set operations, aggregate functions, updates.
    • Data modeling: ER-Model, entities, relationships, keys, cardinality.
    • Introduction to Pandas. Pandas and SQL.
  • Reference material: Database Systems Concepts (Silberschatz)
    • Sections 3.4 - 3.10
    • Sections 2.4 - 2.6 (continued)
    • Sections 6.3 - 6.8

Homework 0 due.
3 24-Sep

Intermediate SQL,Data Modeling, Applications

  • Topics:
    • SQL: Join, Views, Integrity Constraints, Indexes
    • Data Modeling: Design issues, modeling to relational, advanced topics.
    • Patterns: Injeritance, Aggregation, ...
  • Reference material: Database Systems Concepts (Silberschatz)
    • Chapter 4.
    • Sections 6.9 - 6.11

    Homework 1 due.
    Homework 2 assigned.

    4 01-Oct

    Advanced SQL, Data Modeling Patterns, Applications

    • Topics
      • Functions, Procedures, Triggers.
      • Recursive queries, Window Functions.
      • Cursors, Connections, Transactions, Isolation Levels.
    • Reference material: Database Systems Concepts (Silberschatz)
      • Chapter 5

    5 8-Oct

    Example Use Cases and Scenario Implementation

    • Modeling and implementing Columbia University course, sections, ... ...
    • Improving and optimizing IMDB data, ... ...
    • Other models TBD

    Homework 2 due.

    6 15-Oct

    Normalization, De-normalization, Design Patterns

    • Topics
      • Relational design objectives and issues.
      • Functional dependencies.
      • Normal forms.
      • Normalization tradeoffs and examples.
    • Reference material: Database Systems Concepts (Silberschatz)
      • Chapter 7.

    Take home midterm assigned.
    7 22-Oct

    Data Storage, Indexes, Query Processing, Transactions

    • Topics
      • Data storage media, formats and data structures.
      • Indexes.
      • Query processing.
      • Transactions, isolation/currency control, recover.
    • Reference material: Subset of material in chapters 12-19.

    Take home midterm due.
    Proctored midterm quiz.

    8 29-Oct

    No SQL, Cloud Databases (1)

    • Topics
      • No SQL concepts and motivation.
      • CAP "Theorem". Consistency, eventual consistency.
      • Scale-up versus Scale-out.
      • Graph databases, Neo4j.
    • Reference material:
      • TBD

    Homework 3 assigned.
    9 05-Nov

    NoSQL, Cloud Databases (II)

    • Topics
      • Topics
        • Document DBs: MongoDB, DynamoDB
        • Key Value: Redis
        • Time Series DB: TBD
      • Reference material:
        • TBD

    10 12-Nov

    Big Data, Data Insight (I)

    • Topics
      • Data warehose, data lake
      • De-normalization
      • OLAP
    • Reference material:
      • TBD

    Homework 3 due.
    Homework 4 assigned.

    11 19-Nov

    Big Data, Insights (II)

    • Topics
      • MapReduce, Hadoop, Sparq, AWS Glue
      • Dashboard, analysis techniques
    • Reference material
      • TBD

    NA 26-Nov

    NA -- Thanksgiving Holiday

    • NA.

    12 03-Dec

    Python Ecosystem, examples

    • Topics
      • Jupyter
      • Pandas
      • Plotly, Map Plot Lib
      • ScitKit
      • ... ...
    • Reference material
      • TBD

    Homework 4 due.

    13 10-Dec

    Overflow and final exam preparation

    • Topics
      • TBD
    • Reference material
      • TBD

    Take home final issued.