CPSC 343 Database Theory and Practice Fall 2020

CPSC 343 Schedule

Daily lessons are due by 8pm the night before the class/lab 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/24-8/28

Topics: course introduction; information management concepts, database systems, relational databases; SQL - simple queries

    
Mon    
Wed Daily lesson: on Canvas - "introduction to databases" module

Materials from class:

  • slides - introduction to databases
 
Fri Daily lesson: on Canvas - "SQL basics" module

Reference:

Materials from class:

Practice problems: on Canvas - "SQL basics" module - due 9/2

  

Week 2: 8/31-9/4

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

  
Mon Daily lesson: on Canvas - "SQL set operations and subqueries" module

Reference:

Materials from class:

Practice problems: on Canvas - "SQL set operations and subqueries" module - due 9/7

  
Wed Materials from class:  
Fri Daily lesson: on Canvas - "SQL aggregation and grouping" module

Reference:

Materials from class:

  • slides - SQL aggregation and grouping

Practice problems: on Canvas - "SQL aggregation and grouping" module - due 9/11

  

Week 3: 9/7-9/11

Topics: SQL: aggregation and grouping, complex queries, data modification

  
Mon Materials from class:
  • slides - SQL aggregation (subtleties in using aggregation functions, grouping) and other tips
 
Wed Materials from class:
  • slides - strategies for queries (especially complex ones)

Practice problems: on Canvas - "SQL complex queries" module - due 9/14

  
Fri Daily lesson: on Canvas - "SQL data modification" module

Reference:

Materials from class:

  • slides - INSERT, DELETE, UPDATE

Practice problems: on Canvas - "SQL data modification" module - due 9/16

  

Week 4: 9/14-9/18

Topics: data modeling: conceptual modeling, ER (entity-relationship) modeling, ER design principles

  
Mon Daily lesson: on Canvas - "ER modeling" module

Materials from class:

Practice problems: on Canvas - "ER modeling" module - due 9/21

  
Wed practice problems cutoff - SQL project topic proposal
Fri Daily lesson: on Canvas - "ER design principles" module

Materials from class:

  • slides - ER design principles
  • ER design example - see the discussion for the university problem in the in-class problems for the ER modeling module on Canvas

Practice problems: on Canvas - "ER design principles" module - due 9/23

  

Week 5: 9/21-9/25

Topics: EER modeling; relational model

  
Mon Daily lesson: on Canvas - "EER modeling" module

Materials from class:

Practice problems: on Canvas - "EER modeling" module - due 9/28

  
Wed exam 1 (SQL)

review information
Fri Daily lesson: on Canvas - "relational model" module

Materials from class:

  

Week 6: 9/28-10/2

Topics: ER-to-relational mapping; relational design principles and normalization

  
Mon Daily lesson: on Canvas - "ER-to-relational mapping" module

Materials from class:

  • slides - ER/EER-to-relational mapping

Practice problems: on Canvas - "ER-to-relational mapping" module - due 10/5

 practice problems cutoff - conceptual modeling project specifications and conceptual design
Wed  
Fri Daily lesson: on Canvas - "relational design principles and normalization" module

Materials from class:

Practice problems: on Canvas - "relational design principles and normalization" module - due 10/9

  

Week 7: 10/5-10/9

Topics: normalization; SQL data definitions; data integrity

  
Mon Materials from class: exam 2 (conceptual modeling)

review information
Wed Daily lesson: on Canvas - "SQL - data definition" module

Reference:

Materials from class:

  • slides - SQL data definition
 
Fri Daily lesson: on Canvas - "SQL - data integrity" module

Reference:

Materials from class:

  • slides - data integrity (constraints)

Practice problems: on Canvas - "data integrity" module - due 10/14

  

Week 8: 10/12-10/16

Topics: database applications - privacy and security, views, stored routines

  
Mon Daily lesson: on Canvas - "privacy and security" module

Materials from class:

  • slides - privacy and security
 
Wed Daily lesson: on Canvas - "SQL - views" module

Reference:

Materials from class:

Practice problems: on Canvas - "views" module - due 10/19

  
Fri Daily lesson: on Canvas - "SQL - stored routines" module

Reference:

Materials from class:

  • slides - stored routines (procedures, functions, parameters, variables)

Practice problems: on Canvas - "stored routines" module - due 10/26

 practice problems cutoff - DB implementation: ER-to-relational, normalization, data integrity project database design

Week 9: 10/19-10/23

Topics: database applications - stored routines, transactions

  
Mon Materials from class:
  • slides - stored routines (conditionals, loops, cursors, handlers)
 
Wed Materials from class:
  • slides - some comments on triggers
exam 3 (DB implementation)

review information
Fri Daily lesson: on Canvas - "SQL - transactions" module

Materials from class:

  

Week 10: 10/26-10/30

Topics: database applications - the actual application

  
Mon Daily lesson: on Canvas - "database applications" module

Materials from class:

  • slides - database applications
 
Wed 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:

Practice problems: due 11/6

 practice problems cutoff (views, stored routines)
Fri  

Week 11: 11/2-11/6

Topics: database applications; file structure and data storage

  
Mon exam 4 (backend support for database applications)

review information
Wed   project implementation
Fri Daily lesson: on Canvas - "file organization" module

Materials from class:

 practice problems cutoff (photo album)

Week 12: 11/9-11/13

Topics: indexes; relational algebra and query trees

  
Mon Daily lesson: on Canvas - "indexes" module

Materials from class:

Practice problems: on Canvas - "indexes" module - due 11/16

  
Wed Daily lesson: on Canvas - "mysql indexes" module

Materials from class:

  • slides - multilevel indexes, indexes in MySQL
 
Fri Daily lesson: on Canvas - "relational algebra and query trees" module

Materials from class:

  

Week 13: 11/16-11/20

Topics: query processing and evaluation

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

Materials from class:

  • slides - heuristic query optimization

Practice problems: on Canvas - "heuristic optimization" module - due 11/20

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

Materials from class:

Practice problems: on Canvas - "cost-based optimization" module - due 11/24

  • solution for the first practice problem
 
Fri  

Week 14: 11/23-12/2

Topics: database efficiency and tuning

  
Mon Daily lesson: on Canvas - "database efficiency and tuning" module  
Tue practice problems cutoff (file organization, indexing, query processing)
Wed Thanskgiving break
Fri
Mon Materials from class:  
Wed Materials from class:
  • slides - some EXPLAIN examples
  final project due

Reading Period: 12/3-12/6

  
Thu    
Fri    
Sat   exam 5
and
final exam

review information: exam 5, final exam
Sun    

Exams: 12/7-12/11

  
Mon  
Tue  
Wed
scheduled final exam time
12/9 8:30-11:30am
 end-of-semester deadline
no work accepted after 12/9 11:30am
Thu  
Fri      

last updated: --Wed Dec 2 12:09:32 EST 2020--
page owned by: bridgeman@hws.edu