Why understanding relationships matters in SQL - See It in Action

Understanding Relationships in a Library Database

📖 Scenario: You are helping a local library organize its database. The library keeps track of books and authors. Each book can have one author, and each author can write many books. Understanding how these two tables relate helps the library find information quickly.

🎯 Goal: Build a simple database structure with two tables, Authors and Books, and create a relationship between them using a foreign key. This will help you see how data in one table connects to data in another.

📋 What You'll Learn

Create a table called Authors with columns AuthorID (integer, primary key) and Name (text).

Create a table called Books with columns BookID (integer, primary key), Title (text), and AuthorID (integer).

Add a foreign key constraint on Books.AuthorID referencing Authors.AuthorID.

Insert sample data into both tables to demonstrate the relationship.

💡 Why This Matters

🌍 Real World

Libraries, stores, and many businesses use relationships in databases to keep data organized and connected.

💼 Career

Database designers and developers must understand relationships to build efficient and reliable data systems.

1

Create the Authors table

Write SQL code to create a table called Authors with two columns: AuthorID as an integer primary key, and Name as text.

SQL

-- Your code here to create the Authors table

Need a hint?

Use CREATE TABLE statement with AuthorID as the primary key.

2

Create the Books table with AuthorID column

Write SQL code to create a table called Books with three columns: BookID as an integer primary key, Title as text, and AuthorID as an integer.

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);
-- Your code here to create the Books table

Need a hint?

Remember to include AuthorID as an integer column to link to authors.

3

Add foreign key constraint to Books table

Modify the Books table creation SQL to add a foreign key constraint on AuthorID that references Authors.AuthorID.

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    AuthorID INTEGER,
    -- Your code here to add foreign key constraint
);

Need a hint?

Use FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) inside the Books table definition.

4

Insert sample data into Authors and Books

Insert these exact rows into the tables: into Authors, insert (1, 'Jane Austen') and (2, 'Mark Twain'). Into Books, insert (101, 'Pride and Prejudice', 1) and (102, 'Adventures of Huckleberry Finn', 2).

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    AuthorID INTEGER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);
-- Your code here to insert sample data

Need a hint?

Use INSERT INTO statements with the exact values given.