The Dean of the business school wishes to create an UTD family consisting of its graduates and corporations that have been staunch supporters of the School of Management. 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 college. He wants the new database to capture all relevant information on alumni of the college and the corporate supporters of the college. To build a long-term relationship with alumni, he wishes to keep track of all alumni from each functional area (e.g., MIS, Acctg, Fin, Mkt, etc.) of the college, where they work (or have worked in the past), and any donations that they have made to the college. 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., 1999 MIS graduates). The report should list, for the specified area and year of graduation, each alumnuss name, e-mail address, the degree earned (e.g., BS, MS, MBA, Ph.D., 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., Houston), 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., 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).

Additional Information:

1. Donations can be made to one of several funds; example funds are the Colleges General Fund, the TI Scholarship Fund, etc.

2. A companys ID number is a 7 digit number uniquely identifying a particular company. A companys industry is a 3 digit code representing whether a companys PRIMARY business is banking, construction, etc.

Students are required to study the case given, analyze the requirement and create ER model based on the requirements. PLEASE INCLUDE ANY ASSUMPTIONS THAT YOU MAKE. The content of this assignment should be including:

a) User Requirement Based from the case study given, identify and list in detail the user requirement. Students are required to add TWO additional requirement from the existing requirement. However, the additional requirement should not violate the existing requirement.

b) State assumptions Assumptions are to clarify unclear business rules or procedures. Assumptions are to reflect common-sense and students ability to make the right judgment when information is missing. Any unreasonable assumption that aims to make the design simpler and compromise or ignore business rules gains no marks.

c) Entity Relationship Modeling (ERD) You are required to design an ER diagram for the case study given and the additional requirement specified in the section B. Identify entities, identify relationships, identify associate attribute and determine keys.