Week 4 · Basic SQL

Week 4: Basic SQL

Physical Models, Schemas And Querying In MySQL

Converting Logical Models Into Physical Models

Guidelines For Converting ERDs To Schemas

If we have a weak entity that cannot be uniquely identified on its own and depends on the existence of a related strong entity, we create a composite primary key to identify that entity
If there is a 1-to-1 relationship between two entities, we add the foreign key in the table that fully depends on the other table which is the primary key of the other table. The example below shows that as a payment fully depends on an order being placed, a record cannot exist in payment without an order thus it is fully participating so we store the primary key of the order as a foreign key in the payment table:

1-To-1 Relationship Between Orders And Payments

If there is a 1-to-many relationship between two entities, we add the foreign key in the table on the 'many' side of the relation which is the primary key of the table on the '1' side of the relation. The example below shows that as one card issuer may be used in many payments e.g. Mastercard and VISA, while a payment is made using only one card issuer, we add the foreign key in the payments table which is the primary key of the card issuers' table:

1-To-Many Relationship Between Orders And Payments

If there is a many-to-many relationship between two entities, we cannot directly implement it as storing multiple values in a single attribute will violate atomicity. Hence, we create a new 'bridge' table which holds the primary key of both entities in the many-to-many relationship where the primary key of the new table is a composite key. The example below shows that as an order can have multiple items and an item may be in multiple orders, there is a many-to-many relationship between the entities so we created a 'bridge' entity called Items_Ordered which stores the primary key of both entities which gives it a composite primary key

Many-To-Many Relationship Between Orders And Payments

Practice Question

Basic Querying In MySQL

Lets move onto the practical element of COMP23111 and get started with querying our restaurant management database. So far, all the ERD snippets you have seen in this week and previous weeks are parts of the system and we will gradually expose you to more of it as we progress through the weeks.

Note: For the practice querying questions below, you will be able to view a sample solution query after 4 valid attempts without any syntax or semantic errors are made.
To begin with, using the schema above, find the details of the first seven orders that were made after 9th July 2023 whose payments had failed hence the status is 'Rejected' where the results are in ascending order based on their date:

Keeping customers happy is really important as it is a key determinant of sales and profit. The manager wants to find out the type of reviews customers are leaving when they give a rating of 3 or less on their orders. Retrieve the first 9 comments of the reviews which have a rating of at most 3 and fetch the results in descending order by the ID of the orders:

Note: The next couple of questions require the knowledge of built-in functions in MySQL to perform mathematical operations like SUM, COUNT and AVG.
For the last two months, sales have been at an all-time high for the restaurant. Find the number of orders placed in the months July 2023 and August 2023:

The manager wants to find out how much revenue the restaurant made during this period. Find the total amount of money earned in July 2023 and August 2023:

Finally, due to some external issues, several months have passed but some payments that were made for orders still haven't been received by the restaurant from the card issuers. The manager wants to contact the customers to resolve these payment issues so requires their details. Find the phone number and email addresses of the customers and the name of the card issuer they used for the payment for orders placed in September 2023 which still have a status of 'Pending':

Extension: The questions below require the knowledge of certain commands in MySQL to perform substring search
The manager believes that most of the low ratings are coming from their branch located on Oxford Road where their main customers are those living in the M14 postcode region. He wants to check if orders in this region have an average rating of below 5 and if this is true, this would have implications on the administration running that branch. Find the average rating of the orders placed by customers whose postal code begin with M14 to verify the hypothesis of the manager:

To increase sales in the their branch located in the M21 postcode region, the manager has decided to put a 30% discount on all orders for the whole of July 2023. However, he wants to backdate this offer to June 2023 as well so wants to send out coupons to the emails of customers who purchased from the restaurant in June which is worth 30% of the order they had placed. Find the email address, phone number, and calculate 30% of the amount the customers had paid given they live in the M20, M21 and M22 postcode region for orders placed in June 2023:

Laboratory 04 - Some Basic SQL

You will be practising your Basic SQL skills in the form of creating a Database, Inserting some data and running creating some queries.

Tasks 01 - Setting up Your Database:

Go to web.cs.manchester.ac.uk and log in with your University username (i.e. a12345bc) and password
Only the lower box “MySQL Database Account” is relevant. Choose a password, but this should NOT be your university password. Choose something different and remember it: you will be needing it again. Click “Create database account”
Again, only the “MySQL Database Account” is relevant. For $database host and $database name, your username will appear between the quotes.
Make a note of your credentials and then click on the like: dbhost.cs.man.ac.uk/phpMyAdmin and log in with your university username and the new password you have just created.
Here you can use the SQL Tab to paste in SQL Queries for creating and manipulating your databases

Tasks 02 - Using a Larger Database:

Northwind is a sample database distributed with MS Access. It comprises 150K of data. So not particularly large, but not tiny either.

Download the following two files to your laptop:
- northwind-schema.sql
- northwind-data.sql
- northwind schema

In phpMyAdmin, select your username in the lefthand menubar (immediately under “information schema”). Then go to the Import tab, and under “File to import”, select “Browse your computer” and choose your downloaded northwind-schema.sql. The click “Go”. After a short while a lot of MySQL confirmation messages come up, which you can ignore.

Now, select your username in the lefthand menubar again, and you should see all the Northwind tables have been created according to the schema in the file you submitted.

Open northwind-schema.sql in a text editor on your laptop and browse its contents.

Now, in phpMyAdmin, click on the names of some of the tables to see their structure, and see how that structure relates to the SQL commands that create it.

The tables are empty of data so let’s put some in.

Making sure your username in the lefthand menubar is selected, again go to the Import tab and this time select and upload northwind-data.sql. And “Go”.

Repeat the above to examine the data in the tables, and again look at the SQL code in the data file.

Now use the Designer tool to try and make some sense of the structure. Don’t worry if you can’t. But this should show you the difficulty of visualising non-trivial databases.
You now need to write a query to produce specific outcomes:

Write a query on the “customers” table to retrieve the names of all customers who are company owners in Seattle
Write a query on the “purchase orders” and “purchase order status” tables to retrieve the ids of all purchase orders which have not been approved.
Write a query on the “purchase orders”, “purchase order details” and “purchase order status” tables to retrieve the ids of all purchase orders which have been approved and which have a quantity of between 80 and 120, and order the result ascending by quantity

Lab 04 Solutions