CPSC 343 Database Theory and Practice Fall 2024

CPSC 343 Schedule

Unless otherwise noted, daily lessons are to be completed by 10pm the night before the class period where they are listed.

Things in light gray have not been assigned yet; they are listed for planning purposes and dates may shift slightly.

 Assignments

Week 1: 8/26-8/30

Topics: course introduction and preliminaries; information management concepts, database systems, relational databases; SQL - simple queries, set operations, subqueries

  
Mon Materials from class:

Supplemental material: (for additional reference)

 introductory survey
("preliminaries" module on Canvas)

due Fri 8/30

laptop setup — be able to start up MySQL Workbench either through a virtual Linux desktop (recommended) or directly on your laptop
(see info in the "systems and software" module on Canvas)

due Wed 8/28		  
Wed Daily lesson: SQL — basics (on Canvas)

Reference: MySQL SELECT syntax

Materials from class:

 homework
SQL — basics
(on Canvas)

due 9/2		  
Fri Daily lesson: SQL — set operations and subqueries (on Canvas)

Reference: MySQL subquery syntax

Materials from class:

 homework
SQL — set operations and subqueries
(on Canvas)

due 9/6		  

Week 2: 9/2-9/6

Topics: SQL — set operations, subqueries, aggregation and grouping

  
Mon Materials from class:
  • slides: writing queries
  • practice problems (from the SQL set operations and subqueries module on Canvas)
 
Wed Daily lesson: SQL — aggregation and grouping (on Canvas)

Reference: MySQL aggregate functions

Materials from class:

 homework
SQL — aggregation and grouping
(on Canvas)

due 9/11		  
Fri Materials from class:
  • practice problems (from the SQL aggregation and grouping module on Canvas)
 

Week 3: 9/9-9/13

Topics: SQL (complex queries, data modification); conceptual modeling (ER)

  
Mon Materials from class: homework
SQL — complex queries
(on Canvas)

due 9/13
Wed Daily lesson: SQL — data modification (on Canvas)

Reference:

Materials from class: 		homework
SQL — data modification
(on Canvas)

due 9/16
Fri Daily lesson: ER modeling (on Canvas)

Materials from class:

 homework
ER modeling
(on Canvas)

due 9/20 		project — topic due

Week 4: 9/16-9/20

Topics: conceptual modeling (ER and EER), ER design principles

  
Mon Materials from class:
  • practice problems (from the ER modeling module on Canvas)
 
Wed Daily lesson: ER design principles (on Canvas)

Materials from class:

 SQL cutoff
SQL homework problems will not be accepted for credit after 11:59pm 9/18
homework
ER design principles
(on Canvas)

due 9/23
Fri Daily lesson: EER modeling (on Canvas)

Materials from class:

 homework
EER modeling
(on Canvas)

due 9/27

Week 5: 9/23-9/27

Topics: conceptual model (EER), ER-to-relational mapping

  
Mon Materials from class:   project — specifications due
Wed
exam 1
  
Fri Daily lesson: relational model and ER-to-relational mapping (on Canvas)

Materials from class:

 homework
ER-to-relational
(on Canvas)

due 10/4

Week 6: 9/30-10/4

Topics: ER-to-relational mapping, relational design principles, decomposition and normalization

  
Mon Materials from class:  
Wed Daily lesson: relational design principles and normalization (on Canvas)

Materials from class:

 homework
relational design principles and normalization
(on Canvas)

due 10/11
Fri Materials from class:
  • slides: normalization
  • practice problems (from the relational design principles and normalization module on Canvas)
  project — conceptual model due

Week 7: 10/7-10/11

Topics: SQL data definition and data integrity (check constraints, assertions, triggers)

 conceptual modeling cutoff
ER/EER homework problems will not be accepted for credit after 11:59pm 10/6
Mon Daily lesson: SQL — data definition (on Canvas)

Reference:

Materials from class: 		 
Wed
exam 2
Fri Daily lesson: SQL — data integrity (on Canvas)

Reference:

Materials from class: 		homework
data integrity
(on Canvas)

