This page complements the course schedule but focuses on modules where each module is a group of related concepts. This organization provides an integrated view of the material, particularly highlighting cases where lesson preparation is shared across multiple lessons (e.g., when multiple lessons cover the same chapter in the textbook) and the assignment(s) related to those lessons.

This page is not comprehensive, notably omitting graded reviews (GRs), project deliverables, and due dates. See the course schedule for that information.

Introduction

This module lays the groundwork for the course: what is data, why is it important, and why have we created complex systems to manage it? In addition, this module covers key policies and expectations for this course.

Course Overview

Introduction

Introductions

Modeling

With so much data available today, it’s critical to understand what matters to an organization: i.e., what is needed to support the organization’s business processes and to give it a competitive advantage? Data modeling provides a consistent way to record the concepts and relationships among them for a particular business context. We study entity-relationship (ER) models, which are the most common approach to diagram this information when designing a database.

Fundamentals of Database Management Systems, Chapter 2

ER Modeling I

ER Modeling II

Fundamentals of Database Management Systems, Chapter 9 (skim)

ER Modeling III

ER Modeling (solutions)

Database Management Systems (DBMSs)

A database management system (DBMS) provides a environment with which to interact with a database that integrates data while minimizing pitfalls such as data redundancy. In addition, a DBMS maintains control of the underlying data, which ensures its security, supports backups and recovery, and maximizing concurrency. This module focuses on relational DBMSs as the perennial approach to managing data effectively in organizations.

Fundamentals of Database Management Systems, Chapter 3

Database Systems

DBMS Concepts (solutions)

Relational Database Design

This module addresses the translation of a conceptual data model – such as an ER diagram – into a practical, performant database. To some degree, the process is straightforward, following a well-defined set of rules for most entities and their relationships. Data normalization ensures that the resulting relations are free of redundancy.

Fundamentals of Database Management Systems, Chapter 5 and Chapter 6

Relational Data Model

Relational Database Design (solutions)

Fundamentals of Database Management Systems, Chapter 7

Normal Forms I

Normal Forms II

PostgreSQL and pgAdmin

Logical Database Design (solutions)

Structured Query Language (SQL)

The Structured Query Language (SQL) is the standard interface to manage and access data in a relational database. This module starts with its theoretical underpinnings, the relational algebra, before focusing on SQL’s practical application. Unlike many other programming languages, SQL is declarative and focuses on what should be accomplished rather than how to accomplish it. This feature allows DBMSs to optimize queries – often extensively – with minimal involvement from the programmer.

SQL Data Definition

Fundamentals of Database Management Systems, Chapter 5

Relational Algebra I

Relational Algebra II

Relational Algebra (solutions)

Fundamentals of Database Management Systems, Chapter 4

SQL SELECT

SQL Aggregates

SQL Joins

SQL Subqueries

SQL Set Operators

Structured Query Language (SQL) (solutions)

Web Applications

Databases don’t exist in a vacuum. Few of their users today would be willing to learn specialized languages, such as SQL, in order to interact with them. This module provides an introduction to web programming, coupling the power of DBMSs with the accessibility of the Internet so that even non-technical users can reap the benefits of increased access to information.

Fundamentals of Database Management Systems, Chapter 14

Database Connectivity

XML

Databases and the Internet (solutions)

Web Development

HTML and CSS

JavaScript

AJAX

Introduction to PHP

PHP Database Programming

Sessions

REST and JSON

Web Security

SQL Injection and XSS

Performance Issues

This module surveys a variety of issues related to a database’s performance. First, it highlights different types of indexes as a way to speed data retrieval, particularly when the data resides on (relatively) slow magnetic disks. Transaction management is critical to support simultaneous database access by multiple users and to ensure that such access does not corrupt or otherwise return incorrect results. Finally, this module surveys current database trends, particularly the rise of big data and the NoSQL movement.

Fundamentals of Database Management Systems, Chapter 8

Indexing and B+ trees

Physical Database Design (solutions)

Transactions and Concurrency

Fundamentals of Database Management Systems, Chapter 11 and Chapter 12

ACID Properties

Transactions and Concurrency

Control Issues (solutions)

Distributed DBs

Performance and Optimization

Distributed Databases (solutions)

Fundamentals of Database Management Systems, Chapter 13

Big Data and NoSQL

Privacy Protection