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

Understanding Third Normal Form (3NF) in Databases

📖 Scenario: You are designing a simple database for a small bookstore. The database should store information about books, authors, and publishers. To keep the data organized and avoid redundancy, you want to apply the Third Normal Form (3NF) rules.

🎯 Goal: Build a step-by-step example that shows how to organize a bookstore database table into Third Normal Form (3NF) by creating tables and defining relationships.

📋 What You'll Learn

Create an initial table with book details including author and publisher information

Add a configuration step to identify functional dependencies

Apply the core logic of decomposing the table into multiple tables to achieve 3NF

Complete the design by defining primary keys and foreign keys for the tables

💡 Why This Matters

🌍 Real World

Database designers use 3NF to organize data efficiently, avoid duplication, and maintain consistency in applications like bookstores, libraries, and inventory systems.

💼 Career

Understanding 3NF is essential for roles like database administrators, data analysts, and software developers to design scalable and maintainable databases.

1

Create the initial bookstore table

Create a table called Bookstore with columns BookID, BookTitle, AuthorName, AuthorEmail, PublisherName, and PublisherAddress.

DBMS Theory

# Create the Bookstore table with the specified columns
# Your code here

Need a hint?

Define a table with all columns in one place to show initial data structure before normalization.

2

Identify functional dependencies

Create a comment that lists the functional dependencies: BookID determines BookTitle, AuthorName, and PublisherName. Also, AuthorName determines AuthorEmail, and PublisherName determines PublisherAddress.

DBMS Theory

CREATE TABLE Bookstore (
  BookID INT,
  BookTitle VARCHAR(100),
  AuthorName VARCHAR(100),
  AuthorEmail VARCHAR(100),
  PublisherName VARCHAR(100),
  PublisherAddress VARCHAR(200)
);
-- List functional dependencies here
# Your code here

Need a hint?

Write the functional dependencies as comments to understand which columns depend on others.

3

Decompose the table to achieve 3NF

Create three tables: Books with BookID, BookTitle, AuthorName, and PublisherName; Authors with AuthorName and AuthorEmail; and Publishers with PublisherName and PublisherAddress.

DBMS Theory

-- Create Books table
# Your code here

Need a hint?

Split the original table into three tables so that each non-key attribute depends only on the key.

4

Define primary keys and foreign keys

Add foreign key constraints: in Books, set AuthorName as a foreign key referencing Authors(AuthorName) and PublisherName as a foreign key referencing Publishers(PublisherName).

DBMS Theory

ALTER TABLE Books
-- Add foreign key constraints here
# Your code here

Need a hint?

Use FOREIGN KEY clauses inside the Books table to link to Authors and Publishers.