Question

ANDS Bookstore is a bookstore that has been serving in Ankara Bookstore Bazaar for many years. A serious decrease has been observed in the sales of the bookstore especially in the last 5 years. When the bookstore owners investigated the reasons for this, it came to the conclusion that young readers, who make up the majority of their customers, now prefer to shop online. As a result, ANDS Bookstore decided to sell its books on the internet and created a shopping site for it. When the customer enters the website, s/he uses her/his username and password to log in to the system. If the incoming customer comes to the system for the first time, a new customer record is created by obtaining the necessary information. This information; name, surname, e-mail, username (created by the customer), password (created by the customer) date of birth, address, telephone, book types of interest. The book title, author, page number, type, price, publishing house, from which supplier the book was purchased and the amount of stock are kept. The book/s information that the customer wants to order is received and the customer order is recorded. A check is made on the stock status of the incoming order. For stock control, current book stock information comes and stock inquiry is done. As a result of the stock inquiry, if the ordered book/s are not in stock, purchases are made from the supplier. For this, product request information is sent to the supplier using the supplier information. After the sales information from the supplier, the new quantity and price information of the books are updated. If the ordered book/s are in stock, the order information and payment amount are calculated. The order is confirmed with the payment amount information and the payment information received from the customer. Payment information includes payment type (transfer / EFT, pay at the door, credit card, installments etc.) and prices. (The prices for each type of payment must be different.) Orders are prepared with this confirmation information and the cargo tracking number is also received from the contracted cargo company and sent to the customer. The information about the cargo company must include the company name, shipping fee (different prices should be given according to the cargo company), information about whether the cargo has been delivered and the date of delivery of the cargo if the cargo has been delivered. After the order is delivered to the customer, satisfaction information is received from the customer and the feedbacks are kept. ANDS Bookstore needs a database for following purposes: 1. Store information of customers, books, suppliers, cargo companies, payment and order. 2. Perform customer registration and track membership statuses. 3. The book information that the customer wants to order is received. Order is recorded according to the customer's username. 4. Inventory inquiry is made for the incoming order. 5. If the ordered book is out of stock, purchases are made from existing suppliers. 6. The prices and quantities of the books are updated along with the sales and quantity information from the supplier. 7. If the ordered book is available in stock, order information and payment amount are calculated. 8. The order is confirmed with the payment amount information and the payment information received from the customer. 9. With this confirmation information, orders are prepared and the cargo tracking number is also received from the contracted cargo company and sent to the customer. 10. All data should be entered manually with user forms. 11. Prepare queries for the following questions: (The results of the following queries should be presented in report format with a user interface). a) Display the total number of books ordered by customers b) List the name of books with stock quantity below 10 c) Create a status report showing whether cargoes have been delivered d) List orders with payment amounts over 100 liras with their customer names e) List the types of books types and how many books they contain f) List the names, surnames and emails of customers who have ordered 2 or more times. g) List the number of payment types preferred and the earnings from payments. h) List the percentage of books sold for each category in descending order

I. User interface design on paper. II. Use cases III. ER diagram IV. Tables (3NF) V. Queries

Answer 1

Qno - II)

From the above Question, Here are the Use Cases for the above Scenario

Use Case :

USE CASE 2:

Qno - I)

Here is the UI of the Above Scenario