CASE STUDY: ALUMNI DATABASE

CASE STUDY: ALUMNI DATABASE

The Dean of the A&M School of Business wishes to create a list of the School’s “family” consisting of its graduates and corporations that have been staunch supporters of the School of Business. Finding the existing database inadequate in terms of providing him with the information he needs to achieve his goal, he wishes to develop a new alumni database for the School. He wants the new database to capture all relevant information on alumni of the School and the corporate supporters as well. To build a long-term relationship with alumni, he wishes to keep track of all alumni from each functional area (e.g., Accounting, Fin, Mkt, Management, etc.) of the School, where they work (or have worked in the past), and any donations that they have made to the University. Similarly, for corporate supporters, the database must capture all relevant information on donations made by each corporation.

Upon further interviews with the Dean, he has indicated the following specific requirements:

1. A *report that displays alumni information for a specified area for a particular year (e.g., 2014 Accounting graduates). The report should list, for the specified area and year of graduation, each alumnus’s name, e-mail address, the degree earned (e.g., BS, MS, MBA, etc.), work phone number, and home phone number. Note that a similar report could be required for any area and any graduating year.

2. For a specified city (e.g., Dallas), 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 corporate 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 to a particular G/L (General Ledger) account. The 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., US Steel). 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).

Additional Information:
• Donations can be made to one of several funds; example funds are the University’s General Fund, the Business School Scholarship Fund, etc.
• A company’s ID number is a 7 digit number uniquely identifying a particular company. A company’s industry is a 3 digit code representing whether a company’s PRIMARY business is banking, construction, etc.

Answer the question below based on the Alumni Database case study:
For two (2) out of the six (6) requirements (report) above, draw an Entity-Relationship diagram to model the data requirements for that report. Clearly identify the entities, the relationships across entities, and all the relevant attributes in the ER diagrams. Remember to identify primary key attribute(s) for each entity, and indicate the cardinalities associated with each relationship. State any assumptions that you make.