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. 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

4. 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

5. 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

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

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

8. 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

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

10. 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

11. 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

12. 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

13. 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

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_status AS status, o.total_amount, 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_id 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 MENU_ITEM mi ON m.menu_id = mi.menu_id JOIN ITEM i ON mi.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_status AS status, o.total_amount, r.name AS restaurant_name FROM ORDERS o JOIN RESTAURANT r ON o.restaurant_id = r.restaurant_id WHERE o.order_id = 1;