CPSC 343

Database Theory and Practice

Fall 2017

CPSC 343 Schedule

Reading and class prep exercises are to be done for the class period where they are listed. "Additional Reading" is optional and is provided if you want greater depth. "Elmasri and Navathe" refers to Fundamentals of Database Systems, 5th edition; this book has been placed on reserve in the library.

Dates for things in light gray are for planning purposes and may be adjusted slightly.

Assignments

Week 1: 8/28-9/1

Topics: course introduction; introduction to data modeling and database design; conceptual models (ER)

Mon

Materials:

slides: course introduction

Additional Reading:

Elmasri and Navathe, chapters 1 and 2

Wed

Reading:

introduction to data modeling and database design (chapter 2)
conceptual data modeling: the ER model (section 3.1)

Class Prep: available on Canvas

Materials:

slides: ER modeling (the ER model, notation)
practice problems: ER modeling

Additional Reading:

Elmasri and Navathe, sections 3.1-3.5

Fri

Reading:

ER design principles (section 3.3)

Materials:

slides: ER modeling (design decisions)
handouts: ER design problems
practice problems: ER modeling

Additional Reading:

Elmasri and Navathe, sections 3.7.2-3.7.3 and 3.9

Week 2: 9/4-9/8

Topics: the entity-relationship model; the relational model

Mon

Materials:

IMDB example (problem description)
practice problems: ER modeling

Wed

Reading:

the enhanced ER model (chapter 4)

Class Prep: available on Canvas

Materials:

slides: EER modeling
examples: IMDB problem v2, actor/crew/writer/director (specialization), TV show/movie (specialization vs category)
practice problems: ER modeling

Additional Reading:

Elmasri and Navathe, chapter 4 through section 4.5.2

project team survey
due Fri Sep 8

(available on Canvas under Quizzes)

Fri

Reading:

the relational model (chapter 5)

Class Prep: available on Canvas

Materials:

physical therapy example handout, solution (ER modeling)
slides: the relational model

Additional Reading:

Elmasri and Navathe, sections 5.1-5.2
chapters 20, 21, 22 of Elsmasri and Navathe deal with the object-oriented databases - chapter 21 is the best place to start if you are interested in alternatives to the relational model

homework 1
ER modeling

due Fri Sep 15

solutions: #1, #3

Week 3: 9/11-9/15

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

Mon

Reading:

ER-to-relational mapping (chapter 6)

Materials:

slides: mapping ER to relational (entity types, weak entity types, binary relationship types)
practice problems: ER-to-relational mapping

Additional Reading:

Elmasri and Navathe, chapter 7

project specifications
due Mon Sep 25

(teams are listed near the beginning)

Wed

Reading:

design principles for relational schemas (section 7.1)

Materials:

slides: mapping ER to relational (multivalued attributes, n-ary relationships, specialization, category types)
practice problems: ER-to-relational mapping

Additional Reading:

Elmasri and Navathe, section 10.1

Fri

Reading:

normalization (section 7.2)

Class Prep: available on Canvas

Materials:

slides: design principles, normalization
practice problems: normalization

Additional Reading:

Elmasri and Navathe, sections 10.2-10.6 (also chapter 11 for more advanced material that is beyond what we'll do in class)

Week 4: 9/18-9/22

Topics: decomposition and normalization; SQL: data definition, SELECT/PROJECT/JOIN

Mon

Materials:

slides: normalization
practice problems: ER-to-relational mapping

homework 2
ER-to-relational mapping and normalization

due Mon Sep 25

solutions: #1, #3, #4

Wed

Reading:

data definition (chapter 8 intro; sections 8.1-8.2)
MySQL data definition (focus on CREATE DATABASE, CREATE TABLE, DROP DATABASE, DROP TABLE, ALTER TABLE)
MySQL data types (sections 11.1-11.4, 11.7-11.9)

Class Prep: available on Canvas (MySQL Workbench installation)

Materials:

slides: SQL data definition
in-class activities (data definition)

Additional Reading:

Elmasri and Navathe, sections 8.1-8.3

Fri

Reading:

SQL queries (section 8.3 intro; sections 8.3.1-8.3.2)
MySQL SELECT syntax (for reference)

Class Prep: available on Canvas

Materials:

slides: basic SQL queries
practice problems: basic SQL queries (solutions)

Additional Reading:

Elmasri and Navathe, sections 8.4-8.5.1

Week 5: 9/25-9/29

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

Mon

Reading:

SQL queries (sections 8.3.3-8.3.4)
MySQL subquery syntax (for reference)

Class Prep: available on Canvas

Materials:

slides: subqueries, UNION, OUTER JOIN
practice problems: more SQL queries (partial solutions)

Additional Reading:

Elmasri and Navathe, section 8.5.2.-8.5.6

homework 1
ER modeling

#2, #4 due Fri Sep 29

homework 2
ER-to-relational mapping and normalization

#2, #5 due Fri Sep 29

(you must hand in something to get credit - either the new solution if you redo a problem or the original solution if you don't - but you don't need to hand in both new and original solutions for the same problem)

project specifications peer review
due Fri Sep 29

Wed

Materials:

practice problems: more SQL queries (partial solutions)

Fri

Reading:

SQL queries (section 8.3.5)
MySQL aggregate functions (for reference)

Materials:

slides: aggregation and grouping
practice problems: still more SQL queries (partial solutions)

Additional Reading:

Elmasri and Navathe, section 8.5.7-8.5.8

exam 1 (take home)
due Wed 10/4 in class

review information

Week 6: 10/2-10/6

Topics: SQL: queries, data modification

Mon

Materials:

practice problems: still more SQL queries (solutions)

Wed

Reading:

SQL data modification (section 8.5 introduction and 8.5.1-8.5.3)
MySQL INSERT, DELETE, UPDATE (for reference)

Materials:

slides: some notes on queries; data modification
in-class activities (data modification)

Additional Reading:

Elmasri and Navathe, section 8.6

project conceptual model
due Wed Oct 18

Fri

Materials:

practice problems: complex SQL queries (solutions)

Week 7: 10/9-10/13

Topics: security and controlling access; views

Mon fall break
(no class)

Wed

Materials:

slides: some comments on hw 1 and last Friday's SQL exercises
slides: security and access control

homework 3
SQL

due Wed Oct 18

solutions

Fri

Reading:

views (sections 8.4 and 8.5.4)
MySQL CREATE VIEW, DROP VIEW (for reference)

Materials:

slides: views
in-class exercises (views) - solutions

Additional Reading:

Elmasri and Navathe, section 8.8

Week 8: 10/16-10/20

Topics: controlling access: stored routines

Mon

Reading:

stored routines (section 8.8)
MySQL CREATE PROCEDURE and CREATE FUNCTION, CALL (for reference)

Materials:

slides: stored routines

Wed

Materials:

slides: stored routines (handlers)
in-class exercises (stored routines) - solutions

project conceptual model peer review
due Mon Oct 23

Fri

Materials:

in-class exercises (stored routines)

Week 9: 10/23-10/27

Topics: data integrity: triggers and transactions; database applications

Mon

Reading:

triggers (section 8.6)
MySQL CREATE TRIGGER syntax and examples (for reference)

Materials:

slides: permissions for stored routines
slides: triggers
in-class exercises (triggers) - solutions

project database design
due Fri Nov 3

Wed

Reading:

transactions (section 8.7)

Class Prep: available on Canvas

Materials:

slides: transactions

homework 4
Access and Integrity

due Wed Nov 1

solutions and some notes

Fri

Materials:

slides: database applications

Week 10: 10/30-11/3

Topics: database applications

Mon

-- meet in Lansing 310/301 --

Web Applications

Reading:

slides: HTML/PHP introduction
HTML Quick Start
PHP Quick Start
PHP MySQL Quick Start (connecting to a database)
HTML Forms Quick Start
PHP Web Quick Start (processing forms, sessions, cookies)

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 "Remote 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:

JDBC Local Quick Start (specifics about using JDBC in this course)
Creating a GUI With JFC/Swing

Examples:

SimpleQuery (sending a simple query and displaying the results)
PreparedStmt (using prepared statements)

Materials:

Wed -- meet in Lansing 310/301 --

Fri -- meet in Lansing 310/301 -- exam 2 (take home)
due Wed 11/8 in class

review information

Week 11: 11/6-11/10

Topics: database applications; file structure and data storage

Mon -- meet in Lansing 310/301 --

Wed

Materials:

slides: database applications

project database design peer review
due Mon Nov 13

Fri

Reading:

file organization (chapter 9)

Materials:

slides: file organization
practice problems: file organization

Additional Reading:

Elmasri and Navathe, sections 13.1-13.8

Week 12: 11/13-11/17

Topics: indexing; relational algebra

Mon

Reading:

indexes (chapter 10)

Materials:

slides: project comments (DB design)
slides: indexes (primary indexes)

Additional Reading:

Elmasri and Navathe, sections 14.1-14.2

project implementation
due Fri Dec 8

Wed

Materials:

slides: indexes (clustering and secondary indexes)
practice problems: indexing

Fri

Reading:

relational algebra (chapter 11)
query trees (chapter 12 introduction and section 12.1)

Class Prep: available on Canvas

Materials:

slides: indexes (multilevel indexes, indexes in MySQL)
slides: query processing (introduction, relational algebra)

Additional Reading:

Elmasri and Navathe, sections 6.1-6.3 and 6.5
Elmasri and Navathe, section 15.1

Week 13: 11/20-11/24

Topics: query processing and optimization

Mon

Reading:

query processing and optimization (sections 12.2-12.4)

Materials:

slides: query processing (query trees, query execution plans, algorithms for relational algebra operations, query optimization)
practice problems: query processing (query trees)

Additional Reading:

Elmasri and Navathe, sections 15.2-15.8

Wed Thanksgiving break
(no class)

Fri

Week 14: 11/27-12/1

Topics: query processing and optimization

Mon

Materials:

slides: heuristic query optimization
practice problems: query processing (heuristic query optimization)

Wed

Materials:

slides: cost functions and evaluating the cost of an execution plan
practice problems: query processing (cost of an execution plan)

homework 5
File Organization and Query Processing

due Wed Dec 6

partial solutions

Fri

Materials:

practice problems: query processing (query processing)

Week 15: 12/4-12/8

Topics: database efficiency and tuning; wrapup

Mon

Materials:

slides: database tuning and EXPLAIN

Wed

Materials:

EXPLAIN examples

Fri final exam (take home)
due Tue 12/12 at 11:30am

review information

Reading Period: 12/9-12/11

Sat

Sun

Mon

Exams: 12/12-12/15

Tue end-of-semester deadline
no work accepted after 12/12 11:30am

Wed

Thu

Fri