CPSC 343 Syllabus

Reading is to be done for the class period where it is listed. Additional Reading is additional (optional but recommended) reading addressing material covered that day.

Dates for things in light gray are tentative and may shift slightly.

		Assignments
Week 1: 8/31-9/4 Topics: introduction; the relational model and the relational algebra

Mon	Slides: introduction Additional Reading: Elmasri and Navathe, chapters 1 and 2	homework #0 due Wed 9/2 at the start of class
		homework #0 due Wed 9/2 at the start of class
Wed	Reading: the relational model the relational algebra (updated 9/3) Slides and Examples: slides relational algebra queries handout and solutions Additional Reading: Elmasri and Navathe, sections 5.1-5.2, 6.1-6.3, and 6.5	homework #1 due Fri 9/4 at the start of class
		homework #1 due Fri 9/4 at the start of class
Fri	Slides and Examples: slides advanced relational algebra queries handout and solutions	homework #2 due Mon 9/7 at the start of class
Week 2: 9/7-9/11 Topics: the tuple relational calculus

Mon	Reading: the relational calculus Examples: relational calculus queries handout and solutions Additional Reading: Elmasri and Navathe, section 6.6	homework #3 due Wed 9/9 at the start of class
		homework #3 due Wed 9/9 at the start of class
Wed	Examples: more relational calculus queries handout and solutions	homework #4 due Fri 9/11 at the start of class
		homework #4 due Fri 9/11 at the start of class
