Foodie.com, a food delivery startup, has successfully built a local following in Edinburgh. The next stages of development involve the enhancement of the current technical offering, and plans for future expansion into other locations. The database provided contains information from a typical month of operation. To fully understand the database structure, you will need to know some details about the way the company operates, and some of the terminology used:
Customers place orders through a mobile app
Foodie.com processes the customer’s payment and passes it on to the restaurants minus a commission
Restaurants are responsible for updating the status of an order in progress
Restaurants are responsible for recording which employee prepared the order
Deliveries are made by cyclists who are known as riders in the system
Each customer has a main address, but may also make orders for other addresses
Any user can place a food order
Some users are also employed by a restaurant
Some users are also riders
The delivery charge depends on the value of the order:
Section One - SELECT Statements
The Delivery database is available in Moodle as an SQL schema maintenance file. The ER diagram appears at the end of this document, and is also provided in both png and drawio format.
Choose any five questions to answer according to your ability. Questions 1 – 5 are worth 6 marks each, 6 – 10 are worth 9 marks, and 11 – 15 are worth 12 marks.
Each solution is a single SQL statement, which must be compatible with MariaDB in STRICT MODE (e.g. the MariaDB setup on the coursework server). It is strongly recommended that your SQL statements are tested on the coursework server before your submission (updated on 06/10/21). Hard-coded values should be avoided except when the value is included in the question. The target answer has been provided for each question. The output from your SQL statement should reproduce the results provided, including formatting and column order. Row ordering should also be respected when asked for in the question.
1. Dine Thyme. How many different items can be ordered from Dine Thyme whose id is 342?
2. Methodical. How many times has each payment method been used to pay for an order?
3. Moonlighters. How many restaurant employees also currently work as delivery riders?
4. The price is right. What dish is more expensive than Grilled Tamarind Duck Breast but less expensive than Gnocchi alla Sorrentina?
5. Typical night in. On average, how many items are delivered per order to the nearest whole number?
6. Busy riders. Which three riders made the largest number of deliveries?
7. Carbo-loading. Show the name and delivery menu for the restaurant where everything costs more than £10.
8. Spread it around. Which users with over 40 orders to their main address have also made orders to other addresses?
9. Not worth it. Which food items have never been ordered?
10. Must do better. What percentage of orders from Chennai’s Marina were delivered on or before the estimated delivery time?
11. Oops. Find the order in the database which has the wrong delivery charge applied.
12. Profile. How are orders distributed over the day and over the week? Count all orders starting on the hour and before the start of the next hour.
13. Feed me. One post code is responsible for more orders than any other. Show the customers from that post code, the number of orders they have each made and their average daily spend to the nearest penny.
14. Coincidental vegetarians. Who are the people who have placed entirely vegetarian orders and who share the same name?
15. What are the chances? If I order a delivery between 1830 and 1930 on a Friday, what is the probability it will arrive within 40 minutes?
Section 2 – Database Design
You are required to extend the delivery system to include customer ratings and reviews. The requirements are as follows (22 points):
Three categories of ratings are required: food quality, delivery and overall satisfaction
Rating values range from 0 to 5
The customer may leave up to three review comments, one for each category
The customer must provide a rating before being allowed to leave a review for the same category
The customer may provide a rating but no review
The customer may choose to not rating at all
Summary ratings are displayed for a restaurant. They are the average of those received during the past four weeks
A delivery rider has a personal rating based on the last 10 days of delivery ratings
Average ratings are shown to one decimal place
If a restaurant or rider rating is not available within the appropriate time, the message, ‘No rating available’, is display
In addition, the following requirements are required (3 points):
When deciding on your database structure, you should consider that 42% of orders receive no ratings or reviews. A further 28% receive all three ratings, and 30% receive a rating for overall satisfaction only. When a rating is provided, a review is also provided in about 50% of cases.
You are required to submit:
1. An updated ER diagram which includes any new tables/attributes to support these additional features. Your diagram should be embedded into a pdf document.
2. A proof of the modified design in the third normal form
3. An SQL schema maintenance script which:
Alters any existing tables which have been changed in your new design
Creates tables which are added in your new design
Inserts sample data into tables altered and added
4. An SQL script which performs the following queries to retrieve the information needed to demonstrate the correct operation of the new features:
Summary food quality, delivery and overall satisfaction ratings for an example restaurant where ratings exist within the last four weeks
Summary overall satisfaction rating and placeholder message for the other categories where ratings exist for an example restaurant, but only the overall satisfaction ratings are within the last four weeks c. Summary rating for an example rider where data exists within 10 days
Your schema maintenance script should include only the data required for the example queries. You may re-use data from the original schema maintenance script, but you should remove any data that is not required.
Section 3 – Database Security
Provide and discuss a set of recommendations that application developers should follow to prevent unauthorised access to data when using this database in an application (max. 1000 words)
If you need help in any other database like Oracle, SQL Server, MongoDB, PostgreSQL or need solution of above problem then we are ready to help you.
Send your request at realcode4you@gmail.com and get instant help with an affordable price.
We are always focus to delivered unique or without plagiarism code which is written by our highly educated professional which provide well structured code within your given time frame.
If you are looking other programming language help like C, C++, Java, Python, PHP, Asp.Net, NodeJs, ReactJs, etc. with the different types of databases like MySQL, MongoDB, SQL Server, Oracle, etc. then also contact us.