A comprehensive SQL database project for managing a bookstore, including inventory, sales, and customer data.
Table of Contents
This project is a SQL database designed for a bookstore management system named BookHaven with complete ERD diagrams. It contains various tables that store information about books, customers, orders, items, suppliers, and payments. The project uses MySQL as the database server.
SQL: The primary language used for designing and managing the database structure and performing data operations.
DBMS (Database Management System): Utilized to manage and interact with the SQL database, ensuring data integrity and efficient querying.
ERD (Entity-Relationship Diagram): Created to visually represent the data model and relationships between entities within the bookstore database.
The database bookhaven consists of the following tables:
- book: Stores information about books such as item code, ISBN, title, genre, category, author, publisher, and year of publication.
- customer: Stores customer details like customer ID, name, NIC, contact number, email address, and postal address.
- item: Contains information on items such as item code, price, and stock level.
- order_: Stores order details including order ID, quantity, order date, delivery date, destination address, payment ID, and customer ID.
- order_item: Maps the relationship between orders and items.
- payment: Records payment information such as payment ID, amount, date, time, and payment type ID.
- payment_type: Defines different types of payment methods.
- stationary: Stores details about stationary items.
- stock: Represents the stock information for items and their suppliers.
- supplier: Stores supplier details such as supplier ID and name.
To set up the database locally:
-
Clone the Repository:
git clone https://github.com/yourusername/bookhaven-sql-database.git cd bookhaven-sql-database -
Import the SQL Dump: Open phpMyAdmin or any
MySQL/MariaDBclient. Create a new database named bookhaven. Import thebookhaven.sqlfile included in this repository. -
Run SQL Scripts: Ensure that all tables are created and data is loaded by executing the SQL commands in the
bookhaven.sqlfile.
This database can be used to manage the inventory, customer orders, and payment processing of a bookstore. To interact with the database, you can use SQL queries to retrieve, insert, update, or delete data as required.
- Retrieve all books in the store:
SELECT * FROM book;
- Get details of a specific customer:
SELECT * FROM customer WHERE cust_id = '4001';
- Check stock levels of items:
SELECT * FROM item WHERE stock_level > 0;
The SQL dump included in this repository contains initial data for testing and development purposes. It is not intended for use in a production environment.
Contributions are welcome! If you have ideas for new features or bug fixes, please fork the repository and submit a pull request. Here’s how:
- Fork the Project
- Create your Feature Branch (
git checkout -b feature/AmazingFeature) - Commit your Changes (
git commit -m 'Add some AmazingFeature') - Push to the Branch (
git push origin feature/AmazingFeature) - Open a Pull Request
This project is licensed under the MIT License. See the LICENSE.txt for more details. This means you can freely use, modify, and distribute the code, but please include the license notice in any copies or substantial portions of the software
Have questions, suggestions, or just want to chat about the project? Reach out to me!
Name: Himan Withana
Email: [email protected]
Special thanks to the following resources and tools that have been invaluable in the creation and development of this project: