SQL and Relational Databases

Summary

This course focuses on the relational model, the SQL language, and how to work with relational databases. We start with SQL and the relational data model, introduce core database concepts such as tables, rows, columns, keys, constraints and joins, and guide you to learn how to interact with databases from various types of applications. Along the way, we'll discuss key query optimization techniques and utilize database interface libraries to work with higher level relational abstractions.

Phase

Programming and Back-end Development

Prerequisites

Detailed Syllabus

Schema, Data and SQL

  • The SQL Language
  • PostgreSQL Data Types
  • Working with a Single Table
  • Loading Database Dumps
  • More Single Table Queries
  • NOT NULL and Default Values
  • More Constraints
  • Using Keys
  • GROUP BY and Aggregate Functions
  • How PortgreSQL Executes Queries
  • Table and Column Aliases

Relational Data and JOINs

  • What is Relational Data?
  • Database Diagrams: Levels of Schema
  • Database Diagrams: Cardinality and Modality
  • A Review of JOINs
  • Working with Multiple Tables
  • Foreign Keys
  • One to Many Relationships
  • Extracting a 1:M Relationship From Existing Data
  • Many to Many Relationships
  • Converting a 1:M Relationship to a M:M Relationship

Interacting with a Database in Code

  • Getting Started
  • Executing SQL Statements from Ruby
  • Project Demo
  • Project Setup
  • Database Design
  • Listing Expenses
  • Displaying Help
  • Adding Expenses
  • Handling Parameters Safely
  • Code Structure
  • Searching Expenses
  • Deleting Expenses
  • Clearing Expenses
  • Counting and Totaling Expenses
  • Creating the Schema Automatically

Database-backed Web Applications

  • Getting Started
  • Project Overview
  • Extracting Session Manipulation Code
  • Designing a Schema
  • Setting up a Database Connection
  • Executing and Logging Database Queries
  • Loading Records From the Database
  • Solving the Reloading Problem
  • Development Configuration
  • Working with Lists
  • Working with Todos