Card Exercise for Data Modeling

Here are four images:

Image one Image two
Image three Image three

Task One: Descriptions

Use either pen and paper.

Write a short description of these four objects. Entirely free form. About 3 or four sentences only.

Task Two: Discussion

  • Discuss your description (with partner)
  • Circle or mark nouns and adjectives.

Task Three: Standardizing

  • Form into larger groups ~(4-6 members)
  • Narrow down to three kinds of adjectives (make “color” one of them)

Task Four: Craft Time!

Create a string database using the materials provided.

  1. Using one yellow post-it per word/phrase write
  • the name of each item
  • the set of colors
  • the set of your other adjective
  1. Using the string, join up objects with the words that describe them.

This is a database that we can query, by following the links.

Task Five: Representations

There are lots of different ways that we can represent the structured data that you have worked with.

Your group will be assigned three different ways and you will work either on a whiteboard (plan to fit both on the board), or on a large post-it pad (use multiple pages).

One from here:

  1. Three columns: object, attribute, value
  2. One row per object, one column per attribute.
  3. One tab per object
  4. One tab per attribute
  5. Free-text sentences about each object
  6. Free-text sentences about each attribute

One from here:

  1. Positional columns (color_1, color_2, color_3)
  2. Putting a comma separated list inside a cell (e.g., “red, green”)
  3. “One-hot” (e.g., is_red, can_write)

One from here:

  1. A card for each object (think trading card or dating profile)
  2. Restaurant Menu style (grouped headers)
  3. Mad libs template style

Task Six: Queries

Queries:

  1. Find all the red objects
  2. Count how many objects there are.
  3. What else do we know about the green objects?

Task Seven: Schemas vs Formats

Take a photo of your whiteboard. How has the information changed?

What do we learn when someone tells us that data is “in a csv” or “in json format”?

vs

What do we learn when we look at column headers or field names?

Format is mechanical; schema is semantic.

Schema or format?

Four small facts about two objects:

  • (mug, red)
  • (highlighter, green)
  • (mug, holds)
  • (highlighter, writes)

Below, the same four facts are written four ways. Two things are varying at once: how the rows are shaped (down the page) and what the file looks like (across the page).

CSV JSON
Wide
one row per object		 object,color,action
mug,red,holds
highlighter,green,writes		 [
  {"object": "mug",
   "color": "red",
   "action": "holds"},
  {"object": "highlighter",
   "color": "green",
   "action": "writes"}
]
Long
one row per fact		 object,attribute,value
mug,color,red
mug,action,holds
highlighter,color,green
highlighter,action,writes		 [
  {"object": "mug",
   "attribute": "color",
   "value": "red"},
  {"object": "mug",
   "attribute": "action",
   "value": "holds"},
  {"object": "highlighter",
   "attribute": "color",
   "value": "green"},
  {"object": "highlighter",
   "attribute": "action",
   "value": "writes"}
]

Reading the grid

Move across a row. Those are different formats. This is a mechanical change.

Move down a column. These are different schemas. We have more rows, different column names, this ia change requiring judgement and raising questions of usability and semantic understanding.

A useful question to ask test

Could a program do this conversion without knowing what the data means?

Yes → format. No → schema.

They can be intertwined

Formats can limit which schemas they can hold. e.g., CSV has no well-designed way to express nesting. So changing formats sometimes means we have to change schemas (and vice versa).

Two lists of questions

Schema questions Format questions
What is one row? What separates the fields?
What are the columns? How is a missing value written?
What makes a row unique? Is it text or binary?
Where does colour live? Are types preserved, or is everything a string?
What happens if the mug is red and white? What is the encoding (utf8?)?

This course is almost entirely about the left hand side (schemas): we spend about one line of code on formats (csv, json) and the rest of the semester on understanding and changing schemas.

Image Credits

Images obtained via Creative Commons search.