Posted: September 13th, 2017

database

Assignment 1
Question 1 (15 marks) – Relational Algebr
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 2014.
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 2014 by the customer Peter Griffin.
f.    List the dates on which Peter Gruffin 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.

Give the SQL to create the Match table. You may assume that the Player and Team tables have already been created, and that the 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 non-residential. Each customer has at least one water meter that measures the water consumed by the customer at a particular address. Water meters must be replaced when they have been in use for 5 years.
All customers are billed monthly. 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 customer 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 residence, 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 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 address until their arrears have been cleared.
Each customer has the right to request a report that details the historical consumption of water at the address of their account, 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.

3. Marks will be allocated to each of the following functional areas:
–    Legend (5 marks)
–    Entities
o    Have you included all required entities in the design?
o    Have you included specialisation hierarchies where appropriate?
–    Relationships
o    Have you designed relationships between the entities that will support the functional requirements?
o    Are your relationships correctly annotated?
–    Will the overall design support the enterprise requirements?

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