ER diagram to table mapping in SQL - Mini Project: Build & Apply

ER Diagram to Table Mapping

📖 Scenario: You are designing a simple database for a library. The library keeps track of books and authors. Each book has a title and a publication year. Each author has a name and a country. A book can have one author, and an author can write many books.

🎯 Goal: Create SQL tables that represent the entities and relationships from the ER diagram of the library system.

📋 What You'll Learn

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

Create a table called Books with columns BookID (primary key), Title, PublicationYear, and AuthorID (foreign key referencing Authors.AuthorID).

Use appropriate data types for each column.

Set AuthorID in Books as a foreign key to enforce the relationship.

💡 Why This Matters

🌍 Real World

Database designers often convert ER diagrams into SQL tables to build real databases for applications like libraries, stores, or social networks.

💼 Career

Understanding how to map ER diagrams to tables is essential for roles like database developer, data analyst, and backend engineer.

1

Create the Authors table

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

SQL

-- Create the Authors table with AuthorID, Name, and Country
-- Your code here

Need a hint?

Use CREATE TABLE Authors and define AuthorID as INTEGER PRIMARY KEY.

2

Create the Books table

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

SQL

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

-- Create the Books table with BookID, Title, PublicationYear, and AuthorID
-- Your code here

Need a hint?

Define BookID as INTEGER PRIMARY KEY and include AuthorID as an integer column.

3

Add foreign key constraint

Modify the Books table creation to add a foreign key constraint on AuthorID referencing Authors(AuthorID).

SQL

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

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

Need a hint?

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

4

Complete the table definitions

Ensure both Authors and Books tables are created with all columns and the foreign key constraint as specified.

SQL

-- Verify the complete table creation statements are present
-- Your code here

Need a hint?

Check that both tables have all columns and the foreign key constraint.