Class Schedule - Fall 2025

Week 1 - Thursday (8/28)

• Introduction to Databases and Relational Data Model (Chapter 1, 2.1-2.2)

• (Optional) Videos:

  • Lecture 1: Introduction to Databases

  • Lecture 1.2: Introduction to Relational Data Model

Week 2 - Thursday (9/4)

• Introduction to Relational Algebra (Chapter 2)

• (Optional) Videos:

  • Lecture 2.1 Introduction to Relational Algebra

  • Lecture 2.2 Rename, Select, Project

Week 2 - Friday (9/5)

• Introduction to Relational Algebra and Normalization (Chapter 2)

• (Optional) Videos:

  • Lecture 2.3 Cartesian Product

Week 3 - Monday (9/8)

• Introduction to Relational Algebra and Normalization (Chapter 2 and 3)

• (Optional) Videos)

  • Lecture 3.1: Relational Algebra (Join)

    (Errata: Note a mistake at 10 minute mark: hid=hid1 should be hid<>hid1, like in the first solution to the same query.

    This is why, due to inequality, we cannot use natural join for this query easily.)

  • Relational Algebra tl;dr (5 mins Summary)

  • Lecture 3.2: Normalization (Functional dependencies, inference, keys), infomercial style!

• Solve Lecture 4 Exercise on Submitty after class (deadline in 48 hrs).

Week 3 - Thursday (9/11)

• Normalization (Chapter 3)

• Optional (Videos):

  • Lecture 3.2: Normalization (Functional dependencies, inference, keys), infomercial style!

  • Lecture 4:1: Normal Forms

• Solve Lecture 5 Exercise on Submitty after class (deadline in 48 hrs).

Week 4 - Monday (9/15)

• Normalization (Chapter 3)

• Optional (Videos):

  • Lecture 4:2: Minimal Basis

  • Lecture 5:1: Normalization (Introduction to Decompositions: Lossless Decocompositions)

• Solve Lecture 6 Exercise on Submitty after class (deadline in 48 hrs).

Week 4 - Thursday (9/18)

• Normalization (Chapter 3)

• Optional (Videos):

  • Lecture 5:3: Normalization (3NF Decomposition) Errata: in the example in this video, one of the keys is missing for R, BCD should also be a key!

  • Lecture 5:2: Normalization (Dependency Preserving Decompositions)

  • Lecture 5:4: Normalization (BCNF Decomposition)

  • Lecture 5:Bonus: Normalization (BCNF Decomposition, Second example)

• Solve Lecture 7 Exercise after class (deadline in 48 hrs).

Week 5 - Monday (9/22)

• Entity-Relationship Diagrams (Chapter 4)

• Optional (Videos):

  • Lecture 6:1: Normalization (Fourth Normal Form)

  • Lecture 6:2: Entity Relationship Diagrams (Entities)

  • Lecture 6:3: Entity Relationship Diagrams (Relationships and Participation Constraints)

  • Lecture 8:1: ER Diagrams (Entities Reexamined, Weak Entities)

  • Lecture 7:1: Converting ER Diagrams to Relational Data Model

• Solve Lecture 8 Exercise after class (deadline in 48 hrs).

Week 5 - Thursday (9/25)

• Entity-Relationship Diagrams (Chapter 4)

• Optional (Videos):

  • Lecture 7:2: ER Diagrams (Cardinalities Reexamined)

• Solve Lecture 9 Exercise after class (deadline in 48 hrs).

Week 6 - Thursday (10/2)

• Entity-Relationship Diagrams (Chapter 4)

• Basic SQL Language (Chapter 6)

• Optional (Videos):

  • Lecture 10:2+10.3: SQL: Intro, SELECT, WHERE

  • Lecture 10:4: SQL: FROM

Week 7 - Monday (10/6)

• Basic SQL Language (Chapter 6)

• Optional (Videos):

  • Lecture 11:1: Set/bag operators, order by, limit

  • Lecture 11:2: Group by/Having

• Solve Lecture 11 Exercise on Submitty (deadline in 48 hrs after class)

