Class Topics

Approximate schedule and topics covered (notice that this schedule is subject to change and will be adjusted as needed throughout the semester) We would like to have an environment in which everyone can speak up and discuss ideas freely without concern that discussions will be available outside of classroom. Thus we will not record the course sessions. The lectures are slides are comprehensive and should allow you to be able to remember the key lessons from class (except for background stories I may tell you). Lecture slides will be posted after each class, usually by the end of the day following class. Also notice that due dates are usually Thursdays each week (even if shown in the Wednesday row below).

,
#     Date        Approximate Topics Slides & further readings Thursdays
SQL
1 W Sept 7 course overview, databases, client-server architecture, tables, database schema, basic SQL (SELECT FROM WHERE), selection, projection Setup PostgreSQL,
L01-Introduction, L01-SQL
2 M Sept 12 distinct, ordering, in, like, schemas and key constraints and foreign key constraints (referential integrity), joins Setup Gradiance, L02-SQL,
SAMS Ch 1-3, SDK 3.1-3.3, 3.4.4, 3.4.5, 3.9, 4.1, 4.4.5, 4.5.1
3 W Sept 14 table & column alias, alternative join syntax (join on), conceptual evaluation strategy of SQL, table aliases for self-joins, cross join, equi-joins, create tables and insert values, aggregates L03-SQL, SAMS 4 & 12 G1, HW0
4 M Sept 19 aggregates, grouping, having, sequence by which clauses are evaluated, arithmetic functions L04-SQL, SAMS Ch 5-9, SDK 3.7
5 W Sept 21 groupings, nested queries, IN, ANY, ALL L05-SQL, SAMS Ch 10-17, SDK 3.8 G2, HW1
6 M Sept 26 IMDB database schema, with clause (common table expression), witnesses L06-SQL, SDK 3.6
7 W Sept 28 null values, theta joins, inner vs outer vs anti-joins, logical foundation of nested queries L07-SQL, SDK 4.1.3 G3, HW2
8 M Oct 3 anti-joins and null values, examples on grouping and WITH clause, outer joins and coalesce, set operations, window functions L08-SQL, SDK 5.5.1, 3.5
9 W Oct 5 recursion, top-k (limit, fetch first), linear regression, SQL injection L09-SQL, SDK 4.4 G4, HW3
M Oct 10 holiday
10 W Oct 12 Exam 1 HW4
Database Design and Normal Forms
11 M Oct 17 ER diagrams: entities and relationships L11-Design, SDK 6.1-6.5.2, 6.6, 7.9.1-7.9.3
12 W Oct 19 ER diagrams: associative entities L12-Design, SDK 6.9 G5
13 M Oct 24 ER diagrams: weak entities, connection traps L13-Design, SDK 6.5.3
14 W Oct 26 relational modeling, ERD notation overview & textbook figures L14-Design, SDK, 6.7, 6.10 HW5, P0
15 M Oct 31 relational modeling: entities, relationships, associative entities L15-Design, Notation Overview, SDK 6.7
16 W Nov 2 ONLINE CLASS, relational modeling: weak entities SDK 6.8 G6, P1
17 M Nov 7 design practice
18 W Nov 9 Exam 2 in Robinson Hall RB 109 G7, HW6
19 M Nov 14 enhanced ERD = subtypes & translation L19-Design, SDK 6.8
20 W Nov 16 normalization: normal forms and FDs L20-Design, L20-Normalization, SDK 7.8, 7.2, 7.3, 7.3.2, 7.5.2, SDK 7.3.1, 7.3.3, 7.1, 7.2 P2
M Nov 21 cancelled
W Nov 23 holiday
21 M Nov 28 normalization: normal forms and FDs L21-Normalization, SDK 7.8, 7.2, 7.3, 7.3.2, 7.5.2, SDK 7.3.1, 7.3.3, 7.1, 7.2
22 W Nov 30 normalization: normal forms and FDs, BCNF, decompositions L22-Normalization, SDK 7.8, 7.2, 7.3, 7.3.2, 7.5.2, SDK 7.3.1, 7.3.3, 7.1, 7.2
Transaction Processing
23 M Dec 5 course evaluation, a quick overview of transactions (ACID, logging), concurrency (interleaving, conflict serializability), locking (2P, recoverability, strict 2PL, deadlocks) SDK 17, 18.1.1-18.1.3, 18.2.2.1, Stanford book Ch 18 (in Canvas)
24 W Dec 7 L24-Normalization-Practice G8, P3
M Dec 12 Exam 3 in Shillman Hall SH 105 8:20am-9:50am (1.5h) P4 (12/14), P5 (12/16)

Textbook Sections & Class Resources

Our main course textbook is "SKD": Database systems concepts by Silberschatz, Korth, Sudarshan 7th ed. It is available in the Northeastern bookstore or from Amazon for around 60$. Our goal is to cover content from following chapters, yet not in the same sequence as in the book:

For learning SQL, we additionally suggest chapters from following book: SAMS: Teach yourself SQL in 10min by Forta. 5th ed. 2019. (Don't let yourself be fooled by the title, we need more than 10min...). It can be worked through at your own pace in parallel to our treatment of SQL in class. It is available for free as Safari books eBook (NEU free online access, you may have to first login from our library website, then try again the previous link) or as EBSCOhost eBook (NEU free online access) (there is almost no difference between 4th and 5th ed), or an even older version: EBSCOhost eBook (NEU free online access, but even older edition), or as hard copy from Amazon for around 20$.

For our discussion on concurrency and lock, I am also posting an excerpt from chapter 18 of the Stanford textbook "The Complete book" (2nd ed, international edition, 2014) on Canvas.


Postgres & pgAdmin 4

We are using postgres as open-source and high-performance database in this class. Please follow the tutorials below to get started. If you are stuck, please post on Piazza with a screenshot of your problem, we try to help you. If we cannot help you via Piazza, please come to our office hours or schedule a private meeting. Based on issues we see you are having, we may improve and iterate on these tutorials.

cs3200-Setup-PostgreSQL.pdf

cs3200-Setup-PostgreSQL.pdf

Further resources: Handy keyboard shortcuts


Example SQL databases

You will see many SQL examples in class. I like to use various minimal databases to illustrates concepts. You can recreate the scenario and practice hands-on by re-creating those databases with the linked SQL files. I will reference to those databases in class and my slides just by their unique number (think of the number as a "key").


Further optional resources: