ICT285 Databases TM 2016 Assignment 2 Worth: 15% of your final grade. Due: Sunday, 31 July 2016, 11:55 PM Submit to: LMS, via the Assignments tool. Submit Parts 1 and 2, Part 3 sample data and Part 4 CREATE VIEW statements as a SINGLE Word document. Parts 3 and 4 should be completed in Oracle on arion. Ensure you complete the declaration that is part of the submission process. You do not need to include a separate cover sheet but you should include your name and student number as part of your document filename. Your name and student number should also be included within in the assignment document. Late assignments that do not have an extension will be penalised at the rate of 5% per day. This is an INDIVIDUAL assignment. This assignment requires you to implement the database you designed to address the requirements of the Mudrock Alumni case study in Assignment 1. You will need to incorporate any changes you may need to make to your design as a result of the feedback on Assignment 1 both individually and as a class (e.g. on the online forum), and to address the addition/amended requirements listed in The assignment addresses the following learning outcomes for the unit: 1. Demonstrate an understanding of database principles and theory, particularly those relating to the Relational Model 2. Construct database queries using the Relational Algebra and SQL 3. Design a database, demonstrating practical skills in normalisation and data modelling 4. Create a database using a DBMS from a given design and implement specified constraints using appropriate tools and approaches Marks are distributed as follows: Part 1: Revised ERD 10 Part 2: Data dictionary 20 Part 3: Implementation 30 Part 4: Views 40 Total 100 Re-read the description of the Mudrock Alumni Relations case in Assignment 1 if you need to refresh your The Director is pleased with your work so far and asked you to go on to implement your design. She has clarified some specifications:  Some events are for alumni only, while some are for both alumni and business sponsors.  They want to record all the jobs an alumnus has had since graduating, not just the most recent. Part 1: Revised ERD (10 marks) a) Create and submit the ERD for this database that you are going to use as the basis of your implementation. b) Include a one or two paragraph explanation as to the changes you have made to the ERD on the basis of your feedback from Assignment 1 and/or as a result of having to support the transactions and views described in this assignment. Part 2: Data dictionary (20 marks) Create a data dictionary for your database. This should include: a) For each table: a definition of each column (attribute), consisting of the column name, brief description of what it represents, its data type, domain, whether it is required, any default value, and any primary key or foreign key constraints. b) For each of the columns that is a foreign key, give the appropriate referential integrity rules (i.e. the "on delete.... ; on update" etc actions that should apply when the corresponding primary key is altered), even if they can't be implemented in Oracle. c) Any enterprise constraints that should apply to the database as a whole. Note that your data dictionary must be consistent with your ERD. Part 3: Implementation (30 marks) Implement the database in Oracle SQLPlus on arion.murdoch.edu.au. Note the following: a) All tables should be created as per your ERD and data dictionary; the marker will check your ERD against your tables. You do NOT need to include the SQL CREATE TABLE statements that you used to create the tables. b) All entity and referential integrity constraints should be created and appropriately named. c) All columns (attributes) should be of an appropriate data type/size and be set as required or not as d) All tables should be populated with sample data that will allow the marker to test that your database fulfils the application requirements as specified and support the transactions and views listed below. Also provide SCREENSHOTS of the tables showing the sample data in your Word e) SELECT, UPDATE and DELETE permissions should be GRANTED on all database objects (particularly tables and views) to the user MARKERTL. This is most important. If you do not grant this permission, the marker will not be able to mark this part of your assignment. Your implementation should be consistent with your data dictionary. Part 4: Views (40 marks) Create VIEWS for the following in Oracle (views should be named as ViewA, ViewB etc). You should also provide the CREATE VIEW statements you used to create the views in your Word document. View A: List the Business Information Systems alumni from 2015 including their name, e-mail address, work phone number, and home phone number. View B: List the alumni who live in Perth along with the alumns' contact details. View C: List all the business supporters who provide non-donation support, and the support they provide. View D: List all of the business supports who have donated a total amount greater than $25,000. The View E: List all donations made, by both individual alumni and business donors. The Name and ID of the View F: List all the alumni working for a particular company (e.g., Telstra). This report must also display the date an individual joined the company, as well as, their job title and salary. View G: List the employment history for a particular alumnus. The report must show, for each employer that alumnus has worked for, the employer name, the most recent job title the alumnus had with that employer company (e.g., Vice-President), the date the alumnus joined the company, and the View H: List all of the social events held in Singapore during 2015, along with the total number of report should be sorted in descending order of the total donated amount. donor, as well as the date and amount of the donation must be displayed. date the alumnus left the company (if applicable). business and alumni attendees at each. Please note the following about the marking of this assignment:  The marker will view your documentation and then match your documentation to your implementation. This means for example, that tables, columns and constraints should be named in your database as they are in your documentation. Relationships defined in your ERD should be defined in your database using  The marker will view the sample data in your tables.  The marker will execute each of the views created for Part 4 above.  AGAIN, please ensure that you GRANT the appropriate privileges on all relevant objects (tables and views) to the user MARKERTL. If you do not do this, the marker will not be able to mark part of your assignment (and you may be awarded 0 for this section).