| Meet | Date | Topics and Handouts | Readings | Assignments / Project | Activities / POTDs | |
|---|---|---|---|---|---|---|
| Course overview and intro to database systems | ||||||
| 1 | Wed 01/22 | Get Familiar with Our Course
Let's Set Expectation A bit about this course and how we shall work together Intro to Database SystemsWhat is a database? What is DBMS? Do we need both? Who cares? So what? |
Ch.1 | Pre-assignment: Verify access to class platforms (due ASAP) | DIY: complete the Let's start form
(please complete it by 31-Jan-2025, 11:59pm) Activity: Check-in(no submission) |
|
| 2 | Mon 01/27 | Intro to Database Systems (wrap-up) DB Architecture and Data ModelHow do we describe information? |
Ch.2 | Activity: Brainstorm
(no submission) Data model worksheet
(no submission)
|
||
| Database planning and designing | ||||||
| 3 | Wed 01/29 | Entity-Relationship (E-R) diagram
How do we represent things in the world in a database? |
Ch.6 | Report your project team members | ER worksheet #1
(no submission)
|
|
| 4 | Mon 02/03 | E-R
How do we represent real-world semantics and constraints in a database design? What about real-world objects that share some commonality? How do we represent generalization and specialization in a database design? |
Ch.6 | ER worksheet #2
(no submission)
(no submission) |
||
| 5 | Wed 02/05 |
E-R diagrams to relational designs
Now that we have a database design, how do we transform the design into database schemas? Putting it all togetherLet's draw an ER diagram for a (small) database scenario |
Ch.6 | [9-Feb] Assignment 1 released |
ER worksheet #3
(wrap-up)
(due 02/06, 10:10am) This is an open-ended activity. No sample solution provided. Refer to class discussion for ideas and sample solutions. Activity: ER
(no submission)
|
|
| Fine tuning and normalization | ||||||
| 6 | Mon 02/10 | Functional dependencies
Get started with the first step in fine-tuning a database structure. How do we recognize data interrelationships? How do we make use of them to fine-tune our database? |
Ch.7 | Project proposal
due 02/10, 11:59pm
(No late submission, no extension)
(no submission) [02/17] Project proposal grades released |
FD worksheet
(no submission)
(no submission)
|
|
| 7 | Wed 02/12 |
Normalization overview
Fine-tuning part 2: figure out the fundamentals of what makes good database schemas and how to organize the data (i.e., refine the schemas) to promote ease of use and efficiency Summarize the normal forms by FDsHow do we identify what needs refinement? How do we refine the schemas? How do we verify whether the schemas are in the form we want? |
Ch.7 | POTD 2: FD, F+, Normalization
(due 02/13, 10:10am) [sample solution] Normal form worksheet
(no submission)
|
||
| 8 | Mon 02/17 |
3NF and BCNF
How do we refine the schemas? How do we verify whether the schemas are in the form we want? More 3NF-BCNFLet's experience & practice more schema refinement and verification |
Ch.7 |
Assignment 1: E-R & schema
(due 02/17, before class)
[02/26] Assignment 1 grades released
|
worksheet
(no submission)
(no submission)
|
|
|
DIY: Set up DB environment
You may use any relational databases of your choice. If you are unsure which databases or where to host your databases, the following are some options for you to try.
Redeem and use your Google Cloud Platform coupon(s). Please try to set up the environment before next class. The class discussion will be more effective and enjoyable if your environment is readily available. Additional resources: Create Google cloud storage bucket Manage Google cloud project members Google Cloud Skills Boost Many of these resources are free and include detailed steps to complete practice projects of varying complexity. You can use the GCP credits (refer to an announcement on how to redeem and obtain additional GCP coupons) toward these labs. |
||||||
| Query language for relational databases | ||||||
| 9 | Wed 02/19 | SQL: Basics
Now that we have data and schemas, how do we access them? What happens when we execute the SQL query? |
Ch.3
W3 school SQL lab |
[19-Feb] Assignment 2 released |
||
| 10 | Mon 02/24 | SQL: Basics
(wrap-up)
Aggregates How do we effectively summarize the results? What is the order of actions? How do we handle "for-each" semantics? Applying the conditions on groups vs. individuals. |
Ch.4.1, Ch.5.5 | POTD 3:
Candy Inventory
(ER → schema → fine tuning → SQL) (due 02/25, 10:10am) (require: DB environment) (no submission) This is an open-ended activity. No sample solution provided. Activity: Basics-Aggregates-Joins (no submission) |
||
| 11 | Wed 02/26 | Joins
What if we need data from multiple tables? How do we describe a relationship between tables? How do we realize combinations of data? |
Ch.3.8 |
Assignment 2: Normal forms
(due 02/26, before class)
[03/06] Assignment 2 grades released
|
Activity: Basics-Aggregates-Joins
(no submission, wrap-up)
|
|
[02/26: Updated the class schedule]
— Per the class discussion, we decided to
|
||||||
| 12 | Mon 03/03 | More practice: Putting it all together
Let's solve more complicated problems, using basics, aggregates, joins, and subqueries |
POTD 4: Putting it all together
(due 03/04, 10:10am) [sample solution] |
|||
| 13 | Wed 03/05 | Subqueries
Use SQL queries to assist other queries in solving more complicated problems |
Ch.3.8 | Project milestone 1: DB design due 03/05, 11:59pm (No late submission, no extension) | Activity: Subqueries and quantifiers
(no submission) |
|
| 14 | Mon 03/10 |
Exam 1
[Exam 1 guide] |
||||
| 15 | Wed 03/12 | Subqueries in WHERE and quantifiers
How do we answer "there (not) exists," "for all," "is (not) in," and "only" questions? |
Ch.4.4, Ch.5.1-5.3
MariaDB stored procedures |
[14-Mar] Assignment 3 released |
Activity: Subqueries and quantifiers
(no submission, wrap-up) [sample solution] |
|
|
DIY: Set up PHP-enabled webspace and verify database connection
Please try to set up the environment before class. The class discussion will be more effective and enjoyable if your environment is readily available. Note: You are not required to use PDO. You may use PDO, MySQLi, or MySQL (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 PDO is recommended over MySQLi (and CSI 508 uses PDO). PHP deployment (XAMPP)PHP deployment (GCP) Connecting PHP and DB Deploying PHP on Heroku [video] (Thanks to Austin Houck, my former Database TA at UVA, for creating this tutorial) Note on GCP: **DO NOT** enter your credit card. We do not have funding to cover the charge for you. We cannot help you pay. |
||||||
| 03/15−03/21 | Spring break, no class (refer to Albany's Academic Calendar) | |||||
| Database programming | ||||||
| 16 | Mon 03/24 | Advanced SQL: Constraints and Triggers
How much of our business logic should reside at the presentation layer, logic layer, or data layer? How do we implement business logic at the data layer? Assertion (Assertion will not be tested) Stored proceduresPre-define operations to handle business logic at the data layer DB Interfacing (overview) Putting it all together — Let's develop a small web app that interacts with a database to provide services |
Ch.9
(recommended reading) |
sample code
Get started with POTD 5. Save your code. |
||
|
The following resources are for references. They will not be tested. Basic web deploymentAdditional 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) Additional resources and examples: for developers who are curious and/or prefer Java:
|
||||||
| 17 | Wed 03/26 | DB Interfacing
More database programming in action |
Ch.9
(recommended reading) |
Assignment 3: SQL
(due 03/26, before class)
[04/08] Assignment 3 grades released
|
Continue working on POTD 5. Save your code. |
|
| 18 | Mon 03/31 | DB Interfacing
More database programming in action |
Ch.9
(recommended reading) |
POTD 5: DB programming
(due 04/01, 10:10am) [sample solution] |
||
| Database security, social / ethical / legal issues | ||||||
| 19 | Wed 04/02 | DB security
(Some ways to) Protect our databases and apps that use them. How do we minimize the chance of being attacked? How do we enforce "who can do what on which parts of the database"? |
Ch.4.7, Ch.9.8-9.9 | Project milestone 2: DB setup and SQL due 04/02, 11:59pm (No late submission, no extension) | prevent-sql-injection-template |
|
|
The following resources are for references. They may be useful for your project. They will not be tested. Examples:
|
||||||
| Query execution and estimating the cost of operations | ||||||
| 20 | Mon 04/07 | Relational Algebra (RA)
SQL describes "what data to get" but computers only understand the "how" — Let's look at "how to get the data" we want Converting SQL ↔ RALet's solve more problems: English to SQL to RA. How about RA (to design a query) to SQL (to be executed against a database)? |
Ch.2.6 | Activity:
RA–1
(no submission)
(no submission)
(no submission)
|
||
| 21 | Wed 04/09 | Query Cost Estimation
We wrote a SQL query, which tells the computer what we want. How does DBMS find a good way to actually do it? Also, there may be multiple queries to solve the same problem. Which one should we use? Let's explore some ideas to help us pick |
Ch.15-16
(recommended reading) |
[11-Apr] Assignment 4 released |
||
| Index structures | ||||||
| 22 | Mon 04/14 | Indexing
Mechanism to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed. How does indexing work — to search or retrieve data, insert data, or delete data? |
Ch.14 | POTD 6: Query cost estimation
(due 04/15, 10:10am) [sample solution] worksheet
(no submission)
|
||
| 23 | Wed 04/16 | Indexing
Think about indexing choices and consider how they impact the query cost. How to leverage indexing? |
Ch.14 | More practice: Indexing
(no submission)
|
||
| Other aspects in data management | ||||||
| 24 | Mon 04/21 | Transactions and Concurrency Control
How do we support multiple people using a database at the same time? What is "ACID" and how does it help ensure transaction safe? |
Ch.17,
Ch.18-19 (recommended reading) |
Assignment 4: RA and cost estimation
(due 04/21, before class)
(sample solution — in Brightspace) |
||
| 25 | Wed 04/23 | Distributed DB
What to do when our databases scale and have increased traffic? What is "CAP" and how does it help us determine how to handle a distributed database system when some servers refuse to communicate with each other due to some problems? NoSQL DBLet's explore other types of data models. NoSQL vs. Relational databases. What to use and when to use it? |
Ch.20-24
(recommended reading for Distributed DB) Ch.10(recommended reading for NoSQL DB) |
|||
| Course wrap-up | ||||||
| 14 | Mon 04/28 |
Exam 2
[Exam 2 guide] |
||||
| 27 | Wed 04/30 | Q&A, Office hours on demand, Final touch your project
No additional topic/discussion. Feel free to drop by — use the Zoom link — to chat or discuss about anything. Enjoy the last week(s) of the semester.
We meet on Zoom. No in-person class meeting.
Zoom link
(remember to log in with your UAlbany account)
|
||||
| 28 | Mon 05/05 |
Project demo / Showtime - Vote!!
Have fun and show your awesome project; see you in class :-) |
Project final deliverable due 05/05, before class (10:10am) (No late submission, no extension) |
Demo sign-up sheet
(The link is also on the Project's final deliverable's page, Brightspace's 05/01 announcement, and your mailbox with the 05/01 timestamp) |
||
CC-BY-NC-SA 4.0