Aims:

• To analyse and comprehend a given ER diagram and Relational Data Structures;

• To implement a database based on the given ER diagram and Relational Data Structures;

• To write required SQL statements to query the database; and

• To write SQL statements to manipulate the data in the database.

Creating and Using a Database for Discerning Event Organisers (DEO)

Assignment Specification

Discerning Event Organisers (DEO) now require an implementation of the design made in Assignment 1 so that they can see how the system would operate. You have had discussions with the client about the perceived inadequacies of the referencing information and have suggested that these keys should be changed to allow for a more expansive list of options in the future. The client however is happy with the number and type of codes they can use but when you suggest then that some of this reference information might be more conveniently physically implemented using constraints on columns they insist on using the logical design presented to them. You therefore have settled on the logical ER diagram and corresponding relational data structures from assignment1 with some minor changes. This documentation is included at the end of this document.

You are now required to demonstrate a working database system by creating, inserting and querying the data. You should create your database according to the documentation provided. Make sure that your implementation is consistent with this design, i. e., your table names, field names, and data types are according to the specifications provided in this document. The implementation phase includes writing SQL statements to create a database and its tables, populating the tables with appropriate test data and writing a number of queries to create reports that can be used by the management team. You have been provided with a script - ITECH1006_5006_Assignment2_Summer_Semester_2015_201527_Some_Starting_Inserts.sql - with some test data inserts - SUPPLIER and corresponding ADDRESS and ADDRESS_TYPE records as well as some SUPP_PROD, PRODUCT, PROD_TYPE and EVENT_ORDER_ITEM records. You need to incorporate this information into your database and complete the insertion of other data (there should be at least five records in each of the tables where possible).

Implementation of the Database and Manipulation of the Data

You are required to perform the followings tasks:

1. Create a text file named Create_.sql (for example, Create_3087654.sql) that will contain SQL statements to:

I. Create a database named DEODB_;

II. Create all of the tables for the database according to the Relational Data Structures given at the end of this document.

2. Create a text file named Insert_.sql that will contain SQL statements to:

I. Insert at least five records in each of the tables. The test data inserted into the table must ensure that each of the queries, specified in Task 4, outputs at least one record.

3. Create a text file named Query_.sql that will contain all the queries to satisfy the following:

I. Display a list of all food type products sorted in descending order according to their product description. Display the product description, product type and product code.

II. Find the addresses whose street details have more than one word and the last word is six characters long and ends with a ‘ue'. Display the street details, city and address type in ascending order according to the street details.

III. Display a list of all those customers with a delivery address. List the customer id, customer name, customer email, customer phone, customer contact name, the delivery address location and the description of the type of address. Make sure you output the delivery address as one column or field only and sort in descending order according to the customer name.

IV. List the details of the earliest event DEO recorded/serviced. Show the event_id, event location, customer name, attendees, event date and time of that event.

V. Show a list of all products and their suppliers where the product price is greater than $500.00 or the cost is less than or equal to $10.00. Display the product id, product description, product type, product type description, price, cost, supplier id and business name.

VI. List the staff members who have earned more than $200 so far working for DEO. List the staff member's name, phone number, TFN, hourly rate, type and the total money earned. Show the list sorted by the total money earned with highest earning employee first.

VII. List the details - event_id, cust_id, location, event type, attendees, event date and time and event duration - and the total price charged for all products supplied per event for all events.

VIII. Find the staff member(s) - listing the staff member's name, phone number, TFN, hourly rate, type and type description - whose pay rate is greater than the average pay rate of all staff.

IX. List the government level - government level and government description - for which there are no customer records as well as the school level - school level and description - for which there are no customer records. Make sure you only have one listing as output.

X. Using a correlated sub-query and the NOT EXISTS special operator, either list the number of or list all the details of the supplier product records from the supplier product table that have not been used in any event order.

4. Create a text file named Transaction_.sql that will perform the following tasks. For each item, I and II, all changes must be a single unit of work. Insert additional data in the tables appropriately if needed:

I. A new customer decides to ask DEO to provide catering services for their jubilee birthday. You first need to add their customer record with the following details:

i. The customer is St. Stephen's College, 230 Brinkman Way, Fortuna, VIC 3678 a catholic secondary college;

ii. Their postal address is PO Box 878 Fortuna Mail Centre, Fortuna, VIC 3679;

iii. The general contacts for the school are either email - sttephen@catholic.vicc.edu.au or school phone number - 03 4676 8973;

iv. The contact for the school is the headmaster, Mr. Stephen Doublee, whose email is sdouble@catholic.vicc.edu.au and mobile phone 0448768876

II. You now need to enter details about the event:

i. It will be held in the college gymnasium with 200 expected guests;

ii. At this stage the date and time of the event are the 30th March 2016 at 7:00pm;

iii. It is expected the event will go for four hours.

You would then probably go through a process of adding an order, event order items and adding staff members to service the event, including appointing an event manager, but you will not be asked to do that for this assignment.

You are required to adhere to the following output formatting conventions:

• All monetary values should be printed with a dollar symbol ($) or at least the heading should contain that symbol and all durations should be displayed with the suffix ‘hrs' or have that included in the heading;

• You must use consistent and legible formatting in laying out your SQL queries; and

• You should include (brief) comments for your queries.

Attachment:- Assignment.rar