caesarmario/database-bookstore-case-study

A case study about designing simple database system for a bookstore. This repository contains ERD design and SQL codes for design of bookstore database system. The main purpose of this system is to build database system for bookstore.

📚 Case Study - Database Bookstore 📚

using SQL 🔩

📃 Table of Contents:

• About Project
• Scenario
• Business Rules
• Normalization Process
• Data Dictionary
• Database Schema

🖋 About Project:

👉 A database built for e-bookstore case studies.
👉 The database built using SQL in Microsoft SQL Server 18.

🧾 Scenario:

The availability of books and reading material for purchase within the University is quite inadequate. Although the university library has vast collection of books (both hardcopy and e-books), the availability of it is quite limited and bound by many restrictions. Student and staffs only have the option of a small bookshop within the enterprise. Larger books store in the city are often sought for other varieties.

In view of the growing population, the university is planning to establish an e-bookstore. The online store will facilitate the purchase of latest books and material of many genres. Your team is assigned the project to design a database system for online University e-Bookstore System.

• Publishers of books frequently send lists of latest books and materials to the e-bookstore manager. The bookstore manager compiles a list of needed books and sends an order to the publishers. The publisher supplies the ordered books to the university. The bookstore manager records the details of a new book, along with the number of the books that have arrived at the bookstore. An invoice is sent to the accounts department to be processed and payment made.

• Customers, who wish to purchase books online, need to initially register as members. Members will be able to view the book, read reviews and compare the online products with other similar articles.

• Members who wish to purchase can select their books into the website’s shopping cart. The cart will show the summary of the selection and total cost to be paid. Once the payment is made, the customer will be able to print or save the receipts. The bookstore will send the books to the customers within 7 working days.

• The system should manage information about books in the bookstore, inventory, (registered) customers and books they have ordered. It should also store information about user opinions and book ratings.

• Users can also provide 'feedback' for a book, as a score (1-10 which is 0= terrible, 10= masterpiece) along with optional short text. No changes are allowed; only one feedback per user per book is allowed.

📋 ERD Design:

👉 Based on the scenario above, the ERD designs defined as follows here.

📑 Business Rules:

• A publisher can have only one invoice, invoice is made by publisher by the end of the month.
• An invoice can be associated with one or many orders, one order can only be associated with one invoice.
• A publisher can have one or many orders.
• An order can be associated with one or many order detail.
• An employee can manage one or many orders.
• A book can be associated with one or many order detail.
• A publisher can publish one or many books.
• A book can have only one category.
• A member can only give one feedback for each book.
• A book can have one or many feedbacks.
• A member can have one or many carts.
• A cart can be associated with one or many cart detail.
• A book can be associated with one or many cart detail.
• A cart has only one receipt.
• MemberID can only be associated to single member and there shall be no null.
  
  
  
  

⚙ Normalization Process:

▶ UNF

• Feedback Form
• Receipt
• Order Form
• Invoice Form

▶ 1NF

• 1NF - Part 1
• 1NF - Part 2

▶ 2NF

• 2NF - Part 1
• 2NF - Part 2
• 2NF - Part 3

▶ 3NF

• 3NF - Part 1
• 3NF - Part 2
• 3NF - Part 3
• 3NF - Part 4
  
  
  
  

📗 Data Dictionary:

👉 Based on normalization process, below are the screenshot of data dictionaries:

• Data Dictionary - Part 1
• Data Dictionary - Part 2
• Data Dictionary - Part 3
  
  
  
  

📝 Database Schema:

👉 Database schema generated by Microsoft SQL Server 18.

• Database Schema
  
  
  
  

🙌 Support me!

👉 If you find this project useful, please ⭐ this repository 😆!

---

👉 More about myself: here