Mudrock University Alumni Relations Database Assignment - Part 1-4

This assignment involves designing and implementing an Alumni Relations database for Mudrock University. The solution begins with creating an Entity-Relationship Diagram (ERD) to represent the data requirements, including alumni qualifications, work experience, donations, and events. A detailed data dictionary is then developed, defining each table, column, data type, constraints, and referential integrity rules. The database is implemented in Oracle SQLPlus, with the creation of tables, constraints, and population with sample data. Finally, the assignment requires the creation of several views to retrieve specific information, such as alumni from a particular discipline, business supporters, and donation details. The implementation includes granting necessary permissions for the marker to evaluate the solution. The solution also includes screenshots of sample data and SQL CREATE VIEW statements for each view.

Use the case study description and list of requirements below to create an entity-relationship
diagram showing the data requirements of the Alumni Relations database. Your ERD should be able
to be implemented in a relational DBMS.
Mudrock University has a large alumni grouping which consists of graduates and businesses that have
supported the university over time. The Director of Alumni Relations is frustrated with the current Alumni
database because it is not much more than a list of members. As such, she wants to develop a new database
for Alumni Relations, which will provide her with the data she needs to do her job and grow the alumni.
She wants the database to capture all relevant information on the university’s alumni, including
qualifications and work experience, and any donations they have made to the university. In particular, she
wants to record all the qualifications an alumnus has from Mudrock (not just the first. For example, if
someone graduated with a BSc Computer Science in 2014 and then went on to do a Master of IT in 2016,
both qualifications would be recorded.) As well as the qualification, she wants to record the school that the
alumnus was in (e.g. School of Arts; School of Engineering and IT, School of Veterinary and Life Sciences),
and also the general discipline area (information technology, history, biology).
The Director also wants to keep track of the alumni social and professional development events that are
organized; she wants to know when and where they were held, what the focus of the event was (for
example, it might be a social event or it might be PD), who the intended audience for the event was (could be
all alums, or it could be alums from a particular school or discipline area, or it could alums from a particular
city – or even a mixture of all of these). She also wants to know who was invited, who RSVP’d and who
attended.
Finally, a number of businesses also support Almuni Relations with donations and other forms of support
(such as internships or travel grants); as such, the Director wishes to be able to know which businesses
provided what support and when that support was provided.
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
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
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
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
appropriate.
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 document.
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
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 report
should be sorted in descending order of the total donated amount.
View E: List all donations made, by both individual alumni and business donors. The Name and ID of the donor, as
well as the date and amount of the donation must be displayed.
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 date the alumnus left the
company (if applicable).
View H: List all of the social events held in Singapore during 2015, along with the total number of 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 foreign keys.
 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).