Visualizing Relational Database Schemas

Relational databases are powerful tools for organizing and linking data. They are called "relational" because they establish connections between different tables using relationships. In this lesson, you’ll explore how relationships are formed and why foreign keys play a critical role in these connections.

What You’ll Learn

By the end of this lesson, you will understand:

• The purpose and function of foreign keys.
• How relationships are established between tables.
• The types of relationships in relational databases.

The "Relation" in Relational Databases

Relationships between tables are fundamental to relational databases. Let’s consider an example:

• You have a people table that stores information about individuals.
• You also have a cats table that tracks cats owned by people.

Since ANSI SQL doesn’t support "object" value types, we create separate tables for each entity and link them using keys.

Primary Keys

A primary key is a column that uniquely identifies each record in a table. For example:

people table:

ssn           first_name   last_name
123-45-6789   John         Doe
987-65-4329   Jane         Doe
987-65-4320   John         Smith
    

Here, the ssn column acts as the primary key because each value uniquely identifies a person.

Foreign Keys

A foreign key is a column in one table that references the primary key of another table, creating a link between the two tables. For example:

cats table:

owner_ssn     id   name
123-45-6789   1    Callie
123-45-6789   2    Charley
987-65-4320   3    Jaxson
    

In this table, owner_ssn is a foreign key referencing the ssn column in the people table. This relationship indicates which person owns which cat.

Types of Relationships

1. One-to-Many

A one-to-many relationship occurs when one record in Table A is associated with multiple records in Table B. Using the people and cats tables:

• John (SSN 123-45-6789) owns Callie and Charley.
• Jaxson (SSN 987-65-4320) is owned by John Smith.

2. Many-to-Many

A many-to-many relationship occurs when records in Table A are associated with multiple records in Table B and vice versa. For example:

• Each cat can have multiple fur colors.
• Each fur color can belong to multiple cats.

cats table:

owner_ssn     id   name
123-45-6789   1    Callie
123-45-6789   2    Charley
987-65-4320   3    Jaxson
    

cats_fur join table:

cat_id   fur_id
1        1
1        2
2        2
2        3
3        1
    

fur table:

id   name
1    brown
2    white
3    grey
    

Using these tables, you can determine:

• Callie (Cat ID 1) is brown and white.
• Charley (Cat ID 2) is white and grey.
• Jaxson (Cat ID 3) is brown.

Steps to Designing a Database

When designing relational database schemas, follow these steps:

1. Identify Tables: Decide which tables are needed based on the entities in your system.
2. Define Columns: Specify the attributes (columns) for each table.
3. Define Data Types: Assign appropriate data types to each column (e.g., INTEGER, TEXT).
4. Establish Relationships: Use primary and foreign keys to link related tables.
5. Include Join Tables: Add join tables to handle many-to-many relationships as needed.

SQL Example

Here’s how to create the schema for the people, cats, and cats_fur tables:

CREATE TABLE people (
  ssn TEXT PRIMARY KEY,
  first_name TEXT,
  last_name TEXT
);

CREATE TABLE cats (
  id INTEGER PRIMARY KEY,
  name TEXT,
  owner_ssn TEXT,
  FOREIGN KEY (owner_ssn) REFERENCES people(ssn)
);

CREATE TABLE fur (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE cats_fur (
  cat_id INTEGER,
  fur_id INTEGER,
  FOREIGN KEY (cat_id) REFERENCES cats(id),
  FOREIGN KEY (fur_id) REFERENCES fur(id)
);
    

What You Learned

In this lesson, you learned:

• What makes a database relational: Relationships created through primary and foreign keys.
• Types of relationships: One-to-many and many-to-many.
• The role of foreign keys: How they link tables and maintain data integrity.
• Steps to designing a database schema: From identifying tables to defining relationships.

Real-World Applications

Relational database design is used in:

• Customer management: Connecting customers to orders and transactions.
• Content platforms: Linking users, posts, and comments.
• Inventory systems: Associating products, categories, and suppliers.

With this knowledge, you’re ready to design efficient and relational databases for real-world systems!