Meet | Date | Topics and Handouts | Readings | Assignments / Project | Activities / POTDs | |
---|---|---|---|---|---|---|
Course overview and intro to database systems | ||||||
1 | Tue 08/27 | 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 | |||
2 | Thur 08/29 | DB Architecture and Data Model
How do we describe information? |
Ch.2 | |||
Database planning and designing | ||||||
3 | Tue 09/03 | Entity-Relationship (E-R) diagram
How do we represent things in the world in a database? |
Ch.6 | |||
4 | Thur 09/05 | E-R
How do we represent real-world semantics and constraints in a database design? |
Ch.6 | |||
5 | Tue 09/10 |
E-R
What about real-world objects that share some commonality? How do we represent generalization and specialization in a database design? E-R diagrams to relational designsNow that we have a database design, how do we transform the design into database schemas? |
Ch.6 | Project proposal due 09/10, 11:59pm (No late submission, no extension) | ||
Fine tuning and normalization | ||||||
6 | Thur 09/12 | 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 | |||
7 | Tue 09/17 |
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 1
(due 09/18, 10:30am) |
||
8 | Thur 09/19 |
3NF and BCNF
How do we refine the schemas? How do we verify whether the schemas are in the form we want? |
Ch.7 | Assignment 1: E-R & schema (due 09/19, before class) | ||
DIY: Set up DB environment
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. |
||||||
Query language for relational databases | ||||||
9 | Tue 09/24 | 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 |
POTD 2
(due 09/25, 10:30am) |
||
10 | Thur 09/26 |
SQL: 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.3, Ch.5.5 | Assignment 2: Normal forms (due 09/26, before class) | ||
11 | Tue 10/01 | SQL: 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.4.1 | POTD 3
(due 10/02, 10:30am) |
||
12 | Thur 10/03 | SQL: Subqueries
Use SQL queries to assist other queries in solving more complicated problems Subqueries in WHERE and quantifiersHow do we answer "there (not) exists," "for all," "is (not) in," and "only" questions? |
Ch.3.8 | |||
13 | Tue 10/08 | More practice: Putting it all together
Let's solve more complicated problems, using basics, aggregates, joins, and subqueries |
POTD 4
(due 10/09, 10:30am) |
|||
14 | Thur 10/10 | 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? Stored proceduresPre-define operations to handle business logic at the data layer |
Ch.4.4, Ch.5.1-5.3
MariaDB stored procedures |
Project milestone 1: DB design due 10/10, 11:59pm (No late submission, no extension) | ||
DIY: Set up PHP-enabled webspace and verify database connection
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. |
||||||
10/14−10/15 | Fall break, no class (refer to Albany's Academic Calendar) | |||||
Database programming | ||||||
15 | Thur 10/17 | 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) |
Assignment 3: SQL (due 10/17, before class) | Get started with POTD 5. Save your code. (no submission) |
|
16 | Tue 10/22 | DB Interfacing
More database programming in action |
Ch.9
(recommended reading) |
Continue working on POTD 5. Save your code. (no submission) |
||
17 | Thur 10/24 | DB Interfacing
More database programming in action |
Ch.9
(recommended reading) |
POTD 5
(due 10/25, 10:30am) |
||
Database security, social / ethical / legal issues | ||||||
18 | Tue 10/29 | 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 | |||
Query execution and estimating the cost of operations | ||||||
19 | Thur 10/31 | 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 |
Ch.2.6 | Project milestone 2: DB setup and SQL due 10/31, 11:59pm (No late submission, no extension) | ||
20 | Tue 11/05 | Converting SQL ↔ RA
Let'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 | POTD 6
(due 11/06, 10:30am) |
||
21 | Thur 11/07 | 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) |
|||
22 | Tue 11/12 | Query Cost Estimation
More practice to help us analyze and estimate the cost of the query |
Ch.15-16
(recommended reading) |
POTD 7
(due 11/13, 10:30am) |
||
Index structures | ||||||
23 | Thur 11/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 | |||
24 | Tue 11/19 | Indexing
Think about indexing choices and consider how they impact the query cost. How to leverage indexing? |
Ch.14 | Assignment 4: RA and cost estimation (due 11/19, before class) | POTD 8
(due 11/20, 10:30am) |
|
Other aspects in data management | ||||||
25 | Thur 11/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) |
|||
26 | Tue 11/26 | 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) Ch.10 (recommended reading) |
Assignment 5: Indexing (due 11/26, before class) | ||
11/27−12/01 | Thanksgiving Break, no class (refer to Albany's Academic Calendar) | |||||
Course wrap-up | ||||||
27 | Tue 12/03 | Q&A, Office hours on demand
No additional topic/discussion. Feel free to drop by to chat or discuss about anything. |
||||
28 | Thur 12/05 |
Project demo / Showtime - Vote!!
Have fun and show your awesome project; see you in class :-) |
Project final deliverable due 12/05, before class (10:30am) (No late submission, no extension) | |||
FE | Wed 12/11 |
Final exam:
Wednesday 11-December-2024, 10:30am - 12:30pm, Room: TBD
[Please refer to final exam guide for detail] |
Released under the
CC-BY-NC-SA 4.0 license.