jackmyers.info

Schedule for Databases

Monday Wednesday Weekend
Week of Lesson Topics Lesson Topics Work Due
5/22/2017 1 Introduction to Databases

Chapter 1: Databases and Database Users
Chapter 2: Database System Concepts and Architecture

Modeling our Movie Database in Access and Using the Query Wizard

2 Data Modeling

Chapter 3: Data Modeling Using the ER Model
Section 9.1: Relational DB Design Using ER-Relational Mapping Algorithm

Read chapters 3, 4
5/29/2017 Happy Memorial Day 2 Data Modeling

Chapter 4: The Enhanced ER (EER) Model
Section 9.2: Mapping EER Model Constructs to Relations

From Section Tally to ER diagram: individual ERs, then group

ASSIGNMENT DUE: Complete exercise 3.22 in the textbook. Upload your ER diagram and a Word file that states the assumptions you have made. For this exercise, you must use a Baseball team. You do not have to upload the Access file, as we will be modifying this in the next assignment.

Ready for Modeling Test
6/5/2017 2, 3 Data Modeling, Keys and SELECT

Chapter 4: The Enhanced ER (EER) Model - Overlapping and Union models
Chapter 5: The Relational Data Model and Relational DB Constraints
Section 6.3: Basic Retrieval Queries in SQL

Sakila and AdventureWorks queries

3 Keys and SELECT

Chapter 5: The Relational Data Model and Relational DB Constraints
Section 6.3: Basic Retrieval Queries in SQL

Sakila and AdventureWorks queries

6/12/2017 4 Variables and Inner Joins

Chapter 7: More SQL...
Section 7.1.6: Joined Tables in SQL and Outer Join

Using variables in Sakila
Joining Tables in Adventureworks

4, Special Lecture Outer Joins, Unions, JavaEE & Inheritance Modeling

Section 7.1.6: Joined Tables in SQL and Outer Join

Joining Tables in Adventureworks

ASSIGNMENTS DUE:

AdventureWorks Basic SQL
6/19/2017 4 Aggregation Queries

Section 7.1.7: Aggregate Functions in SQL
Section 7.1.8: Grouping: The GROUP BY and HAVING Clauses

emp/secretary/technician/janitor - from Hazel to Hank

Aggregation in AdventureWorks

5 Subqueries

Sections 7.1.2: Nested Queries

Subqueries in AdventureWorks

ASSIGNMENT DUE: Extending the NL East Model (exercise 4.30) then take your EER NL East Model and build it in MS Access including data. Upload the Access file and the EER diagram.

JOINing in AdventureWorks
6/26/2017 5, 6 Subqueries
DDL and DML


Sections 7.1.3 - 7.1.4: Correlated Queries, EXISTS/UNIQUE
Sections 6.1. 6.2 & 6.3: DDL, Data Types, Constraints, DML

Practice queries

7 Concept Review and Introduction to Functional Dependencies

Review:
1) Physical modeling of 1:N with relationship attributes (paintings and museums)
2) Storage of superclass key in 1:1 (inheritance)
3) Practice with subqueries
Chapter 14: Basics of Functional Dependencies and Normalization for Relational DB

ASSIGNMENT DUE: Aggregation in AdventureWorks

Ready for SQL Test
7/3/2017 Happy 4th of July Weekend 7 More Functional Dependencies and Data Normalization

Chapter 14: Basics of Functional Dependencies and Normalization for Relational DB

TBD

ASSIGNMENT DUE: Subqueries in AdventureWorks
7/10/2017 8 Query Optimization

Chapter 18: Strategies for Query Processing

TBD

8 Query Optimization

Chapter 19: Query Optimization

TBD

7/17/2017 9 Programming at DB Level: Procedures and Triggers

Chapter 10: Introduction to SQL Programming Techniques

TBD

9 Programming at Web Server Level

Chapter 11: Web Database Programming Using PHP

TBD

PROJECT DUE: PHP — AdventureWorks and Sakila on the Web
7/24/2017 10 Hierachical, Network and NOSQL Databases — plus the CAP Theorem

Appendix D: Overview of the Hierarchical Data Model
Appendix E: Overview of the Network Data Model
Chapter 24: NoSQL DBs and Big Data Storage Systems

MongoDB

10, 11 NOSQL Databases
Neo4j: A Graph Database


Chapter 24: NoSQL DBs and Big Data Storage Systems
O'Reilly Text: Neo4j

MongoDB, Neo4j

PROJECT DUE: MongoDB
7/31/2017 11 Neo4j: A Graph Database

O'Reilly Text: Neo4j

Neo4j

No class PROJECT DUE: Neo4j database
8/7/2017 12 Transactions and Locking

Chapter 20: Introduction to Transaction Processing Concepts and Theory

MongoDB

12 Transactions and Locking

Chapter 20: Introduction to Transaction Processing Concepts and Theory

TBD


Chapter references from Fundamentals of Database Systems, 7th Edition, Ramez Elmasri & Shamkant B. Navathe, 2016.