ICT218 Databases September Trimester, 2012 Assignment 1 DATE DUE: Sunday 7th October 2012. Some important points worth noting: – Students should submit their assignment using LMS (unless alternative arrangements have been made) BEFORE 11:30pm (Perth time) on the due date. – THE FILE YOU SUBMIT MUST BE NAMED USING THE FORM: StudentNumber.doc or other appropriate file extension. You must include a completed assignment cover page. You must keep a copy of your assignment and be prepared to provide it on request. – If you have questions about the assignment, you can ask your tutor, the unit coordinator, or post a question on the LMS discussion board (preferred). Please check the discussion board before asking to make sure that your question has not already been answered. – This assignment consists of 100 marks. Marks are allocated as described in the assignment. Late submissions will be penalised at the rate of 5 marks per day late or part thereof. Assignments will not be accepted more than 14 days after the submission date as assignment return will have commenced. – The University treats plagiarism, collusion, theft of other students’ work and other forms of dishonesty in assessment seriously. This is an INDIVIDUAL assignment. Any instances of dishonesty in this assessment will be immediately forwarded for investigation. Question 1 (15 marks) – 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 (20 marks) – SQL (DML) Provide SQL queries and the result tables for the following (30 marks): 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 (15 marks) – 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. (7 marks) b. You have been given the following specifications of a simple database for a netball association that keeps record of players, teams and matches. Player PK PlayerID FirstName LastName DateOfBirth FK1 TeamID Team PK TeamID TeamName Grade Match PK MatchID Date Result FK1 HomeTeamID(FK) AwayTeamID(FK) 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). (5 marks) 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. (3 marks) Question 4 (20 Marks) – 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. PATIENT ID PATIENT NAME PATIENT DOB CONSULT DATE ITEM NUMBER ITEM DESCRIPTION FEE PROVIDER NUMBER DOCTOR NAME 437 REARDON 4/08/1989 19/7/2012 A012 Standard 56 S55768 Scott 437 REARDON 4/08/1989 20/7/2012 A012 Standard 56 D42433 Mueller 562 BILSTEIN 12/05/2001 20/7/2012 A013 Short 35 S55768 Scott 361 WILSON 13/08/1997 20/7/2012 A014 Long 75 S55768 Scott 895 LEMOS 13/10/1952 20/7/2012 A013 Short 35 D42433 Mueller 895 LEMOS 13/10/1952 20/7/2012 A014 Long 75 D42433 Mueller 678 MORT 27/02/1969 20/7/2012 A014 Long 75 S55768 Scott 432 SOAPIER 1/08/1976 21/7/2012 A012 Standard 56 S55768 Scott 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 (30 marks) – 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 nonresidential. 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. All customers are billed monthly for each service address. Meter readers, who are employed by DDWC, are paid per meter they read. They are each allocated up to 1000 meters to read each month. Once the reader has read the meter, the reading is recorded and the bill for that service address is calculated based on the consumption since the last reading. For example, if the last meter reading was 123580 kilolitres (kl) and the next reading was 123683kl, the consumption for that period would be 103kl. Residential customers’ bills are calculated as follows: for each residential service address, the customer is billed a flat rate of $25 supply charge. They are then billed a further 24c for each kl consumed since the last reading. There are no GST or other taxes imposed. Non-residential customers’ bills are calculated as follows: there is no supply charge. They are charged only for the consumption since the last reading. They are charged 24c for the first 100 kl, 30c for the second 100 kl and 50c for each kl in excess of 200kl. All customers are given 30 days from the billing date to pay their account. They can pay by cash, paypal, or EFT. Customers who do not pay within the 30 days will have an accounting charge of $5 added to the bill. After 45 days a further $10 accounting charge is added. At 60 days, the customer’s water supply at the affected service address is cut off. Once a customer has had their supply cut off, the person or business responsible for the bill will not be able to create a new account at a different service address until their arrears have been cleared. Each customer has the right to request a report that details the historical consumption of water at any of their service addresses, whether they were the customer at the time or not. What you have to do: 1. Create an entity-relationship diagram (ERD) showing the data requirements of the system. Your ERD should be able to be implemented in a relational DBMS. You should use the ERD notation we have been using in the lectures, and should include a legend to explain the notation. You should include attributes in the ERD. The use of a drawing tool such as Visio will make this task easier. However, whichever tool you use, you must copy and paste the ERD into a word-processed document. This is because your tutor might not have access to the tool(s) you have used. Please note that hand-drawn ERDs are not acceptable. 2. List and explain any assumptions you have made in creating the data model. Part of understanding a system at sufficient enough detail to model well involves asking questions. If you are not sure about some detail of the case study, you should ask on the Discussion Forum in LMS.