Meet | Date | Topics and Handouts | Readings | Assignments / Project | Activities / POTDs | ||
---|---|---|---|---|---|---|---|
The most practical project:
UVA Grounds Locations by Andrew Hunter, Joshua Seiden, Jieshuai Zhu The coolest project: Heritage by Daniel Huynh, Saahith Janapati, Eric Li, Harshal Nallapareddy |
|||||||
Course overview and intro to database systems | |||||||
1 | Wed 01/17 | Get Familiar with Our Course
Let's Set Expectation A bit about this course and how we shall work together |
Activity: Get to know you
(no submission) Activity: Check-in(no submission) DIY: complete the Let's start form (please complete it by 24-Jan-2024, 11:59pm) |
||||
2 | Fri 01/19 | Intro to Database Systems
What is a database? What is DBMS? Do we need both? Who cares? So what? |
Ch.1 | Activity: What is a database?
(no submission) Activity: Brainstorm — scheduler(no submission) |
|||
3 | Mon 01/22 | DB Architecture and Data Model
How do we describe information? |
Ch.2 | worksheet
(no submission)
|
|||
Database planning and designing | |||||||
4 | Wed 01/24 | Entity-Relationship (E-R) diagram
How do we represent things in the world in a database? |
Ch.6 | worksheet
(no submission)
|
|||
5 | Fri 01/26 | Entity-Relationship (E-R) diagram (from slide 17) E-R
How do we represent real-world semantics and constraints in a database design? |
Ch.6 | worksheet
(no submission)
|
|||
6 | Mon 01/29 |
E-R
What about real-world objects that share some commonality? How do we represent generalization and specialization in a database design? |
Ch.6 | Activity: ER (Santa's workshops)
(no submission)
|
|||
7 | Wed 01/31
Add deadline 01/31 |
E-R diagrams to relational designs
(from slide 14)
Now that we have a database design, how do we transform the design into database schemas? |
Ch.6 | worksheet
(no submission, wrap-up)
|
|||
Fine tuning and normalization | |||||||
8 | Fri 02/02 | E-R diagrams to relational designs
(from slide 32)
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 | [4-Feb] Assignment 1 released |
FD worksheet
(no submission)
|
||
9 | Mon 02/05 | Functional dependencies
(from slide 30)
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 |
Ch.7 | Project proposal due 02/05, 11:59pm (No late submission, no extension) | POTD 1: FD, F+, Normalization
(due 02/06, 12pm/noon) [sample solution] |
||
10 | Wed 02/07 | Normalization overview
(from slide 15)
Summarize the normal forms by FDs How 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? 3NF and BCNFHow do we refine the schemas? How do we verify whether the schemas are in the form we want? |
Ch.7 | worksheet (Summarize the normal forms slides)
(no submission)
worksheet (3NF and BCNF slides)
(no submission)
|
|||
11 | Fri 02/09 | 3NF and BCNF
(from slide 10)
more practice Let's experience & practice more schema refinement and verification |
Ch.7 | [11-Feb] Assignment 2 released |
worksheet (more practice slides)
(no submission)
|
||
DIY: Set up DB environment
**DO NOT** enter your credit card. We do not have funding to cover the charge for you. We cannot help you pay. 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: 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 | |||||||
12 | Mon 02/12 | 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 |
Assignment 1: E-R & schema
(due 02/12, before class)
(sample solution in Canvas/Files) |
POTD 2: Candy inventory
(due 02/13, 12pm/noon)
(require: DB environment)
|
||
13 | Wed 02/14 | SQL: Basics (wrap up) |
Ch.3 | Activity: SQL basics
(no submission) [sample solution] |
|||
14 | Fri 02/16 | More basic SQL practice
More SQL queries in action — "What" data do we want? SQL: AggregatesHow 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.7, Ch.5.5 | Activity: SQL basics & aggregates
(no submission) [sample solution] |
|||
15 | Mon 02/19 |
SQL: Aggregates (wrap up) SQL: JoinsWhat 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 |
Assignment 2: Normal forms
(due 02/19, before class)
(sample solution in Canvas/Files) |
POTD 3: SQL join
(due 02/20, 12pm/noon) [sample solution] |
||
16 | Wed 02/21 |
SQL: Subqueries
Use SQL queries to assist other queries in solving more complicated problems |
Ch.3.8 | worksheet
(no submission)
|
|||
17 | Fri 02/23 | SQL:
Subqueries
(wrap up)
Guest speakers:
|
Ch.3.8 | Activity: SQL subqueries
(no submission) [sample solution]Activity: more practice (no submission) [sample solution] |
|||
18 | Mon 02/26 | Subqueries in WHERE and quantifiers
How do we answer "there (not) exists," "for all," "is (not) in," and "only" questions? |
Ch.3.8 | Project milestone 1: DB design due 02/26, 11:59pm (No late submission, no extension) | POTD 4: Subqueries and quantifier
(due 02/27, 12pm/noon) [sample solution] |
||
19 | Wed 02/28
Drop deadline 02/28 |
More practice: Putting it all together
Let's solve more complicated problems, using basics, aggregates, joins, and subqueries |
Activity: Putting it all together
(no submission) [sample solution] |
||||
20 | Fri 03/01 | 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) |
Ch.4.4, Ch.5.1-5.3 | [3-Mar] Assignment 3 released |
Activity: SQL wrap-up
(no submission) [sample solution] |
||
03/02−03/10 | Spring recess, no class (refer to UVA Academic Calendar) | ||||||
21 | Mon 03/11 | Advanced SQL:
Stored procedures
Pre-define operations to handle business logic at the data layer |
Ch.4.4, Ch.5.1-5.3
MariaDB stored procedures |
Assignment 3: SQL
(due 03/11, before class)
Per our conversation in class on 02/28, we decided to move the due date of assignment 3 to 03/18. |
sample code
(no submission) |
||
22 | Wed 03/13
Withdraw deadline 03/13 |
Exam 1
[Exam 1 guide] |
|||||
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 CS 4750 uses PDO). PHP deployment (XAMPP)PHP deployment (CS) PHP deployment (GCP) Connecting PHP and DB Deploying PHP on Heroku [video] (Thanks to Austin Houck, a former CS4750 TA, for creating this tutorial) 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. 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. |
|||||||
Database programming | |||||||
23 | Fri 03/15 | DB Interfacing (overview)
Putting it all together — Let's develop a small web app that interacts with a database to provide services |
Recommended reading: Ch.9 | Get started with POTD 5. Save your code. (no submission) Class recording — in Canvas/Panopto |
|||
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:
|
|||||||
24 | Mon 03/18 | DB Interfacing
More database programming in action |
Recommended reading: Ch.9 |
Assignment 3: SQL
(due 03/18, before class)
(sample solution in Canvas/Files) |
Continue working on POTD 5. Save your code. (no submission) POTD 5: [work in progress] Class recording — in Canvas/Panopto |
||
25 | Wed 03/20 | DB Interfacing
More database programming in action |
Recommended reading: Ch.9 |
POTD 5: DB programming
Class recording — in Canvas/Panopto |
|||
26 | Fri 03/22 | Wrap up database interfacing
Get started with DB security at the application level |
POTD 5: DB programming
(due 03/23, 12pm/noon) [Code for request.php that we finished in class] Please remember to complete the code to manipulate the table (request-db.php). Class recording — in Canvas/Panopto |
||||
Database security, social / ethical / legal issues | |||||||
27 | Mon 03/25 | 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 03/25, 11:59pm (No late submission, no extension) |
prevent-sql-injection-template
sample run: [bad], [good] sample code: [bad], [good] DB security – share your ideas |
||
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 | |||||||
28 | Wed 03/27 | 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 | POTD 6: Relational Algebra
(due 03/28, 12pm/noon) |
|||
29 | Fri 03/29 | Relational Algebra (RA)
(from slide 37)
More "how to retrieve the data" 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)? Please practice on your own. Bring questions to class so we can discuss them. |
Ch.2.6 | Wrap up POTD 6: Relational Algebra
(due 03/30, 12pm/noon) [sample solution] |
|||
30 | Mon 04/01 | 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 |
Recommended reading: Ch.15-16 | worksheet
(no submission)
|
|||
31 | Wed 04/03 | Query Cost Estimation
More practice to help us analyze and estimate the cost of the query |
Recommended reading: Ch.15-16 | [4-Apr] Assignment 4 released |
POTD 7: Query cost estimation
(due 04/04, 12pm/noon) |
||
Index structures | |||||||
32 | Fri 04/05 |
Indexing
Mechanism to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed |
Ch.14 | EC milestone: DB programming due
04/05, 11:59pm
(No late submission, no extension)
[2-Apr] Per your suggestion, the EC milestone's due date was moved to 04/08. |
Wrap up POTD 7: Query cost estimation
(due 04/06, 12pm/noon) [sample solution] worksheet (no submission) |
||
33 | Mon 04/08 | Indexing
How does indexing work — to search or retrieve data, insert data, or delete data? |
Ch.14 | EC milestone: DB programming due 04/08, 11:59pm (No late submission, no extension) | worksheet
(no submission) |
||
34 | Wed 04/10 | Indexing
Let's solve more problems, analyze more scenarios |
Ch.14 | [11-Apr] Assignment 5 released |
POTD 8: Indexing
(due 04/11, 12pm/noon) [sample solution] |
||
35 | Fri 04/12 | 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 04/12, 12pm/noon)
(sample solution in Canvas/Files) |
worksheet
(no submission) [sample solution] |
||
Other aspects in data management | |||||||
36 | Mon 04/15 | 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
Recommended reading: Ch.18-19 |
||||
37 | Wed 04/17 | 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? |
Recommended reading: Ch.20-24 | ||||
38 | Fri 04/19 | NoSQL DB
Let's explore other types of data models. NoSQL vs. Relational databases. What to use and when to use it? |
Recommended reading: Ch.10 |
Assignment 5: Indexing
(due 04/19, 12pm/noon)
(sample solution in Canvas/Files) |
|||
The following resources (borrowed from CS 4640) are for references. They may be useful for your project. They will not be tested.
|
|||||||
Course wrap-up | |||||||
39 | Mon 04/22 | On demand office hours / work on project / prepare for Exam 2
No lecture/class discussion. Feel free to drop by for Q&A or to chat or discuss about anything |
|||||
Class canceled due to emergency. Please refer to the announcement (Canvas > Announcement, Piazza, and UVA mailbox) sent on 04/22. |
|||||||
40 | Wed 04/24 |
Exam 2
[Exam 2 guide] |
|||||
41 | Fri 04/26 | On demand office hours / work on project
No lecture/class discussion. Feel free to drop by for Q&A or to chat or discuss about anything. Enjoy the last week(s) of the semester. |
No class meeting. On demand OHs – Please use
Zoom link (remember to log in with your NetBadge) (Zoom link also in Canvas) |
||||
42 | Mon 04/29 |
Project demo / Showtime - Vote!!
Have fun and show your awesome project; see you in class :-) Remember to sign up & demo your project to the course staff for grading |
Project final deliverable due 04/29, before class (12pm/noon) (No late submission, no extension) | Please complete the
course evaluation
by 3-May-2024
|
Released under the CC-BY-NC-SA 4.0 license.
Last updated 2024-04-29 13:15