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 database will have to support the following querying and reporting requirements:
1. A report that displays alumni information for a specified qualification for a particular year (e.g., 2012 BSc BIS graduates). The report should list, for the specified area and year of graduation, each alumnus's name, e-mail address, the degree earned, work phone number, and home phone number. Note that a similar report could be required for any discipline and any graduating year.
2. For a specified city (e.g., Singapore), a report listing all alumni who live in that city. The report will display the name of the city, and for each alumnus in that city, their name, home address, email address, work phone, and home phone.
3. A report listing all business donors who have donated a total amount greater than $25,000. The report will be sorted in descending order of the total donated amount.
4. A report that displays all donations made. The Name and ID of the donor, as well as the date and amount of the donation must be displayed.
5. A report listing all alumni working for a particular company (e.g., NORTEL). This report must also display the date an individual joined the company, as well as, their job title and salary.
6. A report that displays 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).
What you have to do:
Use the case study description and list of requirements 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.
List and explain any assumptions you have made in creating the data model.
You should use the ERD notation we have been using in the lectures, and should include a legend to explain the notation. You should include attributes in the ERD. The use of a drawing tool such as Visio will make this task easier.
Whichever tool you use, you must copy and paste the ERD into a word-processed document. This is because your tutor might not have access to the tools you have used.
Please note that hand-drawn ERDs are not acceptable.
Some important things to note:
You don't have to create the database or any of the reports at this point. However, Assignment 2 will involve creating the database from your design, so you should be satisfied that it will work.
Part of understanding a system at sufficient enough detail to model well, involves asking questions. If you are not sure about some detail of the case study, you should ask on the Discussion Forum in LMS.
The University email server strips out any Visio (.vsd) files that are sent, even if they are included in a zip archive. So, if you want to send a draft of your design to your tutor by email, you will need to change the extension to something other than .vsd (.blah works well), or (probably simpler) paste the diagram into a word document