Week 7 - Thursday (10/9)

• Advanced SQL Language (Chapter 6)

• Optional (Videos):

  • Lecture 12:1: SQL Outer Join

Week 8 - Thursday (10/16)

• Advanced SQL Language (Chapter 6)

• Optional (Videos):

  • Lecture 12:3: SQL Set Comparisons

  • Lecture 13:1: SQL Correlated Set Comparisons

• Solve Lecture 12 Exercise on Submitty after class (deadline in 48 hrs).

Week 9 - Monday (10/20)

• Advanced SQL Language (Chapter 6), Anonymous Queries and WITH Statement

• Data Definition and Manipulation in SQL

• Introduction to Transactions

• Optional (Videos):

  • Lecture 13:2: Transactions and SQL Insert

  • Lecture 13:3: SQL Update/Delete

• Solve Lecture 13 Exercise on Submitty after class (deadline in 48 hrs).

Week 9 - Thursday (10/23)

• Producedural SQL

• PL/PGSQL

• Optional (Videos):

  • Lecture 15:1: PL/pgSQL

• Solve Lecture 14 Exercise on Submitty after class (deadline in 48 hrs).

Week 10 - Monday (10/27)

• PL/PGSQL - Procedural SQL

• Optional (Videos):

  • Lecture 15:1: PL/pgSQL

Week 10 - Thursday (10/30)

• Triggers and Access Control

• Optional (Videos):

  • Lecture 16_1: Views and Access Control

• Complete Lecture Exercise 15

Week 11 - Monday (11/3)

• Views and Advanced SQL Features

• Optional (Videos):

  • Lecture 16_1: Views and Access Control

  • Lecture 16_2: Other SQL Features (case, recursion, advanced group by)

• Complete Lecture Exercise 16

Week 11 - Thursday (11/6)

• Exam 2 Review Class

Week 12 - Thursday (11/13)

• Introduction to Secondary Storage

• Optional (Videos):

  • Lecture 19 1: Indexing

Week 13 - Monday (11/17)

• B-trees

• Optional (Videos):

  • Lecture 19 2: B-trees

  • Lecture 19 3: B-trees: insertion/deletion

  • Lecture 20 1: B-trees: duplicate values and multiple attributes

• Complete Lecture Exercise 21 (due 11/21)

Week 13 - Thursday (11/20)

• B-tree and Index Scan

• Optional (Videos):

  • Lecture 21 2: Index Scans

• Complete Lecture Exercise 22 (due 11/26)

Week 14 - Monday (12/1)

• Size Estimation

• Index Selectivity

• Block-nested Loop Join

• Optional (Videos):

  • Lecture 21 1: Query Execution

  • Lecture 21 3: Block Nested Loop Join

  • Lecture 22 5: Size Estimation

• Complete Lecture exercise 23 (due on 12/5)

Week 14 - Thursday (12/4)

• External Sorting

• Query Processing

• Cost Estimation for Query Plans

• Query Optimization

• Optional (Videos):

  • Lecture 22 1: External Sort

  • Lecture 22 2: Pipelining

  • Lecture 21 1: Query Execution

  • Lecture 22 3: Query Optimization Introduction

  • Lecture 22 4: Query Plan Generation

• Complete Lecture Exercise 24 and Optional Exercise 3 (due friday 12/12), note that this is actually the fourth lecture exercise

Week 15 - Monday (12/8)

• Transactions Concurrency

• Optional (Videos):

  • Lecture 23 1: Concurrency Control Introduction

  • Lecture 23 2: Checking Serialiazibility

  • Lecture 24 1: Two Phase Locking

  • Lecture 24 2: Lock Management

• Complete Lecture Exercise 25 (due friday 12/12)

Week 15 - Thursday (12/11)

• Transactions Concurrency

• Overall review and Database Tuning

• Optional (Videos):

  • Lecture 24 1: Two Phase Locking

  • Lecture 24 2: Lock Management

  • Course Notes: Database Tuning

• Complete all remaining Lecture Exercises