Start where you are

Below is some transactional data for a fictional DVD rental store.

The spreadsheet table is the universal language of business data. Everyone has an intuitive understanding of what information is about when presented in a two dimensional table with rows and columns. The spreadsheet is the digital representation of the ledger or ruled notebook, which existed far before computers were invented. Many organizations still run entirely on spreadsheets. However, the kind of analytics that your organization likely needs will require accessing data that is in more complex formats. Fortunately, every single type of (structured) data for business purposes has some form of representation of how that data looks, and that is referred to as a data model.

Data Models

A data model is like a blueprint—it shows how data is organized, stored, and related, much like how a floor plan shows the layout of a building. For managers, understanding data models is critical because they reveal where the data lives and how it connects, enabling you to ask better questions and drive data-driven success. In this lab, we’ll use the DVD Rental Database—a simple example of a business tracking DVD rentals—to explore these concepts.

The goal of this lab is to equip you with a basic idea of what the data you’ll need to work with probably looks like. We’ll use the DVD Rental Database as an illustrative example. Your tasks are as follows:

1. Familiarize yourself with the TABLES in the data model.
2. Try to figure out how the tables are CONNECTED to each other.

An extract of the entire data model is presented below. Don’t worry, you’ll get the full data model shortly—but first, let’s build your intuition step-by-step.

customer (customer_id, first_name, last_name, email, address_id)
   |
   |----> address (address_id, address, city, postal_code)
   |
   |----> rental (rental_id, rental_date, customer_id, film_id, return_date)
            |                   |
            |----> film (film_id, title, description, rental_rate)
            |
            |----> payment (payment_id, customer_id, rental_id, amount, payment_date)

• Lines (|---->) show foreign key relationships (e.g., customer_id in rental points to customer).
• Each table is a building block; connections let you assemble the full picture.

Step 1: Familiarize Yourself with the TABLES in the Data Model

Think of tables as individual spreadsheets, each holding a specific type of information. In the DVD Rental Database, the data model consists of several tables that together describe the business of renting DVDs. Here are some key tables from the data model above:

• customer: Stores details about customers.
  • Columns: customer_id (primary key or unique identifier), first_name, last_name, email, address_id.
  • Example Row: 1, John, Doe, [email protected], 5.
• film: Contains information about the DVDs available for rent.
  • Columns: film_id (primary key), title, description, release_year, rental_rate.
  • Example Row: 1, Star Wars, Sci-fi adventure, 1977, 4.99.
• rental: Tracks rental transactions.
  • Columns: rental_id (primary key), rental_date, customer_id, film_id, return_date.
  • Example Row: 100, 2023-01-01, 1, 1, 2023-01-05.
• payment: Records payments for rentals.
  • Columns: payment_id (primary key), customer_id, rental_id, amount, payment_date.
  • Example Row: 50, 1, 100, 4.99, 2023-01-01.
• address: Holds location details for customers.
  • Columns: address_id (primary key), address, city, postal_code.
  • Example Row: 5, 123 Main St, Springfield, 62701.