ALUMNI DATABASE

CASE STUDY: ALUMNI DATABASE The Dean of the A&M School of Busin" rel="nofollow">iness wishes to create a list of the School’s “family” consistin" rel="nofollow">ing of its graduates and corporations that have been staunch supporters of the School of Busin" rel="nofollow">iness. Fin" rel="nofollow">indin" rel="nofollow">ing the existin" rel="nofollow">ing database in" rel="nofollow">inadequate in" rel="nofollow">in terms of providin" rel="nofollow">ing him with the in" rel="nofollow">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 in" rel="nofollow">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., Accountin" rel="nofollow">ing, Fin" rel="nofollow">in, Mkt, Management, etc.) of the School, where they work (or have worked in" rel="nofollow">in the past), and any donations that they have made to the University. Similarly, for corporate supporters, the database must capture all relevant in" rel="nofollow">information on donations made by each corporation. Upon further in" rel="nofollow">interviews with the Dean, he has in" rel="nofollow">indicated the followin" rel="nofollow">ing specific requirements: 1. A *report that displays alumni in" rel="nofollow">information for a specified area for a particular year (e.g., 2014 Accountin" rel="nofollow">ing 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 graduatin" rel="nofollow">ing year. 2. For a specified city (e.g., Dallas), a report listin" rel="nofollow">ing all alumni who live in" rel="nofollow">in that city. The report will display the name of the city, and for each alumnus in" rel="nofollow">in that city, their name, home address, email address, work phone, and home phone. 3. A report listin" rel="nofollow">ing all corporate donors who have donated a total amount greater than $25,000. The report will be sorted in" rel="nofollow">in descendin" rel="nofollow">ing 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 listin" rel="nofollow">ing all alumni workin" rel="nofollow">ing for a particular company (e.g., US Steel). This report must also display the date an in" rel="nofollow">individual join" rel="nofollow">ined 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 join" rel="nofollow">ined 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 Busin" rel="nofollow">iness School Scholarship Fund, etc. • A company’s ID number is a 7 digit number uniquely identifyin" rel="nofollow">ing a particular company. A company’s in" rel="nofollow">industry is a 3 digit code representin" rel="nofollow">ing whether a company’s PRIMARY busin" rel="nofollow">iness is bankin" rel="nofollow">ing, 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" rel="nofollow">in the ER diagrams. Remember to identify primary key attribute(s) for each entity, and in" rel="nofollow">indicate the cardin" rel="nofollow">inalities associated with each relationship. State any assumptions that you make.