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.
Learning Objectives
In the process of this assessment task you will:
• plan, schedule and execute project tasks with a view to improving your personal productivity;
• gain awareness of the typical challenges related to the practical implementation of databases;
• learn how to used Data Definition statements to implement a database from a given ER diagram and the corresponding Database schema; and
• learn how to use Data Manipulation statements to query a database, and insert and update data in the tables.
Due date: Week 11, Friday, 5pm
Submit the individual work file, named 'a2-
Student_No _ Surname_ID .zip', by Week 11, Friday, 5pm via Moodle.
Marks: This assignment has a total 100 marks and it is worth 20% out of the total assessment.
Resources Provided The following files are available on your Moodle shell:
• An electronic copy of this assignment that includes: • Logical ER Diagram of DEO system
• Relational Data Structures of DEO system • Some insertion data to get you started -
ITECH1006_5006_Assignment2_Summer_Semester_20
15_201527_Some_Starting_Inserts.sql
• Links to relevant Federation University resources
Authorship: This assignment is an individual assignment and it shall be completed by the individual student only. The final submission must be identifiably the work of the individual.
Late submission: Any submission after the due date will receive a deduction of 10% per day, this includes weekends.
CRICOS Provider No. 00103D Assignment 2 Specification - 201527 Page 1 of 12
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). You must ensure that you have sufficient data in your tables to ensure that each of the queries returns at least one record. It may be of assistance to refer again to the description of the system in the assignment 1 document. Implementation of the Database and Manipulation of the Data
You are required to perform the followings tasks:
1. Create a text file named Create_ StudentID .sql (for example, Create_3087654.sql) that will contain SQL statements to:
I. Create a database named DEODB_ StudentID ;
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_ StudentID .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_ StudentId .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_ StudentId .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.
Submission Requirements
An electronic copy of your assignment should be submitted through Moodle and should include a copy of your report, completed according to the Federation University Australia Guide for the Presentation of Academic Work and the four files described in Task 1 – Task 4 above. Zip all the files into a single file named 'a2- Surname_ID .zip' before uploading.
Your submission should be in electronic form (zip file named ‘a2- Surname_ID .zip’) through Moodle and should include:
1. A report that adheres to the academic writing standards of the University – see General Guide to Writing and Study Skills and General Guide to Referencing – and includes:
a. A copy of the SEIT Assignment Coversheet that includes a copy of the plagiarism statement;
b. An appropriate title page that includes an acknowledgement of all students you have spoken to about the assignment;
c. A table of contents and automatically generated page numbers;
d. A report of the results from running the SQL queries (Task 3) by using copy/paste of their output.
e. A bibliography containing a list of all resources used to complete the assignment. If no resources, apart from the course materials, have been used please indicate this.
2. Script files for creating, inserting and querying the DEO database:
a. Create_ StudentID .sql;
b. Insert_ StudentID .sql;
c. Query_ StudentId .sql; and
d. Transaction_ StudentId .sql
Assessment Criteria and Marking Overview
Some of the considerations for assessment are:
• Adherence to University standards;
• How clear and well organised your presentation is;
• Commenting in and formatting of your SQL scripts - you should follow the examples in the text for the format of your SQL statements;
• Data correctness and quality. Please use appropriate data for your examples (e.g. do not use inappropriate person names)
Please refer to the following marking guide to see the distribution of marks.
Tasks Marks
1. Academic and Professional Presentation
How clear, compliant and well-presented your submission is. 10
2. Creation of database and tables (Task 1)
• Database correctly named and created;
• Correct creation of all relevant tables including all of the PKs and FKs in the database;
• No evidence that tables have been created using the GUI; • Database tables function correctly. 25
3. Insertion of data (Task 2)
• Successfully inserts data into the tables; • No evidence of exporting from GUI; • Insertions all work correctly. 25
4. Query (Task 3)
• Use of appropriate query statements; • They work as intended. 30
5. Transaction (Task 4)
• Use of appropriate data manipulation statements to perform the required actions;
• They work as intended. 10
Total 100
Logical ER Diagram
CRICOS Provider No. 00103D Assignment 2 Specification - 201527 Page 7 of 12
Relational Data Structures
Entity Field Name Datatype Length Note
CUST_TYPE Cust_Type CHAR 1 Primary Key
Cust_Type_Desc VARCHAR 50
GOVT_LEVEL Govt_Level CHAR 1 Primary Key
Govt_Level_Desc VARCHAR 50
BUS_SECTOR Sector_ID CHAR 2 Primary Key (as per ANZSIC Division codes)
Sector_Level_Desc VARCHAR 50
LEGAL_STRUCT Legal_Struct_ID CHAR 1 Primary Key
Legal_Struct_Desc VARCHAR 50
SCHOOL_LVL School_Lvl_ID CHAR 1 Primary Key
School_Lvl_Desc VARCHAR 50
SCHOOL_TYPE School_Type CHAR 1 Primary Key
School_Type_Desc VARCHAR 50
ADDRESS_TYPE Address_Type CHAR 1 Primary Key
Address_Type_Desc VARCHAR 50
EVENT_TYPE Event_Type CHAR 1 Primary Key
Event_Type_Desc VARCHAR 50
CERT_TYPE Cert_Type INT(AUTO) Primary Key
Cert_Type_Desc VARCHAR 50
STAFF_TYPE Staff_Type CHAR 1 Primary Key
Staff_Type_Desc VARCHAR 50
PRODUCT_TYPE Product_Type CHAR 1 Primary Key
Product_Type_Desc VARCHAR 50
ADDRESS Address_ID INT Primary Key
Address_Type CHAR 1 Primary Key, Foreign Key references
ADDRESS_TYPE ( Address_Type)
Street_Dtls VARCHAR 50
City VARCHAR 30
Page
State CHAR 3
Postcode CHAR 4
CUSTOMER Cust_ID INT (AUTO) Primary Key
Cust_Name VARCHAR 50
Cust_Email VARCHAR 50
Cust_Phone VARCHAR 15
Cust_Contact_Name VARCHAR 50
Cust_Contact_Phone VARCHAR 15
Cust_Contact_Email VARCHAR 50
Cust_Type CHAR 1 Foreign Key references
CUST_TYPE ( Cust_Type)
Address_ID INT Foreign Key references
ADDRESS ( Address_ID)
GOVERNMENT Cust_ID INT Primary Key,
Foreign Key References
CUSTOMER (Cust_ID)
Govt_Level CHAR 1 Foreign Key References GOVT_LEVEL
(Govt_Level)
BUSINESS Cust_ID INT Primary Key,
Foreign Key References
CUSTOMER (Cust_ID)
Bus_URL VARCHAR 50
Sector_ID CHAR 2 Foreign Key References BUS_SECTOR
(Sector_ID)
Legal_Struct_ID CHAR 1 Foreign Key References
LEGAL_STRUCT ( Legal_Struct_ID)
SCHOOL Cust_ID INT Primary Key,
Foreign Key References
CUSTOMER (Cust_ID)
School_Lvl_ID CHAR 1 Foreign Key References SCHOOL_LVL
(School_Lvl_ID)
School_Type CHAR 1 Foreign Key References SCHOOL_TYPE
(School_Type)
SUPPLIER Supplier_ID INT (AUTO) Primary Key
Supplier_Bus_Name VARCHAR 50
Supplier_URL VARCHAR 50
Supplier_Email VARCHAR 50
Page
Supplier_Phone VARCHAR 15
Supplier
_Contact_Name VARCHAR 50
Supplier
_Contact_Phone VARCHAR 15
Supplier
_Contact_Email VARCHAR 50
Address_ID INT Foreign Key references
ADDRESS ( Address_ID)
STAFF Staff_ID INT (AUTO) Primary Key
Staff_Name VARCHAR 50
Staff_Phone VARCHAR 15
Staff_Email VARCHAR 50
Staff_TFN CHAR 9 Unique
Staff_Hourly_Rate DECIMAL 5,2
Staff_Type CHAR 1 Foreign Key references
STAFF_TYPE (Staff_Type)
Staff_Supervisor INT Foreign Key references
STAFF (Staff_ID) – when a staff member has no supervisor you need to insert NULL/not insert anything and allow default of NULL to allow this relationship to function
EVENT Event_ID INT (AUTO) Primary Key, part of Foreign Key references EVENT_STAFF(Event_ID,
Staff_ID)
Cust_ID INT Foreign Key references
CUSTOMER( Cust_ID)
Location VARCHAR 50 Name of location
Event_Type CHAR 1 Foreign Key references
EVENT_TYPE (Event_Type)
Attendees INT Number of people attending
Event_Date_Time DATETIME
Event_Duration_Exptd DECIMAL 4,2 hours
Event_Duration_Act DECIMAL 4,2 hours
Event_Manager INT Part of Foreign Key that references EVENT_STAFF(Event_ID,Staff_ID) – you will need to establish this relationship after you have created the EVENT_STAFF table
Address_ID INT Foreign Key references
ADDRESS ( Address_ID)
PRODUCT Product_ID INT (AUTO) Primary key
Product_Type CHAR 1 Foreign Key references
PRODUCT_TYPE(Product_Type)
Product_Desc VARCHAR 50
SUPP_PROD Supplier_ID INT Primary key, Foreign Key references
SUPPLIER(Supplier_ID)
Product_ID INT Primary key, Foreign Key references
PRODUCT(Product_ID)
SP_Price DECIMAL 7,2
SP_Cost DECIMAL 7,2
EVENT_STAFF Event_ID INT Primary key, Foreign Key references
EVENT(Event_ID)
Staff_ID INT Primary key, Foreign Key references
Staff(Staff_ID)
ES_Actual_Hours DECIMAL 4,2
STAFF_CERT * Staff_ID INT Primary key, Foreign Key references
STAFF(Staff_ID)
Cert_Type INT Primary key, Foreign Key references
CERT_TYPE(Cert_Type)
Cert_Awarded_By VARCHAR 50
Cert_Grant_Date DATE
Cert_Expire_Date DATE
* Even though a uniqu e code was stipulated this would have introduced redundant data and the concatenated key is sufficient to den ote uniqueness.
EVENT_ORDER Event_ID INT Primary key, Foreign Key references
EVENT(Event_ID)
Order_ID INT(AUTO) Unique i.e. add this constraint
Order_Date DATE Date when order was created
EVENT_ORDER_ITEM Order_ID INT Primary key, Foreign Key references
EVENT_ORDER(Order_ID)
Supplier_ID INT Primary key, part of Foreign Key that references
SUPP_PROD(Supplier_ID,Product_ID)
Product_ID INT Primary key, part of Foreign Key that references
SUPP_PROD(Supplier_ID,Product_ID)
EOI_Price DECIMAL 7,2
EOI_Quantity INT No part quantities