Why functional dependencies guide schema design in DBMS Theory - See It in Action

Understanding Functional Dependencies in Schema Design

📖 Scenario: You are designing a simple database for a small bookstore. You want to organize the data so that it is easy to manage and avoids mistakes like duplicate or inconsistent information.

🎯 Goal: Build a small database table and use functional dependencies to understand how to organize the data properly.

📋 What You'll Learn

Create a table with columns for BookID, Title, Author, and Publisher

Define a functional dependency that BookID determines Title and Author

Add a configuration variable to represent the primary key

Write a query to check the uniqueness of BookID

💡 Why This Matters

🌍 Real World

Functional dependencies help organize data so that each piece of information is stored only once, avoiding mistakes and making updates easier.

💼 Career

Understanding functional dependencies is key for database design roles, ensuring data integrity and efficient storage in real-world applications.

1

Create the Books table

Create a table called Books with columns BookID (integer), Title (text), Author (text), and Publisher (text).

DBMS Theory

-- Your code here to create the Books table

Need a hint?

Use the CREATE TABLE statement with the specified columns and data types.

2

Define the primary key

Add a primary key constraint on the BookID column in the Books table to represent the functional dependency that BookID determines Title and Author.

DBMS Theory

CREATE TABLE Books (
    BookID INT,
    Title TEXT,
    Author TEXT,
    Publisher TEXT,
    -- Add primary key constraint on BookID
);

Need a hint?

Add PRIMARY KEY after the BookID INT declaration.

3

Insert sample data

Insert three rows into the Books table with these exact values: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Scribner'), (2, '1984', 'George Orwell', 'Secker & Warburg'), and (3, 'To Kill a Mockingbird', 'Harper Lee', 'J.B. Lippincott & Co.').

DBMS Theory

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT,
    Author TEXT,
    Publisher TEXT
);
-- Insert sample data here

Need a hint?

Use INSERT INTO Books (BookID, Title, Author, Publisher) VALUES (...), (...), (...); with the exact values.

4

Query to check uniqueness of BookID

Write a SQL query to select BookID and count how many times each BookID appears in the Books table, grouping by BookID. This helps verify the functional dependency that BookID is unique.

DBMS Theory

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT,
    Author TEXT,
    Publisher TEXT
);

INSERT INTO Books (BookID, Title, Author, Publisher) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Scribner'),
(2, '1984', 'George Orwell', 'Secker & Warburg'),
(3, 'To Kill a Mockingbird', 'Harper Lee', 'J.B. Lippincott & Co.');

-- Write query to count BookID occurrences

Need a hint?

Use SELECT BookID, COUNT(*) AS Count FROM Books GROUP BY BookID; to check uniqueness.