Case Study: Online Bookstore

Let’s say we want to create a database for an online bookstore that sells books to customers. We want to keep track of the books in our inventory, the customers who buy our books, and the orders that they place.

Create Schema

First, we need to create a schema for our database. We’ll call it online_bookstore.

				
					CREATE SCHEMA online_bookstore;

Create Tables

Next, we’ll create the tables that we need for our database.

Books Table

The books table will store information about the books in our inventory.

				
					CREATE TABLE books (
  book_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

Customers Table

The customers table will store information about our customers.

				
					CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

Orders Table

The orders table will store information about the orders that our customers place.

				
					CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  book_id INT NOT NULL,
  quantity INT NOT NULL,
  total_price DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Insert Entries

Now, let’s insert some sample data into our tables.

Books Table 

				
					INSERT INTO books (title, author, price) VALUES
  ('To Kill a Mockingbird', 'Harper Lee', 15.99),
  ('The Great Gatsby', 'F. Scott Fitzgerald', 12.99),
  ('1984', 'George Orwell', 10.99),
  ('Pride and Prejudice', 'Jane Austen', 9.99);

Customers Table 

				
					INSERT INTO customers (first_name, last_name, email) VALUES
  ('John', 'Doe', 'johndoe@example.com'),
  ('Jane', 'Doe', 'janedoe@example.com'),
  ('Bob', 'Smith', 'bobsmith@example.com');

Orders Table

				
					INSERT INTO orders (customer_id, book_id, quantity, total_price) VALUES
  (1, 1, 2, 31.98),
  (1, 2, 1, 12.99),
  (2, 3, 3, 32.97),
  (3, 4, 1, 9.99),
  (3, 2, 2, 25.98);

Reviews Table

The reviews table will store information about the reviews provided by customers after reading different books.

				
					CREATE TABLE reviews (
  review_id INT AUTO_INCREMENT PRIMARY KEY,
  book_id INT NOT NULL,
  rating INT NOT NULL,
  comment TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (book_id) REFERENCES books(book_id)
);

INSERT INTO reviews (book_id, rating, comment)
VALUES (1, 4, 'This book was great!'),
       (2, 3, 'I enjoyed this book.'),
       (3, 5, 'This is one of my all-time favorites.'),
       (1, 5, 'A must-read for any book lover.'),
       (2, 2, 'I was disappointed with this book.');

Start Practice

Easy
Medium
Hard