CPSC 343 Database Theory and Practice Fall 2022

CPSC 343 Schedule

Unless otherwise noted, daily lessons are to be completed by 8pm 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.

< /tr>
 Assignments

Week 1: 8/22-8/26

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)

    
Wed Modules: on Canvas - "preliminaries" (syllabus quiz, introductory survey) and "software installation" (MySQL installation)

Daily lesson: on Canvas - "SQL - basics"

Reference:

Materials from class:

 practice problems
SQL - basics
(on Canvas)

due 8/29		  
Fri Daily lesson: on Canvas - "SQL - set operations and subqueries"

Reference:

Materials from class:

 practice problems
SQL - set operations and subqueries
(on Canvas)

due 9/2		  

Week 2: 8/29-9/2

Topics:

  • SQL - set operations, subqueries, aggregation and grouping

   
Mon Materials from class:

Reference:

  • MySQL commandline client instructions (for the Linux computers in Rosenberg 009 and Lansing 310) in the "software installation" module on Canvas
   
Wed Daily lesson: on Canvas - "SQL - aggregation and grouping"

Materials from class:

Reference:

 practice problems
SQL - aggregation and grouping
(on Canvas)

due 9/7		  
Fri Materials from class:
  • slides: grouping
  • examples: in-class exercises - "SQL - aggregation and grouping" module on Canvas
   

Week 3: 9/5-9/9

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

    
Mon Materials from class:
  • examples: in-class exercises - "SQL - aggregation and grouping" module on Canvas
  • slides: complex queries
  • examples: in-class exercises - "SQL - complex queries" module on Canvas
practice problems
SQL - complex queries
(on Canvas)

due 9/9		  
Wed Daily lesson: on Canvas - "SQL - data modification"

Reference:

Materials from class:

  • slides: data modification
  • examples: in-class exercises - "SQL - data modification" module on Canvas (do at least the inserting and importing data exercises to have something to work with if you want to test your solutions for the practice problems)
practice problems
SQL - data modification
(on Canvas)

due 9/12		  
Fri Daily lesson: on Canvas - "ER modeling"

Materials from class:

 practice problems
ER modeling
(on Canvas)

due 9/16		 project - topic proposal

due 9/21 (or sooner)

Week 4: 9/12-9/16

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

  
Mon Materials from class:
  • examples: car insurance (on Canvas, in the "ER modeling" in-class problems)
SQL cutoff
SQL practice problems will not be accepted for credit after 11:59pm 9/12
Wed Daily lesson: on Canvas - "ER design principles"

Materials from class:

If you encounter trouble running dia on a Mac, see "dia on Macs" in the "ER modeling" module on Canvas and/or stop by office hours. 		practice problems
ER design principles
(on Canvas)

due 9/21
Fri Daily lesson: on Canvas - "EER modeling"

Materials from class:

  • examples: ER design decisions - accounts (on Canvas, in the "ER design principles" in-class problems)
  • examples: IMDB (on Canvas, in the "ER modeling" in-class problems)
  • slides: EER - specialization
practice problems
EER modeling
(on Canvas)

due 9/26

Week 5: 9/19-9/23

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

  
Mon
exam 1 - SQL
meet in Rosenberg 009
  
Wed Materials from class:    
Fri Daily lesson: on Canvas - "relational model" and "ER-to-relational mapping" modules

Materials from class:

 practice problems
ER-to-relational
(on Canvas)

due 9/30		  

Week 6: 9/26-9/30

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

    
Mon Materials from class: modeling cutoff
conceptual modeling practice problems will not be accepted for credit after 11:59pm 9/26		 project - specifications and conceptual model

due 10/7 (in class)
Wed Daily lesson: on Canvas - "relational design principles and normalization" module

Materials from class:

 practice problems
relational design principles and normalization
(on Canvas)

due 10/7
Fri Materials from class:  

Week 7: 10/3-10/7

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

  
Mon
exam 2 - conceputal modeling
meet in Rosenberg 009
  
Wed Daily lesson: on Canvas - "SQL - data definition" module

Reference:

Materials from class:

 practice problems
data definition
(on Canvas)

