辅导SET08120、辅导SQL编程设计

日期：2022-07-14 08:25

1. Module number SET08120/SET08420
2. Module title Database Systems
3. Module leader Taoxin Peng
4.Tutor with responsibility for this Assessment
Student’s first point of contact

Taoxin Peng
5. Assessment SQL - Delivery
6. Weighting 100%
7. Size and/or time limits for assessment Section 1 – up to 60 points
Five (of 15) problems to be attempted.
1-5 attract a maximum of 6 points each
6-10 attract a maximum of 9 points each
11-15 attract a maximum of 12 points each
Section 2 – up to 25 points
Section 3 – up to 15 points
8. Deadline of submission

Hand in to Moodle by 15:00 on Friday 29 July 2022
9. Arrangements for submission

See the hand out.
10. Assessment Regulations
All assessments are subject to the University
Regulations.

11. The requirements for the assessment
12. Special instructions
13. Return of work You will receive feedback via moodle.
14. Assessment criteria

You will be assessed on the correctness, accuracy and
the quality of your answers.


Scenario
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:
Order value Delivery charge
< ￡10 ￡3
< ￡20 ￡2
< ￡30 ￡1
>= ￡30 Free

Deliverables & Submission:-

One zipped file (.zip or .rar or .7z) called set08120cw_, including:
1. Section 1: a PDF file, named _section1.pdf, containing
answers attempted, including SQL statements attempted and the output generated.
Note: SQL code should be text formatted using a fixed-width font. Output may be
in the form of a screen shot and must be legible.
2. Section 2: a PDF file, named _section2.pdf, containing
a. SQL statements (which must be compatible with MariaDB) to:
i. modify the database to include any additional tables and attributes
required
ii. insert sample data to tables added or altered
iii. query the database to retrieve the information needed to illustrate
the requirements.

b. outputs generated in the above section a. iii).
c. A proof of the modified design in the third normal form. A screenshot
from the spreadsheet used to do the normalisation is fine.
d. Your modified ER diagram.
3. Section 3: a PDF file, named _section3.pdf, containing
your report.

Section One - SELECT Statements (60 points)
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. 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. Just Pizza 2 Go. How many different items can be ordered from Just Pizza 2 Go
whose id is 406?
+----------+
| count(*) |
+----------+
| 10 |
+----------+

2. Popular Methodical. Which payment method has been used for more than 10000
times to pay for an order?
+------------------+----------+
| name | orders |
+------------------+----------+
| Maestro | 10928 |
| Mastercard | 11581 |
| PayPal | 15097 |
| Visa | 16419 |
+------------------+----------+

3. Moonlighters. How many restaurant employees also currently work as delivery
riders?
+--------------+
| moonlighters |
+--------------+
| 84 |
+--------------+

4. The Most Expensive. What dish is the most expensive?
+-------------------------------+
| name |
+-------------------------------+
| Cambo for 4 Person 102 Pieces |
+-------------------------------+

5. Typical Night In. On average, how many items are delivered per order to the
nearest whole number?
+-------+
| items |
+-------+
| 6 |
+-------+

6. Quiet Riders. Which three riders made the smallest number of deliveries?
+------------+-----------+------------+
| first_name | last_name | deliveries |
+------------+-----------+------------+
| Lucy | Walker | 5 |
| Marissa | Kaiser | 6 |
| Haley | Patrick | 6 |
+------------+-----------+------------+
7. Carbo-loading. Show the name and delivery menu for the restaurant where
everything costs more than ￡12.
+---------------------------------------+---------------------------------------+-------+
| restaurant | item | price |
+---------------------------------------+---------------------------------------+-------+
| Krispy Kreme - Edinburgh Lothian Road | 6 Assorted Doughnuts | 12.95 |
| Krispy Kreme - Edinburgh Lothian Road | Original Glazed Dozen | 14.95 |
| Krispy Kreme - Edinburgh Lothian Road | Original Glazed Double Dozen | 23.95 |
| Krispy Kreme - Edinburgh Lothian Road | Sharer Dozen | 17.95 |
| Krispy Kreme - Edinburgh Lothian Road | Original Glazed & Sharer Double Dozen | 24.95 |
| Krispy Kreme - Edinburgh Lothian Road | Sharer Double Dozen | 27.95 |
+---------------------------------------+---------------------------------------+-------+
8. Spread It Around. Which users with over 40 orders to their main address have
also made orders at least twice to other addresses?
+------------+-----------+------+-------+
| first_name | last_name | main | other |
+------------+-----------+------+-------+
| Eve | Leroy | 41 | 3 |
| Derek | Parsons | 41 | 3 |
| Gunner | Bentley | 42 | 2 |
| Jaylen | Leonard | 43 | 2 |
| Emma | Reynolds | 44 | 3 |
| Reilly | Ellison | 46 | 3 |
| Aniyah | Velasquez | 49 | 2 |
+------------+-----------+------+-------+



