Project activity 3: Data Analysis with SQL

Project activity 3: Data Analysis with SQL

Online Bookstore Inventory Management System

Project Description:

The Online Bookstore Inventory Management System is a simplified project that focuses on managing the inventory of an online bookstore. The system allows users to add new books, update book information, and view the current inventory status. It utilizes a database to store book details and track the available quantity.

Database Schema:

Table: books

Columns:

– book_id (integer, primary key)

– title (text)

– author (text)

– price (decimal)

– quantity (integer)

Project Tasks:

1. Create the database: Set up a local database using a DBMS of your choice (e.g., MySQL, PostgreSQL, SQLite) and create the “books” table based on the provided schema.

2. Add books: Implement a functionality to add new books to the database. Prompt the user to enter the book title, author, price, and quantity. Insert a new record into the “books” table with the provided information.

3. Update book information: Allow users to update the information of existing books. Prompt the user to enter the book ID of the book to be updated and provide options to modify the title, author, price, or quantity. Update the corresponding record in the “books” table based on the user’s input.

4. View inventory: Implement a query to display the current inventory status. Retrieve all books from the “books” table and display their titles, authors, prices, and available quantities.

5. Generate reports (optional): Create SQL queries to generate reports, such as the total number of books in inventory or the average price of the books.

6. Additional features (optional):

   – Implement a feature to search for books based on the title or author using SQL queries.

   – Allow users to delete books from the database using SQL queries.

   – Enhance the user interface by adding a simple command-line menu for easy interaction.

Remember to test the system functionality by executing SQL queries against the database and verifying the expected results. The project can be extended further by incorporating more advanced features or integrating it with a user interface, depending on your learning objectives and requirements.

Note: As mentioned earlier, the actual setup and hosting of the database depend on your chosen DBMS and hosting environment. You can use a local development environment or an online database hosting service for this project.