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
Wed
exam 3
Fri Daily lesson: SQL — views (on Canvas)

Week 11: 11/4-11/8

Topics:

Mon
Wed  
Fri   project — frontend design due

Week 12: 11/11-11/15

Topics:

 
Mon  
Wed homework problems cutoff
Fri   project — backend design due

Week 13: 11/18-11/22

Topics:

 
Mon
Wed
exam 4
Fri

Week 14: 11/25-11/29

Topics:

Mon
Wed Thanksgiving break
Fri

Week 15: 12/2-12/6

Topics:

Mon
Wed
Fri   project — implementation due

Reading Period: 12/7-12/9

  
Sat  
Sun homework problems cutoff
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: --Fri Oct 25 11:56:39 EDT 2024--
page owned by: bridgeman@hws.edu