Database design best practices in SQL - Mini Project: Build & Apply

Database Design Best Practices

📖 Scenario: You are creating a simple database for a small bookstore. The bookstore needs to store information about books, authors, and sales. To keep the data organized and easy to use, you will design the database using best practices.

🎯 Goal: Build a well-structured database with tables for Authors, Books, and Sales. Use proper keys and relationships to ensure data is organized and easy to manage.

📋 What You'll Learn

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

Create a Books table with columns BookID (primary key), Title, AuthorID (foreign key), and Price.

Create a Sales table with columns SaleID (primary key), BookID (foreign key), SaleDate, and Quantity.

Use appropriate data types and constraints to ensure data integrity.

Establish foreign key relationships between tables to link authors to books and books to sales.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use databases to keep track of products, customers, and sales. Designing the database well helps them find information quickly and avoid mistakes.

💼 Career

Database design skills are essential for roles like database administrators, backend developers, and data analysts who work with data storage and retrieval.

1

Create the Authors table

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

SQL

-- Write the CREATE TABLE statement for Authors here

Need a hint?

Use CREATE TABLE Authors and define AuthorID as the primary key.

2

Create the Books table with foreign key

Write a SQL statement to create a table called Books with these columns: BookID as an integer primary key, Title as text, AuthorID as an integer foreign key referencing Authors(AuthorID), and Price as a decimal number.

SQL

CREATE TABLE Authors (
    AuthorID INT PRIMARY KEY,
    Name TEXT,
    Country TEXT
);
-- Write the CREATE TABLE statement for Books here

Need a hint?

Remember to add a foreign key constraint on AuthorID referencing Authors(AuthorID).

3

Create the Sales table with foreign key

Write a SQL statement to create a table called Sales with these columns: SaleID as an integer primary key, BookID as an integer foreign key referencing Books(BookID), SaleDate as a date, and Quantity as an integer.

SQL

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

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT,
    AuthorID INT,
    Price DECIMAL(10, 2),
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);
-- Write the CREATE TABLE statement for Sales here

Need a hint?

Use a foreign key on BookID referencing Books(BookID) to link sales to books.

4

Add NOT NULL constraints and improve data integrity

Modify the Authors, Books, and Sales tables to add NOT NULL constraints on all columns except foreign keys, and ensure Price in Books cannot be negative by adding a CHECK constraint.

SQL

-- Modify the tables to add NOT NULL constraints and a CHECK on Price
-- Your code here

Need a hint?

Add NOT NULL to columns that must always have data and a CHECK constraint on Price to prevent negative values.