The database used for this question is a very simple one with the following schema: (Primary keys are bold, foreign keys are underlined)
CUSTOMER (CustID, FirstName, LastName, City, Phone, Email)
INVOICE (InvoiceNumber, CustID, Date)
INVOICE_ITEM(InvoiceNumber, ItemNumber, Quantity)
ITEM (ItemNumber, ItemName, UnitPrice)
When a customer makes a purchase, an invoice is created. The invoice may be for many items. For example, in a single purchase, a customer might buy 10 Back Scratchers, 4 Hair Removers and a Dog Lead.
Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.
NOTE:

• You can use the symbols s, P, etc., or the words’ PROJECT’, ‘RESTRICT’ etc. as you prefer.
• You do not need to try to make efficient queries – just correct ones.
• Where you use a join, always show the join condition.

1. List the first and last names of Customers who come from the City named Perth.
2. List the first and last names of customers who had transactions on 1^st August 2020.
3. List the price of the item called “Back Scratcher”
4. List the first and last names of any customer who has purchased more than 10 “Back Scratchers” in a single transaction.
5. List the names and quantities of items purchased on 1^st August 2020 by the customer Peter Simpson.
6. List the dates on which Homer Griffin made purchases.
7. List the first and last names of customers who have bought “Back Scratcher” or “Hair Remover”
8. List the first and last names of customers who have bought “Back Scratcher” but have not bought “Hair Remover”
9. List the first and last names of customers who have bought “Back Scratcher” and “Hair Remover”
10. List the first and last names of any customers who have bought all of the items listed in the Item relation. (This does not need to be as part of a single purchase).

Question 2: SQL – SELECT queries (20 marks) This question is based on the View Ridge Gallery database you have been using in the labs. See the textbook for background to the case and the table structures.
The tables are:
ARTIST
CUSTOMER
WORK
TRANS
CUSTOMER_ARTIST_INT
You can use the dtoohey tables that we have been using. If you prefer, you can create your own copies of these tables under your own account to work with. If you do so, you should ensure you copy the same sample data and constraints as in the dtoohey tables.
Provide SQL AND result tables for the following queries. Paste the queries and the result tables from either your SSH client or SQL Developer into your assignment document. You can use a screen capture for the result tables, but NOT for the SQL.
Each question is worth 2 marks.

1. List the full details of any work of art, including the name of the artist who created the work, that have been described as Surrealist.
2. List the details of any work of art (including the name of the artist who created the work and the acquisition and asking price details) currently held in the gallery (i.e. works of art that have not been sold) with an asking price of > $400.
3. List the title of any work of art that has two (and only two) copies recorded in the database.
4. List the names of all deceased artists and the age they were when they died.
5. List the name of each artist represented in the database and the number of works of art by that artist, ordered from lowest to highest number.
6. List the work ID, title and artist name of all the works of art that sold for more than the average price of all sales, and the price they sold for.
7. List the total amount of sales of works of art by the gallery for each year, and the number of works sold that year.
8. Which artist has had the most works of art sold, and how many of the artist’s works have been sold?
9. Calculate the total profit made on all the works of art that have been sold (the profit/loss on a work of art is the difference between the acquisition price and the sales price)
10. List the name of any customers who have an interest in all artists.