due 10/14
Fri Daily lesson: on Canvas - "SQL - data integrity" module

Reference:

Materials from class:

 practice problems
data integrity
(on Canvas)

due 10/17		  

Week 8: 10/10-10/14

Topics: database applications

    
Mon fall break
Wed Daily lesson: on Canvas - "database applications" module

Materials from class:

   project - database design

due 10/21 (in class)
Fri 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:

meet in Rosenberg 009

Materials from class:

 practice problems
database applications

photo album application: web version or JavaFX version

due 11/7

Week 9: 10/17-10/21

Topics: database applications; privacy and security; views

  
Mon
meet in Rosenberg 009
 implementation cutoff
implementation practice problems will not be accepted for credit after 11:59pm 10/17
Wed Daily lesson: on Canvas - "privacy and security" module

Materials from class:

  
Fri Daily lesson: on Canvas - "SQL - views" module

Reference:

Materials from class:

 practice problems
views
(on Canvas)

due 10/28		 project - application design

due 11/4 (in class)

Week 10: 10/24-10/28

Topics: stored routines

  
Mon
exam 3
meet in Rosenberg 009
  
Wed Daily lesson: on Canvas - "SQL - stored routines" module

Reference:

Materials from class:

  • slides: stored routines (procedures, functions, variables)
  • examples: in-class problems in the "SQL - stored routines" module (on Canvas)
practice problems
stored routines
(on Canvas)

due 11/2
Fri Materials from class:
  • slides: stored routines (conditionals, loops, cursors, handlers)
  • examples: in-class problems in the "SQL - stored routines" module (on Canvas)
 

Week 11: 10/31-11/4

Topics: database applications; transactions

  
Mon
meet in Rosenberg 009
  
Wed Daily lesson: on Canvas - "SQL - transactions" module

Materials from class:

  
Fri
meet in Rosenberg 009
    

Week 12: 11/7-11/11

Topics: file organization; indexes

    
Mon Daily lesson: on Canvas - "file organization" module

Materials from class:

 applications cutoff
applications practice problems will not be accepted for credit after 11:59pm 11/7
practice problems
file organization
(on Canvas)

due 11/11		 project - implementation

due 12/2 11:59pm
Wed Daily lesson: on Canvas - "indexes" module

Materials from class:

 practice problems
indexes
(on Canvas)

due 11/16
Fri Daily lesson: on Canvas - "mysql indexes" module

Materials from class:

  • slides: indexes (multilevel indexes, indexes in MySQL)
 

Week 13: 11/14-11/18

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

  
Mon
exam 4
meet in Rosenberg 009
  
Wed Daily lesson: on Canvas - "relational algebra and query trees" module

Materials from class:

  
Fri Daily lesson: on Canvas - "query processing - heuristic optimization" module

Materials from class:

 practice problems
heuristic optimization
(on Canvas)

due 11/30

Week 14: 11/21-11/25

Topics: query processing and evaluation

  
Mon Daily lesson: on Canvas - "query processing - cost-based optimization" module

Materials from class:

 practice problems
cost-based optimization
(on Canvas)

due 12/2
Wed Thanksgiving break
Fri

Week 15: 11/28-12/2

Topics: query processing and evaluation; database efficiency and tuning

  
Mon Materials from class:  
Wed Daily lesson: on Canvas - "database efficiency and tuning" module

Materials from class:

  
Thu
  • office hours 2:30-4:30pm
Fri

Materials from class:

 query processing cutoff
file organization, indexing, and query processing practice problems will not be accepted for credit after 11:59pm 12/2

Reading Period: 12/3-12/5

 review problems
(optional)

on Canvas - "review problems" modules

due 1:30pm 12/6		  
Sat  
Sun  
Mon
  • review session 11am-1pm Gulick 223
  • office hours 1:30-3:30pm
 

Final Exams: 12/6-12/9

  
Tue
exam 5 and final exam
12/6 1:30-4:30pm
meet in Rosenberg 009
 end-of-semester deadline
no work accepted after 12/6 4:30pm
Wed    
Thu    
Fri    

last updated: --Fri Dec 2 20:48:37 EST 2022--
page owned by: bridgeman@hws.edu