Relational database concepts in MySQL - Mini Project: Build & Apply

Building a Simple Relational Database with MySQL

📖 Scenario: You are creating a small database for a bookstore. The store wants to keep track of books and their authors. Each book has a title and belongs to one author. Each author has a name and a country.

🎯 Goal: Build two related tables: authors and books. Link them using a foreign key so you can find which author wrote each book.

📋 What You'll Learn

Create a table called authors with columns author_id (integer, primary key), name (text), and country (text).

Create a table called books with columns book_id (integer, primary key), title (text), and author_id (integer).

Set author_id in books as a foreign key referencing author_id in authors.

Insert sample data into both tables.

Write a query to list all books with their author's name.

💡 Why This Matters

🌍 Real World

Bookstores and libraries use relational databases to organize books and authors so they can quickly find information and manage inventory.

💼 Career

Understanding how to create tables, define relationships, and query data is essential for database administrators, backend developers, and data analysts.

1

Create the authors table

Write a SQL statement to create a table called authors with columns: author_id as an integer primary key, name as VARCHAR(100), and country as VARCHAR(100).

MySQL

-- Your code here to create the authors table

Need a hint?

Use CREATE TABLE authors and define the columns with their types. Remember to set author_id as the primary key.

2

Create the books table with a foreign key

Write a SQL statement to create a table called books with columns: book_id as an integer primary key, title as VARCHAR(200), and author_id as an integer. Add a foreign key constraint on author_id referencing author_id in the authors table.

MySQL

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(100)
);
-- Your code here to create the books table with foreign key

Need a hint?

Use FOREIGN KEY (author_id) REFERENCES authors(author_id) to link the tables.

3

Insert sample data into authors and books

Insert these rows into authors: (1, 'Jane Austen', 'UK'), (2, 'Mark Twain', 'USA'). Insert these rows into books: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2).

MySQL

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(100)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
-- Your code here to insert data into authors and books

Need a hint?

Use INSERT INTO authors and INSERT INTO books with the exact values given.

4

Query books with their authors' names

Write a SQL query to select the title of each book and the name of its author by joining the books and authors tables on author_id.

MySQL

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(100)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

INSERT INTO authors (author_id, name, country) VALUES
(1, 'Jane Austen', 'UK'),
(2, 'Mark Twain', 'USA');

INSERT INTO books (book_id, title, author_id) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Adventures of Huckleberry Finn', 2);
-- Your code here to select book titles with author names

Need a hint?

Use JOIN to combine books and authors on author_id.