Understanding Keys and Relationships

The Foundation of Relationships

In a relational database, data is organized into tables. To make this data useful, we need a way to uniquely identify every record and connect different tables together. This is achieved through Keys.

Welcome to this lesson on database foundations. To manage data effectively, we use keys to organize and link information across different tables like <code>Movie</code> and <code>Rating</code>.

• Data is organized into rows and columns within tables.
• Keys are used to identify records uniquely.
• Keys allow different tables to connect to one another.

The Foundation of Connections

Why specialized tables?

In a relational database, we don't use one massive spreadsheet. Instead, we organize data into specialized tables like Movie and Rating.

To make these tables work together without repeating data, we use Keys—the glue that connects our data together.

Imagine a massive, messy spreadsheet where movie titles and ratings are repeated over and over. Relational databases solve this by splitting data into clean, specialized tables. Today, we will learn how 'Keys' allow these separate tables to talk to each other.

• Data is organized into specialized tables to avoid redundancy.
• Keys act as the 'glue' between different sets of data.

The Primary Key (PK)

A Primary Key is a column that uniquely identifies each row. Characteristics include:

• No two rows can have the same value.
• It can never be empty (NULL).

In our Movie table, the ID column is the Primary Key. In our Rating table, the Code column (like 'G' or 'PG') serves this purpose.

A Primary Key is like a fingerprint for a row. In the <code>Movie</code> table, the <code>ID</code> column ensures every film has a unique identifier. Similarly, the <code>Code</code> column in the <code>Rating</code> table uniquely identifies each rating type. Notice that as we select different rows, the Primary Key value is always distinct. This prevents any confusion between two different movies or ratings.

• Primary Keys must be unique.
• Primary Keys cannot contain NULL values.
• Movie.ID and Rating.Code are the Primary Keys in our schema.

The Primary Key (PK)

The Unique Identifier

A Primary Key is a column that uniquely identifies every single row. No two rows can share the same PK, and it can never be empty (NULL).

• Movie Table: The ID column is the PK.
• Rating Table: The Code column is the PK.

Look at the <code>Movie</code> table. Even if two movies have the same title, the <code>ID</code> column ensures every record is unique. Think of it like a unique barcode for every movie row. It can never be blank, and it can never be duplicated.

• A Primary Key must be unique for every row.
• A Primary Key cannot contain NULL values.
• In our schema, Movie.ID and Rating.Code are the Primary Keys.

The Foreign Key Bridge

Connecting the Tables

A Foreign Key is a column in one table that points to the Primary Key in another. It creates a bridge between them.

In our database, Movie.RatingCode links directly to Rating.Code.

Here is our visual map. Notice the <code>Movie</code> table has a column called <code>RatingCode</code>. This isn't just a label—it is a bridge that points directly to the <code>Code</code> column in the <code>Rating</code> table. This link allows us to find the full description of a rating without storing that text inside the Movie table itself.

• A Foreign Key references a Primary Key in another table.
• Movie.RatingCode acts as the bridge to the Rating table.

The Foreign Key (FK) Bridge

A Foreign Key is a column in one table that refers to the Primary Key in another table. It acts as a bridge.

In our database, the Movie table has a column called RatingCode. This stores values that correspond exactly to the Code column in the Rating table.

By storing just the code 'G' or 'PG' in the movie table, we avoid repeating long descriptions over and over. We just follow the bridge to find the details. The Foreign Key is the secret to relational databases. Look at how <code>Movie.RatingCode</code> acts as a bridge, pointing directly to the <code>Code</code> column in the <code>Rating</code> table.

• A Foreign Key 'points' to a Primary Key in another table.
• Movie.RatingCode is the Foreign Key.
• This relationship prevents data redundancy.

Defining Keys in SQL

SQL Syntax

We explicitly define these relationships when creating tables using the PRIMARY KEY and FOREIGN KEY keywords.

CREATE TABLE Movie (
  ID INT PRIMARY KEY,
  Title VARCHAR(100),
  RatingCode VARCHAR(10),
  FOREIGN KEY (RatingCode) 
  REFERENCES Rating(Code)
);

When writing SQL, we have to be very specific. We mark <code>ID</code> as the PRIMARY KEY. Then, we use the FOREIGN KEY clause to tell the database that <code>RatingCode</code> refers to the <code>Code</code> in the <code>Rating</code> table. This ensures the database maintains 'Data Integrity'—preventing us from entering a rating that doesn't exist.

• Use PRIMARY KEY to define a unique identifier.
• Use FOREIGN KEY (...) REFERENCES ... to create a link between tables.

Following the Link

Let's trace the relationship for the movie 'Toy Story'.

1. Find 'Toy Story' in the Movie table.
2. Identify its RatingCode ('G').
3. Find the matching Code in the Rating table.
4. Retrieve the Description ('General Audiences').

Now, we follow that 'G' over to the <code>Rating</code> table. There it is! The description is 'General Audiences'. Let's practice the logic a database uses. First, we find 'Toy Story' in our movie list. We see its <code>RatingCode</code> is 'G'.

• Data retrieval follows the Foreign Key path.
• The bridge allows us to fetch related data from other tables.

Interactive Exercise: Find the Link

The Interstellar Challenge

You want to find the Description for the movie 'Interstellar'. Based on our schema, which columns must you 'match' to connect the two tables?

Let's test your understanding. You have the movie 'Interstellar' in your <code>Movie</code> table. To get its rating description, you need to use a bridge. Talk to your tutor to identify which columns create that bridge.

• Identifying common columns for data retrieval.
• Applying key concepts to a specific query scenario.

Query Logic Diagnosis

Explain why we use a Foreign Key (like RatingCode) instead of just typing the full description into every row of the Movie table.

Before we move on to writing SQL code, think about the design. Why bother with two tables and a bridge? Type your explanation below.

• Data redundancy
• Data integrity
• Efficiency