due 10/21

Week 8: 10/14-10/18

Topics: database applications — front end

  
Mon fall break
Wed Materials from class:  
Fri meet in Rosenberg 009

Daily lesson: look through the "Reading" section below for your chosen approach (no quiz)

Web Applications

Reading:

Reference:

  • HTML/PHP Local Quick Start (specifics about using PHP and creating web pages for this course, including information on remote access)

Examples:

  • Quick Start examples (must be on campus for link to work; see "Off-Campus Access" in the Local Quick Start if you are off-campus)
   

Java Applications

Reading:

  • JDBC Basics (from the Java Tutorial) — especially "Establishing a Connection", "Handling SQLExceptions", "Retrieving and Modifying Values from Result Sets", "Using Prepared Statements", and "Using Transactions"

Reference:

Examples: (both of these expect the database username to be provided on the commandline)

 homework
database applications

photo album application: web version or JavaFX version

due 11/1

Week 9: 10/21-10/25

Topics: database applications — front end

  
Mon meet in Rosenberg 009

Materials from class:

  
Wed meet in Rosenberg 009
Fri meet in Rosenberg 009 relational database cutoff
ER-to-relational, normalization, DB implementation homework problems will not be accepted for credit after 11:59pm 10/25		 project — database design due

Week 10: 10/28-11/1

Topics: privacy and security; views
Mon Materials from class:
Wed
exam 3
Fri Daily lesson: SQL — views (on Canvas)

Materials from class:

  • slides: views
  • practice problems (from the SQL views module on Canvas)
homework
views
(on Canvas)

due 11/6

Week 11: 11/4-11/8

Topics: stored routines; transactions
Mon Daily lesson: SQL — stored routines (on Canvas)

Reference:

Materials from class:

 homework
stored routines
(on Canvas)

due 11/11
Wed Materials from class:
  • slides: stored routines (parameters, stored functions; conditionals, loops, cursors)
 
Fri Daily lesson: SQL — transactions (on Canvas)

Materials from class:

   project — front end design due

Week 12: 11/11-11/15

Topics: file organization; indexes

  
Mon Daily lesson: file organization (on Canvas)

Materials from class:

 homework
file organization
(on Canvas)

due 11/15
Wed Daily lesson: indexes (on Canvas)

Materials from class:

  • slides: indexes (overview, figuring out search times for primary and clustering indexes)
homework
indexes
(on Canvas)

due 11/22
Fri Daily lesson: mysql indexes (on Canvas)

Materials from class:

  • slides: indexes (figuring out search times for secondary indexes, multilevel indexes)
DB applications cutoff
views, stored routines homework problems will not be accepted for credit after 11:59pm 11/15		 project — back end design due

Week 13: 11/18-11/22

Topics: relational algebra and query trees; query processing and evaluation

  
Mon Daily lesson: relational algebra and query trees (on Canvas)

Materials from class:

 homework
query trees
(on Canvas)

due 11/25
Wed
exam 4
Fri Daily lesson: heuristic query optimization (on Canvas)

Materials from class:

 homework
heuristic query optimization
(on Canvas)

due 12/4

Week 14: 11/25-11/29

Topics: query optimization
Mon Daily lesson: cost-based query optimization (on Canvas)

Materials from class:

 homework
cost-based query optimization
(on Canvas)

due 12/6
Wed Thanksgiving break
Fri

Week 15: 12/2-12/6

Topics: query optimization; database efficiency and tuning
Mon
Wed Daily lesson: database efficiency and tuning (to be posted)
Fri   project — implementation due

Reading Period: 12/7-12/9

  
Sat  
Sun query processing cutoff
file organization, indexing, and query processing homework problems will not be accepted for credit after 11:59pm 12/8
Mon  

Final Exams: 12/10-12/13
Tue  
  project — final handin
Wed
exam 5
final exam
12/11 7-10pm
 end-of-semester deadline
no work accepted after 12/11 10pm
Thu    
Fri    

last updated: --Mon Nov 25 13:25:54 EST 2024--
page owned by: bridgeman@hws.edu