COMP210: Database Design I (F'04)
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

• Handout: Special Joins
• Lab Notes: Special Joins Practice
• Exercise: Special Joins Practice
• Review: Voting System Database
• Review: Exercises
• Readings: Text Chapter 7 pp168-169 (CROSS JOIN), pp198-210 (OUTER JOINs)

Week 13

• Handout: Database View Objects
• Handout: Grouping Queries
• Lab Notes: Grouping Queries and Views Practice
• Exercise: Grouping Queries Practice
• Exercise: Views Practice
• Readings: Text chapters 6 (Summarizing and Grouping Data) and 12 (Views)

Week 12

• Lab Notes: Query Review and Practice
• Exercise: Query Review and Practice
• Sample Database: Famous Drummer Tanning Emporium
• Readings: Review Text chapters 4, 5, and 7; Review SELECT statement notes from weeks 4, 5, and 6

Week 11

• Handout: Surrogate Keys and INSERT INTO SELECT
• Lab Notes: Populating Surrogate Key Values
• Exercise: Populating Surrogate Key Values

Week 10

• Handout: Data Manipulation Language: Adding, Modifying, and Removing Data
• Lab Notes: Writing DML Population Scripts
• Exercise: Writing DML Population Scripts
• Readings: Text Chapter 9 (Inserting, Updating, and Deleting Rows)

Week 9

• Lecture Notes: Denormalization
• Handout: Anti-Normalization
• Lab Notes: Yet More Normalization
• Exercise: Anti-Normalization

Week 8

• Handout: Schema Script Files
• Lab Notes: Writing Schema Scripts
• Exercise: Writing Schema Scripts

Week 7

• Lecture Notes: Normalization Practice
• Handout: Practical Table Validation
• Lab Notes: Data Definition Language
• Exercise: Data Definition Language
• Readings: Text Chapter 10 (Creating, Altering, and Dropping Tables) but skip pp326-335

Week 6

• Handout: Normalization Rules
• Lab Notes: SELECT Queries
• Exercise: SELECT Queries
• Readings: Text Chapter 5 (Operators and Functions) but skip the CASE expression pp127-130; Text Chapter 7 (Retrieving Data from Multiple Tables) but skip "Creating Outer Joins with OUTER JOIN" section between p198 and p210, and note that in class we always use the INNER JOIN syntax instead of the WHERE syntax.

Week 5

• Lecture Notes: Normal Forms
• Handout: How the SELECT Statement Works, Redux
• Handout: SELECT Statement Details
• Lab Notes: SELECT Statement Details
• Exercise: Normal Forms and Even More Modelling
• Readings: Text Chapter 4 (Retrieving Data from a Table); Text Chapter 2 pp29-33 (Normalization)

Week 4

• Handout: Database Tables, Columns, and Constraints
• Handout: Database Queries
• Lab Notes: Yet More Entity-Relationship Modelling
• Exercise: Yet More Entity-Relationship Modelling
• Readings: Text Introduction; Text Chapter 1 (DBMS Specifics) pp2-3, 15-16; Text Chapter 2 (Relational Model) pp17-28; Text Chapter 3 (SQL Basics); Text Chapter 4 (Retrieving Data from a Table) pp61-64

Week 3

• Lecture Notes: More Entity-Relationship Modelling
• Handout: Entity-Relationship Modelling Tips
• Lab Notes: More Entity-Relationship Modelling
• Exercise: More Entity-Relationship Modelling
• Sample Database: Discount Diva Prop Rentals

Week 2

• Lecture Notes: Database Design and Entity-Relationship Modelling
• Handout: Crow's Foot Entity-Relationship Diagram Notation
• Lab Notes: Animal Shelter Modelling Exercise
• Exercise: Entity-Relationship Modelling
• Sample Model: Discount Diva Prop Rentals

Week 1

• Lecture Notes: Introduction to Relational Databases