Fri	Slides: slides (comments on relational algebra and relational calculus from hw #3)	homework #5 due Mon 9/14 at the start of class
Week 3: 9/14-9/18 Topics: E/R modeling

Mon	Reading: the E/R model Slides: slides (basic E/R model, examples) Additional Reading: Elmasri and Navathe, sections 3.1-3.7	homework #6 due Wed 9/16 at the start of class
		homework #6 due Wed 9/16 at the start of class
Wed	Examples: E/R design: handout and a solution for #1	homework #7 due Fri 9/18 at the start of class
		homework #7 due Fri 9/18 at the start of class	project topics due Fri 9/18
Fri	Reading: E/R design principles Slides and Examples: slides (design discussion and principles) E/R design: handout Additional Reading: Elmasri and Navathe, sections 3.7.3 and 3.9	homework #8 due Mon 9/21 at the start of class	project specifications / requirements gathering due Mon 9/28
Week 4: 9/21-9/25 Topics: E/R modeling

Mon	Examples: E/R design: quotes and movies handout E/R design: grades and courses version 1, version 2, version 3	homework #9 due Wed 9/23 at the start of class
		homework #9 due Wed 9/23 at the start of class
Wed	Reading: enhanced E/R model Examples: E/R design: grades and courses version 3 (alternative), version 4, version 4 (alternative), version 5 E/R design: IMDB handout Additional Reading: Elmasri and Navathe, chapter 4 through section 4.5.2

Fri	Slides: slides (design decisions - constraints, enhanced E/R)	homework #10 due Mon 9/28 at the start of class
Week 5: 9/28-10/2 Topics: relational DB design

Mon	Reading: E/R to relational mapping Slides: slides (mapping E/R to the relational model) Additional Reading: Elmasri and Navathe, chapter 7	homework #11 due Wed 9/30 at the start of class
		homework #11 due Wed 9/30 at the start of class
Wed	Slides: slides (mapping EER to the relational model)	homework #12 due Fri 10/2 at the start of class	project conceptual design due Fri 10/9
		homework #12 due Fri 10/2 at the start of class
Fri	Reading: relational design principles and normalization Slides: slides (relational design principles and normalization) Additional Reading: Elmasri and Navathe, chapter 10	homework #13 due Mon 10/5 at the start of class
Week 6: 10/5-10/9 Topics: SQL - data definition and queries

Mon	Reading: data definition MySQL data definition (focus on CREATE DATABASE, CREATE TABLE, DROP DATABASE, DROP TABLE, ALTER TABLE) MySQL data types (sections 11.1-11.4) Additional Reading: Elmasri and Navathe, sections 8.1-8.3	homework #14 due Wed 10/7 at the start of class
		homework #14 due Wed 10/7 at the start of class
Wed	Reading: SQL queries (basic queries) MySQL `SELECT` syntax (for reference) Slides and Examples: slides (MySQL data types and data definition) slides (basic SQL queries) SQL queries handout (partial solutions) Additional Reading: Elmasri and Navathe, sections 8.4-8.5.1	homework #15 due Fri 10/9 at the start of class
		homework #15 due Fri 10/9 at the start of class
Fri	Reading: SQL queries (set operations, subqueries) Slides and Examples: slides (set operations and subqueries) SQL queries handout (partial solutions) Additional Reading: Elmasri and Navathe, sections 8.5.2-8.5.6
Week 7: 10/12-10/16 Topics: SQL - queries

Mon	fall break (no class)

Wed		homework #16 due Fri 10/16 at the start of class
		homework #16 due Fri 10/16 at the start of class
Fri	Reading: SQL queries (aggregation and grouping) Slides and Examples: slides (aggregation and grouping) SQL queries handout (partial solutions) Additional Reading: Elmasri and Navathe, sections 8.5.7-8.5.9
Week 8: 10/19-10/23 Topics: SQL - queries, views, insert/delete/update

Mon	exam 1 (take home) due Mon 10/19 in class review information Slides and Examples: slides (example from hw 16) SQL queries handout (partial solutions)	homework #17 due Wed 10/21 at the start of class	project logical design and DB implementation due Fri 10/30
		homework #17 due Wed 10/21 at the start of class
Wed	Reading: views Additional Reading: Elmasri and Navathe, section 8.8	homework #18 due Fri 10/23 at the start of class
		homework #18 due Fri 10/23 at the start of class
Fri	Reading: data modification (insert, delete, update) Slides: slides (assorted comments about SQL) Additional Reading: Elmasri and Navathe, sections 8.6 and 8.8.3	homework #19 due Mon 10/26 at the start of class
Week 9: 10/26-10/30 Topics: SQL - triggers; web applications: HTML and PHP

Mon	Reading: triggers MySQL `CREATE TRIGGER` syntax and examples (for reference) MySQL Date and Time Functions (for reference) Additional Reading: Elmasri and Navathe, sections 8.7 and 24.1	homework #20 due Wed 10/28 at the start of class
		homework #20 due Wed 10/28 at the start of class
Wed	Reading: web applications HTML Quick Start PHP Quick Start Reference: HTML/PHP Local Quick Start Slides and Examples: web applications, HTML, PHP Quick Start examples (must be on campus to use) examples from class (must be on campus to use) For the PHP examples, load `viewphp.html` in the respective directories and enter the name of the desired file to see the PHP source.	homework #21 due Mon 11/2 at the start of class

Fri	Slides: exam 1 comments
Week 10: 11/2-11/6 Topics: web applications: DB access and programming, HTML forms, sessions and cookies

Mon	Reading: PHP MySQL Quick Start (connecting to a database) Slides and Examples: communicating with databases, prepared statements, the hazards of SQL injection Quick Start examples (must be on campus to use) examples from class (must be on campus to use) For the PHP examples, load `viewphp.html` in the respective directories and enter the name of the desired file to see the PHP source.	homework #22 due Fri 11/6 at the start of class

Wed			project application design due Wed 11/11

Fri	Reading: HTML Forms Quick Start PHP Web Quick Start (processing forms, sessions, coookies) Slides and Examples: brief overview of forms, maintaining state, redirection Quick Start examples (must be on campus to use) examples from class (must be on campus to use) For the PHP examples, load `viewphp.html` in the respective directories and enter the name of the desired file to see the PHP source.	homework #23 due Wed 11/11 at the start of class
Week 11: 11/9-11/13 Topics: forms and sessions; transactions; stored routines

Mon	Slides: DB security

Wed	Reading: transactions Slides: transactions

Fri	Reading: stored routines Slides: stored routines
Week 12: 11/16-11/20 Topics: stored routines; security and user authorization; file structure and data storage

Mon	exam 2 (take home) due Mon 11/16 in class review information Examples: stored routines	homework #24 due Wed 11/18 at the start of class	project implementation due Fri 12/11
		homework #24 due Wed 11/18 at the start of class
Wed	Slides: handlers authentication and authorization	homework #25 due Fri 11/20 at the start of class
		homework #25 due Fri 11/20 at the start of class
Fri	Reading: file organization and indexing Slides: file organization Additional Reading: Elmasri and Navathe, sections 13.1-13.8
Week 13: 11/23-11/27 Topics: indexing

Mon	Slides: indexes Additional Reading: Elmasri and Navathe, sections 14.1-14.2	homework #26 due Wed 12/2 at the start of class

Wed	Thanksgiving break (no class)

Fri
Week 14: 11/30-12/4 Topics: indexes, query processing

Mon	Slides: multilevel indexes, indexes in MySQL, recap

Wed	Reading: query processing Slides: query processing, query trees, heuristic query optimization Additional Reading: Elmasri and Navathe, sections 15.1-15.8	homework #27 due Fri 12/4 at the start of class
		homework #27 due Fri 12/4 at the start of class
Fri	Slides and Examples: partial solutions for hw 26 query execution plans
Week 15: 12/7-12/11 Topics: query processing

Mon	Slides and Examples: cost-based query optimization	homework #28 due Wed 12/9 at the start of class
		homework #28 due Wed 12/9 at the start of class
Wed	Slides and Examples: project presentation info query optimization in MySQL (sample queries to try out)

Fri	project demos in class
Reading Period: 12/12-12/14

Sat

Sun

Mon
Exams: 12/15-12/18

Tue

Wed	final exam (take home) due Wed 12/16 10pm review information	end-of-semester deadline no work accepted after 12/16 10pm	final project handin due Wed 12/16 10pm

Thu

Fri

CPSC 343 Syllabus

Week 1: 8/31-9/4

Week 2: 9/7-9/11

Week 3: 9/14-9/18

Week 4: 9/21-9/25

Week 5: 9/28-10/2

Week 6: 10/5-10/9

Week 7: 10/12-10/16

Week 8: 10/19-10/23

Week 9: 10/26-10/30

Week 10: 11/2-11/6

Week 11: 11/9-11/13

Week 12: 11/16-11/20

Week 13: 11/23-11/27

Week 14: 11/30-12/4

Week 15: 12/7-12/11

Reading Period: 12/12-12/14

Exams: 12/15-12/18