关于算法:SET08120-数据库原理分析

SET08120

1. Module number SET08120/SET08420
2. Module title Database Systems

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?

12. Profile. How are orders distributed over the day and over the week?

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