9. Not Worth It. Which food items have never been ordered?
+-------------------------------------------+----------------------------------------------+
| restaurant | item |
+-------------------------------------------+----------------------------------------------+
| Starbucks - Edinburgh - Waverley Steps | Cinnamon Swirl |
| Starbucks - Edinburgh - The Cube | Strawberry & Cream Cake |
| Starbucks - Edinburgh - The Cube | Rainbow Veg & Bean Protein Pot (plant based) |
| Starbucks - Edinburgh - The Cube | Hickory Ham & Cheese Toastie |
| STARBUCKS? - Edinburgh - Canongate | Pain au Chocolat |
| Starbucks - Edinburgh- Middle Meadow Walk | Oat & Raisin Cookie |
| Starbucks - Edinburgh- Middle Meadow Walk | Tomato & Mozzarella Panini |
| Starbucks - Edinburgh - Haymarket Station | Cheese and Marmite? Panini |
| Greggs - Edinburgh, Forrest Road | Porridge (GF) Original |
| Greggs - Edinburgh, Newington | Breakfast Omelette Baguette |
+-------------------------------------------+----------------------------------------------+

10. Must Do Better. What restaurants have less than 45% of orders that were
delivered on or before the estimated delivery time?

+--------------------------+---------+
| name | on_time |
+--------------------------+---------+
| 275 Cafe | 42.14% |
| BurgeRegan | 43.11% |
| Happy Rice | 43.98% |
| Los Cardos | 44.57% |
| The Chicken Crew | 44.44% |
| The Royal Cafe And Pizza | 42.02% |
| Yum Yum Kebab House | 44.44% |
| Yum Yum New | 44.66% |
+--------------------------+---------+

11. Oops. Find the order in the database which has the wrong delivery charge applied.
+----------+-------------+----------+-------+------------------------+--------------+
| order_id | items total | delivery | total | stored delivery charge | stored total |
+----------+-------------+----------+-------+------------------------+--------------+
| 89625 | 30.00 | 0 | 30.00 | 3.00 | 33.00 |
+----------+-------------+----------+-------+------------------------+--------------+

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.
+-----------+------+------+------+------+------+------+------+------+-------+-------+------+
| day | 1100 | 1200 | 1300 | 1400 | 1500 | 1600 | 1700 | 1800 | 1900 | 2000 | 2100 |
+-----------+------+------+------+------+------+------+------+------+-------+-------+------+
| Monday | 177 | 1455 | 1381 | 431 | 110 | 72 | 298 | 967 | 2291 | 1619 | 844 |
| Tuesday | 220 | 1779 | 1683 | 533 | 135 | 91 | 354 | 1142 | 2722 | 1946 | 1011 |
| Wednesday | 224 | 1798 | 1714 | 542 | 133 | 88 | 303 | 986 | 2356 | 1667 | 864 |
| Thursday | 163 | 1329 | 1268 | 401 | 102 | 67 | 305 | 999 | 2379 | 1700 | 877 |
| Friday | 176 | 1433 | 1348 | 426 | 108 | 71 | 387 | 1257 | 3002 | 2121 | 1099 |
| Saturday | 47 | 353 | 341 | 104 | 25 | 18 | 357 | 1179 | 2777 | 1981 | 1019 |
| Sunday | 24 | 186 | 174 | 61 | 15 | 9 | 328 | 1081 | 2568 | 1825 | 949 |
| All days | 1031 | 8333 | 7909 | 2498 | 628 | 416 | 2332 | 7611 | 18095 | 12859 | 6663 |
+-----------+------+------+------+------+------+------+------+------+-------+-------+------+


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.

+-----------+------------+-----------+--------+---------------------+
| post_code | first_name | last_name | orders | average daily spend |
+-----------+------------+-----------+--------+---------------------+
| EH3 9LT | Logan | Randall | 40 | 63.14 |
| EH3 9LT | Cali | Richard | 36 | 62.53 |
| EH3 9LT | Bailey | Davidson | 45 | 54.10 |
| EH3 9LT | Maleah | Chase | 43 | 53.75 |
| EH3 9LT | Marcus | Hopkins | 37 | 53.08 |
| EH3 9LT | Sylvia | Moon | 41 | 52.24 |
| EH3 9LT | Jabari | Barnes | 38 | 49.05 |
+-----------+------------+-----------+--------+---------------------+

14. Coincidental vegetarians. Who are the people who have placed entirely
vegetarian orders and who share the same name?
+------+------+------------+-----------+
| id1 | id2 | first_name | last_name |
+------+------+------------+-----------+
| 25 | 2530 | Bailey | Allen |
| 253 | 254 | Reina | Brooks |
| 1018 | 2532 | Alisa | Hester |
| 1219 | 2533 | Krish | Kendall |
| 1259 | 2534 | Vivianna | Kiser |
| 1459 | 1460 | Gracelyn | Mason |
| 1866 | 1867 | Kianna | Pham |
| 2188 | 2190 | Dianna | Smiley |
| 2463 | 2538 | Adison | Wilkerson |
+------+------+------------+-----------+

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 (exclusive, <40)?
+------+
| P |
+------+
| 0.52 |
+------+

Section 2 – Database Design (25 points)
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 displayed
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. (Hint: The provided percentage statistics is to give you an idea how the realistic data
will look like. Therefore, you should consider the given statistics while doing the
modifications because an improved and better design should minimise empty spaces in
database.)
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:
a. Alters any existing tables which have been changed in your new design
b. Creates tables which are added in your new design
c. 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:
a. Summary food quality, delivery and overall satisfaction ratings for an example
restaurant where ratings exist within the last four weeks
b. 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 (15 points)

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