Domain Modeling and Databases

Learning Objectives

Describe the basic parts of an ERD
Construct ERDs to model the nouns and relationships in a domain
Explain what a database is and why you would use one as opposed to other persistent storage mechanisms
Explain the difference between a database management system (R/DBMS) and a database, and name the major DBMSes
Convert an ERD into a schema and load it into SQLite3

Framing (5 minutes)

Mention no slides today, but I can provide brief notes / resources if desired.

We've been working with objects for a bit now, and can see some of the benefits. What are those benefits?

Think-Pair-Share (30 sec + 2 minutes + 2 minutes)

What problems do we still have working with object? In other words, what's missing? What happens when our program quits?

While objects and OOP are great, we don't have a way to save our objects, do we?

What happens when our program quits?

Even if we could save our objects, how do we 'find' them?

By the end of today, we should have a solution to these problems using ActiveRecord, which is a major component of Rails. ActiveRecord uses a database to store our objects even when our program quits.

We can use it to create, retrieve, modify, and delete objects.

To get there, we need to talk about four topics:

Domain modeling
Databases
Gems
Active Record Basics

Domain Modeling

Background - What is domain modeling? (5 minutes)

I do

When talking about a problem, we often think of things in terms of nouns and verbs.
The verbs are really methods in our code (so they are 'saved there')
The nouns are the objects in our program.

Often what we can do in our code is dictated by what our objects look like and how they relate to each other. So it's good practice to think through what types objects are in our program, and what attributes they have.

One tool we have for doing this is called an ERD. ERDs only capture the nouns, their attributes (instance variables), and their relationships, NOT the verbs.

Class Exercise - Modeling a Library (10 minutes)

We do

Work with students to model a library. List the sorts of nouns that we need to include.

As we build the ERD, talk about the concept of relationships (one-one, one-many, many-many)

Eg:

Books
Authors
Patrons
Shelves
Employees

Once we're done, highlight the section of the ERD that we'll be working with for today (books and authors).

Group Exercise - Modeling the class (5 minutes + 10 minutes to present)

(3 ppl / group) You do

In groups of 3, choose a problem you're interested in and construct an ERD. Your ERD should have at least 3 models, and appropriate relationships and attributes.

Give each group 2 minutes to explain their ERD to the class.

BREAK (5 minutes)

Intro to Databases

Background - Why DBs (5 minutes)

I DO So we have these ERDs / models for our objects. They only describe the general attributes. When we create instances, that's where the actual 'data' is.

We need a way to store that data. How can we do that? Ask class for a few ways to store data

It turns out that DBs are generally a good solution to solve this problem, why?

Fast
Efficient
Very reliable
Adaptable
Lots of features ('querying', consistency, GIS, etc)

What is a DB? (10 minutes)

A set of tables, where each table has a structure (columns), and the data (rows).

If you've ever used a spreadsheet, DBs are like really advanced spreadsheets.

Demonstrate by drawing the table(s) from the library db on the whiteboard.

DB Software (5 minutes)

It's important to mention that there are lots of different database tools. Common ones include:

MySQL
PostgreSQL
SQLite
MS SQL Server

Each one has some pros/cons, and features, etc, but they all share something in common (SQL - Structured Query Language). These are often called 'Relational DBs'

Why is the 'relational' important? Consider the R in ERD...

We'll be using SQLite3 because it's easy to set up, but much of what we'll learn will transfer to other SQL DBs.

There are other types of DBs that differ in fundamental approach, such as the new NoSQL DBs (e.g. MongoDB, Cassandra, Redis)

Creating a DB (5 minutes)

Demonstrate by creating a simple database, e.g. pokeman:

sqlite3 pokedex

Write the following schema and use Use .read pokemon_schema.sql to read load it in.

CREATE TABLE pokemon (
  id INTEGER PRIMARY KEY,
  name VARCHAR(255),
  type VARCHAR(255),
  hp INTEGER
);

Describe the different datatypes and why we have to define them in advance. Describe the purpose of a primary key and auto-increment.

Group Exercise - Write the schema for Library (10 minutes)

YOU DO In groups of 2, create a file folder called my_library. In that folder create a file called schema.sql. In that file, create the necessary CREATE statements to create the books and authors table from our example.

Provide students with the seed.sql file and have them load it into their DB.

Wrap Up - Quick demo of SQL (5 minutes)

Frame that we're not going to really be using SQL in this class, instead we'll use ActiveRecord to abstract away the DB / SQL.

Demonstrate using SELECT to find records. Demonstrate a JOIN to show how SQL can handle relationships.

BREAK (10 minutes)

Should be 8pm at this point!

adambray/example_lesson.md