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 dtooheys 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. …
Looking for solution of this Assignment?
WHY CHOOSE US?
We deliver quality original papers |
Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected |
Free revisions |
We offer our clients multiple free revisions just to ensure you get what you want. |
Discounted prices |
All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount |
100% originality |
We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI |
On-time delivery |
We will deliver your paper on time even on short notice or short deadline, overnight essay or even an urgent essay |