Tentative Class Schedule - Fall 2020

Week 15 - Monday (12/7)

• Before lecture:

  • Read: Chapter 17 (Durability)

    • Course Notes: Durability

  • Watch (Vimeo Links):

    • Lecture 25 1: Durability: Logging

    • Lecture 25 2: Durability: Force/Steal

    • Lecture 25 3: Durability: Recovery: Aries Algorithm

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 25

Week 14 - Thursday (12/3)

• Before lecture:

  • Read: Chapter 18 (Concurrency Control, 18.1-18.6) and Chapter 17 (Durability)

  • Course Notes: Concurrency Control

  • Course Notes: Durability

  • Watch (Vimeo Links):

    • Lecture 24 1: Two Phase Locking

    • Lecture 24 2: Lock Management

    • Lecture 24 3: Durability introduction

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 24

Week 14 - Monday (11/30)

• Before lecture:

  • Read: Chapter 16 (Query Compiler) and Chapter 18 (Concurrency Control, 18.1-18.4)

  • Course Notes: Query Optimization - Course Notes: Concurrency Control

  • Watch (Vimeo Links):

    • Lecture 22 5: Size Estimation

    • Lecture 23 1: Concurrency Control Introduction

    • Lecture 23 2: Checking Serialiazibility

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 23

Week 13 - Monday (11/23)

• Before lecture:

  • Read: Chapter 15 (Query Execution) and Chapter 16 (Query Compiler)

  • Course Notes: Query Processing

  • Course Notes: Query Optimization

  • Watch (Vimeo Links):

    • Lecture 22 1: External Sort

    • Lecture 22 2: Pipelining

    • Lecture 22 3: Query Optimization Introduction

    • Lecture 22 4: Query Plan Generation

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 22

Week 12 - Thursday (11/19)

• Before lecture:

  • Read: Chapter 15 (Query Execution)

  • Course Notes: Query Processing

  • Watch (Vimeo Links):

    • Lecture 21 1: Query Execution

    • Lecture 21 2: Index Scans

    • Lecture 21 3: Block Nested Loop Join

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 21

Week 12 - Monday (11/15)

• Before lecture:

  • Read: Chapter 14 (Index Structures)

  • Course Notes: Secondary Storage

  • Watch (Vimeo Links):

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

    • Lecture 20 2: Other types of indices

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 20

Week 11 - Thursday (11/12)

• Before lecture:

  • Read: Chapter 13 (Secondary Storage Management)

  • Course Notes: Secondary Storage

  • Watch (Vimeo Links):

    • Lecture 19 1: Indexing

    • Lecture 19 2: B-trees

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

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 19

Week 11 - Monday (11/9)

• Before lecture:

  • Read: Chapter 13 (Secondary Storage Management)

  • Course Notes: Secondary Storage

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• No Lecture Exercises

Week 9 - Thursday (10/29)

• Before lecture:

  • Read: Views and Indexes (Chapter 8.1-8.2)

  • Course Notes: Procedural SQL

  • Watch (Vimeo Links):

    • Lecture 16_1: Views and Access Control

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

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 16

Week 9 - Monday (10/25)

• Before lecture:

  • Read: Constraints and Trigger (Chapter 7)

  • Course Notes: Data Definition and Manipulation in SQL

  • Course Notes: Procedural SQL

  • Watch (Vimeo Links):

    • Lecture 15:1: PL/pgSQL

    • Lecture 15:2: SQL Triggers

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Note: No lecture Exercise for this lecture!

Week 8 - Thursday (10/21)

• Before lecture:

  • Read: SQL Language (Chapter 6), Constraints and Trigger (Chapter 7)

  • Course Notes: Data Definition and Manipulation in SQL

  • Course Notes: Procedural SQL

  • Watch (Vimeo Links):

    • Lecture 14:1: SQL Constraints

    • Lecture 14:2: SQL Isolation

    • Lecture 14:3: SQL Introduction to Procedural SQL

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 14

Week 8 - Monday (10/18)

• Before lecture:

  • Read: SQL Language (Chapter 6)

  • Course Notes: Advanced SQL

  • Course Notes: Data Definition and Manipulation in SQL

  • Watch (Vimeo Links):

    • Lecture 13:1: SQL Correlated Set Comparisons

    • Lecture 13:2: Transactions and SQL Insert

    • Lecture 13:3: SQL Update/Delete

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 13

Week 7 - Thursday (10/14)

