A relational database schema for an online bookstore built with MySQL. The database covers the complete customer journey — from browsing and adding books to a cart, through checkout, payment, and shipping.
The schema contains 9 tables:
| Table | Purpose |
|---|---|
| customer | Stores customer account details |
| admin | Stores staff/admin account details |
| book | Product catalog with pricing and stock info |
| shoppingcart | Tracks a customer's active cart |
| cartitem | Individual books inside a shopping cart |
| order | Completed purchases/checkout records |
| orderitem | Individual books within a completed order |
| payment | Payment method, amount, and status records |
| shipping | Delivery method, date, and status tracking |
- Full relational design with primary and foreign keys linking all tables
- Data validation constraints (e.g. price must be > 0, stock cannot go negative)
- Unique key enforcement on book ISBN and customer email
- Covers the complete order lifecycle: Cart → Order → Payment → Shipping
- Install MySQL (version 8.0 or higher recommended)
- Open MySQL Workbench or any MySQL client
- Run the script:
RealBooks.sql - The database and all tables will be created automatically
- Relational database design
- MySQL schema creation
- Primary keys, foreign keys, and constraints
- Data integrity enforcement
- Database export and documentation
- Built with MySQL 8.0.45
- One sample book record is included in the
booktable for reference - All other tables are structured and ready for data insertion