Fall 2020 — Schedule

Tentative class schedule (may be updated without prior notice) How are you feeling?   
Meet Date Topics and Handouts Readings Assignments / Project In-Class Exercises
1 Wed 08/26 Course overview
Ch.1   Activity: Check-in
(no submission)
2 Fri 08/28 Intro to Database Systems
DB Architecture and Data Model
3 Mon 08/31 DB Architecture and Data Model (from slide 21)

DB Design: E-R Model
  • Entity sets, attributes, relationships
  • Cardinality, participation
4 Wed 09/02 DB Design:
E-R Model (continue, from 08/31, slide 16)

E-R Model
  • Roles in relationships
  • Binary vs. U-ary relationships
5 Fri 09/04 DB Design: E-R Model
  • Weak entity
  • Subclassing
  • E-R diagrams to relational designs
6 Mon 09/07
Add deadline 09/08
DB Design: E-R Model Ch. 6   Practice: ER to tables
(no submission)
Practice: Apply to a scenario
(no submission)
7 Wed 09/09 DB Design: Functional dependencies Ch.7   POTD 1: FD and F+
(Submit to Collab by 09/10, 11:00am)
8 Fri 09/11 DB Design: Normalization overview Ch.7 Assignment 1: E-R model (due before class, 11:00am)  
9 Mon 09/14 Summarize the normal forms by FDs
DB Design: 3NF and BCNF
Ch.7 Project proposal due Monday 09/14 by 11:59pm (No late submission, no extension)  Activity: 3NF and BCNF 
(no submission)
10 Wed 09/16 DB Design: 3NF and BCNF Ch.7   Activity: 3NF and BCNF (no submission)

POTD 2: 3NF and BCNF
(Submit to Collab by 09/17, 11:00am)

To do (DIY): Setup environment Please try to set up the environment before Friday's class. The class discussion will be more effective and enjoyable if your environment is readily available.
11 Fri 09/18 SQL: Basics Ch. 3   Activity: Basic SQL (no submission)
(require DB environment)
12 Mon 09/21 SQL: Aggregates Ch.3.7, Ch.5.5 Assignment 2: Normal forms (due before class, 11:00am)  
13 Wed 09/23 SQL: Aggregates Ch.4.1   POTD 3: Basic SQL
(Submit to Collab by Friday 09/25, 11:00am)
14 Fri 09/25 SQL: Joins Ch.4.1   Activity: Join (no submission)
15 Mon 09/28 SQL: Subqueries Ch.5.3    
16 Wed 09/30 SQL: Subqueries (from slide 14)
Subqueries in WHERE
Ch.5.3   POTD 4: SQL — subqueries
(Submit to Collab by Friday 10/02, 11:00am)
17 Fri 10/02 SQL: Triggers,  Stored procedures 

Additional resource (will not be tested): Assertions
MariaDB stored procedures
Project milestone 1: DB design due Sunday 10/04 by 11:59pm (No late submission, no extension)  
18 Mon 10/05 Stored procedures
DB Interfacing

Note: (If you plan to develop a web-based project using PHP) You are not required to use PDO. You may use PDO, MySQLi, or MySQL (of course, the syntax and implementation will be slightly different). MySQLi is a replacement for the MySQL functions, with object-oriented and procedural versions. PDO (PHP Data Objects) is a database abstraction layer providing flexibility for many database engines (and thus is recommended over MySQLi).

    PHP deployment (XAMPP)
PHP deployment (CS)
PHP deployment (GCP)
Connecting PHP and DB
Basic web deployment (borrowed from CS 4640)

Activity: get started with database programming
[example run]

Example: call a stored procedure from PHP
Additional software development resources including basic HTML, CSS, JavaScript, JQuery, Ajax, Angular, PHP, Java servlet, JSP — may be useful if you plan to develop a web-based project.
19 Wed 10/07 DB Interfacing     POTD 5: Database interfacing
(Submit to Collab by  Friday 10/09, Sunday 10/11, 11:00am)
[example run]

For developers who are curious, like challenging, and prefer Java: (will not be tested)
20 Fri 10/09  Relational Algebra (RA) 
DB Interfacing (continue)
    POTD 5: Database interfacing (continue)
(Submit to Collab by  Friday 10/09, Sunday 10/11, 11:00am)
[example run]
21 Mon 10/12
Drop deadline 10/13
Relational Algebra (RA) Ch.2.6 Assignment 3: SQL (due before class, 11:00am)  
22 Wed 10/14 Relational Algebra (RA) Ch.2.6   Activity: RA — sailors
(no submission)

(Submit to Collab by Friday 10/16, 11:00am)
23 Fri 10/16 Query Cost Estimation Ch. 15    
24 Mon 10/19 Query Cost Estimation Ch. 15    
25 Wed 10/21 Query Cost Estimation (wrap up, from slide 54)

Ch.14    POTD 7: B+ tree
(Submit to Collab by Friday 10/23, 11:00am)  
26 Fri 10/23 Indexing

Additional resource: Storage and file structure (will not be tested)
Ch.14 Project milestone 2: DB setup and SQL due Sunday 10/25 by 11:59pm (No late submission, no extension) POTD 7: B+ tree
(Submit to Collab by Sunday 10/25, 11:00am)
(see slides for sample solutions)
27 Mon 10/26
Withdraw deadline 10/27
Indexing Ch.14   B+ tree worksheet
(see slides for sample solutions)
 Activity: Indexing 
28 Wed 10/28 Guest speaker: DB security: part 1, part 2, part 3

Additional resources: (will not be tested)
AWE servers to serverless
AWS101 workshop
    POTD 8: DB security
(Submit to Collab by Friday 10/30, 11:00am)
(This is an open-ended activity; no sample solution)
29 Fri 10/30 More DB security
Indexing (wrap-up, from slide 79)

Activity: Indexing
(see class recording for sample solutions)
30 Mon 11/02 Transactions and Concurrency Control Ch.17, 18 Assignment 4: Relational Algebra and query cost estimation (due before class, 11:00am)
Example: prevent-sql-injection
— sample run: [bad], [good]

 Activity: Transactions 
31 Wed 11/04 Transactions and Concurrency Control (from slide 34)
Distributed DB
    Activity: Transactions
(sample solution included)
32 Fri 11/06 NoSQL DB
Guest speaker: Where to learn more: (will not be tested)
33 Mon 11/09 Distributed DB (from slide 11, wrap-up)

On demand office hours (use lecture Zoom link),
Work on project
  Assignment 5: Indexing (due before class, 11:00am)
34 Wed 11/11 On demand office hours (use lecture Zoom link),
Work on project
35 Fri 11/13 On demand office hours (use lecture Zoom link),
Work on project
  Project final deliverable due Sunday 11/15 by 11:59pm (No late submission, no extension)  
36 Mon 11/16 Project demo / Showtime - Vote!!
No class meeting — enjoy the last week(s) of the semester; have fun and demo your awesome project :-)
The "most practical" project The "coolest" project

Congratulations to these awesome projects !!

  Extra credit: SQL due Friday 11/20, before class, 11:00am (No late submission, no extension)  
37 Wed 11/18
38 Fri 11/20
39 Mon 11/23 Course wrap-up and final exam guide      
FE Thur 12/03 Final Exam: Thursday, December 03, 2020
Please refer to Course wrap-up and final exam guide for time and detail
(please refer to the university final exam schedule)

Copyright © 2020 Upsorn Praphamontripong

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2020-11-22 13:36