Application Design – Use Cases and Database Access Scenarios – SQL View

Actors

• Restaurant Owner: Users who owns a restaurant. They can do modifications on their restaurants menu’s, items, their prices. Management of their own restaurants
• Delivery man: Users who are responsible for delivering orders assigned to them. They are assigned by the delivery firm.
• Customer: User who has an overview of all the restaurants and their menus. Once signed in, they can order items and leave reviews.
• Super user: Owner of the application

Scenarios

1. Login
   • Actors: User (Restaurant Owner, Delivery Man, Customer)
   • Steps:
     1. User presses the login button
     2. Displays a login form
     3. User fills the necessary fields
     4. After successful log, the home page is displayed

2. Register for the Customer
   • Actors: Customer
   • Steps:
     1. The user clicks to the register button
     2. Displays a registration form,
     3. User fills the necessary fields
     4. After the successful registration, the home page is displayed

3. Register for the Delivery Man
   • Actors: Delivery Man
   • Steps:
     1. The credentials are provided by the delivery firm.
     2. The delivery man should log in with the given mail and password.

4. Register for the Restaurant Owner
   • Actors: Restaurant Owner
   • Steps:
     1. Clicks to the register button
     2. A registration page opens
     3. Clicks to the register as a restaurant button
     4. Instruction page opens with a mail section
     5. After uploading the necessary files, receives credentials
     6. Log in with the credentials
     7. After the successful registration, the home page is displayed

5. Browse the site
   • Actors: Customer
   • Steps:
     1. Clicks on the search bar
     2. Searches name of a restaurant or an item
     3. Applies extra filters if needed
     4. After clicking the enter, items and restaurants according to the search will appear

6. Place an order
   • Actors: Customer
   • Steps:
     1. Clicks on the desired item
     2. Clicks on the add to the basket button
     3. Selects the amount
     4. Clicks on the confirm button
     5. Clicks on the basket button
     6. Clicks on the place the order
     7. Enters the address or choose the address if he/she has ordered before
     8. Selects the payment method
     9. Makes the payment
     10. Enters notes if needed
     11. Clicks order

7. View my food orders
   • Actors: Customer
   • Steps:
     1. Clicks on my orders button
     2. A page with all past and active orders will appear
     3. On click of the order, details will show

8. Add Restaurant
   • Actors: Super user
   • Steps:
     1. Clicks on the notifications button
     2. Sees the request to add a new restaurant
     3. Checks the files
     4. Clicks on add a new restaurant
     5. A form appears
     6. Fills in the necessary information
     7. Clicks confirm

9. Edit Restaurant
   • Actors: Super user
   • Steps:
     1. Clicks on the restaurant he/she wants to edit
     2. Editing tab appears
     3. Changes the needed parts
     4. Clicks confirm

10. Delete Restaurant
    • Actors: Super user
    • Steps:
      1. Clicks on the restaurant he/she wants to delete
      2. Edit page opens
      3. Scrolls to the bottom
      4. Clicks delete restaurant button
      5. Confirms

11. Manage restaurant's menu (items)
    • Actors: Restaurant owner
    • Steps:
      1. Clicks on the edit menu button
      2. Changes the needed parts
      3. Clicks confirm

12. View menu of a restaurant
    • Actors: Customer
    • Steps:
      1. Clicks on a restaurant
      2. Restaurants page opens
      3. Scrolls down to the items

13. Manage profile
    • Actors: User (Customer, Delivery Man, Restaurant Owner)
    • Steps:
      1. Clicks on the profile icon
      2. A page with the profile settings opens
      3. Clicks to the settings he/she wants to change
      4. Clicks confirm

14. Receive orders
    • Actors: Customer, Restaurant Owner
    • Steps:
      1. Customer sets an order
      2. Restaurant owner receives the order
      3. Restaurant owner accepts the order

15. Comment
    • Actors: Customer
    • Steps:
      1. The order delivers
      2. Receives message asking for rating
      3. Selects the ratings
      4. Confirms

16. Update order status
    • Actors: User (Customer, Delivery Man, Restaurant Owner)
    • Steps:
      1. Restaurant owner receives an order
      2. Restaurant owner changes the status of the order to being prepared
      3. Restaurant owner changes the status of the order to being delivered
      4. Delivery man changes the status of the order to delivered

17. Track performance
    • Actors: Restaurant Owner
    • Steps:
      1. Clicks to the see performance
      2. Clicks on current performance
      3. Page with the information about the restaurants performance opens

18. Accept Delivery
    • Actors: Restaurant Owner, Delivery Man
    • Steps:
      1. Customer sets an order
      2. Delivery man gets request for the delivery
      3. Delivery man accepts the request
      4. Delivery man receives the navigation

19. Navigate to restaurant and customer
    • Actors: Delivery Man
    • Steps:
      1. Receives a delivery
      2. Clicks to the map
      3. Receives a map with the directions

20. Earnings and history
    • Actors: Restaurant Owner
    • Steps:
      1. Clicks on see performance
      2. Clicks on see history
      3. Selects the dates
      4. Information about the earnings and spendings of that time frame appears

21. Manage Users
    • Actors: Super user
    • Steps:
      1. Click to the users
      2. A page with all users appears
      3. Clicks on the user he/she wants to edit
      4. An edit page appears
      5. Does the needed changes
      6. Clicks confirm

22. Manage deliveries
    • Actors: Super user
    • Steps:
      1. Click to the deliveries
      2. A page with all deliveries appears
      3. Clicks on the delivery he/she wants to edit
      4. An edit page appears
      5. Does the needed changes
      6. Clicks confirm

Most important use cases

1. View My Food Orders

This SQL statement selects the order_id, order_date, status, and the total price from the order table together with the ordered items from the order_items table and their names from the item table. A join is made between order_items, item, and order. It filters only the orders of the user with user_id = 1 and displays them ordered by date (order_date) in descending order (newest first).

SELECT o.order_id, o.order_date, o.status, i.name AS item_name, oi.quantity, oi.total_price FROM "ORDERS" o JOIN ORDER_ITEMS oi ON o.order_id = oi.order_id JOIN ITEM i ON oi.item_id = i.item_id WHERE o.user_id = 1 ORDER BY o.order_date DESC;

2. Manage Restaurant's Menu

This SQL statement selects all items item_id, name, price and description of a given restaurant. A join is made between the menu table and the item table through item_id. It filters by restaurant_id = 2 to show only the menu for that restaurant.

SELECT i.item_id, i.name, i.price, i.description, i.image_url FROM MENU m JOIN ITEM i ON m.item_id = i.item_id WHERE m.restaurant_id = 2 ORDER BY i.name ASC;

3. Track Order Status

This SQL statement selects the order_id, order_date, status, and the restaurant name from which the order was placed. A join is made between the order, restaurant, and restaurant_owners tables. It filters for the order with order_id = 1.

SELECT o.order_id, o.order_date, o.status, r.name AS restaurant_name FROM "ORDERS" o JOIN RESTAURANT r ON o.address_id = r.address_id JOIN RESTAURANT_OWNERS ro ON r.restaurant_id = ro.restaurant_id WHERE o.order_id = 1;