CPSC 343

Database Theory and Practice

Fall 2018

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/27-8/31

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

introductory survey

due Fri Aug 31

Fri

Reading:

ER design principles (section 3.2)

Materials:

slides: ER modeling (design decisions)
example: car insurance (incomplete)
practice problems: ER modeling

Additional Reading:

Elmasri and Navathe, sections 3.7.2-3.7.3 and 3.9

Week 2: 9/3-9/7

Topics: the entity-relationship model; the relational model

Mon

Materials:

example: car insurance (mostly complete)
registrar and IMDB problems handout
practice problems: ER modeling

Wed

Reading:

the enhanced ER model (chapter 4)

Class Prep: available on Canvas

Materials:

example: IMDB problem handout (page 2), IMDB example (very incomplete)
slides: enhanced ER model
practice problems: ER and EER modeling

Additional Reading:

Elmasri and Navathe, chapter 4 through section 4.5.2

Fri

Reading:

the relational model (chapter 5)

Class Prep: available on Canvas

Materials:

examples: IMDB problem v2, actor/crew/writer/director (specialization), TV show/movie (specialization vs category)
slides: 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 14

solutions: #1, #3, other comments

Week 3: 9/10-9/14

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

Mon

Reading:

ER-to-relational mapping (chapter 6)

Materials:

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

Additional Reading:

Elmasri and Navathe, chapter 7

Wed

Reading:

design principles for relational schemas (section 7.1)

Materials:

slides: ER-to-relational mapping, relational design principles

Additional Reading:

Elmasri and Navathe, section 10.1

project topic proposal
due Wed Sep 19

Fri

Reading:

normalization (section 7.2)

Class Prep: available on Canvas

Materials:

slides: 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/17-9/21

Topics: decomposition and normalization; SQL - data definition, basic queries

Mon

Materials:

slides: normalization

homework 2
ER-to-relational mapping and normalization

due Mon Sep 24

partial solutions and comments: #1, #3, #4, #5

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 - optional)

Materials:

slides: SQL data definition
in-class exercises (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 queries
in-class exercises and practice problems: basic queries (solutions)

Additional Reading:

Elmasri and Navathe, sections 8.4-8.5.1

Week 5: 9/24-9/28

Topics: SQL - set operations and subqueries

Mon

Reading:

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

Class Prep: available on Canvas

Materials:

slides: set operations and subqueries
in-class exercises and practice problems: set operations and subqueries (solutions)

Additional Reading:

Elmasri and Navathe, sections 8.5.2-8.5.6

project specifications
due Fri Oct 5

Wed

exam 1 (in class)
Rosenberg 009

review information

Fri

Materials:

in-class exercises and practice problems: set operations and subqueries (solutions)

Week 6: 10/1-10/5

Topics: SQL - aggregation and grouping

Mon

Reading:

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

Materials:

slides: aggregation and grouping
in-class exercises and practice problems: aggregation and grouping (solutions)

Additional Reading:

Elmasri and Navathe, sections 8.5.7-8.5.8

Wed

exam 2 (in class)
Rosenberg 009

review information

Fri

Materials:

in-class exercises and practice problems: set operations and subqueries (solutions), aggregation and grouping (solutions)

Week 7: 10/8-10/12

Topics: SQL - data modification; data integrity - triggers

Mon fall break (no class)

Wed

Reading:

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

Materials:

practice problems: complex SQL queries (solutions)
in-class exercises (data modification)

Additional Reading:

Elmasri and Navathe, section 8.6

project conceptual model
due Wed Oct 24

Fri

Reading:

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

Materials:

slides: project comments
slides: triggers
practice problems: triggers (solutions)

homework 3
SQL

due Fri Oct 19

solutions and comments

Week 8: 10/15-10/19

Topics: data integrity - transactions; views; stored routines

Mon

Reading:

transactions (section 8.7)

Materials:

slides: transactions

Wed

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

Fri

Reading:

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

Materials:

slides: stored routines (procedures, functions, parameters, variables)
practice problems: stored routines (solutions)

Week 9: 10/22-10/26

Topics: stored routines; data integrity and security; database applications

Mon

Materials:

slides: stored routines (conditionals, loops, cursors, handlers)
practice problems: stored routines (solutions)

Wed

Materials:

slides: security

homework 4
Access and Integrity

due Wed Oct 31

solutions and comments

Fri

Materials:

slides: database applications

project database design
due Wed Nov 7

Week 10: 10/29-11/2

Topics: database applications

Mon

-- meet in Rosenberg 009 --

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 Rosenberg 009 --

Fri -- meet in Rosenberg 009 --

Week 11: 11/5-11/9

Topics: database applications; file structure and data storage

Mon -- meet in Rosenberg 009 --

Wed

exam 3 (in class)
Rosenberg 009

review information

Fri

Reading:

file organization (chapter 9)

Materials:

slides: file organization

Additional Reading:

Elmasri and Navathe, sections 13.1-13.8

project implementation
due Tue Dec 11

Week 12: 11/12-11/16

Topics: indexing

Mon

exam 4 (in class)
Rosenberg 009

review information

Wed

Reading:

indexing (chapter 10)

Materials:

slides: indexing (primary, clustering)

Additional Reading:

Elmasri and Navathe, sections 14.1-14.2

Fri

Materials:

slides: indexing (secondary, multilevel; indexes in MySQL)
practice problems: file organization and indexing

Thanksgiving Break: 11/19-11/23

Week 13: 11/26-11/30

Topics: relational algebra; query processing and optimization

Mon

Reading:

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

Class Prep: available on Canvas

Materials:

slides: relational algebra and query trees
practice problems: query processing (query trees)

Additional Reading:

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

Wed

Reading:

query processing and optimization (sections 12.2-12.4)

Materials:

query processing (algorithms, query optimization, heuristic optimization)

Additional Reading:

Elmasri and Navathe, sections 15.2-15.8

Fri

Materials:

slides: query processing (heuristic optimization, cost-based optimization)
practice problems: query processing (heuristic optimization, execution plans)

Week 14: 12/3-12/7

Topics: query processing and evaluation; database efficiency and tuning

Mon

Materials:

slides: query processing (cost-based optimization)
practice problems: query processing (cost-based optimization)

homework 5
File Organization and Query Processing

due Mon Dec 10

partial solutions and comments

Wed

Materials:

slides: database tuning (EXPLAIN and optimizer hints)
handouts: EXPLAIN examples

Reference:

InnoDB On-Disk Structures and InnoDB Row Formats

Fri

Materials:

handouts: EXPLAIN examples
slides: more EXPLAIN examples

Week 15: 12/10-12/11

Topics: wrapup

Mon

-- bring a device (laptop, tablet, phone) if you can --

Materials:

handouts: EXPLAIN examples

Reading Period: 12/12-12/14

Wed office hours 12-2pm

Thu office hours 1-3pm

Fri office hours 1-3pm

Exams: 12/15-12/18

Sat

Sun

Mon

exam 5 and final exam
12/17 1:30-4:30pm
Rosenberg 009

end-of-semester deadline
no work accepted after 12/17 4:30pm

Tue