Introduction to Relational Databases

Welcome to the Movie Database

The Digital Environment

Before writing code, you must understand a Relational Database. It is a system that organizes information into related tables, much like a collection of interconnected spreadsheets.

We will use a Movie Database throughout this course to master SQL.

Welcome to the world of data management! Before you can write your first line of code, you must understand the digital environment where data lives. A Relational Database is a system that organizes information into related tables, much like a collection of interconnected spreadsheets. In this course, we will explore the 'Movie Database' to learn these foundational concepts.

• A relational database organizes data into related tables.
• It functions like a collection of digital spreadsheets.

Tables: The Containers

What is a Table?

A Table is a container for a specific category of information. In our database, we have two primary tables:

• Movie: Details about films.
• Rating: Definitions for film ratings.

Think of a database as a library. A Table is like a single book in that library, containing one specific category of information. For example, we have a monospaced Movie table for film details. And a separate monospaced Rating table for content ratings. Keeping these separate ensures the database remains organized and efficient.

• Tables store specific categories of information.
• Separating data into tables keeps the database organized and efficient.

Columns: The Attributes

Defining Columns

Columns run vertically and define the type of information being stored. They are the properties or attributes of an object.

In the Movie table, columns include:

• Title
• Year
• Genre

Columns run vertically. Think of a column as a property of the object. In our Movie table, the monospaced Title column stores the name of the film. The monospaced Year column stores the four-digit release date. And the monospaced Genre column stores the category, like Sci-Fi or Drama.

• Columns run vertically.
• They define the attributes or properties of the data.

Rows: The Records

Identifying Rows

Rows run horizontally and represent a single, unique item or Record.

If the column is the 'category,' the row is the 'actual data'.

While columns are categories, Rows represent the actual data. Each horizontal row is a single, unique record. For example, this row represents the film 'The Matrix', released in 1999. It contains all the attributes defined by our columns in one single entry.

• Rows run horizontally.
• Each row represents one unique record (e.g., one specific movie).

The Database Schema

The Blueprint

A Schema is the blueprint of the database. It shows how tables are structured and how they connect.

The RatingCode in the Movie table connects it to the Rating table.

A Schema is the 'blueprint' of the database. It shows how tables are structured and how they connect. Here is our Movie table with its columns. And here is our Rating table. Notice the monospaced RatingCode in the Movie table? It acts as a bridge, connecting a specific movie to its full description in the Rating table.

• A schema is a structural blueprint.
• Tables connect through shared columns to create relationships.

Interactive Anatomy

Test Your Knowledge

Examine the Rating table snippet. Identify the structural components.

Let's practice. Look at this snippet from the monospaced Rating table. Click on the Column Header that holds the abbreviation of the rating. Not quite. Remember, columns are vertical headers, and rows are horizontal records. Try again. Excellent. 'Parental Guidance Suggested' is the value found in the Description column for the second record. Correct! The monospaced Code column holds the abbreviations like G and PG. Now, click on the Description value in the second row.

• Distinguish between columns and rows.
• Locate specific data values within a table structure.

The Analyst's Perspective

Scenario Analysis

Imagine you are a data analyst. A user asks for a list of all Sci-Fi movies from 1999.

Briefly describe which table you would use and which columns you would look at to find this information.

Imagine you are a data analyst at a streaming service. A user wants to find Sci-Fi movies from 1999. In the box below, explain which table you'd look at and which columns you'd use to filter the results.

• Identify the correct table for a query.
• Determine which columns are needed for filtering.

Common Pitfalls & Summary

Summary Checklist

• Identify Tables: What objects are stored?
• Scan Columns: What details do we have?
• Locate Connections: How do tables link?

Pitfall: Don't confuse Columns (Categories) with Rows (Records)!

To wrap up, remember this mental checklist when approaching any database. First, identify the Tables. Second, scan the Columns. And third, locate the Connections. A common pitfall is confusing rows and columns. Just remember: Columns are Categories—both start with 'C' and run vertical. You're now ready to start thinking about how to query this data!

• Columns = Categories (Vertical).
• Rows = Records (Horizontal).
• A database is the whole library; a table is just one book.