This is a tentative syllabus for the course. Links will be added regularly with topics and assignments. Notes will be provided for each class, linked from the date. Unless otherwise indicated, chapter references are to the textbook Database System Concepts, 7th edition, by Silberschatz, Korth, and Sudarshan. Feedback is appreciated — typos, suggestions for more detailed explanations, additional examples, etc.
| Date | Topic | Subtopics | Chapter | Out | Due |
|---|---|---|---|---|---|
| Query existing relational databases for insight | |||||
| Mar 31 | Introduction | Logistics, intro to databases | skim 1.1-1.10 | Lab 0 | |
| Apr 2 | Basic SQL | Relational databases design, relational algebra, intro to SELECT, NYC open data | 2.1-2.6 | Project | Lab 0 |
| Apr 7 | Aggregation | Aggregate functions, grouping, nested queries, window functions | 3.4-3.8 | ||
| Apr 9 | CRUD | Inserting/updating/deleting rows, keys | 3.1-3.3, 3.9-3.10 | ||
| Apr 14 | Joins | Relational algebra 2, natural, inner, outer, left, right joins | 4-4.1 | Lab 1 | |
| Apr 16 | Intermediate SQL | Views, transactions, object-oriented data, date and calculated fields | 4.2-4.6, 8.2 | Lab 2 | Lab 1 |
| Apr 21 | Advanced SQL | Stored functions and procedures, triggers | 5.2-5.3 | ||
| Apr 23 | midterm 1 | Lab 2 | |||
| Design your own efficient databases | |||||
| Apr 28 | ER models | Tables, relationships, attributes, reduction to relational schemas | 6.1-6.7, 6.10 | ||
| Apr 30 | Normalization | First, second, third normal forms (and more!) | skim 7.1-7.9 | Lab 3 | Project teams |
| May 5 | Programming access | Application Access & REST APIs | 5.1, 9.1-9.2 | ||
| May 7 | Security | Authorization, SQL injection attacks, salt & pepper, password cracking | 4.7 | Lab 4 | Lab 3 |
| May 12 | Project review meetings | Team meeting with instructor to discuss final project idea | |||
| May 14 | midterm 2 | Lab 4 | |||
| Understand database internals | |||||
| May 19 | Data persistence | File organization; indexing | 14.1-14.3 | Project plan | |
| May 21 | Query optimization | Query cost, select, sort, join | 15.1-15.7 | ||
| May 26 | Transactions/concurrency | Inconsistencies, ACID properties, locks, concurrency control | 17.1-17.6, 18.1-18.2 | Project EERD | |
| Describe alternative database technologies | |||||
| May 28 | Parallel and distributed systems | Mongo intro | 21.1-21.6 | ||
| Jun 2 | NoSQL/Mongo CRUD | Mongo documents, JSON, CRUD operations | skim 10.2-10.6, 20.7 | ||
| Jun 7 | Project final | ||||