Question 1 - Relational Algebra
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 for the following:
a. List the first and last names of Customers who come from the City named Perth.
b. List the first and last names of customers who had transactions on 1st August 2012.
c. List the price of the item called "Back Scratcher"
d. List the first and last names of any customer who has purchased more than 10 "Back Scratchers" in a single transaction.
e. List the names and quantities of items purchased on 1st August 2012 by the customer Peter Griffin.
f. List the dates on which Peter Griffin made purchases.
g. List the first and last names of customers who have bought "Back Scratcher" or "Hair Remover"
h. List the first and last names of customers who have bought "Back Scratcher" but have not bought "Hair Remover"
i. List the first and last names of customers who have bought "Back Scratcher" and "Hair Remover"
j. List the first and last names of any customers who have bought all of the items. (This does not need to be as part of a single purchase).
Question 2 - SQL (DML)
Provide SQL queries and the result tables for the following:
Please ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer.
Each query is worth 2 marks. These tables exist in sphinx and are owned by the user dtoohey. You may, if you wish, create your own copies of the tables under your own account. If you do so, you should ensure that you copy the sample data in dtoohey's tables.
These queries are based on the View Ridge Gallery database you have been using in the Lab sessions. Please see Chapters 6 and 7 of Kroenke for background to the case and table structures.
Marks are allocated not only for correct answers, but also for best practice in the creation of the queries.
a. List the details of any works of art (including the Artist who created the work) that have more than one copy recorded in the database.
b. List the details of any work of art (including the Artist who created the work) that has an Expressionist style.
c. List the details of the works of art (including 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).
d. List the sales for each customer (i.e., when a customer purchases a work of art from the Gallery, a transaction line is created. For a purchase, there will be values in the DateSold and SalesPrice columns). The query should include the details of the customer, the transaction and the work of art purchased.
e. List the names of the deceased artists and the number of years of age they were when they died (for example, an artist born in 1950 and deceased in 2001 has an age of 51).
f. The sum of the acquisition price of works of art for each year (for example, if there were two works of art purchased for $1500 and $1000 in 2007, and one work of art purchased for $500 in 2008, then the sums would be $2500 and $500, for 2007 and 2008 respectively).
g. Calculate the profit made on works of art that have been sold (i.e., the profit/loss on an individual work of art is the difference between the acquisition price and the sales price).
h. Which artist has had the most works of art sold, and how many of the artist's works have been sold?
i. Sales of which artist's works have resulted in the highest average profit (i.e., the average of the profits made on each sale of works by an artist), and what is that amount?
j. Customer name of any customers who have an interest in ALL artists.
Question 3 - SQL (DDL)
a. Provide ALL of the SQL statements required to insert the details of the following: A customer, John Smith, of 47 Moodle Street, Highwater, WA, 6709, Australia (email: [email protected]) has sold a work of art called "Gorillas in the Mist" by the renowned French artist, Gallic Symbol (b. 1973) to the Gallery (i.e., the Gallery purchased it from him). It is a unique Watercolour on Paper and is 45 * 30cm signed by the Artist. The purchase price was $4500 and the transaction took place on 27th July 2013.
b. You have been given the following specifications of a simple database for a netball association that keeps record of players, teams and matches.
Give the SQL to create the Match table. You may assume that the Player and Team tables have already been created, and that the MatchID, PlayerID and TeamID colums are of the data type VARCHAR2(5). The result of the match refers to the result for the HomeTeam and can be only W, L or D (win, lose or draw).
c. Your match table must also include a column that records the scores of both teams in the match. Provide the SQL to amend the original table design to allow for this change in requirement. It is most unlikely (impossible) that a team would ever score more than 999 goals in a
match.
Question 4 - Relational Database Design
The following question is based upon the Patient-Treatment relation which records the details of transactions occurring in a medical surgery. You may assume the data are representative.
You have been asked to design a relational database for this system. You know that there are problems with the current design and that it will need to be modified in order to work effectively.
You need to write a 1-2 page report that addresses the following:
1. What are the specific problems associated with the current design and why do they arise?
2. How would you change the current design and how does your new design address the problems you have identified with the current design.
In order to receive high marks for this question, you will need to demonstrate an understanding of the theories discussed in Topics 1, 2 and 3, how they apply to this problem, and justify the changes you are making to the system. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question.
Question 5 - Conceptual Design
You have been asked to develop a data model for the Drip Drip Water Company (DDWC). DDWC is the sole supplier of water to the citizens and businesses of the town of Drip Drip.
Each of DDWC's customers is classified as being either residential or non-residential. Each customer may have a water meter that measures the water consumed by the customer at a particular service address. Customers may have more than one service address.
Water meters must be replaced when they have been in use for 5 years. We can assume that a meter will never be damaged or become unserviceable and will not be re-assigned to another service address.