• Before lecture:

  • Read: SQL Language (Chapter 6)

  • Course Notes: Advanced SQL

  • Watch (Vimeo Links):

    • Lecture 12:1: SQL Outer Join

    • Lecture 12:2: SQL Anonymous and Scalar Queries

    • Lecture 12:3: SQL Set Comparisons

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 12

Week 6 - Thursday (10/8)

• Before lecture:

  • Read: SQL Language (Chapter 6)

  • Course Notes: SQL Basics

  • Watch (Vimeo Links):

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

    • Lecture 11:2: Group by/Having

  • Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 11 (assuming it is ready after class!)

Week 6 - Monday (10/5)

• Before lecture:

  • Read: SQL Language (Chapter 6)

  • Course Notes: SQL Basics

  • Watch Vimeo videos below (or in lecture videos posted on Mediasite)

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

    • Lecture 10:4: SQL: FROM

    Note: I recorded the content in Vimeo after lecture (for 10.2, 10.3 and 10.4), but for exercises and introduction to PSQL, Mediasite videos are also very useful.

  • Join lecture at 2:30PM on Webex Teams! (Recording on Mediasite after lecture)

Week 4 - Thursday (9/24)

• Before lecture:

  • Read: Entity-Relationaship Diagrams (Chapter 4)

  • Course Notes: ER Diagram

  • Watch (Vimeo Links):

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

    • Lecture 8:2: ER Diagrams (Entity Hierarchies)

• Hand written notes from videos can be found here

• Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• Solve Lecture Exercise for Lecture 8

Week 4 - Monday (9/21)

• Before lecture:

  • Read: Entity-Relationaship Diagrams (Chapter 4.1-4.5)

  • Course Notes: ER Diagram

  • Watch (Vimeo Links):

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

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

• Hand written notes from videos can be found here

• Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

• No Lecture Exercise for Lecture 7

Week 3 - Thursday (9/17)

• Before lecture:

  • Read: Entity-Relationaship Diagrams (Chapter 4.1-4.3)

  • Course Notes: ER Diagram

  • Watch (Vimeo Links):

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

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

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

• Hand written notes from videos can be found here

• Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

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

Week 3 - Monday (9/13)

• Before lecture:

  • Read: Normalization (Chapter 3)

  • Course Notes: Normalization

  • Watch (Vimeo Links):

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

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

    • 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:4: Normalization (BCNF Decomposition)

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

• Hand written notes from videos can be found here

• Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

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

Week 2 - Thursday (9/10)

• Before lecture:

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

  • Course Notes: Normalization

  • Watch (Vimeo Links):

    • Lecture 4:1: Normal Forms

    • Lecture 4:2: Minimal Basis

• Hand written notes from videos can be found here

• Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

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

Week 2 - Tuesday (9/8)

• Reading: Introduction to Relational Algebra and Normalization (Chapter 2)

  • Before lecture:

  • Read: Rest of Chapter 2 and Start reading Chapter 3

  • Course Notes: Relational Algebra - Course Notes: Normalization

  • Watch (Vimeo Links):

    • 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.)

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

  • Optional:

    • Relational Algebra tl;dr (5 mins Summary)

• Hand written notes from videos can be found here

• Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

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

Week 1 - Thursday (9/3)

• Reading: Introduction to Relational Algebra (Chapter 2)

  • Before lecture:

  • Read: Rest of Chapter 2

  • Course Notes: Relational Algebra

  • Watch (Vimeo Links):

    • Lecture 2.1 Introduction to Relational Algebra

    • Lecture 2.2 Rename, Select, Project

    • Lecture 2.3 Cartesian Product

  • If you want, try out the following queries that we will solve in class tomorrow: Sample queries. from the class example database

• Hand written notes from videos can be found here

• Join lecture at 3:20PM on Webex Teams! (Recording on Mediasite after lecture)

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

• Make sure your time zone is marked on Submitty!

Week 1 - Monday (8/31)

• Introduction to Databases and Relational Data Model

• Before lecture:

  • Read: Chapter 1, 2.1-2.2

  • Course Notes: Introduction

  • Watch (Vimeo Links):

  • Lecture 1: Introduction to Databases

  • Lecture 1.2: Introduction to Relational Data Model

• Hand written notes from videos can be found here

• Attend lecture at 3PM (meet on Webex Teams) (Recording on Mediasite after lecture)

  • Update your personal info, especially Time Zone on Submitty!

  • Complete Lecture 1 Exercise 1 on Submitty (test exercise, not counted towards a grade!)