Description

Forum #2: ER Modeling

Scenario:

Cal Lutheran School of Management has hired you to help create conceptual data models

(ERDs) for the following requirements below.

Cal Lutheran School of Management needs a database that tracks graduate student and alumni

information. As a first step, your data model needs to track information about current students

such as name, local address, major (or program) (e.g. IT, MBA, MPPA), gender, GPA and

country of origin. Each student is assigned an advisor for the duration of the program who helps

the student navigate program requirements. Information about advisors needs to be stored

including name, campus address, office number, phone, email. During their program, students

register for courses. Because the courses are graduate-level (and require a lot of work),

students may register in a maximum of 3 courses per term. 

It is possible a student does not take

classes during a term (because s/he can take the term off). Data about courses needs to be

stored in the system such as course prefix (e.g. IT509), section number, course name, number

of credits, day it is offered and time (e.g. 7-10PM). In addition, the ERD also needs to show

instructor data such as name, title (e.g. MS, Ph.D., Ed.D.), campus address, phone and email.

Instructors are scheduled to teach various classes thorough the academic year. Any class can

be co-taught (which means that it is possible a class is co-taught by a team of generally 2

instructors). The ERD needs to track such data. In addition, the data model needs to track

alumni information. Basic information about alumni needs to be maintained such as name,

address, CLU email, personal email, phone number, major (or program) (e.g. IT, MBA, MPPA),

gender, GPA and graduation date. Cal Lutheran organizes alumni events around the world to

better serve their alumni. Data about events such as country of location, specific address in that

country, date of the event, brief description. In addition, each event is managed by a dedicated

staff. Data about staff needs to be recorded such as position, name and contact info (address,

phone and email). Staff members can manage various different events over time. Yet, each

event has one staff member in charge who is directly responsible for the event. The ERD needs

to track this. Alumni attend such alumni events. It is possible that a student attends various such

events while others attend none. Upon attendance, alumni leave comments related to the

particular event. Such comments also need to be stored. Please note that the comments vary

with the student and the event attended.

Due date: 3/23 by 7 PM (firm deadline).

Specific requirements:

 Please submit 2 ER diagrams:

1. First ERD should include all original M: N relationships.

2. Second ERD should resolve all M: N relationships into associative entities.

 For both ERDs, you need to resolve any composite and multivalued attributes.

 You need to list ALL assumptions that you have made (that augment but do not violate

given requirements).

 For both ERDs, you need to use M.S. Visio (and the stencil). Please make sure

both ERDs are professionally prepared (this includes alignment on the page, lines not

crossing, etc). Hand-drawn diagrams are not acceptable.