Spring 2024 — Schedule

All times on this page are in U.S. Eastern Time
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

     
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    
3 Mon 01/22 DB Architecture and Data Model

How do we describe information?

Ch.2    
Database planning and designing
4 Wed 01/24 Entity-Relationship (E-R) diagram
  • Entity sets, attributes, relationships
  • Cardinality, participation

How do we represent things in the world in a database?

Ch.6    
5 Fri 01/26

Entity-Relationship (E-R) diagram (wrap up)

E-R
  • Roles in relationships
  • Binary vs. Multi-way relationships
  • Weak entity

How do we represent real-world semantics and constraints in a database design?

Ch.6    
6 Mon 01/29 E-R
  • Subclassing

What about real-world objects that share some commonality? How do we represent generalization and specialization in a database design?

Ch.6    
7 Wed 01/31
Add deadline 01/31 		E-R diagrams to relational designs

Now that we have a database design, how do we transform the design into database schemas?

Ch.6    
Fine tuning and normalization
8 Fri 02/02 E-R diagrams to relational designs
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    
9 Mon 02/05 Functional dependencies (wrap up)
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    
10 Wed 02/07 Normalization overview (wrap up)
3NF and BCNF

How do we refine the schemas? How do we verify whether the schemas are in the form we want?

Ch.7    
11 Fri 02/09 3NF and BCNF

Let's experience & practice more schema refinement and verification

Ch.7    
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 		   
13 Wed 02/14 SQL: Basics

More SQL queries in action — "What" data do we want?

Ch.3    
14 Fri 02/16 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.7, Ch.5.5    
15 Mon 02/19

SQL: Aggregates (wrap up)

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    
16 Wed 02/21 SQL: Subqueries

Use SQL queries to assist other queries in solving more complicated problems

Ch.3.8    
17 Fri 02/23 SQL: Subqueries (wrap up)
Guest speakers:
Jared Conway
Charles Fish
Ch.3.8    
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    
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

     
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    
  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 		   
22 Wed 03/13
Withdraw deadline 03/13 		Exam 1
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    
24 Mon 03/18 DB Interfacing

More database programming in action

Recommended reading: Ch.9    
25 Wed 03/20 DB Interfacing

More database programming in action

Recommended reading: Ch.9    
26 Fri 03/22 Wrap up database interfacing
Get started with DB security at the application level 		     
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    
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    
29 Fri 03/29 Relational Algebra (RA) (wrap up)

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    
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    
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    
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    
33 Mon 04/08 Indexing

How does indexing work — to search or retrieve data, insert data, or delete data?

Ch.14    
34 Wed 04/10 Indexing

Let's solve more problems, analyze more scenarios

Ch.14    
35 Fri 04/12 Indexing

Think about indexing choices and consider how they impact the query cost. How to leverage indexing?

Ch.14    
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    
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

     
40 Wed 04/24 Exam 2
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.

     
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

     

Top
Copyright © 2024 Upsorn Praphamontripong

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2024-04-29 13:15