Description:
Food donation is a database application for the homeless. This is an application which helps homeless people to get food from nearby restaurants. Restaurants that volunteer to donate the food that goes into waste every day to those in need register themselves on this platform and donate the food.
Functionalities: The basic functionalities of our application are -
- The users and restaurants can register themselves.
- Login into the application
- Search for a restaurant
- Place an order.
- Update the food quantity every time when a user places an order.
- Show the popular restaurants around the user (trigger)
- Generate a monthly report of the restaurants with number of orders placed from them.(stored procedure)
Attributes:
- We have implemented three tables:
- Restaurant table, customer table and the order table.
- The restaurant table keeps track of all restaurants, each of which is identified by a unique identifier: rID.
- The customer table keeps track of all registered users, with attributes such as email, phone number and password, which are essential for authentication and login.
- The order table keeps track of all orders placed by customers in every restaurant he/she has visited.
Restaurant table:
rID: unique identifier of the restaurant
rLatitude: latitude of the restaurant
rLongitude: longitude of the restaurant
rPhone: phone number used to contact the restaurant
rCuisine: Food type: Indian/Mexican/Italian, etc.
Cuisine_qty: the amount of servings of food left in the restaurant
Food property: Gluten free/ peanut allergy/ dairy free
rRating: rating of the restaurant, from 0-5
rAddress: zip code of the restaurant
Orders table:
order_id: unique id for the orders placed
order_date: Date of the orders placed
Order_quantity: no. of orders placed
Customer table:
Cust_id: primary key, unique identification for customer
Cust_name: customer name
Cust_email: customer email
Cust_phone: customer phone number, used to contact customer
Schema:
Entity Relation Diagram:
Data:
Customers table:
Orders table:
Restaurant table:
Backend:
We use Azure SQL server as the database to hold the data. We have used Azure VM (4vCPU, 16G RAM) as the backend server and deploy our backend script there. We also have made a REST API that accepts user requests and could directly interact with the Azure database.
Functional dependencies:
-
Restaurant table:
- rID → rName, rCuisine, rPhone, rAddress, rRating, cuisine_qty, rAddress
-
Orders table:
- Order_id → order_date, order_quantity, cust_id, order_cuisine, rID
-
Customers table:
- Cust_id → cust_name, cust_email, cust_phone, credential
Functionality description
Homeless people can-
- Register: Adds new entry to the customer table, email, phone, credential
- Login: verify their name and credential
- Search for nearby restaurants based on ratings and location.
- Place order: add a new entry to the order table, update cuisine_qty in restaurant table.
Other functionalities-
- Restaurants can register themselves.
- Generate a monthly report to know the restaurant names and number of orders placed that month
SQL Queries:
Table creation:
Insertion queries
Stored procedure
Frontend
We use Reactive Native and build a mobile App. When user first open the App, he/she would be prompt to Registration or Log In. After successfully authenticating him/herself, A map with nearby restaurants would be displayed. Users can also switch to a list view to view restaurants in a different fashion. In the List view page, use can also check out store sales report. After the user clicks an item and enters the restaurant detail page, Store details would be presented, and he/she can place an order for this restaurant.
API Reference
http://fooddonationdb.westus2.cloudapp.azure.com:5000/signup
Function for user signup. Body parameters:
- cust_name: full name of the user
- cust_email: email for user registration
- cust_phone: phone number of the user
- credential: password for user login
Sample Query:
{
"order_quantity": 20,
"cust_id": "cs003",
"rID": "keyvgz56hizj7jd8djotfmvlh4it73uyaibiu2o68q00lcvyojy58k9hytre2vsy"
}
Sample Response:
{
"success": true
}
http://fooddonationdb.westus2.cloudapp.azure.com:5000/login
Function for login. Body parameters:
- email: user email for login
- password: user password for authentication
Sample query:
{
"email": "[email protected]",
"password": "12345678"
}
Sample response:
{
"cus_id": "12kqtw6hy64dobkk0ofnv21y223fe0z667uaqw0czfy3u7ym2z8lilwsy1pw6dg9",
"cus_name": "ada lovelace",
"success": true
}
http://fooddonationdb.westus2.cloudapp.azure.com:5000/placeOrderWithTrigger
Function for placing a order at a restaurant:
- order_quantity: the amount of food to be consumed
- cust_id: customer ID number
- rID: restaurant ID number
Sample query:
{
"order_quantity": 20,
"cust_id": "cs003",
"rID": "keyvgz56hizj7jd8djotfmvlh4it73uyaibiu2o68q00lcvyojy58k9hytre2vsy"
}
Sample response:
{
"success": true
}
http://fooddonationdb.westus2.cloudapp.azure.com:5000/searchMostPopularRestaurants
Function for getting a list of "popular" restaurants nearby:
- latitude: user latitude
- longitude: user longitude
- radius: search radius
Sample query:
{
"latitude": 47.6248771,
"longitude": -122.3258786,
"radius": 1000
}
Sample response:
[
{
"cuisine": "Mexican",
"distance": 608,
"id": "9wvo61nj4m28cojww4fbvewbl3g2k0vsgusyduu9lbfjvxcm8yop2tgofses3vjl",
"lat": 47.6215392,
"lng": -122.3323116,
"name": "R9",
"phone": "2424905651",
"popularity": 4.0784,
"quantity": 540,
"rating": "4.2"
},
{
"cuisine": "Greek",
"distance": 0,
"id": "49s25nvxlksftgmh5a89jo2bf2trgurxur5exgd8rh9e0vjtn0s2xnonktekqskn",
"lat": 47.6248771,
"lng": -122.3258786,
"name": "R10",
"phone": "6107972792",
"popularity": 4.0,
"quantity": 550,
"rating": "4.0"
},
{
"cuisine": "Indian",
"distance": 742,
"id": "1ow7277jgn13w5i9u4howyj5qjjmmypewojtv8pwvjrjx902v03m8zs182wewhhb",
"lat": 47.6239122,
"lng": -122.3356781,
"name": "R1",
"phone": "5211198870",
"popularity": 3.8516,
"quantity": 525,
"rating": "4.0"
},
{
"cuisine": "Japanese",
"distance": 882,
"id": "r76uzsfmijohthjcevrcqwcjh1ydsj5x0caa9l279gjzfcq4h79xmj32iapvljmt",
"lat": 47.6218226,
"lng": -122.3367534,
"name": "R8",
"phone": "1121682299",
"popularity": 3.8236,
"quantity": 537,
"rating": "4.0"
},
{
"cuisine": "Italian",
"distance": 921,
"id": "keyvgz56hizj7jd8djotfmvlh4it73uyaibiu2o68q00lcvyojy58k9hytre2vsy",
"lat": 47.6226383,
"lng": -122.3377189,
"name": "R2",
"phone": "4557682231",
"popularity": 3.3158,
"quantity": 478,
"rating": "3.5"
},
{
"cuisine": "British",
"distance": 802,
"id": "yjmg2si4e7mfe7mm1q516moaksi9kir7ks27sdtds8j7ix1qfe006n2kxf6a4em4",
"lat": 47.6236507,
"lng": -122.3364272,
"name": "R3",
"phone": "8722127616",
"popularity": 2.8396,
"quantity": 570,
"rating": "3.0"
}
]
http://fooddonationdb.westus2.cloudapp.azure.com:5000/searchRestaurantByLatLng
Function for getting a list of nearby restaurants:
- latitude: user latitude
- longitude: user longitude
- radius: search radius
Sample query:
{
"latitude": 47.6248771,
"longitude": -122.3258786,
"radius": 1000
}
Sample response:
[
{
"cuisine": "Greek",
"distance": 0,
"id": "49s25nvxlksftgmh5a89jo2bf2trgurxur5exgd8rh9e0vjtn0s2xnonktekqskn",
"lat": 47.6248771,
"lng": -122.3258786,
"name": "R10",
"phone": "6107972792",
"quantity": 550,
"rating": "4.0"
},
{
"cuisine": "Mexican",
"distance": 608,
"id": "9wvo61nj4m28cojww4fbvewbl3g2k0vsgusyduu9lbfjvxcm8yop2tgofses3vjl",
"lat": 47.6215392,
"lng": -122.3323116,
"name": "R9",
"phone": "2424905651",
"quantity": 540,
"rating": "4.2"
},
{
"cuisine": "Indian",
"distance": 742,
"id": "1ow7277jgn13w5i9u4howyj5qjjmmypewojtv8pwvjrjx902v03m8zs182wewhhb",
"lat": 47.6239122,
"lng": -122.3356781,
"name": "R1",
"phone": "5211198870",
"quantity": 525,
"rating": "4.0"
},
{
"cuisine": "British",
"distance": 802,
"id": "yjmg2si4e7mfe7mm1q516moaksi9kir7ks27sdtds8j7ix1qfe006n2kxf6a4em4",
"lat": 47.6236507,
"lng": -122.3364272,
"name": "R3",
"phone": "8722127616",
"quantity": 570,
"rating": "3.0"
},
{
"cuisine": "Japanese",
"distance": 882,
"id": "r76uzsfmijohthjcevrcqwcjh1ydsj5x0caa9l279gjzfcq4h79xmj32iapvljmt",
"lat": 47.6218226,
"lng": -122.3367534,
"name": "R8",
"phone": "1121682299",
"quantity": 537,
"rating": "4.0"
},
{
"cuisine": "Italian",
"distance": 921,
"id": "keyvgz56hizj7jd8djotfmvlh4it73uyaibiu2o68q00lcvyojy58k9hytre2vsy",
"lat": 47.6226383,
"lng": -122.3377189,
"name": "R2",
"phone": "4557682231",
"quantity": 478,
"rating": "3.5"
}
]
http://fooddonationdb.westus2.cloudapp.azure.com:5000/getOrderNumbers
Function for getting a list of restaurants with non-zero orders in a given month and year
- month: query month
- year: query year
Sample query:
{
"month":2,
"year": 2021
}
Sample response:
{
"results": [
{
"name": "R1",
"order": 10
},
{
"name": "R7",
"order": 5
},
{
"name": "R9",
"order": 4
},
{
"name": "R2",
"order": 3
},
{
"name": "R10",
"order": 2
},
{
"name": "R4",
"order": 2
},
{
"name": "R6",
"order": 2
},
{
"name": "R8",
"order": 1
},
{
"name": "R3",
"order": 1
}
],
"success": true
}