Table of Contents
- Business Description (Database Requirements)
- A List of Entities
- A List of Relationships
- ER Diagrams
- Schemas
- Normalization
- Table Creation and Data Entry (Using SQL Statements)
- Sample Run (Create at Least 4 Queries)
- Project Summary
- Presentation
1. Business Description (Database Requirements)
The project involves creating a database for a small online bookstore. The bookstore sells various books and allows customers to browse, purchase, and review books. The database must support the following requirements:
- Customer Management: Store customer information, including name, email, and address.
- Book Inventory: Maintain a catalog of books, including title, author, genre, price, and stock quantity.
- Order Processing: Track customer orders, including order date, total amount, and order status.
- Reviews: Allow customers to leave reviews for books, including rating and comments.
- Shopping Cart: Enable customers to add books to a shopping cart before finalizing their purchase.
2. A List of Entities
-
Customer
- Attributes: CustomerID (PK), Name, Email, Address, Phone
-
Book
- Attributes: BookID (PK), Title, Author, Genre, Price, StockQuantity
-
Order
- Attributes: OrderID (PK), CustomerID (FK), OrderDate, TotalAmount, OrderStatus
-
OrderItem
- Attributes: OrderItemID (PK), OrderID (FK), BookID (FK), Quantity, Price
-
Review
- Attributes: ReviewID (PK), BookID (FK), CustomerID (FK), Rating, Comment, ReviewDate
-
ShoppingCart
- Attributes: CartID (PK), CustomerID (FK), CreatedDate
-
CartItem
- Attributes: CartItemID (PK), CartID (FK), BookID (FK), Quantity
3. A List of Relationships
- Customer - Order: One-to-Many (One customer can have multiple orders)
- Order - OrderItem: One-to-Many (One order can contain multiple order items)
- Book - OrderItem: One-to-Many (One book can be part of multiple order items)
- Customer - Review: One-to-Many (One customer can leave multiple reviews)
- Book - Review: One-to-Many (One book can have multiple reviews)
- Customer - ShoppingCart: One-to-One (One customer has one shopping cart)
- ShoppingCart - CartItem: One-to-Many (One shopping cart can have multiple cart items)
- Book - CartItem: One-to-Many (One book can be part of multiple cart items)
4. ER Diagrams
5. Schemas
Customer
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE,
Address VARCHAR(255),
Phone VARCHAR(15)
);
Book
CREATE TABLE Book (
BookID INT PRIMARY KEY,
Title VARCHAR(255),
Author VARCHAR(100),
Genre VARCHAR(50),
Price DECIMAL(10, 2),
StockQuantity INT
);
Order
CREATE TABLE Order (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATETIME,
TotalAmount DECIMAL(10, 2),
OrderStatus VARCHAR(50),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
OrderItem
CREATE TABLE OrderItem (
OrderItemID INT PRIMARY KEY,
OrderID INT,
BookID INT,
Quantity INT,
Price DECIMAL(10, 2),
FOREIGN KEY (OrderID) REFERENCES Order(OrderID),
FOREIGN KEY (BookID) REFERENCES Book(BookID)
);
Review
CREATE TABLE Review (
ReviewID INT PRIMARY KEY,
BookID INT,
CustomerID INT,
Rating INT CHECK (Rating BETWEEN 1 AND 5),
Comment TEXT,
ReviewDate DATETIME,
FOREIGN KEY (BookID) REFERENCES Book(BookID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
ShoppingCart
CREATE TABLE ShoppingCart (
CartID INT PRIMARY KEY,
CustomerID INT,
CreatedDate DATETIME,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);
CartItem
CREATE TABLE CartItem (
CartItemID INT PRIMARY KEY,
CartID INT,
BookID INT,
Quantity INT,
FOREIGN KEY (CartID) REFERENCES ShoppingCart(CartID),
FOREIGN KEY (BookID) REFERENCES Book(BookID)
);
6. Normalization
The database is normalized to the third normal form (3NF):
- 1NF: All tables have primary keys, and all attributes are atomic.
- 2NF: All non-key attributes are fully functionally dependent on the primary key.
- 3NF: There are no transitive dependencies; all attributes are dependent only on the primary key.
7. Table Creation and Data Entry (Using SQL Statements)
-- Create tables
CREATE TABLE Customer (...);
CREATE TABLE Book (...);
CREATE TABLE Order (...);
CREATE TABLE OrderItem (...);
CREATE TABLE Review (...);
CREATE TABLE ShoppingCart (...);
CREATE TABLE CartItem (...);
-- Insert sample data
INSERT INTO Customer (CustomerID, Name, Email, Address, Phone) VALUES (1, 'John Doe', '[email protected]', '123 Elm St', '555-1234');
INSERT INTO Book (BookID, Title, Author, Genre, Price, StockQuantity) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 10.99, 5);
INSERT INTO Order (OrderID, CustomerID, OrderDate, TotalAmount, OrderStatus) VALUES (1, 1, NOW(), 10.99, 'Completed');
INSERT INTO OrderItem (OrderItemID, OrderID, BookID, Quantity, Price) VALUES (1, 1, 1, 1, 10.99);
INSERT INTO Review (ReviewID, BookID, CustomerID, Rating, Comment, ReviewDate) VALUES (1, 1, 1, 5, 'Amazing book!', NOW());
INSERT INTO ShoppingCart (CartID, CustomerID, CreatedDate) VALUES (1, 1, NOW());
INSERT INTO CartItem (CartItemID, CartID, BookID, Quantity) VALUES (1, 1, 1, 2);
8. Sample Run (Create at Least 4 Queries)
-
Query to find all books in stock:
SELECT * FROM Book WHERE StockQuantity > 0;
-
Query to find all orders for a specific customer:
SELECT * FROM Order WHERE CustomerID = 1;
-
Query to get all reviews for a specific book:
SELECT * FROM Review WHERE BookID = 1;
-
Query to calculate total sales for a specific book:
SELECT SUM(Price * Quantity) AS TotalSales
FROM OrderItem
WHERE BookID = 1;
9. Project Summary
This project involved designing a database for a small online bookstore. The database supports customer management, book inventory, order processing, reviews, and shopping cart functionalities. The design was normalized to ensure data integrity and efficiency. SQL statements were used to create tables and insert sample data, and various queries were executed to demonstrate the database's capabilities.
10. Presentation
The presentation will include slides covering the following topics:
- Introduction to the Online Bookstore
- Database Requirements
- Entity-Relationship Model
- Database Schema
- Normalization Process
- SQL Table Creation and Data Entry
- Sample Queries and Results
- Conclusion and Future Enhancements
This outline provides a comprehensive framework for developing a conceptual and relational model for the online bookstore database. Each section can be expanded with more details as needed.