Third Normal Form (3NF) in SQL - Mini Project: Build & Apply

Organizing a Library Database in Third Normal Form (3NF)

📖 Scenario: You are helping a small library organize its book records. The library currently stores all information in one big table, which makes it hard to update and causes repeated data. You will break this big table into smaller, well-structured tables following the Third Normal Form (3NF) rules.

🎯 Goal: Create three separate tables: Books, Authors, and Publishers. Each table should have the right columns so that data is not repeated and each fact is stored only once. This will make the library database easier to maintain and update.

📋 What You'll Learn

Create a table called Books with columns BookID, Title, AuthorID, and PublisherID.

Create a table called Authors with columns AuthorID and AuthorName.

Create a table called Publishers with columns PublisherID and PublisherName.

Use AuthorID and PublisherID as foreign keys in the Books table to link to the other tables.

Ensure each table has a primary key to uniquely identify each record.

💡 Why This Matters

🌍 Real World

Libraries, bookstores, and many businesses use normalized databases to keep their data clean and easy to update.

💼 Career

Database designers and developers must understand normalization to build efficient and reliable databases.

1

Create the initial Books table

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

SQL

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

Need a hint?

Use CREATE TABLE Books and define each column with its type. Mark BookID as the primary key.

2

Create the Authors table

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

SQL

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title TEXT,
  AuthorID INT,
  PublisherID INT
);
-- Create the Authors table with AuthorID and AuthorName
-- Your code here

Need a hint?

Define Authors with AuthorID as primary key and AuthorName as text.

3

Create the Publishers table

Write a SQL statement to create a table called Publishers with columns PublisherID as an integer primary key and PublisherName as text.

SQL

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title TEXT,
  AuthorID INT,
  PublisherID INT
);

CREATE TABLE Authors (
  AuthorID INT PRIMARY KEY,
  AuthorName TEXT
);
-- Create the Publishers table with PublisherID and PublisherName
-- Your code here

Need a hint?

Define Publishers with PublisherID as primary key and PublisherName as text.

4

Add foreign key constraints to Books table

Modify the Books table to add foreign key constraints on AuthorID referencing Authors(AuthorID) and on PublisherID referencing Publishers(PublisherID).

SQL

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title TEXT,
  AuthorID INT,
  PublisherID INT,
  -- Add foreign key constraints here
);

CREATE TABLE Authors (
  AuthorID INT PRIMARY KEY,
  AuthorName TEXT
);

CREATE TABLE Publishers (
  PublisherID INT PRIMARY KEY,
  PublisherName TEXT
);

Need a hint?

Use FOREIGN KEY clauses inside the Books table to link AuthorID and PublisherID to their tables.