Lecture Notes

Week 14

Jump to Top of Page.

• Lab Notes: Outer Joins
• Exercise: Queries with Views and Joins
• Review: Exam Overview and Term Review
• Review: Exercises
• Review: Voting System Database

Week 13

Jump to Top of Page.

• Lab Notes: Grouping Queries, Views, and HAVING
• Lab Notes: Queries with Functions and GROUP BY
• Exercise: Grouping Queries
• Exercise: Queries with Functions
• Schema: Discount Diva Prop Rentals-Take 5

Week 12

Jump to Top of Page.

• Handout: PostgreSQL Metadata
• Handout: SELECT Statement Details
• Handout: SQL Functions
• Schema: Discount Diva Prop Rentals-Take 4

Week 11

Jump to Top of Page.

• Lab Notes: DDPR-Take 3 Solution
• Exercise: Discount Diva Prop Rentals-Take 3
• Handout: Surrogate Keys and INSERT INTO SELECT

Week 10

Jump to Top of Page.

• Lab Notes: DDPR-Take 2 Solution
• Exercise: Discount Diva Prop Rentals-Take 2
• Handout: Data Manipulation Language: Querying Data
• Readings: Text (9th ed) Chapter 6 (Introduction to Structured Query Language) the rest of the chapter; Text (8th ed) Chapter 8 (Foundations of Relational Implementation) the rest of the chapter; W3Schools' Online SQL Tutorial

Week 9

Jump to Top of Page.

• Lab Notes: Writing DML Population Scripts Solution
• Exercise: Writing DML Population Scripts
• Handout: Data Manipulation Language: Adding, Modifying, and Removing Data

Week 8

Jump to Top of Page.

• Lab Notes: Writing DDL Solution
• Exercise: Writing DDL
• Handout: Installing the WAMP Environment
• Handout: Referential Integrity

Week 7

Jump to Top of Page.

• Lecture Notes: Data Definition Language
• Lab Notes: Discount Diva Schema Script-Take 1
• Handout: Data Definition Language: CREATE TABLE
• Handout: Linux Survival Guide
• Handout: Schema Script Files
• Schema: Discount Diva Prop Rentals-Take 1
• Readings: Text (9th ed) Chapter 6 (Introduction to Structured Query Language) just up to the section called "SQL for Relational Query" on page 199; Text (8th ed) Chapter 8 (Foundations of Relational Implementation) just the section titled "Defining Relational Data"

Week 6

Jump to Top of Page.

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

Week 5

Jump to Top of Page.

• Lecture Notes: Normalization Practice
• Lab Notes: Normalization Exercise
• Exercise: Normalization
• Handout: Normalization Rules
• Readings: Text (9th ed) Chapter 5 (Database Design); Text (8th ed) Chapter 6

Week 4

Jump to Top of Page.

• Lecture Notes: Normal Forms
• Lab Notes: Normal Forms and Yet More Modelling
• Exercise: Normal Forms and Yet More Modelling
• Handout: Normal Forms
• Readings: Text (9th ed) Chapter 4 (The Relational Model and Normalization), skip the section called domain/key normal form; Text (8th ed) Chapter 5 (The Relational Model and Normalization), skip the section called domain/key normal form

Week 3

Jump to Top of Page.

• Lecture Notes: More Entity-Relationship Modelling
• Lab Notes: More Entity-Relationship Modelling
• Exercise: More Entity-Relationship Modelling
• Handout: Entity-Relationship Modelling Tips
• Readings: Text (9th ed) Chapter 3 (Entity-Relationship Data Modelling: Process and Examples), don't concern yourself too much with the section called "Constructing Data Models from Forms and Reports", but you should at least have a gander; There are no additional readings for those using the 8th edition.

Week 2

Jump to Top of Page.

• Lecture Notes: Database Design
• Lab Notes: Animal Shelter Modelling Exercise
• Exercise: Entity-Relationship Modelling
• Handout: Crow's Foot Entity-Relationship Diagram Notation
• Readings: Text (9th ed) Chapter 2 (Entity-Relationship Data Modeling: Tools and Techniques), the IDEF1X and UML sections will not be tested, but you may find them informative, particularly the portion of the IDEF1X section that discusses domains; Text (8th ed) Chapter 3 (The Entity Relationship Model)

Week 1

Jump to Top of Page.

• Lecture Notes: Introduction to Relational Databases
• Readings: Text (9th ed) Chapter 1 (Introduction to Database Processing); Text (8th ed) Chapters 1 (Introduction to Database Processing) & 2 (Introduction to Database Development)