乐趣区

关于算法: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

  1. Assessment SQL – Delivery
  2. Weighting 100%
  3. 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
  4. Deadline of submission

Hand in to Moodle by 15:00 on Friday 29 July 2022

  1. Arrangements for submission

See the hand out.

  1. Assessment Regulations
    All assessments are subject to the University
    Regulations.
  2. The requirements for the assessment
  3. Special instructions
  4. Return of work You will receive feedback via moodle.
  5. 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.

  1. 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
  1. 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
  2. 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%
order_id items total delivery total stored delivery charge stored total
89625 30.00 0 30.00 3.00 33.00
  1. Profile. How are orders distributed over the day and over the week?
  1. 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
  1. 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
  2. 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.
退出移动版