COMP210: Database Design (F'06)
Class Notes

Lecture notes, handouts, and readings for upcoming classes will be posted here. Lecture notes are usually posted two or three days after class.

Week 14

• Review: Exercises

Week 13

• Handout: Outer Joins
• Exercises: Outer Joins
• Exercise Solutions: Outer Joins
• Readings (1ed): Text Chapter 7 (Retrieving Data from Multiple Tables), pp168-169 and pp198-210.
• Readings (2ed): Text Chapter 7 (Joins), pp186-187 and pp217-228.

Week 12

• Handout: Grouping Queries
• Exercises: Grouping and Views
• Exercise Solutions: Grouping and Views
• Readings: Text Chapter 6 (Summarizing and Grouping Data).

Week 11

• Handout: Database View Objects
• Exercise: COMP210 Grade Calculation Views
• Exercise Solution: COMP210 Grade Calculation Views
• Sample Database: Famous Drummer Tanning Emporium
• Exercise Solutions: Query Review and Practice
• Readings (1ed): Text Chapter 12 (Views) everything but pp358-362.

Week 10

• Handout: SQL Functions
• Exercise: FDTE Surrogate Key Population
• Exercise Solution: FDTE Surrogate Key Population
• Readings (1ed): Reread Text Chapter 9 (Inserting, Updating, and Deleting Rows) pp. 289-291; Reread Text Chapter 5 (Operators and Functions) everything but pp123-130.

Week 9

• Handout: Data Manipulation Language: Adding, Modifying, and Removing Data
• Exercises: Reverse Schema Conversion
• Exercise Solution: Movie Awards Database Reverse Schema Conversion
• Exercise Solution: Writing DML Population Scripts
• Readings (2ed): Text Chapter 10 (Inserting, Updating, and Deleting Rows).

Week 8

• Handout: Schema Script Files
• Exercise: Writing Schema Scripts
• Exercises: More Table Validation
• Exercise Solution: FDTE Surrogate Conversion
• Exercise Solution: Writing Schema Scripts
• Exercise Solutions: More Table Validation
• Readings (2ed): Text Chapter 5 (Operators and Functions) everything but pp139-146.

Week 7

• Handout: How the SELECT Statement Works
• Exercise: Famous Drummer Tanning Emporium
• Exercise Solution: Famous Drummer Tanning Emporium
• Readings (2ed): Text Chapter 2 (The Relational Model) pp33-38; Text Chapter 11 (Creating, Altering, and Dropping Tables) everything but pp379-388.
• Readings (1ed): Text Chapter 2 (The Relational Model) pp29-33; Text Chapter 10 (Creating, Altering, and Dropping Tables) everything but pp326-335.

Week 6

• Lecture Notes: Table Validation
• Handout: Two-bit Guide to Normal Forms
• Exercises: SELECT Statement Queries
• Exercises: Table Validation
• Exercise Solutions: SELECT Statement Queries
• Exercise Solutions: Table Validation
• Readings: Text Chapter 4 (Retrieving Data from a Table), the rest of the chapter.

Week 5

• Lecture Notes: Schema Conversion Details
• Handout: Linux and psql Survival Guide
• Handout: Database Queries
• Handout: Database Tables, Columns, and Constraints
• Sample Database: Discount Diva Prop Rentals
• Readings (2ed): Text Chapter 1 (DBMS Specifics) pp19-20; Text Chapter 3 (SQL Basics); Text Chapter 4 (Retrieving Data from a Table) pp69-86.

Week 4

• Handout: Schema Conversion Rules
• Exercises: Schema Conversion
• Exercise Solution: StubbleScrape Razors Conversion
• Exercise Solution: Student Grading System Conversion
• Readings (1ed): Text Chapter 2 (The Relational Model) pp17-28.
• Readings (2ed): Text Chapter 2 (The Relational Model) pp21-32.

Week 3

• Lecture Notes: Reflexive Relationships
• Lecture Notes: Ternary Relationships
• Exercises: Yet More Entity-Relationship Modelling
• Exercise Solution: Topical Tropical Topographic Maps
• Exercise Solution: Discount Diva Prop Rentals Seating Assignments

Week 2

• Lecture Notes: Visualizing Relationship Cardinality
• Handout: Entity-Relationship Modelling Tips
• Exercises: Entity-Relationship Modelling
• Exercise Solution: Sci-Fi Star Maps
• Exercise Solution: Student Grading System
• Exercise Solution: StubbleScrape Razors
• Link: Gliffy is an online diagram tool that can at least handle almost everything in an entity-relationship diagram but the cardinality and participation symbols. You can then save your diagram as a JPEG or PNG image. Get rid of the drop shadow and gradient fill for the entity rectangles if you do decide to use it.

Week 1

• Lecture Notes: The Entity-Relationship Model
• Handout: Crow's Foot Entity-Relationship Diagram Notation
• Exercises: Entity-Relationship Modelling
• Exercise Solution: Discount Diva Prop Rentals